MySQL中创建高性能的索引

一 索引基础

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，索引优化应该是对查询优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，”最优“的索引有时比一个”好的“索引性能要好两个数量级。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

索引的类型

索引有很多中类型，可以为不同的场景提供更好的性能。在MySQL中，索引是存储引擎层而不是服务器层实现的。下面是MySQL支持的索引类型：

B-Tree索引

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

下图展示了B-Tree索引的抽象标识，大致反应了InnoDB索引是如何工作的。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

可以使用B-Tree索引的查询类型

B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。

• 全值匹配：指和索引中的所有列进行匹配。
• 匹配最左前缀：只使用索引的第一列。
• 匹配列前缀：也可以匹配某一列的值的开头部分。
• 匹配范围值。
• 精确匹配某一列并范围匹配另外一列。
• 只访问索引的查询：即查询只需要访问索引，而无须访问数据行。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。

下面是关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引的列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

因为索引自身只需存储对应的哈希值，所以索引的结构非常紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引值包含哈希值和行指针，而不存储字段值，所以不能使用索引中给的值来避免读取行。不过，访问内存中的行的速度很快，索引大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引页不支持部分索引列匹配查找。
• 只支持等值比较查询，不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现冲突是，存储引擎必须遍历链表中所有的行指针，逐行比较，直到找到所有符合条件的行。如果哈希冲突很多的话，一些索引维护操作的代价也会提高。

因为这些限制，哈希索引只适用于某些特定的场合。

InnoDB引擎有一个特殊的功能叫做”自适应哈希索引（adaptive hash index）“。当InnoDB注意到某些索引值被使用得很频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引得一些优点。

创建自定义哈希索引

如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，着可以享受一些哈希索引得便利，例如只需要很小的索引就可以为超长的键创建索引。

思路：在B-Tree基础上创建一个伪哈希索引，这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引，需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

空间数据索引（R-Tree）

可以用作地理数据存储。空间索引会从所有维度来索引数据，查询时，可以有效地使用任意维度来组合查询。

全文索引

全文索引时一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

其他索引类别

二 索引的优点

索引可以让服务器快速地定位到表的指定位置，还有一些其他附加作用。最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。据此，总结出索引三个优点：

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

三 高性能的索引策略

3.1 独立的列

独立的列是指索引的列不能是表达式的一部分，也不能是函数的参数。建议养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

3.2 前缀索引和索引选择性

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

索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总是（#T）的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

3.3 多列索引

一种常见的错误时，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫”索引合并“的策略一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略有时候是一种优化的结果，但实际上更多适合说明了表上的索引建得很糟糕：

• 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时（通常由多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别时当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会报这些计算到”查询成本“（cost）中，优化器只关心随机页面读取。这会是的查询的成本被”低估“，导致该执行计划还不如直接走全表扫描。

3.4 选择合适的索引列顺序

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

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

当不需要考虑排序和分组时，将选择性最高的列放在前面通常时很好的。这时候索引的作用只是用于优化WHERE条件的查找。然而，性能不只依赖于所有索引列的选择性（整体基数），也和查询条件的具体有关，也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

3.5 聚簇索引

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

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

InnoDB通过主键聚集数据，没有没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚集的数据有一些重要的优点：

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

一些缺点：

• 聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没有什么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键的顺序加载数据，那么加载完数据后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为为强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行，这是一次页分裂操作。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象中的更大，因为二级索引的叶子节点包含了引用行的主键。
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引叶子节点保存的不是指向行的物理位置指针，而是行的主键值。这意味着二级索引查找行，存储引擎需要找到二级索引的叶子节点获取主键值，然后根据这个值去聚簇索引中查找对应的行，这里进行了两次B-Tree查找，自适应哈希索引能够减少这样的重复工作。

聚簇与非聚簇表对比图

注意：对于高并发的工作负载，在InnoDB中按主键顺序插入可能会导致明显的争用。主键的上界会成为“热点”，因为所有的插入都发生在这里，所有并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mod配置，如果你的不支持这个参数，可以升级到新版本的innodb。

3.6 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为”覆盖索引“。

覆盖索引时非常有用的工具，能够极大的提高性能。考虑一下如果查询只需要扫描索引而无须回表，通过会带来多少好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况响应时间大部分花在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为所有比数据更小，更容易全部放入内存（这对MyISAM尤其正确，因为MyISAM能够压缩索引以使索引变得更小）。
• 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存行的主键值，所以如果二级主键能覆盖查询，则可以避免对主键索引的二次查询。

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

3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。

扫描索引本身是很快的，因为只需要从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应尽可能的满足这两种任务。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都是一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的额时候，如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以”弥补“索引的不足。

3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存，这在某些情况下能极大的提高性能。默认值压缩字符串，但通过参数设置也可以对整数做压缩。

压缩使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在所言块中使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但如果是倒序就不是很好了。测试表明，对于cpu密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在cpu内存资源与磁盘之间做平衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

可以在create table语句中指定PACK_KEYS参数来控制索引压缩的方式。

3.9 冗余和重复索引

MySQL允许在相同列上创建多个索引，但需要单独维护重复的索引，并且优化器在优化查询的时候页需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。

大多是情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致起变得太大，从而影响其他使用该索引的查询的性能。

3.10 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，建议考虑删除。

3.11 索引和锁

索引可以让查询锁定更少的行。如果你的查询不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。

InnoDB只在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回到服务器层以后，服务器才能应用WHERE子句。这是已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。

关于InnoDB、索引和锁有一些很少有人直到的细节：InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。

四 维护索引和表

维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

4.1 找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至会导致数据库的崩溃。可以尝试允许CHECK TABLE来检查是否发生了表损坏，该命令通常能找出大多数表和索引的错误。

可以使用REPAIR TABLE命令来修复损坏的表，如果存储引擎不支持，也可通过一个不做任何操作的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎。

4.2 更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。

第一个API时records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。

第二个API时info()，该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

如果存储引擎向优化器提供的扫描行数信息时不准确的，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行，如果表没有统计信息或者不准确，优化器就很有可能做出错误的决定。可以通过允许ANALYZE TABLE来重新生成统计信息来解决这个问题。

4.3 减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率，碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片化：

• 行碎片：这种碎片指的是数据行被存储在多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
• 行间碎片：指逻辑上顺序的页，行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响。
• 剩余空间碎片：是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

对于MyISAM表，这三类碎片化都有可能发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行并重写到一个片段中。

可以通过执行OPTIMIZE TABLE或者导出在导入的方式来重新整理数据。

五 总结

在选择索引和编写利用这些索引的查询时，有如下三个原则需要始终记住：

• 单行访问是很慢的。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能够包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
• 按顺序访问范围数据是很快，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GRUOP BY查询页无须再做排序和将行按组进行聚合计算了。
• 索引覆盖查询是很快的。如果一个索引包含了查询需要的索引列，那么存储引擎就不需要再回表查找行，这避免了大量的单行访问。