MySQL 深入浅出系列04—高性能的索引策略

1 独立的列

索引列不能是表达式的一部分。

2 前缀索引和索引选择性

索引选择性指不重复的索引值(基数)和数据表的记录总数 (#T) 的比值，范围从 1/#T 到 1 之间。索引选择性越高，则查询效率越高。可以在查找时过滤更多行。唯一索引选择性是1 ，是最好的索引选择性，性能也是最好的。

BLOB TEXT 或很长的 VARCHAR 类型列必须用前缀索引，因为 MySQL 不允许索引这些列的完整长度。诀窍足够长保证高选择性又不能太长。

前缀索引缺点：无法使用前缀索引做 ORDER BY 和 GROUP BY 也无法使用覆盖扫描。一个常⻅的场景是针对很⻓的十六进制唯一 ID 使用前缀索引。

3 多列索引

索引合并策略

OR 条件的联合 (union)，AND 条件的相交 (intersection)，组合前两种情况的联合及相交。

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

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

4 选择合适的索引顺序

本节内容适用于 B-Tree 索引 ， 哈希或者其他类型的索引并不会像 B-Tree 索引一样按顺序存储数据。

在一个多列 B -Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次第二列。如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。但通常不如避免随机 IO 和排序那么重要。

5 聚簇索引

聚簇索引是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑存储。因为无法同时把数据行存放在两个不同的地方，所有一个表只能有一个聚簇索引。图展示了聚簇索引的记录如何存放。叶子页包含行的全部数据，节点页只包含了索引列。图中的索引列办好的是整数值。
InnoDB 将通过主键聚集数据，这也就是说图中的 “ 被索引的列〞就是主键列 。如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个⻚面中的记录。包含相邻键值的页面可能会相距甚远。

优点：可以关联相关数据。数据访问更快。使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

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

InnoDB 和 MyISAM 的数据分布对比

MyISAM 的数据分布。MyISAM 的数据分布非常简单，MyISAM 按照数据插入的顺序存储在磁盘上。行旁边显示行号，从零递增，行是定长的。MyISAM 可以从表的开头跳过所需的字节找到需要的行。这种分布容易创建索引。并不是所有非聚簇索引都可以一次查询就能找到，有更新的时候，会在原来位置保存向前指针。
InnoDB 的数据分布。聚簇索引的每一个叶子节点都包含了主键值、事务 ID、用于事务和 MVCC 的回滚指针以及所有的剩余列。 如果主键是一个列前缓索引，InnoDB 也会包含完整的主键列和剩下的其他列。InnoDB 的二级索引和聚簇索引很不相同。InnoDB 二级索引的叶子节点中存储的不是 “行指针”，而是主键值，并以此作为指向行的 “指针”。 这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空问，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个 “指针”。

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

如果正在使用 InnoDB 表并且没有什么数据需要聚集，那么可以定义一个代理键 (surrogate key) 作为主键，这种主键的数据应该和应用无关，最简单的方法是使用 AUTO_ I NCREMENT 自增列。这样可以保证数据行是按顺序写人，对于根据主键做关联操作的性能也会更好。最好避免随机的 (不连续且值的分布范围非常大)聚簇索引，特别是对于 I/O 密集型的应用。如从性能的角度考虑，使用 UUID 来作为聚筷索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

当达到页的最大填充因子时 (InnoDB默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改)，下 一条记录就会写人新的页中。

使用随机的 UUID 的缺点：新的主键值不一定比插入之前的大无法简单按序插入，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配空间。会增加很多额外工作，并导致数据分布不够优化。

1. 写人的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中， InnoDB 在插人之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机 I/O 。
2. 因为写人是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。 页分裂会导致移动大量数据， 一次插人最少需要修改三个页而不是一个页。
3. 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。
4. 插入后也许需要做一次 OPTIMIZETABLE 来重建表并优化页的填充。从这个案例可以看出，使用 InnoDB 时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插人新行。
5. 顺序主键什么时候会带来最坏结果：对于高并发工作负载，在 InnoDB 中按主键顺序插入可能会造成明显的争用。主键的上界会成为 “ 热点” 。因为所有的插入都发生在这里，所以并发插人可能导致间隙锁竞争。另一个热点可能是 AUTO_INCREMENT 锁机制。

6 覆盖索引

好处：

1. 索引条目通常远小于数据行大小。覆盖素引对 I/O 密集型的应用也有帮助，因为索引比数据更小， 更容易全部放人内存中。

2. 因为索引是按照列值顺序存储的 (至少在单个页内是如此)，所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。对于某些存储引擎，如 MyISAM 和 Percona XtraDB，甚至可以通过 OPTIMIZE 命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。

3. 一些存储引擎如 MyISAM 在内存中只缓存索引，数据依赖于操作系统缓存，访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
   由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

4. 覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支 持覆盖索引。

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

延迟关联，因为延迟了对列的访问。在查询的第一阶段 MySQL 可以使用覆盖索引，在 FROM 子句的子查询中找到匹配的 prod_id，然后根据这些 prod_ id 值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

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

7 使用索引扫描来做排序

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

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

MySQL 可以使用同一个索引既满足排序，又用于查找行，是最好的结果。只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向一样，才能使用索引来对结果做排序。如果查询需要关联多张表 ， 则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则， MySQL 都需要执行排序操作，而无法利用索引排序。有一种情况下 ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候 。 如果 WHERE 子句或者 JOIN 子句中对这些列指定了常量， 就可以“ 弥补” 索引的不足。

8 压缩(前缀压缩)索引

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

MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前级的字节数和剩余的不同后缀部分，把这部分存储起来即可 。 如索引块中的第一个值是“perform”，第二个值是“ performance”，那么第二个值的前级压缩后存储的是类似“7,ance” 这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

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

9 冗余和重复索引

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

冗余索引和重复索引有一些不同。如果创建了索引 (AB)，再创建索引 (A) 就是冗余余索引， 因为这只是前 一个索引的前缀索引。因此索引 (A,B) 也可以当作索引 (A) 来使用 (这种冗余只是对 B-Tree 索引来说的)。但是如果再创建索引 (B,A)，则不是冗余索引，索引 (B) 也不是，因为B不是索引 (A,B) 的最左前级列。另外，其他不同类型的索引 (例如哈希索引或者全文索引)也不会是 B-Tree 索引的冗余索引，而无论覆盖的索引列是什么。冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引 (A,B)，而不是扩展已有的索引 (A)。还有 一种情况是将一个索引扩展为 (A,ID)，其中 ID 是主键， 对于 InnoDB 来说主键列已经包含在二级素引中了，所以这也是冗余的。大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

10 未使用的索引

服务器永远不用的索引，完全是累赘，建议考虑删除。有两个工具可以帮助定位未使用的索引。最简单有效的办法是在 Percona Server 或者 MariaDB 中先打开 userstates 服务器变量(默认是关闭)， 然后让服务器正常运行一段时间，再通过查询 INFORMATION_SCHEMA. INDEX_STATISTICS 就能查到每个索引的使用频率。还可以使用 Percona Toolkit 中的 pt-index-usage，该工具可以读取查询日志， 对日志中的每条查询进行 EXPLAIN 操作，然后打印出关于索引和查询的报告。

11 索引和锁

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

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

底层存储引擎的操作是“从索引的开头开始获取满足条件 actor_id < 5 的记录”，服务器并没有告诉 InnoDB 可以过滤第 1 行的 WHERE 条件。注意到 EXPLAIN 的 Extra 列出现了“Using where”，这表示 MySQL 服务器将存储引擎返回行以后再应用 WHERE 过滤条件。即使使用了索引，InnoDB 也可能锁住一些不需要的数据。