MySQL高性能的索引策略——《高性能MySQL》

读《高性能MySQL》第三版，笔记。

正确地创建和使用索引是实现高性能查询的基础。
高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定的行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧，则需要持续不断地学习。

接下来几个小节帮助理解如何高效地使用索引。

---

1. 独立的列

我们通常会看到一些查询不当地使用索引，或者使用 MySQL 无法使用已有的索引。如果查询中的列不是独立的，则 MySQL 就不会使用索引。“ 独立的列 ” 是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面这个查询无法使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

还有一种常见的错误：

SELECT ... FROM TO_DAY(CURRENT_DATE) - TO_DAY(date_col) <=10;

---

2. 前缀索引和索引的选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是模拟哈希索引。但是有时候这样做还不够，还可以做些什么呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。
但是这样也会降低索引的选择性。

索引的选择性：

属性	索引的选择性
定义	不重复的索引值（也称为基数，cardinality）和 数据表的记录总数（#T）的 比值
范围	从 1/#T 到 1 之间
规律	越高则查询的效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行
最好	唯一索引的选择是 1，这是最好的索引选择性，性能也是最好的

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

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

举个栗子：

计算完整列的选择性：

mysql> select count(distinct name)/count(name) from city;
+----------------------------------+
| count(distinct name)/count(name) |
+----------------------------------+
|                           0.9809 |
+----------------------------------+
1 row in set (0.02 sec)

计算不同前缀长度的选择性：

mysql> select count(distinct left(name,3))/count(name) as sel3,
    -> count(distinct left(name,4))/count(name) as sel4,
    -> count(distinct left(name,5))/count(name) as sel5,
    -> count(distinct left(name,6))/count(name) as sel6,
    -> count(distinct left(name,7))/count(name) as sel7 from city;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.3986 | 0.7279 | 0.8718 | 0.9257 | 0.9475 |
+--------+--------+--------+--------+--------+
1 row in set (0.02 sec)

---

3. 多列索引

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

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

MySQL 5.0 和更新版本引入了一种叫 “ 索引合并 ”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

查询能够同时使用两个单例索引进行扫描，并将结果进行合并。这种算法有三个变种：OR 条件的联合（union），AND 条件的相交（intersection），组合前两种情况的联合及相交。

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

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

如果在 EXPLAIN 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭索引合并功能。也可以使用 IGNORE INDEX提示让优化器忽略掉某些索引。

---

4. 选择合适的索引列顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。（顺便说明，本节内容适用于 B-Tree 索引；哈希或者其他索引并不会像 B-Tree 索引一样按顺序存储数据）

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

多列索引的列顺序至关重要。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。

这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机 IO 和排序那么重要，考虑问题需要更全面。（场景不同则选择不同，没有一个放之四海而皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要。）

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

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

---

5. 聚簇索引

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

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

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

叶子页包含了行的全部数据，但是节点页只包含了索引列。

如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。 InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细考虑聚簇索引，尤其是将表的存储引擎从 InnoDB 改成其他引擎的时候（反过来也一样）。

聚簇索引优点：

• 可以把相关数据保存在一起。
• 数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引缺点：

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

为什么二级索引需要两次索引查找？

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

5.1 InnoDB 和 MyISAM 的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布页有区别，通常会让人感到困扰和意外。

5.1.1 MyISAM 的数据分布

MyISAM 的数据分布非常简单，所以先介绍它。MyISAM 按照数据插入的顺序存放在磁盘上。如下图。

在行的旁边显示了行号，从 0 开始递增。因为行是定长的，所以 MyISAM 可以从表的开头跳过所需的字节找到需要的行。（MyISAM 并不总是使用 “ 行号 ”，而是根据定长还是变长的行使用不同的策略）

这种分布方式很容易创建索引。索引中的每个叶子节点包含 “ 行号 ”。

二级索引和主键索引一样，区别是主键索引就是一个名为 PRIMARY 的唯一非空索引。

5.1.2 InnoDB 的数据分布

因为 InnoDB 支持聚簇索引，所以使用非常不同的方式存储同样的数据。如下图。

可以注意到该图显示了整个表，而不是只有索引，因为在 InnoDB 中，聚簇索引 “ 就是 ” 表，所以不像 MyISAM 那样需要独立的行存储。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和 MVCC（多版本控制）的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB 页会包含完整的主键列和剩下的其他列。

