MySQL数据库管理与应用 第4章 索引与视图

4.4 创建视图
4.4.1 查看创建视图权限 MySQL用户要在数据库中创建视图，则必须拥有CREATE VIEW权限和查询涉及的列的SELECT权限。 这些权限信息包含在系统数据库mysql的user表中，可以使用下列SELECT语句进行查询。 SELECT Select_priv, Create_view_priv FROM er WHERE user='用户名'; 其中Select_priv列表示用户是否具有SELECT权限，如果该列的值为Y，则表示拥有SELECT权限，如 果该列的值为N，则表示没有此权限。 Create_view_priv列表示用户是否拥有CREATE VIEW权限，如果该列的值为Y，则表示拥有CREATE VIEW权限，如果该列的值为N，则表示没有此权限。 er表示MySQL系统数据库mysql中的user表。 用户名的值必须用引号括起来。
4.3 视图概述
4.3.1 视图的概念
使用SELECT语句从数据库中查询数据时，这些语句并不存储在数据库中，为了重复使用这些语句 通常是把它们存储在外部脚本文件中。当需要执行数据查询时，可以使用命令行客户端工具mysql 或某个GUI客户端工具（如MySQL Workbench或Navicat for MySQL）提供的SQL编辑器来执行脚 本文件。实际上，为了便于执行比较复杂的数据查询，一个更好的方案是将那些常用的SELECT语 句以视图对象的形式存储在数据库中。
自定义数据。视图允许用户以不同方式查看数据，即使在他们同时使用相同的数据时也是如此。 这在具有许多不同目的和技术水平的用户共用同一数据库时尤其有用。例如，可以创建一个视 图以仅检索由客户经理处理的客户数据，该视图可以根据使用它的客户经理的登录ID来决定检 索哪些数据。
提高数据库的安全性。通常的作法是让用户通过视图来访问表中的特定列和行，而不对他们授 予直接访问基表的权限。此外，可以针对不同的用户定义不同的视图，在用户视图上不包括那 些机密数据列，从而提供对机密数据的保护。
4.2 创建索引
4.2.5 删除索引 使用DROP INDEX语句从表中删除索引： DROP INDEX 索引名 ON 表名 [算法选项 | 锁定选项] ... 算法选项: ALGORITHM [=] {DEFAULT | INPLACE | COPY} 锁定选项: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} 其中索引名指定要删除的索引，表名指定该索引所在的表。这个语句映射为一个ALTER TABLE， 用于删除指定的索引。
全文索引（FULLTEXT）：在定义全文索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索 引只能在TEXT、CHAR或VARCHAR类型的列上创建，并且只能在MyISAM表中创建。全文索引可以在创建表或修改表 时创建。
空间索引（SPATIAL）：这是在MyISAM表的空间数据类型的字段上建立的索引。MySQL支持4种空间数据类型。创建 空间索引时要使用SPATIAL关键字进行扩展。
占用的空间要少。 可以在多个列上创建索引，索引列最多可以由15个列组成。 只有当表使用InnoDB、BDB或MyISAM存储引擎时，才能向具有NULL、TEXT或BLOB的列添加索引。
4.1 索引概述
4.1.3 索引的分类
在MySQL中，大多数索引都是以B-树方式存储的，这种方式构建为多个节点的一棵树。顶部的节点为索引的起始点，称 为根。每个节点中包含索引列的几个值，节点中的每个值指向另一个节点，或者指向表中的一行，一个节点中的值必须是 有序排列的。指向一行的节点称为叶子页。叶子页本身也是相互连接的，一个叶子页包含一个指针，该指针指向下一组。 索引中的节点是存储在文件中的。在MySQL中，一个表中的索引都保存在同一个索引文件中。如果在表添加或删除一行， 或者更新表中的值，MySQL会自动地更新索引，因此索引始终与表的内容保持一致。
| {DISABLE | ENABLE} KEYS
4.2 创建索引
4.2.3 使用CREATE INDEX创建索引 使用CREATE INDEX语句可以在现有的表中创建索引： CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 [索引类型] ON 表名 (索引列, ...) 索引类型：USING {BTREE | HASH} 索引列：列名 [(长度)] [ASC | DESC] 关键字UNIQUE用于创建唯一性索引，FULLTEXT用于创建全文索引，SPATIAL用于创建空间索引。使用 CREATE INDEX语句不能创建主键。 索引名表示索引的名称，在一个表中索引名必须是唯一的。 索引类型为BTREE或HASH，只有部分存储引擎可以在创建索引时指定索引的类型。 索引列可以是一个或多个，使用格式为 (索引列1, 索引列2, ...）的列清单可以创建出一个多列索引。 对于CHAR和VARCHAR列，只用一列的一部分就可以创建索引。创建索引时，使用列名 (长度) 格式可 以对前缀编制索引。前缀包括每列值前面的部分字符。BLOB和TEXT列也可以编制索引，但是必须给出 前缀长度。
当表中包含大量记录时，从表中查询数据通常有以下两种方式。
全表搜索，即从表取出所有的记录，并与查询条件逐一进行对比，然后返回满足条件的记录，这样 做会消耗大量系统时间，并造成大量磁盘I/O操作。
事先在表中基于一列或多表创建索引，然后在索引中找到符合查询条件的索引值，最后通过保存在 索引中的行标识（相当于页码）快速找到表中对应的记录。
[索引名] [索引类型] (索引列, ...) | {FULLTEXT | SPATIAL} [INDEX | KEY] [索引名] (索引列, ...) | [CONSTRAINT [字符串]] FOREIGN KEY
[索引名] (列名, ...) 引用定义 索引类型：USING {BTREE | HASH} 索引列：列名 [(长度)] [ASC | DESC]
索引可能不会产生优化效果。 在条件表达式中经常用到的不同值较多的列上创建索引，在不同值很少的列上不要创建索引。 当唯一性是某种数据本身的特征时应创建唯一索引，使用唯一索引可以保证定义的列的数据完
整性，从而提高查询速度。 在频繁进行排序或分组的列上创建索引，如果要排序的列有多个，可考虑在这些列上创建组合
|ADD [CONSTRAINT [字符串]] PRIMARY KEY [索引类型] (索引列, ...) [索引选项] ...
| ADD [CONSTRAINT [字符串]] UNIQUE [INDEX | KEY] [索引名] [索引类型] (索引列, ...) [索引选项] ...
| ADD FULLTEXT [INDEX | KEY] [索引名] (索引列, ...) [索引选项] ...
以B-树方式存储的索引有以下几种主要类型。
普通索引（INDEX）：这是最基本的数据类型，它没有唯一性之类的限制，创建普通索引时使用关键字INDEX。
唯一索引（UNIQUE）：这种索引与普通索引基本相同，所不同的是，索引列的值不能重复，即必须是唯一的。创建 唯一索引时使用关键字UNIQUE。
主键（PRIMARY KEY）建表时设置，也可以通过 修改表来设置主键。创建主键时使用关键字PRIMARY KEY。
使用MySQL数据库时也不是总要通过索引来优化查询。使用索引固然可以提高检索数据的速度，但创 建和维护索引需要耗费一定的时间，而且存储索引也需要占用一定的磁盘空间。索引可以提高查询数 据的速度，但也会影响插入数据的操作，这是因为向有索引的表中插入记录时，这些记录会按索引进 行排序。
4.1 索引概述
所谓视图，就是从一个或多个表（或视图）中导出的虚拟表。视图与SELECT语句的关系十分密切， 视图中的数据就是通过执行SELECT语句获取的。与真实的表一样，视图也包含一系列带有名称的 列和行数据，但这些列和行数据来自SELECT语句所引用的表，并且是在引用视图时动态生成的， 而不是以数据集形式存在于数据库中。数据库中的数据存储在表中，创建视图时也只存储的视图 的定义。
4.1.2 索引的作用 在数据库中创建索引主要有以下作用。 快速读取数据。 保证数据记录的唯一性。 实现表与表之间的参照完整性。 在SELECT语句中使用GROUP BY、ORDER BY子句进行数据查询时，通过索引可以缩短分组和排序的
时间。 在MySQL中，对所有数据类型的列都可以创建索引，但使用时需要注意以下几点。 一个表中最多可以有16个索引，最大索引长度为256字节。 对于CHAR和VARCHAR类型的列可以索引列的前缀。这样，索引的速度更快，并且比索引整个列所
4.2 创建索引
4.2.4 查看索引信息 使用SHOW INDEX返回表中的索引信息： SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} 表名 [{FROM | IN} 数据库名] [WHERE 条件] 其中表名指定索引所在的表，数据库名指定索引所在的数据库；“表名 FROM 数据库名”语法的替 代方法是“数据库名.表名”。
| ADD SPATIAL [INDEX | KEY] [索引名] (索引列, ...) [索引选项] ...
/*添加普通索引*/ /*添加主键*/ /*添加唯一性索引*/ /*添加全文索引*/ /*添加空间索引*/
| ADD [CONSTRAINT [字符串]]
/*添加外键*/
FOREIGN KEY [索引名] (列名, ...) 引用定义
4.1 索引概述
4.1.1 索引的概念
在关系型数据库中，索引是一种对表中一列或多列的值进行排序的一种存储结构，它是某个表中一列 或多列的值的集合与指向表中标识这些值的数据页的逻辑指针列表。索引的作用相当于图书的目录， 可以根据目录中的页码快速找到所需要的内容。
索引提供指向存储在表的指定列中的数据值的指针，并且根据指定的排序顺序对这些指针进行排序。 查询数据库时可以通过索引找到特定值，然后跟随指针找到包含该值的行。这样可以使对应于表的SQL 语句执行得更快，可以快速访问数据库表中的特定信息。
4.1 索引概述
4.1.4 索引的设计原则 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响数据增删改操
作的性能，因为当表中数据更改时索引也会进行调整和更新。 避免对经常更新的表设置过多的索引，并且索引中的列尽可能少。对于经常查询的字段应创建
索引，但要避免添加不必要的字段。 数据量小的表最好不要索引，由于数据量小，查询花费的时间可能比遍历索引的时间还要短，
简化数据操作。使用选择查询检索数据时，如果查询中的数据分散在两个或多个表中，或者所 用搜索条件比较复杂，往往要多次使用JOIN运算符来编写很长的SELECT语句。如果需要多次 执行相同的数据检索任务，则可以考虑在这些常用查询的基础上创建视图，然后在SELECT语句 的FROM子句中引用这些视图，而不必每次都输入相同的查询语句。
索引。
4.2 创建索引
4.2.1 创建表时创建索引 使用CREATE TABLE语句创建表时可以直接包含索引的定义： CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 [(列定义, ... | 索引定义)] [表选项] 索引定义： [CONSTRAINT [字符串]] PRIMARY KEY [索引类型] (索引列, ...) | {INDEX | KEY} [索引名] [索引类型] (索引列, ...) | [CONSTRAINT [字符串]] UNIQUE [INDEX|KEY]
视图定义中所引用的表也称为基表。当在数据库中创建视图以后，视图也可以像表一样用在 SELECT语句的FROM子句中作为查询的数据源来使用，或者用在数据的更新和删除操作中。当通 过视图进行数据操作时，MySQL将根据视图的定义去操作与视图相关联的基表。
4.3 视图概述
4.3.2 视图的作用
视图可以用来集中、简化和自定义每个用户对数据库的不同访问，也可以用作安全机制。视图通 常用在以下3种场合。
/*主键*/ /*普通索引*/ /*唯一性索引*/
/*全文索引*/ /*外键*/
4.2 创建索引
4.2.2 使用ALTER TABLE创建索引
对于已经存在的表，可以使用ALTER TABLE语句在表中指定的列上创建索引：
ALTER TABLE 表名
...
|ADD {INDEX|KEY} [索引名] [索引类型] (索引列, ...) [索引选项] ...