MySQL数据库教程（第2版）-课件 第7章索引

第7章

索引7.1索引概述索引是一种单独的、物理的、存储在磁盘上的数据库结构。数据库中的索引类似于一本书中的目录，读者可以通过目录快速找到需要的章节页码，翻到对应的页码即可看到具体的内容。1．索引的概念

索引是按照数据表中某一个字段或多个字段进行排序，并用指针指向记录的位置。索引存放在索引页面上，当进行数据检索时，系统从索引页面中找到所需数据的指针，再直接利用指针读取数据。因为索引是排好序的，不必逐一查找，可以采用针对排序数据的查找算法快速检索到数据。在没有索引的表中查询某个特定记录时需要从第一条记录开始，逐条比较记录值是否匹配，当表中的记录比较多时，得到结果耗时较长，影响查找的效率。例如，在学生表student中按照学号stuno查找入学总分enterscore时，需要从第一条记录开始扫描全表，找到对应的记录，当表中的数据非常多时，查询比较耗时。为stuno字段创建索引后，系统会利用该索引找到对应的记录，避免了对student表的全表扫描，加快了查询速度。索引具有如下的优点（1）可能会提高查询速度。（2）可以实现数据的唯一性。（3）提高ORDERBY和GROUPBY的执行速度。使用索引可以提升性能，但也要付出一定的代价，主要体现为索引需要占用磁盘的存储空间；索引会降低更新表中数据的速度，当更新表中数据时，系统会自动更新索引页面的数据，可能需要重新组织索引，这也需要时间。索引设计不当可能会影响系统的性能，合理设置索引对于数据库系统非常重要，需要考虑到以下几点规则。（1）为频繁查询的字段创建索引。例如，在WHERE、JOIN、ORDERBY、GROUPBY等子句涉及的字段上创建索引（2）数据量较小的表最好不要创建索引。（3）尽量在不同值较多的字段上创建索引，例如，学生表student的姓名stuname字段；不要在不同值很少的字段上创建索引，例如，性别gender字段。（4）一个表中的索引不是越多越好，需要限制索引的数量。（5）尽量不为频繁进行插入、删除、修改操作的表创建索引。（6）定期维护索引，及时删除无用索引。2.索引的分类

MySQL的索引可以从多个维度进行分类，最常用的是按照逻辑功能分类，主要包含以下几种类型。（1）普通索引这是基本的索引类型，它没有唯一性的限制，可以有重复值和空值。创建普通索引的关键字是INDEX。（2）唯一索引唯一索引的索引字段的值必须是唯一的，不允许重复，但允许有空值。创建唯一索引的关键字是UNIQUE。主键索引是一种特殊的唯一索引，特殊之处在于每张表只能有一个主键索引，且不允许有空值。创建主键索引的关键字是PRIMARYKEY，即主键。在一张表中指定主键时，数据库会同时自动创建相应的主键索引。（3）全文索引全文索引只能在CHAR、VARCHAR或者TEXT类型的字段上创建，并且只能在存储引擎为MyISAM和InnoDB的表中创建。创建全文索引的关键字是FULLTEXT。当查询数据量较大的字符串型的字段时，使用全文索引可以提高查询速度。创建在一个字段上的索引称为单索引。创建在多个字段上的索引称为组合索引、复合索引或多列索引。如果唯一索引是组合索引，则多个字段的组合必须是唯一的。在编写SELECT查询语句时，有索引的表与没有索引的表没有任何区别，索引只是提供了一种快速访问指定记录的方法。7.2创建索引

索引是在表中创建的，不可以针对视图创建索引。在MySQL中，可以在已有的表中创建索引，也可以在创建表的同时创建索引。索引创建成功后，将由数据库自动管理和维护，当向表中插入、删除和修改记录时，数据库会自动更新相应的索引。创建索引的目的是提高查询的速度，应该为需要经常查询的字段创建索引。（1）

使用CREATEINDEX语句创建索引使用CREATEINDEX语句可以在一个已有的表上创建索引语法格式：CREATE[UNIQUE

FULLTEXT]INDEX索引名ON表名(字段名称1[(长度1)][ASC

DESC][,字段名称2[(长度2)][ASC

DESC]

]);说明：（1）索引名：指定创建的索引名称，在一个表中可以创建多个索引，但是每个索引名必须是唯一的。（2）UNIQUE

FULLTEXT：可选项，UNIQUE表示创建的是唯一索引；FULLTEXT表示创建的是全文索引。（3）长度：只能对字符串类型的字段指定长度，表示使用字段值的前多少个字符创建索引，这样可以减少索引文件的大小。（4）ASC

DESC：指定索引按照升序或者降序排序。默认值为ASC。【例7-1】在学生表student的出生日期birthdate字段上创建一个普通索引idx_birthdate。mysql>CREATEINDEXidx_birthdateONstudent(birthdate);【例7-2】在课程表course的课程名称crsname字段上创建一个唯一索引idx_crsname。mysql>CREATEUNIQUEINDEXidx_crsnameONcourse(crsname);【例7-3】在学生表student的院系代码deptno、班级classno和姓名stuname字段上创建一个组合索引idx_dept_class_name，按照deptno升序排列，如果deptno相同，则按照classno升序排列，如果deptno和classno都相同，则按照stuname升序排列。mysql>CREATEINDEXidx_dept_class_nameONstudent(deptno,classno,stuname);组合索引创建成功后，索引的顺序是deptno

classno

stuname，需要按照最左前缀原则来有效的使用。满足最左前缀使用索引的情况有以下几种。①

完全使用所有列：例如，查询时使用WHEREdeptno='106'ANDclassno='计算2401'ANDstuname='王刚'，索引效果最好。②

