如何编写快速高效的SQL查询（三）——高性能索引策略与样例

是时候开始讨论使用索引了！正确地创建和使用索引是实现高性能查询的基础，现在我们一起来看看如何真正地发挥这些索引的优势。

高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。¹使用哪个索引，以及如何评估选择不同索引对性能的影响，则需要持续不断地学习。接下来的几节将帮助读者理解如何高效地使用索引。

前缀索引和索引的选择性

有时候为了提升索引的性能，同时也节省索引空间，可以只对字段的前一部分字符进行索引，这样做的缺点是，会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（＃T）的比值，范围从1/＃T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下，列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL并不支持对这些列的完整内容进行索引。

这里的关键点在于，既要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了确定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。在示例数据库Sakila中并没有合适的例子，所以我们从表city中生成一个示例表，这样就有足够的数据进行演示了：

现在我们有了示例数据集。数据分布当然不是真实的分布，由于我们使用了RAND()函数，所以你的结果会与此不同，但对这个练习来说这并不重要。首先，找到最常见的城市列表：

注意，上面的每个值都出现了45～65次。现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：

每个前缀都比原来的城市名出现的次数更多，因此唯一前缀比唯一城市名要少得多。然后，我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现，前缀长度为7时比较合适：

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近完整列的选择性。下面显示如何计算完整列的选择性：

通常来说（尽管也有例外情况），在这个例子中，如果前缀的选择性能够接近0.031，基本上就可以用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性：

查询显示，当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。

只看平均选择性是不够的，还有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。观察前缀为4的最常出现城市的次数，可以看到明显不均匀：

如果前缀是4字节，则最常出现的前缀出现的次数比最常出现的城市出现的次数要多很多，即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据，就更有可能看到这种现象。例如，在真实的城市名上建一个长度为4的前缀索引，对于以“San”和“New”开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。

在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引：

前缀索引是一种能使索引更小、更快的有效办法，但它也有缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。

一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。但如果业务系统使用了某个整体的解决方案，因而无法修改存储结构，那该怎么办？例如，使用vBulletin或者其他基于MySQL的应用在存储网站的会话（SESSION）时，需要在一个很长的十六进制字符串上创建索引。此时，如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每列创建独立的索引，或者按照错误的顺序创建多列索引。

我们会在接下来的章节中单独讨论索引列的顺序问题。先来看第一个常见问题：为每列创建独立的索引。从SHOW CREATE TABLE中很容易看到这种情况：

这种索引策略，一般是由于大家会听到一些“专家”这样模糊建议：“把WHERE条件里面的列都建上索引”。实际上这种建议是错误的，这样一来，在最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

在多列上独立地创建多个单列索引，在大部分情况下并不能提高MySQL的查询性能。MySQL引入了一种叫“索引合并”（index merge）的策略，它在一定程度上可以使用表中的多个单列索引来定位指定的行。在这种情况下，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。下面的查询就使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：

MySQL会使用这类技术来优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。

索引合并策略有时候效果非常不错，但更多的时候，它说明了表中的索引建得很糟糕：

• 当优化器需要对多个索引做相交（相交操作是使用“索引合并”的一种情况，另一种是做联合操作）操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当优化器需要对多个索引做联合操作时（通常有多个OR条件），通常需要在算法的缓存、排序和合并操作上耗费大量CPU和内存资源，尤其是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会把这些操作计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接进行全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响并发的查询，但如果单独运行这样的查询则往往会忽略对并发性的影响。通常来说，使用UNION改写查询，往往是最好的办法。

如果在EXPLAIN中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能，还可以使用IGNORE INDEX语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划。

选择合适的索引列顺序

最容易让人感到困惑的问题之一就是索引列的顺序。正确的顺序依赖于使用该索引的查询语句，同时，还需要考虑如何更好地满足排序和分组操作的需要。

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

所以，多列索引的列顺序至关重要。在Lahdenmaki和Leach的“三星索引”系统中，列顺序还决定了一个索引是否能够成为一个真正的“三星索引”，在后续部分我们将通过更多的例子来说明这一点。