还有一点和 MyISAM 的不同是，InnoDB 的二级索引和聚簇索引很不相同。

InnoDB 二级索引的叶子节点中存储的不是 “ 行指针 ” ，而是主键值，并以此作为指向行的 “ 指针 ” 。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个指针。

下图描述 InnoDB 和 MyISAM 如何存放表的抽象图。

5.2 在 InnoDB 表中按主键顺序插入行

如果正在使用 InnoDB 表并且没有什么数据需要聚集，那么可以定义一个代理键（surrogate key）作为主键，这种主键的数据应该和应用无关，最简单的方法是使用 AUTO_INCREMENT 自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能页会更好。

最好避免随机的（不连续且值得分布范围非常大）聚簇索引，特别是对于 I/O 密集型得应用。
例如，从性能的角度考虑，使用 UUID 来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

因为新行的主键值不一定比之前插入的大，所以 InnoDB 无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。

总结下随机写入聚簇索引的缺点：

• 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机 I/O。
• 因为写入是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

使用 InnoDB 时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

顺序的主键什么时候回会造成更坏的结果？

对于高并发工作负载，在 InnoDB 按主键顺序插入可能会造成明显的争用。主键的上界会称为 “ 热点 ”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。
另一个热点可能是 AUTO_INCREMENT 锁机制；可以考虑重新设计表或者应用。或者更改 innodb_autoinc_lock_mode 配置。

---

6. 覆盖索引

通常大家都会根据查询的 WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是 WHERE 条件部分。

索引确实是一种查找数据的高效方式，但是 MySQL 页可以使用索引来直接获取列的数据，这样就不再需要读取数据行。

如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 “ 覆盖索引 ”。

覆盖索引是非常有用的工具，能够极大地提高性能。好处如下：

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

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做为覆盖索引。

不是所有存储引擎都支持覆盖索引。如，Memory 存储引擎就不支持覆盖索引。

当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在 EXPLAIN 的 Extra 列可以看到 “ Using index ” 的信息。

很容易把 Extra 列的 “ Using index ” 和 type 列的 “ index ” 搞混淆。其实这两者完全不同，type 列和覆盖索引毫无关系；它只是表示这个查询访问数据的方式，或者说是 MySQL 查找行的方式。MySQL 手册中称之为连接方式（join type）。官方详细说明

索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL 查询优化器会在执行查询前判断是否有一个索引能进行覆盖。

• 没有任何索引能覆盖这个查询。
• MySQL 不能在索引中执行 LIKE 操作。

可以通过延迟关联（deferred join）解决。延迟了对列的访问。

---

7. 使用索引扫描来做排序

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

如果 EXPLAIN 出来的 type 列的值为 “ index ”，则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的 “ Using index ” 搞混淆了）。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引就要回表查询一次对应的行。

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

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

有一种情况下 ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。

---

8. 压缩（前缀压缩）索引

MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。

默认只压缩字符串，但通过参数设置可以对整数做压缩。

MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。

例如，索引块中的第一个值是 perform ，第二个值是 performance，那么第二个值的前缀压缩后存储的是类似 7,ance 这样的形式。MyISAM 对行指针页采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还是不错，但是如果是倒序扫描——例如 ORDER BY DESC ——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

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

---

9. 冗余和重复索引

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

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

冗余索引通常发生在为表添加新索引的时候。

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

解决冗余索引和重复索引的方法很简单，删除这些索引就可以。但是在决定哪些索引可以被删除的时候要非常小心。

---

10. 未使用的索引

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

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

---

11. 索引和锁

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

• 首先，虽然 InnoDB 的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销。
• 其次，锁定超过需要的行会增加锁争用并减少并发性。

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

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

---

总结

理解索引是如何工作的非常重要，应该根据这些理解来创建最合适的索引，而不是根据一些诸如 “ 在多列索引中将选择性最高的列放在第一列 ” 或 “ 应该为 WHERE 子句中出现的所有列创建索引 ” 之类的经验法则以及推论。

那如何判断一个系统创建的索引是合理的呢？一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的 shema、SQL 和索引结构，判断是否需要查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机 I/O 访问数据，或者是有太多回表查询那些不在索引中的列的操作。

如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提高性能。如果不行，也可以看看是否可以重写该查询，将其转化成一个能够高效利用现有索引或者新创建索引的查询。