数据库原理：视图和索引

视图和索引均可用于优化数据库系统。视图在已构建的基本表上定义满足不同业务需要的外模式，以提升查询业务语句的编写效率、隐藏基本表中与业务无关的数据、提高数据的逻辑独立性。索引是在表或视图已有字段上建立的存储结构，该存储结构使MySQL 查询引擎在无须遍历全表的情况下，快速定位满足条件的目标记录，提升SQL语句查询速度。本章将重点介绍视图和索引的产生背景、作用、适用场景，以及MySQL视图和索引的管理方法。

本章学习目标：理解视图和索引的概念及适用场景；能够根据数据库查询、管理等需要，建立相关视图并能够对视图进行基本管理操作；学会选择合适的索引类型， 构建并操作相关索引。

一、视图

1.1、视图概述

视图是在一个或多个基本表或视图的基础上，通过查询语句定义的虚拟表。与基本表类似，视图可用于SELECT语句中进行查询。不同之处在于，视图只存储其定义语句，并未存储其数据。当使用视图进行查询时，视图包含的数据才会临时生成。

视图可以理解为与具体业务相关的外模式。以teaching数据库为例，当需要学号(sno)、姓名(sn)、课程号(cno)和选课名称(cn)信息时，我们需要查询3张基本表——学生表(s)、i课程表©和选课表(sc)。我们可构建视图sc_view，利用学生表(s)、课程表©和选课表(sc)中与业务相关的字段，构建一张虚拟表，如图7-1所示。当需要查询上述信息时，我们可直接查询视图sc_view，这提高了查询语句的编写效率，隐藏了与业务无关的字段和数据。

1.2、视图的作用

视图提升了数据操作的便携性

视图可定义在多个表上，使用视图时，用户无须了解视图构建细节，只需在视图提供的字段中进行查询即可，这提高了编写相关SOL语句的速度。

视图提升了数据的逻辑独立性

利用视图可以在一定程度上将基本表结构与操作该表的业务程序进行逻辑分离。例如，在基本表字段名称改变后，用户只需利用视图将修改的字段重命名为原来程序使用的字段，程序调用视图，好像调用原来的基本表一样，从而实现了数据的逻辑独立性。

视图提升了数据的安全性

使用视图可在表权限基础上，进一步针对视图使用进行授权，这增加了权限授予的层次。同时，通过视图可隐藏表中敏感数据，为数据库用户提供其权限范围可见的数据，实现数据安全。

视图可用于数据集成

在分布式数据库环境下，视图也可以用于数据集成。例如，某单位在不同地区存储了属地业务数据，该单位可使用分布式查询定义视图，定期将不同区域的数据组合起来。

1.3、视图的工作机制

视图是虚表，当SQL语句引用视图时，视图才会根据定义动态地产生数据。因此，视图中的内容总是与基本表中数据保持一致，即当基本表中数据发生变化时，相关视图的数据也随之变化。

二、MySQL视图管理

2.1、创建视图