对于如何选择索引的列顺序有一个重要的经验法则：将选择性最高的列放到索引最前列。这个建议准确吗？在很多场景中可能有帮助，但是要全面地考虑各种场景的话，考虑如何避免大量随机I/O和排序可能更重要。（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象中那么重要。）

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时索引的作用只是优化查询语句中的WHERE条件。在这种情况下，按这个原则设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说，选择性也更高。

然而，性能不只依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的因素一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

以下面的查询为例：

是应该创建一个（staff_id、customer_id）索引还是应该颠倒一下顺序？

这时，可以通过运行某些查询来确定在这个表中值的分布情况，并确定哪列的选择性更高。先用下面的查询预测一下，看看各个WHERE条件的分支对应的数据基数有多大：

根据前面的经验法则，应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更小。我们再来看看对于这个customer_id的条件值，对应的staff_id列的选择性如何：

这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。如果按上述办法优化，可能对其他一些条件值的查询不公平，服务器的整体性能可能会变得更糟，或者其他某些查询的运行变得不如预期。

如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询，那么再按上述办法选定索引顺序，往往可以获得更好的性能。如果没有运行类似的查询来确认实际的情况，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

customer_id的选择性更高，所以答案是将其作为索引的第一列：

和前缀索引例子中描述的情况一样，当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。例如，在某些应用程序中，对于没有登录的用户，都将其用户名记录为“guset”，在记录用户行为的会话（session）表和其他记录用户活动的表中，“guest”就成为一个特殊用户ID，一旦查询涉及这个用户，那么和对于正常用户的查询就大不相同了，因为通常有很多会话都是没有登录的。有些系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。

这在实际中是一个非常典型的问题。对于任何异常用户，不仅那些用于管理应用的设计糟糕的账号会有同样的问题，那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的与系统账号一样的问题。

下面是一个我们遇到的真实案例。在一个商品购买和经验分享的用户论坛系统中，这个特殊表中的查询运行得非常慢：

这个查询看似没有建立合适的索引，所以客户咨询我们是否可以优化。

EXPLAIN的结果如下：

MySQL为这个查询选择了索引（groupId，userId），如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下user ID和group ID条件匹配的行数，你可能就会有不同的想法了：

从上面的结果来看，符合组（groupId）条件的几乎满足表中的所有行，符合用户（userId）条件的有130万条记录，也就是说，索引基本上没什么用。因为这些数据是从其他应用中迁移过来的，迁移的时候把所有的消息都赋予了管理员组的用户。这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止针对这类用户和组执行这个查询。

从这个小案例可以看到，经验法则和推论在多数情况下是有用的，但要注意，不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定别忘了查询子句中的排序、分组和范围条件等其他因素，这些因素可能会对查询的性能造成非常大的影响。

聚簇索引

聚簇索引²并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。

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

因为是存储引擎负责实现索引，因此，不是所有的存储引擎都支持聚簇索引。本节我们主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

图展示了聚簇索引中的记录是如何存放的。注意，叶子页包含了一条记录的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。

有些数据库服务器允许你选择用于聚簇的索引，但是MySQL内置的存储引擎都不支持这个特性。InnoDB根据主键聚簇数据。这意味着图中所示的“索引列”就是主键列。

如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。这样做的缺点在于，所有需要使用这种隐藏主键的表都依赖一个单点的“自增值”，这可能会导致非常高的锁竞争，从而出现性能问题。

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

• 你可以把相互关联的数据保存在一起。例如，在实现电子邮箱应用时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。如果在设计表和查询时能充分利用上面的优点，那么就能极大地提升性能。

同时，聚簇索引也有一些缺点：

• 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象中的要更大，因为二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

最后一点可能会让人有些疑惑，为什么二级索引需要两次索引查找？答案是，二级索引中保存的是“行指针”。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点，以获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。这里做了双倍工作：两次B-tree查找而不是一次。⁴对于InnoDB，自适应哈希索引能够减少这样的重复工作。

覆盖索引

