MySQL数据库管理与应用 第9章 索引.ppt

主要内容 9.1 索引概述 9.2 创建索引 9.3 查看索引 9.4 删除索引 9.5 使用EXPLAIN进行索引分析 9.6 本章小结 9.1 索引概述 数据库应用系统中，数据查询及处理速度是衡量系统性能的重要标准，如何提高数据库的性能是数据库设计时需要重点考虑的问题，利用索引来提高数据查询速度是最常用的一种性能优化方法。。 9.1 索引概述 9.1.1 索引的概念 索引是对数据表中一列或多列的值进行排序的一种结构。索引就像图书的目录一样用于快速查找需要的数据，提升数据库的查询性能。 在一个数据表中查找特定的记录也可以采取两种方法：一种是全表扫描，从第一行开始一一查看表中的每一行数据，与查询条件进行对比，返回满足条件的记录；另一种方法是通过对表中的数据创建索引，先在索引中找到符合查询条件的索引值，然后通过索引值对应的位置快速找到表中的记录。 当表中的数据很多的时候，全表扫描的效率很低，而如果合理地创建了索引，就可以利用索引避免全表扫描从而有效提高查询效率。 9.1 索引概述 9.1.1 索引的概念 数据库中索引的作用主要体现在以下几个方面： 索引可以提高查询的速度，这是创建索引的主要原因； 通过创建唯一索引，可以保证表中每一行数据的唯一性； 对有参照关系的父表和子表进行连接查询时，索引可以加速表与表之间的连接； 使用GROUP BY和ORDER BY子句进行查询时，索引可以显著减少分组和排序的时间。 9.1 索引概述 9.1.2 MySQL索引的分类 1．普通索引和唯一索引 普通索引是最基本的索引类型，创建普通索引时对于索引列的数据类型和值是否唯一没有限制。 唯一索引要求索引列的值必须唯一，但允许有空值（除非列的定义中有NOT NULL）。主键是一种特殊的唯一索引，不允许有空值。 2．单列索引和组合索引 可以在表的单个列上创建索引，称为单列索引。 也可以在表的多个列的组合上创建索引，称为组合索引、复合索引或多列索引。如果创建的是组合索引，只有查询条件中用到了索引中的第一个列才会使用该索引。 9.1 索引概述 9.1.2 MySQL索引的分类 3．前缀索引 MySQL中，对于字符串列（数据类型为CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT）可以创建只使用列值的前导部分的索引，使用col_name（length）语法指定索引前缀长度，前缀限制以字节为单位。 使用列前缀索引可以使索引文件小得多，从而节省大量磁盘空间，还可能加快插入操作。 4．函数索引 MySQL 8.0.13及更高版本提供了函数索引，可以对表达式的值进行索引，又称为表达式索引。 9.1 索引概述 9.1.2 MySQL索引的分类 5．全文索引 MySQL中使用参数FULLTEXT设置索引为全文索引。 全文索引基于文本的列（数据类型为CHAR、VARCHAR或TEXT）上创建，以加快对这些列中数据的查询和DML操作。 6．空间索引 MySQL中使用参数SPATIAL设置索引为空间索引。 空间索引只能建立在空间数据类型的列上，提高系统查询空间数据的效率。空间索引中的列必须声明为NOT NULL。 9.1 索引概述 9.1.2 MySQL索引的分类 7．聚集索引和辅助索引 聚集索引是指索引项的排序方式和表中数据记录排序方式一致的索引，每张表只能有一个聚集索引，聚集索引的叶子节点存储了所有的行数据。 聚集索引对表中数据重新组织以按照一个或多个列的值排序。由于聚集索引的叶子节点存储了表中的所有数据，索引搜索直接指向包含行数据的页面，所以使用聚集索引查询数据通常要比使用非聚集索引快。 9.1 索引概述 9.1.2 MySQL索引的分类 7．聚集索引和辅助索引 每个InnoDB表都必须有一个聚集索引： 如果表上定义了主键，那么主键就作为聚集索引； 如果表上没有定义了主键，那么该表的第一个唯一非空索引被作为聚集索引； 如果表上没有主键也没有合适的唯一索引，InnoDB会在包含行ID值的合成列上生成一个名为GEN_CLUST_index的隐藏聚集索引。行ID是一个6字节的字段，随着新行的插入而单调增加。因此，按行ID排序实际上是按插入顺序排列。 9.1 索引概述 9.1.2 MySQL索引的分类 7．聚集索引和辅助索引 聚集索引以外的索引称为辅助索引（二级索引、次索引）。 在InnoDB中，辅助索引中的每条数据都包含该行的聚集索引值（通常为主键值），以及该辅助索引中的列值。 InnoDB使用此聚集索引值搜索聚集索引中的行。如果主键较长，则辅助索引会占用更多空间，因此主键较短是有利的。 9.1 索引概述 9.1.3 索引的设计原则 为查询条件中经常用到的并且重复值较少的列上创建索引以便提高查询效率，重复值较多的列无须创建索引。 考虑为经常作为