MySQL- 索引详解

索引（键（key）），是存储引擎用于快速找到记录的一种数据结构。

如果没有索引，数据库就必须从第一条记录开始进行全表扫描，直到找出相关的行。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

 

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。因此，没有统一的索引标准，不同存储引擎的索引并不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的额限制。

 

• 索引的优点：

加快数据的查询速度。

唯一索引，可以保证数据库表中每一行数据的唯一性。

在实现数据的参考完整性方面，可以加速表和表之间的连接。

在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

 

• 索引的缺点：

占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸（合理运用，问题不大）。

损耗性能（添加、修改、删除）索引需要动态地维护。

• 索引的类型：

MySQL中索引的存储类型有两种： B-TREE和HASH（回头有时间另起一篇扒一下这块），具体和表的存储引擎相关；

 

MyISAM和InnoDB存储引擎只支持B-TREE索引，MEMORY/HEAP存储引擎可以支持HASH和B-TREE索引。

B-TREE索引中，所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。存储引擎以不同的方式使用B-TREE索引：MyISAM使用前缀压缩技术使得索引更小，通过数据的物理位置引用被索引的行。而InnoDB按照原数据格式进行存储，根据主键引用被索引的行。

B-TREE对索引列是顺序组织存储的，所以很适合查找范围数据。B-TREE索引适用于全键值，键值范围或者键前缀（根据最左前缀）查找。因为B-TREE是有序的，还可以用于查询中的ORDER BY操作。

 

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。因为索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序。哈希索引是使用索引列的全部内容来计算哈希值的，不支持部分索引列匹配查找。哈希索引只支持等值比较查询，包括=，IN()，<=>，不支持任何范围查询。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

 

• 索引的分类：

普通索引和唯一索引

普通索引：数据库中的基本索引类型，允许在定义索引的列中插入重复值和空值

唯一索引：索引列的值必须唯一，但允许有空值，主键索引是一种特殊的唯一索引，不允许有空值（比如自增ID）。

单列索引和组合索引

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引

组合索引（多列索引）：指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。

 

全文索引：类型为 FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引。

 

稠密索引和稀疏索引：

稠密索引：文件中的每个搜索键值有一个索引记录，在稠密聚集索引中，索引记录包括搜索键值以及指向具有该搜索键值的第一个数据记录的指针。具有相同搜索键值的其余记录顺序地存储在第一个数据记录之后，由于该索引是聚集索引，所以记录根据相同的搜索键值排序。

 

稀疏索引：只为搜索键值的某个值建立索引记录。和稠密索引一样，每个索引记录也包括一个搜索键值和指向该搜索键值的第一个数据记录的指针。为了定位一条记录，我们找到其最大搜索键值小于或等于所赵记录的搜索键值的索引值。然后从该索引项指向的记录开始，沿着文件中的指针查找，直到找到所需记录为止。

通常使用稠密索引可以比稀疏索引更快的定位一条记录。但是稀疏索引也有比稠密索引优越的地方：它所占的空间较小，而且所需要的插入和删除时的维护开销也较小。

 

聚簇索引和非聚簇索引：

聚簇索引（聚集索引）：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-TREE索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。无法同时把数据行存放在两个不同的地方，所以一个表中只能有一个聚簇索引。聚簇索引也称为主索引（primary index）。主索引看起来是指建立在主键上的索引，但是实际上它可以建立在任何搜索键值上。主索引能确定记录在数据文件中的位置。通常情况下聚集索引的搜索键值是主键，但也并非如此。

 

非聚簇索引（非聚集索引）：非聚簇索引必须是稠密索引，对每一个搜索键值都有一个索引值，而且对文件中的每个记录都有一个指针。聚簇索引（主索引）可以是稀疏索引，可以只存储部分搜索键值，因为通过顺序存取文件得一部分，总可以找到两个有索引项的搜索键值之间的搜索键值所对应的记录。如果非聚簇索引只存储部分搜索键值，两个有索引项的搜索键值之间的搜索键值所对应的记录可能存在于文件中的任何地方，并且我们通常只能通过扫描整个文件才能找到它们。

非聚簇索引看起来和稠密聚集索引没有太大的区别，只不过索引中的一系列连续值指向的记录不是顺序存放的。然而一般来说，非聚簇索引的结构可能和聚簇索引不同。具有同一个搜索键值的其它记录可能分布在文件的任何地方，因为记录按聚簇索引而不是非聚簇索引的搜索键值顺序存放。因此，非聚簇索引必须包含指向每一个记录的指针。

按聚簇索引顺序对文件进行顺序扫描是非常有效的，因为文件中记录的物理存储顺序和索引顺序一致。但是，我们不能使存储文件的物理顺序既和聚簇索引的搜索键值顺序相同，又和非聚簇索引的搜索键值顺序相同。由于非聚簇索引的顺序和物理键值的顺序不同，如果要按搜索键值的顺序对文件进行顺序扫描，那么每读一个记录都很可能需要从磁盘读入一个新的块，这是非常慢的。

非聚簇索引能够提高使用聚簇索引搜索键值以外的键值的查询性能。

 

聚簇索引与非聚簇索引的区别：

聚簇索引和非聚簇索引的根本区别是数据记录的排列顺序和索引的排列顺序是否一致，聚簇索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后，从而缩小了搜索范围，对于返回某一范围的数据效果最好。

聚簇索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

非聚簇索引指定了表中记录的逻辑顺序，数据记录的物理顺序和索引的顺序不一致，聚簇索引和非聚簇索引都采用了B-TREE的结构，但非聚簇索引的叶子层顺序并不与实际的数据页相同，而采用指向表中的记录在数据页中位置的方式。非聚簇索引比聚簇索引层次多，添加记录不会引起数据顺序的重组。在有大量不同数据的列上建立非聚簇索引，可以提高数据的查询和修改速度。

在对聚簇索引列查询时，聚簇索引的速度要比非聚簇索引速度快。

在对聚簇索引列排序时，聚簇索引的速度要比非聚簇索引速度快。但是如果数据量比较大时，如10万以上，则二者的速度差别不明显。

 

• 索引的设计原则

 

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响 INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新。

避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果。

在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

在频繁排序或分组（即group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

本文参考了高性能MYSQL第三版