大家通常都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单是WHERE条件部分。索引的确是一种高效找到数据的方式，但是如果MySQL还可以使用索引直接获取列的数据，这样就不再需要读取数据行了。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。需要注意的是，只有B-tree索引可以用于覆盖索引。

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

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存型的应用负载非常重要，因为在这种情况下，响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
• 因为索引是按照列值的顺序存储的（至少在单页内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。可以通过OPTIMIZE命令使得索引完全实现顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
• 由于InnoDB的聚簇索引的特点，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了记录的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

在所有这些场景中，在索引中满足查询的成本一般比查询记录本身要小得多。当执行一个被索引覆盖的查询（也叫作索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。⁵例如，表sakila.inventory有一个多列索引（store_id，film_id）。MySQL如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

在大多数存储引擎中，索引只能覆盖那些只访问索引中部分列的查询，不过，可以更进一步优化InnoDB。回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。

例如，sakila.actor表使用InnoDB存储引擎，并在last_name字段有二级索引，虽然该索引的列不包括主键列actor_id，但下面的查询也能够使用索引来对actor_id列做覆盖查询：

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果在EXPLAIN的输出结果中，type列的值为“index”，则说明MySQL使用了索引扫描来做排序（注意，不要和Extra列的“Using index”搞混）。

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

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

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

有一种特殊情况，如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求。如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了。

例如，Sakila示例数据库的表rental在列（rental_date，inventory_id，customer_id）上建有名称为rental_date的索引：

MySQL可以使用rental_date索引为下面的查询做排序，从EXPLAIN中可以看到没有出现文件排序（filesort）操作：⁷

即使ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这正是因为索引的第一列被指定为了一个常数。

还有很多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀。

下面这个查询也没有问题⁸，因为ORDER BY使用的两列就是索引的最左前缀：

下面是一些不能使用索引做排序的查询。
下面这个查询使用了两种不同的排序方向，但是索引中的列都是按正序排序的：

在下面这个查询的ORDER BY子句中，引用了一个不在索引中的列：

下面这个查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀：

下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：

这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：

在下面这个例子中，理论上是可以使用索引进行联接排序的，但由于优化器在优化时将film_actor表当作联接的第二张表，所以实际上无法使用索引：

使用索引做排序的另一个最重要的场景是，查询语句中同时有ORDERBY和LIMIT子句的情况。

冗余和重复索引

不幸的是，MySQL允许在相同列上创建多个相同的索引。虽然MySQL会抛出一个警告，但是并不会阻止你这么做。MySQL需要单独维护重复的索引，优化器在优化查询的时候也需要逐个地进行评估，这会影响性能，同时也浪费磁盘空间。

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

有时，还是会在不经意间创建重复索引，例如下面的代码：

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常，这样做是完全没有必要的，除非是在同一列上创建不同类型的索引来满足不同类型查询的需求。⁹

冗余索引和重复索引有一些不同。如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，因此，索引（A，B）也可以当作索引（A）来使用（这种冗余只是对B-tree索引来说的）。但是如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列。另外，如果新建的是其他不同类型的索引（例如，哈希索引或者全文索引），那么无论覆盖了哪些索引列，也不会是B-tree索引的冗余索引。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A，B）而不是扩展已有的索引（A）。还有一种情况是，将一个索引扩展为（A，ID），其中ID是主键，因为主键列已经包含在二级索引中了，所以这也是冗余的。

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

例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那么性能可能会急剧下降。特别是当有查询把这个索引当作覆盖索引使用的时候。

考虑如下的userinfo表：

这个表有1，000，000行，每个state_id值大概有20，000条记录。在state_id列有一个索引，可以用于优化如下的查询，假设该查询名为Q1：

一个简单的基准测试表明，该查询的执行速度大概是每秒115次（QPS）。还有一个相关查询需要检索几列的值，而不是只统计行数，假设名为Q2：

对于这个查询，测试结果QPS小于10。¹⁰提升该查询性能的最简单办法就是扩展索引为（state_id，city，address），让索引能覆盖查询：

索引扩展后，Q2运行得更快了，但是Q1却变慢了。如果我们想让两个查询都变得更快，就需要两个索引，但这样一来原来的单列索引就是冗余的了。表1-1显示了这两个查询在不同的索引策略下的详细结果。