使用前两列：例如，查询时使用WHEREdeptno='106'ANDclassno='计算2401'，索引有效。③

仅使用第一列：例如，查询时使用WHEREdeptno='106'，索引有效。如果违反最左前缀，则不能使用索引。例如，查询时使用WHEREclassno='计算2401'ANDstuname='王刚'，因为缺少最左列，索引无效。创建索引时就要考虑到其后续的应用，避免建立无效的索引。2．使用ALTERTABLE语句创建索引对于已有的表，也可以使用ALTERTABLE语句创建索引，其语法格式如下。ALTERTABLE表名ADDINDEX[索引名](字段名称1[,字段名称2…])

ADDPRIMARYKEY(字段名称1[,字段名称2…])

ADDUNIQUE[索引名](字段名称1[,字段名称2…])

ADDFULLTEXT[索引名](字段名称1[,字段名称2…]);

说明

（1）索引名：如果省略则采用默认索引名。默认索引名的命名规则是主键的索引名为PRIMARY，其他索引使用索引的第一个字段名称为索引名。如果多个索引的第一个字段名称相同，则在字段名称后加递增的数字进行区别。（2）一次添加多个索引时，各个索引之间用英文逗号分隔。可以使用ALTERTABLE语句一次添加多个索引。【例7-5】在课程表course的课程名称crsname字段上创建一个唯一索引，在学分credit字段上创建一个普通索引并按照credit降序排列。mysql>ALTERTABLEcourseADDUNIQUE(crsname),ADDINDEX(creditDESC);相应的索引名分别是crsname和credit。【例7-4】在学生表student的入学总分enterscore字段上创建一个普通索引idx_enterscore，并按照enterscore降序排列。mysql>ALTERTABLEstudentADDINDEXidx_enterscore(enterscoreDESC);3．使用CREATETABLE语句创建索引使用CREATETABLE语句可以在创建表的同时创建索引，其中的索引项语法格式如下。PRIMARYKEY(字段名称1[,字段名称2…])

{INDEX

KEY}[索引名](字段名称1[,字段名称2…])

UNIQUE[INDEX][索引名](字段名称1[,字段名称2…])

[FULLTEXT][INDEX][索引名](字段名称1[,字段名称2…]);【例7-6】创建数据表course1，指定课程编号crsno字段为主键索引、课程名称crsname字段为唯一索引、学分credit字段为普通索引。mysql>CREATETABLEcourse1->(->crsnoCHAR(8)NOTNULL,->crsnameVARCHAR(50),->creditDECIMAL(3,1),->PRIMARYKEY(crsno),->UNIQUE(crsname),->INDEX(credit)->);4．评估索引性能EXPLAIN是一个SQL语句分析工具，是显示MySQL如何执行查询的基本方法。通过EXPLAIN可以了解MySQL如何获取数据，确认索引是否生效，指导索引设计，及时发现未使用索引的查询。【例7-7】比较例7-1中的索引idx_birthdate创建前和创建后，查询2007年1月12日出生的学生信息的查询语句的性能。

索引idx_birthdate创建成功前。mysql>EXPLAINSELECT*FROMstudentWHEREbirthdate='2007-01-12';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|1|SIMPLE|student|NULL|ALL|NULL|NULL|NULL|NULL|12|10.00|Usingwhere|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------++----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+|1|SIMPLE|student|NULL|ref|idx_birthdate|idx_birthdate|4|const|1|100.00|NULL|+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+创建索引idx_birthdate后。mysql>EXPLAINSELECT*FROMstudentWHEREbirthdate='2007-01-12';创建索引前和创建索引后几个关键的指标对比如下，结果表明索引可以提高查询的速度。（1）id：SELECT语句标识符。（2）select_type：查询的类型。SIMPLE表示简单查询，即查询语句中不包含子查询或联合查询。（3）table：访问的表名。（4）partitions：要访问的表的分区名称。NULL表示student表没有分区。（5）type：访问类型。创建索引前值为ALL，表示无索引，执行了全表扫描。创建索引后值为ref，表示使用了普通索引来查找匹配某个值的所有行。（6）possible_keys：可能使用的索引。创建索引前值为NULL，表示没有可用的索引。创建索引后值为idx_birthdate。（7）key：实际使用的索引。创建索引前值为NULL，表示查询未使用索引，执行了全表扫描。创建索引后使用了idx_birthdate索引。（8）key_len：使用的索引长度。创建索引前值为NULL。创建索引后使用的索引长度为4字节，其中，出生日期birthdate字段（数据类型为DATE）的存储长度固定为3字节，再加上该字段允许空值需要额外使用1字节。（9）ref：与索引比较的列或常量，即在查找索引时用什么值去和索引列做比较。创建索引前值为NULL，表示没有比较。创建索引后值为CONST，表示用常量（2007-01-12）和索引列birthdate做比较。（10）rows：需要扫描的行数。创建索引前值为12行，与student表中的12条记录对应。创建索引后值为1行，表示只扫描1行就找到。（11）filtered：结果集过滤百分比，即结果集占扫描到的总行数的百分比。创建索引前值为10%，创建索引后值为100%。（12）Extra：其他信息。创建索引前值为USINGWHERE，表示查询使用了WHERE条件进行过滤。创建索引后值为NULL。7.3查看索引使用SHOWINDEX语句可以查看表中的索引，其语法格式如下。SHOW{INDEX

INDEXES

KEYS}{FROM

IN}表名[{FROM

IN}数据库名];SHOWINDEX语句以二维表的形式显示指定表中的所有索引信息，由于显示的信息较多且不易查看，使用\G参数可以