一篇文章带你了解如何创建高性能索引

概述

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大，索引对性能的影响越发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还没有那么明显，但当数据量增大时性能会极具下降。

索引基础

MySQL中的索引工作机制类似书的目录，想要在一本书中找到特定的主题，首先看书的索引（目录），然后找到对应的页码。MySQL的存储引擎使用类似的方法使用索引，其先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。例如执行下面的查询语句：

mysql> SELECT name FROM student WHERE id = 3;

如果在id列上建立索引，则MySQL将使用该索引找到id为3的行，即MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行

索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也很重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引和创建两个包含一个列的索引时不相同

索引的类型

索引有很多类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式是不一样的，而不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

B-Tree索引

它使用B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引。存储引擎以不同的方式使用B-Tree索引，性能也各有不同。MyISAM使用前缀压缩技术使得索引更小，InnoDB则按照原来数据格式进行存储。再如，MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，B-Tree索引的抽象表示如下图

B-Tree索引能加速数据的访问，因为存储引擎不再需要进行全表扫描来获取数据取而代之从索引的根节点开始查找。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下查找，最终存储引擎要么找到对应的值，要么该记录不存在

哈希索引

哈希索引基于哈希表实现，只有准确匹配索引所有列的查询才生效。对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码不同哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针

在MySQL中，只有Memory引擎显示支持哈希索引。这也是Memory引擎表默认索引类型，Memory引擎同时支持B-Tree索引。Memory引擎支持非唯一哈希索引的。如果多个列的哈希值相同，索引会以链表的形式存放多个记录指针到同一哈希条目中
因为索引自身只存储对应的哈希值，所以索引的结构非常紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引的限制如下：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快。
• 哈希索引并不是按照索引值顺序存储的，所以无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询，不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值有相同得哈希值）。当出现相同的哈希冲突时，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行

如果哈希冲突很多的话，一些索引维护操作的代价很高。例如，如果在某个选择性很低（哈希冲突多）的列上建立哈希索引，当从表中删除一行时，存储引擎需要遍历对应的哈希值链表中的每一行，找到并且删除对应的应用，冲突越多，代价越高

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引（adaptive hash index）”。当InnoDB注意岛某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上在创建一个哈希索引，这样使B-Tree索引具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该该功能。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多注意的细节。全文索引更像是搜索引擎做的事，而不是简单的WHERE条件匹配。

在相同的列上同时创建基于值的B-Tree索引和全文索引是不会冲突的，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

索引的优点

索引可以让服务器快速的定位到表的指定位置，但是这并不是索引唯一的作用，根据创建索引的数据结构不同，索引也可而已有其他的附加作用。

最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作，因为数据是有序的，所以B-Tree也就会将相关列值存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询，据此特性，索引优点有三大类：

1. 索引可以大大减少服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变成了顺序I/O

高性能索引

独立的列

“独立的列”指的是索引列不能是表达式的一部分，也不能是函数的参数。如果查询中的列不是独立的，则MySQL就不能使用索引

例如这个查询：

mysql>SELECT id FROM student WHERE id+1 =5;

对于id+1=5，MySQL无法解析主动这个方程式，所以不能使用索引

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得更大，一个策略是模拟哈希索引，另一种方式是索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但是这样会降低索引的选择性。

索引的选择性指的是，不重复的索引值（也成为基数）/数据表的记录总数，范围从1/T到1之间。索引的选择性越高，查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，性能最高。

对于BLOB,TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

前缀索引是一种能使索引更小，更快的办法，但是另一方面缺点是：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引来做覆盖扫描

多列索引

在多个列上建立独立索引大部分情况下不能提高MySQL的查询性能。MySQL的合并索引的策略可以在一定程度上使用表中的多个单列来定位指定的行。MySQL查询时能同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件联合，AND条件相交，组合前两种情况的联合和相交。

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更改好的满足排序和分组的需求。

在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以索引可以按照升序或者降序进行扫描，以满足精度符合列顺序的OEDER BY，GROUP BY和DISTINCT等子句的查询需求

当不需要考虑排序和分组时，将选择性最高的列放到最前面是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的所以你确实能够最快的过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而性能不只依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，即和值的分布有关。可能会根据那些运行频率最高的查询来调整索引列的顺序。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB中的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在不同的地方，一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）

因为存储引擎负责负责实现索引，所以不是所有的存储引擎都支持聚簇索引。下图展示了聚簇索引中的记录是如何存放的，（叶子页其实是包含行的全部数据的，包含索引列，这里没有画出来。）
InnoDB将通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoD会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相差甚远

聚簇索引的优点

• 可以把相关数据保存在一起。例如实现电子邮件，可以根据用户ID来聚集数据，这样只需要从磁盘中读取少量数据页就可以获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件可能导致一次磁盘I/O
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取的数据通常比非聚簇索引中查找更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点

• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂操作会导致表占用更多的磁盘空间
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
• 二级索引（非聚簇索引）可能比想象中的大，因为在二级索引的叶子节点包含了引用行的主键列
• 二级索引访问需要两次索引查找，而不是一次

二级索引中保存的“行指针”实质，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引二点叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。对于InnoDB，自适应哈希索引能够减少这样重复的工作。

覆盖索引

大家会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。索引确实是一种高效的查找数据的方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么没必要在回表中读取。如果一个索引包含了所有查询的字段的值，我们称之为“覆盖索引”

• 覆盖索引是非常有用的工具，能极大提高性能，那MySQL就会极大减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易放到内存上（在MyISAM中尤其正确，因为MyISAM能压缩索引以变得更小）
• 因为索引是按照列值顺序存储的，所以对I/O密集型的范围查询比随机从磁盘读取每一行的I/O少的多。对于某些存储引擎，例如MyISAM，甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，会导致性能问题
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表表很有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，那么可以变面对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，索引MySQL只能使用B-Tree索引做覆盖索引。

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引放入到内存中，这在某些情况下能极大提高性能，默认只压缩字符串。MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字结束和剩余的不同后缀的部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值 是“performance”，那么第二个值的前缀压缩后存储的类似“7，ance”这样的形式。

压缩块使用更少的空间，代价是某些操作变慢。因为每个值的压缩前缀都依赖于前面的块，所以MyISAM查找时无法使用二分查找，只能从头开始扫描。

总结

在MySQL中，大部分情况下是B-Tree索引。其他类型的索引用于特殊目的，在选择索引并利用索引查询时需要记住以下几点：

1. 单行访问很慢。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么浪费很多工作。最好的读取的块尽可能包含可能多所需的行。使用索引可以创建位置引用以提升效率
2. 按顺序访问范围数据是很快的。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O快。第二，如果服务器能够按照需要的顺序读取数据，那么就不再需要额外的排序操作
3. 索引覆盖很快。如果一个索引包含可查询需要的所有列，那么存储引擎就不需要回表中查找。这样就避免了大量的单行访问。

总的来说，就是编写查询语句时尽可能避免单行查找，尽可能使用数据原声顺序而避免额外的排序操作，尽可能使用索引覆盖查询。