表1-1：不同索引策略下SELECT查询的QPS测试结果

	只有state_id	只有state_id_2	同时有state_id 和state_id_2
Query 1	108.55	100.33	107.97
Query 2	12.12	28.04	28.06

建两个索引的缺点是，会带来一定的维护成本。表1-2展示了向表中插入100万行数据所需要的时间。

表1-2：使用不同索引策略插入100万行数据的速度

	只有state_id	同时有state_id 和state_id_2
InnoDB，两个索引都有足够的内存	80秒	136秒

可以看到，表中的索引越多，插入的速度越慢。一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后达到了内存瓶颈的时候。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以了，但首先要做的是找出这样的索引。可以针对INFORMATION_SCHEMA表编写各种复杂的查询来识别这类索引，也有更简单的技术，比如可以使用Percona工具箱中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复索引。

在删除或扩展索引的时候要非常小心。回忆一下，在前面的InnoDB的示例表中，因为二级索引的叶子节点包含了主键值，所以在列（A）上的索引就相当于在（A，ID）上的索引。如果有像WHERE A=5 ORDER BY ID这样的查询，这个索引会很有用。但如果将索引扩展为（A，B），则实际上就变成了（A，B，ID），那么上面查询的ORDER BY子句就无法使用该索引做排序，而只能用文件排序了。所以，建议使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。

对于上述的两种情况，都可以考虑使用MySQL 8.0的不可见索引特性，而不是直接删除索引。要使用这个特性，可以通过ALTER TABLE语句，改变索引的一个标志位，使得优化器在确定执行计划时，忽略该索引。如果你发现计划删除的索引依旧有非常重要的作用，可以直接把索引改成可见，而不需要重新构建该索引。

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议删除。¹¹
找到未使用索引的最好办法就是使用系统数据库performance_schema和sys。在sys数据库中，在table_io_waits_summary_by_index_usage视图中可以非常简单地知道哪些索引从来没有被使用过：

---

1. MySQL优化器是一个非常神秘且强大的“装置”，不过还好，它的“强大”应该略胜于“神秘”一筹。鉴于它查找最优执行计划的方式，你应该更多依靠EXPLAIN和具体业务负载情况，来决定最优的执行策略。 ↩︎

2. Oracle用户可能更熟悉索引组织表（index-organized table）的说法，它们实际上是一样的意思。 ↩︎

3. 这并非总成立，很快就可以看到。 ↩︎

4. 顺便提一下，并不是所有的非聚簇索引都能做到一次索引查询就找到行。当行更新的时候，它可能无法被存储在原来的位置，这会导致表中出现行的碎片化或者移动行并在原位置保存“向前指针”，这两种情况都会导致在查找行时需要做更多的工作。 ↩︎

5. 很容易把Extra列的“Using index”和type列的“index”搞混。其实这两者完全不同，type列和覆盖索引毫无关系，它只表示这个查询访问数据的方式，或者说是MySQL查找记录的方式。MySQL手册中称之为联接类型。 ↩︎

6. 如果需要按不同方向做排序，一个技巧是存储该列值的反转串或者相反数。 ↩︎

7. MySQL在这里称其为文件排序（filesort），其实并不一定使用磁盘文件，只有无法在内存中完成排序时才会用到磁盘文件。 ↩︎

8. 这里需要注意，虽然这里可以使用索引进行排序，但是在实际使用8.0.25版本测试的时候，除非强制使用FORCE INDEX FOR ORDER BY，否则，优化器不会使用索引进行排序——所以，需要特别注意，有时候优化器可能会与预期的行为不相同，总是需要通过EXPLAIN语句来确认实际的执行计划。 ↩︎

9. 如果索引类型不同，则不算是重复索引。例如，实际情况中经常会创建KEY（col）和FULLTEXTKEY（col）这两种索引。 ↩︎

10. 这里使用了全内存的案例，如果当表逐渐变大，导致工作负载变成I/O密集型时，性能测试结果差距会更大。对于COUNT()查询，覆盖索引性能提升100倍也是很有可能的。 ↩︎

11. 有些索引的功能相当于唯一约束，虽然该索引一直没有被查询使用，却可能是用于避免产生重复数据的。 ↩︎