使用CREATE VIEW语句可在一张或多张基本表或视图上创建视图，其语法格式如下。

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPSTABLE}]
[DEFINER={user|CURRENT_USER}]
VIEW 视图名[(视图字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

对于上述语法格式，相关说明如下。

(1)使用CREATE VIEW语句创建视图需要具有CREATE VIEW权限及查询语句中涉及列的SELECT权限。

(2)添加OR REPLACE可选参数需要具有DROP权限，使用该参数表明可以在创建视图时替换数据库已有同名视图。

(3) ALGORITHM为可选参数，表示视图的使用方法。其中，UNDEFINED表示由MySQL自行决定使用方法；MERGE表示使用CREATE VIEW语句中的条件与引用该视图的查询语句条件作为整体条件，然后使用视图依赖的基本表直接查询；参数TEMPTABLE表示使用该视图时，先将视图涉及的数据存储在临时表中，然后使用临时表中数据进行视图查询。

(4) DEFINER为可选参数，指明视图的创建者。默认情况下，视图的创建者为当前用户，也可指明其他用户为视图创建者。

(5)视图字段列表为可选的，当省略视图字段时，使用查询语句的字段名称作为视图的字段名称；当给定视图字段时，将重命名查询语句中对应字段。

(6) AS指明视图的定义，其后由一个完整的SELECT语句构成。

(7) WITH CHECK OPTION为可选参数，表示更新、修改和插入视图数据时，只有满足检查条件，操作才会执行。该参数可以添加CASCADED或LOCAL为参数，CASCADED为默认参数。有关WITH CHECK OPTION的详细内容将在7.2.5小节中介绍。

下面分别举例说明在一张或多张基本表和其他视图基础上创建视图的方法

在一张基本表上创建视图

【例7-1】创建信息学院学生视图s_view。

CREATE VIEW s_view
AS SELECT * FROM s WHERE dept='信息学院';

该例子将学生表s上所有字段作为视图s_view的字段。创建视图后，可使用SELECT语句对视图s_view的数据进行无条件查询，查询视图语句如下，查询结果如表7-1所示。

SELECT * FROM s_view;

在多张基本表上创建视图

【例7-2】创建学生选课情况视图s_sc_c_view，视图字段列表为学号sno、姓名sname、课程名cname及成绩score。

CREATE VIEW s_sc_c_view(sno, sname, cname, score)
AS SELECT s.sno,sn,cn,score FROM s,c,sc WHERE s.sno=sc.sno AND sc.cno=c.cno;

由于在s表和sc表中均存在学号sno列，因此在SELECT语句中需指定列名来源。另外，由于给定的视图字段列表与SELECT查询的字段不一致，因此在创建视图时，需要对照查询结果，使用视图字段列表依次对查询的字段进行重命名。

下面进一步举例说明限制条件的视图查询。查询视图s_sc_c_view上学号为"s1"的同学分数不低于60分的数据，查询视图语句如下，查询结果如表7-2所示。

SELECT * FROM s_sc_c_view WHERE score>=60 AND sno='s1';

在视图上创建视图

【例7-3】在信息学院的学生视图s_view基础上，创建计算机专业学生视图s_maj_ iew。

CREATE VIEW s_maj_view AS SELECT * FROM s_view WHERE maj='计算机';

执行上述语句后，视图s_maj_view只包含信息学院计算机专业的学生信息。

创建视图的注意事项

(1) SELECT语句中不能包含系统、用户变量（系统和用户变量内容将在第15章介绍）及处理语句参数，同时，FROM子句中不能包含子查询。

(2)删除视图依赖的基本表后，视图使用会报错，此时可通过"CHECK TABLE 表名”检查基本表状态。

(3)不能为临时表创建视图。

(4)创建视图时，ALGORITHM参数指定视图的使用方法。下面举例说明。

①创建学生视图s_view_condition, 要求年龄大于25岁。

CREATE ALGORITHM=MERGE
VIEW s_view_condition
AS SELECT * FROM s WHERE age>25;

②使用s_view_condition进行条件查询，要求年龄小于40岁。

SELECT * FROM s_view_condition WHERE age<40;

③上述查询执行时，将把s_view_condition的条件与查询条件结合，直接对s表进行查询，即实际执行的SQL语句如下。

SELECT * FROM s WHERE age>25 AND age<40;

(5)如果视图定义和视图查询语句中均包含了ORDER BY语句，则查询结果以视图查询语句中的ORDER BY为准。

(6)如果视图依赖的基本表增加了字段，则视图查询不包括新的字段。

2.2、查看视图的定义

使用DESCRIBE语句、SHOW语句、系统表和MySQL Workbench等，可查看已创建视图的结构、状态、定义语句等信息。查看视图需要具有SHOW VIEW权限。

2.2.1、使用DESCRIBE语句查看视图结构信息

使用DESCRIBE语句可查看视图的结构信息，即视图各组成字段信息，其语法格式如下。

DESCRIBE 视图名称;

其中，DESCRIBE可以使用缩写DESC。

【例7-4】查看视图s_view的结构信息。

DESCRIBE s_view;

执行结果如7-3所示。

2.2.2、使用SHOW TABLE STATUS语句查看视图状态情况

使用SHOW TABLE STATUS语句可查看视图的名称、创建时间、更新时间、注释等状态信息，其语法格式如下。

SHOW TABLE STATUS LIKE '视图名称';

【例7-5】查看视图s_view的状态信息。

SHOW TABLE STATUS LIKE='s_view';

2.2.3、使用SHOW CREATE VIEW语句查看视图创建信息

使用SHOW CREATE VIEW语句可以查看视图的名称、创建该视图的SQL语句、视图使用的字符集等创建信息，其语法格式如下。

SHOW CREATE VIEW 视图名称;

【例7-6】查看视图s_view的创建信息。

SHOW CREATE VIEW s_view;

注意：使用SHOW CREATE VIEW语句和DESCRIBE语句时，视图名称没有引号；使用SHOW TABLE STATUS LIKE时，视图名称作为字符串匹配条件，需要添加引号。

2.2.4、使用系统表查询视图的元信息

通过MySQL系统表information_schema.VIEWS，可查看指定视图的定义、状态等元信息。

【例7-7】查询视图s_view的元信息。

SELECT * FROM information_schema.VIEWS WHERE table_name='s_view';

2.3、修改视图的定义

创建视图后，因视图相关的业务需求发生变化或视图涉及的基本表结构发生变化，需修改视图的定义时，用户可以使用CREATE OR REPLACE VIEW语句、ALTER VIEW语句和MySQL Workbench等来修改视图的定义。

2.3.1、使用CREATE OR REPLACE VIEW语句修改视图的定义

使用CREATE OR REPLACE VIEW语句修改视图的语法格式同使用CREATE VIEW语句创建视图类似，读者可参照7.2.1小节内容。但需注意，使用CREATE OR REPLACE VIEW修改视图时，需要检查修改视图名称的正确性，若名称错误，则不会替换原视图，反而会按照错误名称新建视图。

2.3.2、使用ALTER VIEW语句修改视图的定义

使用ALTER VIEW语句可以修改已创建视图的定义，其语法格式如下。

ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
[DEFINER={user|CURRENT_USER}]
VIEW 视图名[(视图列表)]
AS 查询语句
[WITH [CASCADED|LOCAL]] CHECK OPTION;

上述语句的参数定义同CREATE VIEW语句。但需注意，若在ALTER VIEW语句中使用了错误的视图名称，则会导致修改失败。

【例7-8】修改s_view视图，按s_id、s_name和s_maj字段名称显示理学院学生的学号、姓名和专业。

ALTER VIEW s_view(s_id, s_name, s_maj)
AS SELECT sno,sn,maj FROM s WHERE dept='理学院';

提交视图修改语句后，使用SELECT语句查看视图s_view定义修改后的结果，如表7-4所示。

2.4、删除视图

创建视图后，用户可使用DROP VIEW语句和MySQL Workbench等删除现有视图。使用DROP VIEW语句可删除一个或多个已有视图，其语法格式如下。

DROP VIEW [IF EXISTS] 视图名称1[, ···] [RESTRICT|CASCADED];

对于上述语法格式，相关说明如下。

(1)如果需要一次性删除多个视图，可以将需要删除的视图名称以逗号分隔。

(2) IF EXISTS为可选参数，可确保即使要删除的视图不存在，提交语句后也不会报错。

(3) RESTRICT为默认参数，表示如果有其他视图依赖当前需要删除视图建立，则不允许删除当前视图。CASCADED参数表示DROP VIEW语句会级联删除其他依赖当前需要删除视图而建立的视图。

【例7-9】删除视图s_view。

DROP VIEW s_view;

2.5、更新视图的数据

在某些特殊业务中，需要通过视图对基本表中数据执行增加记录、删除记录和修改记录等更新操作。此时，如果视图提供的字段能够满足更新数据要求，则可以像操纵基本表中数据一样，使用INSERT、UPDATE和DELETE语句对视图引用的基本表数据进行更新。

由于视图是一张由基本表建立的虚表，所以对视图进行数据更新操作将转换成对基本表中数据的更新操作。但需注意，视图的本质是方便查询或保护数据，因此，当对视图进行数据更新操作时，有些情况下是不可能的，如视图依赖于多张基本表。实际中很少使用视图更新数据，但从知识的完整角度出发，本节会介绍使用INSERT、UPDATE和DELETE语句对视图进行数据更新的方法，以及创建视图的WITH CHECK OPTION参数对更新数据的影响。

为便于举例，构建用于更新数据的学生视图s_update_view。

【例7-10】构建用于更新数据的学生视图s_update_view，提供学生表中的所有信息。

CREATE VIEW s_update_view
AS SELECT * FROM s;

2.5.1、使用INSERT语句向视图中插入数据

【例7-11】使用s_update_view视图向学生表s中插入一条学生记录，学生记录中各字段信息为(s10，韩义，男，19，计算机，信息学院）。

INSERT INTO s_update_view
VALUES('s10', '韩义', '男', '19', '计算机', '信息学院');

上述语句相当于执行如下操作。

INSERT INTO s
VALUES('s10', '韩义', '男', '19', '计算机', '信息学院');

2.5.2、使用UPDATE语句修改视图中的数据

【例7-12】使用s_update_view视图，将学号为s10的学生年龄修改为20岁。

UPDATE s_update_view
SET age=20
WHERE sno='s10';

上述语句相当于执行如下操作。

UPDATE s
SET age=20
WHERE sno='s10';

2.5.3、使用DELETE语句删除视图中的数据

【例7-13】删除视图s_update_view中学号为s10的数据。

DELETE FROM s_update_view
WHERE sno='s10';

上述语句相当于执行如下操作。

DELETE FROM s
WHERE sno='s10';

2.5.4、WITH CHECK OPTION参数对更新视图数据的影响

创建视图时，可以指定WITH CHECK OPTION参数，明确更新视图数据时需进行条件检查，即检查插入或更新的数据是否满足视图创建语句中SELECT语句的条件，如果不满足，则拒绝对视图的更新操作。

【例7-14】构建一个提供工学院学生信息的s_check_view视图，并指定WІТH CHECK OPTION参数。

CREATE VIEW s_check_view
AS SELECT * FROM s WHERE dept='工学院' WITH CHECK OPTION;

尝试使用INSERT语句向s_check_view中插入一条不满足WHERE条件的数据，即非工学院学生信息，各字段信息为(s11，杨青，男，19，计算机，信息学院）。

INSERT INTO s_check_view
VALUES ('s11', '杨青', '男', '19', '计算机', '信息学院'）

系统会提示"Error Code: 1369. CHECK OPTION failed ‘teaching.s_check_view’"，表示插入数据违反CHECK OPTION约束。

将上述插入的数据修改为满足视图创建时SELECT条件的数据，即插入一条工学院的学生信1息(s11，杨青，男，19，计算机，工学院）。再次使用INSERT语句向视图s_check_view插入数据，系统提示执行成功。

INSERT INTO s_check_view
VALUES ('s11', '杨青', '男', '19', '计算机', '工学院'）

使用WITH CHECK OPTION参数对更新视图进行检查时，也可以理解为只有插入的数据显示在视图中，才可通过检查；反之，则无法通过检查。使用UPDATE语句更新视图中数据与使用INSERT语句向视图插入数据对数据更新的检查过程相似。

根据上述分析，当创建视图指定了WITH CHECK OPTION参数时，使用DELETE语句只能删除视图中已有的数据，如只能使用DELETE语句删除s_check_view中工学院学生数据，非工学院学生信息无法过s_check_view删除。

当视图是依赖其他视图创建时，更新视图可将WITH CHECK OPTION设置为LOCAL或CASCADED参数，明确更新数据是否按照依赖的层次进行检查，CASCADED为默认参数。使用LOCAL和CASCADED参数的情况说明如下。

(1)如果当前视图依赖的父视图中具有WІТH CHECK OPTION，则无论当前视图是否指定WITH LOCAL CHECK OPTION或WITH CASCADED CHECK OPTION，都将在更新视图时，检查父视图中SELECT的WHERE条件是否满足。

(2)如果当前视图依赖的父视图中设置了WHERE条件，但没有指定WІТH CHECK OPTION，且当前视图指定WITH CHECK OPTION，则表明对当前视图更新数据会默认按照CASCADED参数处理，级联检查其依赖的各父视图中SELECT的WHERE条件。

(3)如果当前视图的父视图中设置了WHERE条件，但未指定WITH CHECK OPTION，且当前视图指定WITH LOCAL CHECK OPTION，则表明对当前视图更新数据时只检查当前视图本地的WHERE条件，不会级联检查其依赖的父视图中SELECT的WHERE条件。

2.5.5、更新视图数据的其他注意事项

如果视图只依赖于一张基本表，则可通过视图更新该基本表。如果视图依赖于多张基本表建立，则一次只能修改一张基本表中数据。创建视图时，如果视图定义语句中包含以下结构，则不可更新视图数据。

(1)视图字段列表或查询语句中包含聚合函数。

(2)视图字段列表或查询语句是通过表达式或计算得到的。

(3)视图定义语句中包含DISTINCT关键字或GROUP BY、ORDER BY、HAVING子句。

(4)视图定义的查询语句中使用了集合查询UNION或UNIONALL。

(5)视图的列来自于查询语句中子查询的列。

(6)创建视图时ALGORITHM为TEMPTABLE类型。

(7)视图依赖于其他不可更新视图建立。

总之，视图更新数据的限制较多，原则上尽量不要使用视图更新数据。

三、索引

3.1、索引的作用

在很多数据库系统中，数据库读取的次数多于数据库写的次数，因此，如何提高数据库读取数据效率是数据库优化的主要工作之一。索引采用键值对的数据结构，可加快检索速度。索引的键由表或视图中一列或多列生成，值存储了键所对应数据的存储位置。如果把数据库看作字典，可以将索引的键看作字典的拼音，值为该拼音所在的第一个汉字的位置，借助拼音检索可以缩小目标汉字查找范围，避免逐页查找。在字典中，人们既可以使用拼音检索，也可以使用偏旁部首检索。因此，在数据库中，用户可以按照检索效率需要，建立多个索引。

实际上，索引是一种以空间代价提高时间效率的方法，它采用预先建立的键值结构，根据查询条件，快速定位目标数据。但需注意，索引一旦创建，将由MySQL自动管理和维护，索引的维护需要消耗计算资源和存储资源，特别是对数据更新时，为确保索引的查询效率，需要更新现有索引结构，故要避免在一个表中创建大量的索引，否则每次更新数据时，系统的整体响应效率将下降。同时，在查询数据时，索引并不是总能生效的，只有查询条件中使用了索引的字段，索引才会生效，用户可在执行查询语句时，通过EXPLAIN分析查询语句是否使用索引。在系统开发中，如何设计索引，是提高数据库使用效率的关键。

在MySQL中，不同存储引擎使用不同的索引和数据存储方式。MyISAM存储引擎将索引和数据分成2个文件存储，而InnoDB存储引擎将索引和数据放在同一文件中。不同存储引擎的存储策略反映了不同存储引擎查找效率和存储代价存在差异的原因。

与其他数据库管理系统类似，MySQL也使用B-Tree和Hash技术存储索引，其中B-Tree为默认的索引存储技术。不同存储引擎使用的索引存储技术不同，InnoDB和MyISAM存储引擎均支持B-Tree索引，MEMORY存储引擎支持哈希类型索引。

3.2、索引类型

我们可以从多种角度对索引进行分类。

根据索引特征进行分类

从索引的特征角度可将索引分为普通索引、唯一索引、主键索引、全文索引和空间索引。

(1)普通索引是指创建索引时不附加任何约束和限制条件的索引。普通索引字段是否需要满足唯一性和非空要求由字段本身的完整性约束决定。例如，我们可在教师表中，对教师姓名建立普通索引。普通索引的创建方式如例7-15所示。

(2)唯一索引是指创建索引时，使用了UNIQUE关键字的索引。由于唯一索引涉及的列值必须唯一，因此使用唯一索引比使用普通索引能够获得更快的查询速度，但是如果索引所在列中出现多个重复数据，则不能使用唯一索引。唯一索引允许所在列包含多个NULL值。唯一索引的创建方式如例7-16所示。

(3)主键索引是指建立数据表时依据主键自动建立的索引。该索引要求索引列值唯一且非空。主键索引是在主键创建时自动建立的，很少直接创建主键索引。同时，一个数据表只能有一个主键，因此，一个数据表只能有一个主键索引。但对于其他类型的索引，一个数据表可以根据业务需要建立多个其他类型的索引。

(4)全文索引是指在创建索引时，使用了FULLTEXT关键字的索引。查询数据量较大的字符串类型字段时，使用全文索引可提高查找速度。例如，在一些新闻发布系统中，人们可以对新闻内容字段建立全文索引，以提高新闻内容搜索效率。全文索引适用于字符串类型的字段，如CHAR、VARCHAR和TEXT类型。需注意的是，MySQL 5.6版本后，MyISAM和InnoDB均支持全文索引。MySQL5.6版本前，只有MyISAM支持全文搜索。全文索引的创建方式如例7-17所示。

(5)空间索引是指在创建索引时，使用了SPATIAL关键字的索引。其适用于GEOMETRY、POINT、POLYGON等空间数据类型的列。目前，只有MyISAM存储引擎支持空间索引且索引字段不能为空值。

根据索引涉及列数进行分类

从索引涉及的列数角度可以将索引分为单列索引和复合索引。

(1)单列索引是指针对某张表或视图上单列创建的索引。结合索引特征分类方法，用户可以创建一个单列的唯一索引，也可以建立一个单列的主键索引。例如，学生表学号字段(sno)既是一个主键索引，也是一个单列索引。

(2)复合索引是指针对某张表或视图上多个列创建的索引。复合索引中列的出现顺序决定了索引的使用方式，只有查询条件中使用了复合索引的第一个字段，复合索引才会生效。例如，对课程表中课程名称和学时2个字段建立复合索引，当查询条件的第一个参数为课程名称时，复合索引才会生效。需注意，复合索引不能跨表建立。

根据索引存储方式进行分类

如从索引存储技术角度可以将索引分为B-Tree索引和Hash索引。

(1) B-Tree索引是指使用了B-Tree数据结构的索引。B-Tree是一种支持范围查询且查询时间复杂度较低的平衡多叉树结构。目前，多数商业数据库管理系统和开源数据库管理系统均采用B-Tree数据结构存储索引。

(2) Hash索引是指使用了Hash结构的索引。对于单个值查询，Hash索引比B-Tree索引的查询效率要高，但是Hash索引不支持不等式范围查询。MySQL中MEMORY存储引擎使用Hash结构存储索引。

根据索引与数据物理存储关系进行分类

从索引与数据物理存储关系角度可将索引分为聚集型索引和非聚集型索引。

(1)聚集型索引指明了数据在物理存储设备上存储的方式。通常使用主码作为聚集型索引。

(2)非聚集型索引是指在聚集型索引基础上，通过额外的列或列集合建立记录的索引。非聚集型索引通常使用主码外的其他常用查询列。例如，对教师姓名建立的普通索引可以看作非聚集型索引。

3.3、索引设置原则

建立索引时，需结合业务查询需要，综合索引特征及存储方式，构建适合的索引。索引设置具体原则包括以下几条。

(1)严格限制同一个表或视图上的索引数量。索引增多将会严重影响INSERT、UPDATE 和DELETE语句的执行性能。对于表中使用频度较低或者不再使用的索引，需及时删除。

(2)对于重复值较多的列，不建议建立索引。

(3)对排序、分组或者表连接涉及的字段建立索引，可提高数据检索效率。

(4)对视图建立索引可提高使用视图进行检索的效率。

(5)注意唯一索引和全文索引对NULL的处理方式。

四、MySQL索引管理

4.1、创建索引

用户可以通过4种方式创建索引，分别是使用CREATE INDEX语句为已有表或视图添加索引、创建表时直接附带创建索引、通过ALTER TABLE语句为已有表添加索引和使用MySQL Workbench创建索引。

4.1.1、使用CREATE INDEX语句创建索引

使用CREATE INDEX语句可为已有表或视图添加索引，其语法格式如下。

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称;
ON 表名称(字段名称[(索引字符长度) [ASC|DESC]][,...]);

对于上述语法格式，相关说明如下。

(1) UNIQUE|FULLTEXT | SPATIAL为可选参数，用于指明索引类型。UNIQUE参数表示创建唯一索引。FULLTEXT参数表示创建全文索引。SPATIAL参数表示创建空间索引。未选择任何索引类型表明创建普通索引。

(2) ON关键字指明索引针对的表。字段名称［（索引字符长度） [ASC|DESC] ］表明索引涉及的列信息，针对数据库中复杂数据类型，如BLOB和TEXT，索引的建立需要消耗存储和计算资源，因此，创建索引时需指明编制索引的字符的长度，权衡索引建立代价，（索引字符长度）为可选参数，表明为字段名的前指定字符编制索引。ASC和DESC为可选参数，表明索引的排序方式，ASC为升序排列，该参数为默认参数，DESC为降序排列。MySQL 8之前的版本，虽然SQL 语法上支持升序索引，但实际只支持降序索引。 MySQL 8后才真正意义上支持升序索引。

(3)当字段名称［（索引字符长度） [ASCIDESC] ]只有一项时，将建立单列索引。当字段名称[（索引字符长度） [ASCDESC] ］有多项时，将建立复合索引。

下面举例说明不同索引的创建方式。

【例7-15】为学生表s的姓名字段(sn)建立普通索引s_name_index，索引针对sn的前6个字符且以降序方式排列。

CREATE INDEX s_name_index
ON s(sn(6) DESC);

如果上述语句所在数据库引擎非MEMORY，则上述语句实际上创建了一个普通、单列、B-Tree、非聚集型索引。

【例7-16】为课程表c的课程名(cn)和学时(ct)字段建立复合唯一索引c_cn_ct_index。

CREATE UNIQUE INDEX c_cn_ct_index
ON c(cn,ct);

【例7-17】假设已经在学生表s中增加了TEXT类型的学生基本信息列(info)，为info列创建全文索引s_info_index。

CREATE FULLTEXT INDEX s_info_index
ON s(info);

4.1.2、创建表时直接附带创建索引

使用CREATE TABLE语句创建表时，可附带创建表索引，语法格式如下。

CREATE TABLE 表名(
    属性名1 数据类型 [列完整性约束],
    ...
    属性名n 数据类型 [列完整性约束],
    [表约束],
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名1] (字段名称[(索引字符长度) [ASC|DESC]]),
    ...
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名n] (字段名称[(索引字符长度) [ASC|DESC]]),
);

对于上述语法格式，相关说明如下。

(1)在表约束后，可以使用INDEX关键字为表创建索引。上述参数与使用CREATE INDEX语句添加索引时的参数含义相同。

(2) CREATE TABLE语句可一次附带多个索引，不同索引间使用逗号分隔。

(3) CREATE TABLE创建索引时无须提供表名，而使用CREATE INDEX语句创建索引时要指明表名。

【例7-18】创建教室表classroom，要求包含自增主键cid、教室编号crno（非空字符串）、教学楼名称cbn（非空字符串）。创建classroom表时，附加由教室编号crno和教学楼名称cbn构成的普通唯一索引cn_cb_index。

CREATE TABLE classroom(
  cid INT AUTO_INCREMENT,
  crno VARCHAR(10) NOT NULL,
  cbn VARCHAR(10) NOT NULL,
  UNIQUE INDEX cn_cb_index(cron,cbn)
);

4.1.3、通过ALTER TABLE语句为已有表添加索引

使用ALTER TABLE为已有表添加索引的语法格式如下。

ALTER TABLE 表名
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名1] (字段名称[(索引字符长度) [ASC|DESC]]),
...
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名n] (字段名称[(索引字符长度) [ASC|DESC]]),

说明：

(1)使用ALTER TABLE语句添加索引的参数含义与使用CREATE INDEX语句创建索引的参数含义相同；

(2)可以使用ALTER TABLE语句一次创建多个索引，不同索引间使用逗号分隔。

【例7-19】为教师表t中的教师姓名tn添加索引tn_index，索引长度为6且使用降序排列。

ALTER TABLE t
ADD INDEX tn_index(tn(6) DESC);

4.2、查看索引

有时用户需要查看已有表或视图上构建的索引情况，用户可使用SHOW INDEX语句和MySQL Workbench查看索引。使用SHOw INDEX语句查看已有表或视图上的索引信息，语法格式如下。

SHOW INDEX 表名 [FROM 数据名];

相关说明如下。

(1) [FROM 数据库名］指明查看索引的位置。如果使用数据库名称，则显示指定数据库下表或视图的索引信息。如果查看当前数据库下表的索引信息，可以省略［FROM 数据库名］。

(2) SHOW INDEX语句的另一种等价形式如下。

SHOW INDEX FROM 数据库名.表名;

【例7-20】查看当前数据库下学生表s的索引信息。

SHOW INDEX FROM s

在SHOW INDEX返回的内容中，Table指明索引所在表，Non-unique指明列值是否唯一(0表示唯一，1表示不唯一），Key_name为索引名称，Column为索引涉及的列名称，Collation指明索引排序方式(A表示升序排列），Index_type表示索引方法（包括BTREE、FULLTEXT、HASH等）。

4.3、删除索引

索引创建后可按需对索引进行删除。使用ALTER TABLE语句、DROP INDEX语句和MySQL Workbench可删除指定索引。使用ALTER TABLE语句删除指定索引的语法格式如下。

ALTER TABLE 表名
DROP INDEX 索引名;

【例7-21】删除当前数据库学生表s上的索引s_name_index。

ALTER TABLE s
FROP INDEX s_name_index;

使用DROP INDEX语句删除指定索引的语法格式如下。

DROP INDEX 索引名 on 表名;

针对例7-21，使用DROP INDEX语句来实现，如下所示。

DROP INDEX s_name_index ON s;

对于ALTER TABLE和DROP INDEX语句，相关说明如下。

(1)使用ALTER TABLE和DROP INDEX语句删除索引都需要指明索引所在表及索引名称，但是当使用ALTER TABLE语句删除指定表的主键索引时，由于主键索引的唯一性，可无须指定索引名称。例如，使用以下语句可删除学生表s上的主键索引。

(2)如果单列索引或者复合索引依赖的列被删除，则删除的列也会同时从索引中删除。极端情况：如果删除了单列索引或复合索引依赖的所有列，则索引也将被删除。

五、小结

本章讲述了视图和索引的概念、作用、设置原则，具体介绍了在MySQL中使用SQL语句和MySQL Workbench创建、修改与删除视图和索引的操作方法。

视图和索引属于数据库优化技术。视图以虚拟表形式存在，可以提高数据库操作的便捷性、数据的逻辑独立性及数据的安全性。索引采用键值对的存储结构，以B-Tree或Hash结构对数据进行排序，通过空间代价提高数据的检索效率。

原则上视图只用于检索数据，不用于更新视图中的数据。如果用户需要更新视图数据，可在定义视图时配套使用WITH CHECK OPTION参数，限制视图更新数据的检查条件。

索引数量并非越多越好，构建索引需要遵循索引设置原则，并结合业务需要，选择合适的索引类型。

六、参考

《数据库原理及应用教程（MySQL版）》

《数据库系统原理 自考04735》