创建高性能的索引

索引基础

索引：存储引擎用于快速找到记录的一种数据结构。

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

创建一个包含两列的索引，和创建两个只包含一列的索引是大不相同的。

索引的类型

B-Tree 索引

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

• 全值匹配
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询（覆盖索引）

B-Tree 索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引——最左前缀原则。
• 不能跳过索引中的列，一旦跳过只会使用前面的列。
• 如果某个列属于范围查询，则其右边的索引将失效。由此可以看出，建立索引时，列的顺序至关重要。

有些限制并不是 B-Tree 数据结构本身的问题，而是 MySQL 优化器和存储引擎使用索引的方式导致的，这部分限制可能会在未来的某个版本被解决。

哈希索引

哈希索引基于哈希表实现，只有精确匹配所有的列才有效。

对于每一行数据，存储引擎都会对索引列计算一个哈希码，哈希码是一个较小的值，不同的键值计算的哈希码也不同，哈希索引将所有的哈希码存储在索引库中，同时在哈希表中保存指向对应数据行的指针。

哈希索引中的排序是针对哈希码排序，而非针对索引列排序，所以无法利用哈希索引进行范围查询。

哈希索引的速度很快，但是有它的限制：

• 哈希索引只包含哈希码和行指针，不存储字段值，所以不能利用索引来避免读取行。
• 哈希索引无法用于排序
• 哈希索引必须列全匹配，无法使用部分列，因为是根据所有的列来计算哈希码的。
• 哈希索引只支持等值比较，不支持范围查询
• 如果哈希冲突很多的话，索引维护的代价会很高。

InnoDB 有一个特殊的功能——“自适应哈希索引”。当 InnoDB 注意到某些索引的值被使用的非常频繁时，它会在内存中基于 B-Tree 之上再创建一个哈希索引，这样可以让 B-Tree 具有一些哈希索引的优点。

创建自定义哈希索引

如果存储引擎不支持哈希索引，可以自己模拟实现，这样就可以使用很小的索引来为超长的列建立索引。

在 B-Tree 的基础上，再创建一个伪哈希索引，索引的底层实现还是 B-Tree，不过索引的值不再是列值，而是列进过哈希计算后的哈希码。如果列值本身数据很大，建立索引后会导致索引很大，效率低下。使用哈希码来作为索引值可以大大减少索引的空间和效率。

MySQL 自带的哈希函数 CRC32()，速度很快，但是当表中的数据很多时，容易出现哈希冲突。

如果哈希冲突过多，则可以考虑自己实现一个 64 位的哈希函数，一个简单的方案就是使用 MD5 码的部分字符来作为自定义哈希函数。

SELECT CONV(RIGHT(MD5('admin'),16) ,16,10);
-- 4866502297590439875

使用哈希索引检索时，应该带上列的原始值，这样即使发生哈希冲突，仍然可以将冲入的数据与原始值进行比较以获取到正确的数据。

空间数据索引（R-Tree）

MyISAM 表支持空间索引，用来存储地理位置。和 B-Tree 索引不同，这类索引无须前缀查询。

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

全文索引的匹配方式和其他类型的索引完全不同，全文索引适用于 MATCH AGAINST，而不是 WHERE 条件操作。

比较鸡肋的是，在 MySQL5.7.6 之前，全文索引并不支持中文，5.7.6 之后也必须通过插件来支持中文。

事实上项目中也不太常用全文索引，真正要做搜索一般用 Lucene、Solr、或 ElasticSearch。

索引的优点

索引可以让 MySQL 快速定位到数据行，但这并不是索引的全部，因为索引是有序的，还带来了其他一些优点，总结如下：

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

索引并不总是最好的工具，索引也需要 MySQL 单独维护，带来了系统开销和磁盘的占用。

当索引过多时，也会导致插入和更新的效率变得很低。

对于一些小型表，索引则意义不大，全表扫描即可，对于中大型的表，索引可以大大提升查询的性能。对于特大型的表，建立和使用索引的代价也随之增大，索引将不能解决问题，需要使用分区技术。

高性能的索引策略

评价索引的“三星系统”：

• 一星：索引将相关的记录放到一起。
• 二星：索引中的数据顺序和查找中的排列顺序一致。
• 三星：索引中的列包含查询中的全部列。

独立的列

索引列不能是表达式的一部分，也不能是函数的参数。

下列两个例子将无法使用索引：

SELECT * FROM person WHERE person_id + 1 = 100;
SELECT * FROM person WHERE LEFT(name,3) = 'Lisa';

前缀索引和索引选择性

索引字符串很长的列，会让索引变得又大又慢。

可以利用上文中提到了哈希索引，除此之外，还有一些方法。

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

索引选择性：不重复的值索引/总记录数，选择性越高，查询效率越高，唯一索引的选择性=1，性能是最好的。

对于 BLOB 和 TEXT 大文本，MySQL 只能使用前缀索引，不可能索引列的完整文本内容。

后缀索引：MySQL 不支持后缀索引，但有时后缀索引也很有用。可以把字符反过来存储，并建立前缀索引来实现。

多列索引

很多人常犯的错误就是：为每个列创建独立的索引，或按照错误的顺序创建多列索引。

“把 WHERE 条件里面的列都加上索引”，我们经常听到这种话，实际上这个建议是非常错误的，这样子最好也只能是“一星”索引，其性能比起真正最优的索引还差好几个数量级。

MySQL5.0 加入了“索引合并”的策略，一定程度上可以使用多个索引列来定位数据行，更早的版本却只能使用单列索引。

通过 EXPLAIN 的 Extra 可以看看 MySQL 为我们做了哪些“索引合并策略”，一旦出现这种策略，说明我们的索引建立的很糟糕。

• 服务器对多个索引做相交操作时，意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引。
• 服务器对多个索引做联合操作时，通常需要消耗大量的 CPU 和内存资源在算法的缓存、排序和合并上。
• 设计的不好的索引不但会消耗更多的 CPU 和内存资源，还会影响查询的并发性。

可以通过参数 optimizer_switch 来关闭索引合并功能。

选择合适的索引列顺序

在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，然后依次是后面的列。

经验法则：将选择性最高的的列放到索引最前列。

当不考虑排序和分组时，将选择性最高的列放在前面通常是更好的。这时候索引的作用只是用于 where 条件的查找。这种情况下，索引可以快速过滤出需要的行。

聚簇索引

聚簇索引不是单独的索引类型，而是一种数据存储格式。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。

聚簇：表示数据行和相邻的键值紧凑的存储在一起。

因为无法同时把多个数据行存放在不同的地方，所以一个表只能有一个聚簇索引（覆盖索引可以模拟多个聚簇索引的情况）。

优点：

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

缺点：

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

主键越大时，二级索引也会越大。

存储引擎需要先找到二级索引，然后根据二级索引的主键值去聚簇索引中找对应的行。

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

如果 InnoDB 表没什么数据需要聚集，那么可以设置一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用自增列。这样可以保证数据行是按照顺序写入的，对于根据主键做关联操作的心梗也会更好。

尽量避免随机的聚簇索引，特别是对于 I/O 密集型应用。

使用 UUID 作为聚簇索引会非常糟糕：使得聚簇索引的插入变得完全随机，数据完全没有任何聚集特性，顺序 I/O 变为随机 I/O，严重影响性能。由于不是顺序插入，随机的插入会导致“页分裂”和索引碎片，造成索引文件太大。

UUID 作为聚簇索引的缺点总结如下：

• 由于插入的顺序是随机的，导致大量的随机 I/O。
• 由于写入是乱序的，导致 InnoDB 不得不做频繁的“页分裂”操作，以便为新的行分配空间。
• 由于频繁的页分裂，页会变得稀疏并被不规则填充，导致大量的索引碎片。

顺序插入就没有缺点吗？

• 主键顺序的插入会造成明显的锁争用。
• 因为所有的行都发生在这里，并发插入可能导致 间隙锁 竞争。
• 自增列的锁机制。

覆盖索引

除了根据 where 条件来创建索引外，设计优秀的索引还应该考虑到整个查询。

索引是一种高效“查找”数据的方式，但是 MySQL 也可以使用索引直接来“获取”数据。

如果索引的叶节点已经包含了查询的所有字段，那还有什么理由需要回表进行查询呢？

覆盖索引：索引中包含查询的所有字段的值。

有了覆盖索引可以直接从索引中获取数据，而无需“回表”，能够极大的提升性能：

• 索引条目远小于数据行大小，所以如果只需要读取索引，MySQL 会极大的减少数据访问量，这对缓存的负载非常重要。
• 索引按照列值顺序存储（至少单页是如此），所以对于 I/O 密集型范围查询比从随机从磁盘读取每一行数据的 I/O 要少得多。
• 由于 InnoDB 的聚簇索引，覆盖索引特别有用。InnoDB 的二级索引如果可以覆盖索引，就无需再去聚簇索引中根据主键查找数据行。

MySQL 只支持 B-Tree 做覆盖索引，因为其他索引不会存储索引列的值。

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

EXPLAIN
SELECT id FROM person WHERE id = '42ff47f0-9508-49a8-b103-7c6f190050ed';
-- Using index

由于查询的列索引中已全部包含，则不需要回表，显示“Using index”。

使用索引扫描来做排序

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

如果 EXPLAIN 出来的 type 列显示“index”，说明使用了索引扫描来排序。

扫描索引本身很快，因为索引是有序的，只需一条一条往下读即可。但是如果索引不能覆盖查询的话，索引需要带着主键值回表查询一次，通常这类查询都是随机 I/O，就导致 通过索引排序会比全表顺序扫描更慢。

如果一个索引既可以满足排序，又可以覆盖查询，这样是最完美的。

只有当索引的列顺序和 ORDER BY 的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能使用索引来对结果做排序。

排序和查询一样，也需要满足最左前缀原则。

前缀压缩索引

MyISAM 使用前缀压缩来减少索引的大小，使得更多的索引可以被加载进内存。

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

冗余和重复索引

MySQL 允许在同一个列上创建多个索引，包括在主键列创建索引，称为“重复索引”。

MySQL 会单独维护这些索引，影响系统的性能，发现“重复索引”应该立即删除。

重复索引没有任何意义，反而会降低系统的性能，没有存在的必要，“冗余索引”却不同。

冗余索引发生在新建索引的时候，例如：现有索引 A，不想扩展 A，于是新建了索引（A，B）。那么索引 A 就是冗余索引。

大多数情况下不需要冗余索引。

未使用的索引

服务器可能存在一些永远用不到的索引，不管它们是如何产生的，这样的索引只能是服务器的累赘，建议删除。

索引和锁

索引可以让查询锁定更少的行，减小系统开销，提高并发能力。

InnoDB 只有在访问行时，才会对其加锁。索引可以减少访问行的数量，从而减少锁的数量。

但这只有 InnoDB 在存储引擎层能过滤掉不需要的行时才有效，当索引不能有效过滤行时，那么 InnoDB 检索到数据返回给服务器后才能应用 where 子句，而此时行已经被锁定。

即使使用了索引，InnoDB 也会锁住一些不需要的行，如果没用索引，将会更加糟糕，会锁住所有的行。

InnoDB 在二级索引上使用共享锁，访问主键索引使用排它锁。这消除了使用覆盖索引的可能性，并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢很多。

避免范围查询

通过 EXPLAIN 分析，MySQL 将大于、小于和 IN 的 type 都描述为“range”。

但两者效率其实不同：对于多列索引，一旦使用了范围查询，就无法使用后面的索引列了，但是 IN 没有这个限制。

找到并修复损坏的表

损坏的索引会导致查询返回错误的结果，或者莫须有的主键冲突等问题，严重时还会导致数据库崩溃。

可以尝试使用 CHECK TABLE 来检查表是否发生了损坏，CHECK TABLE 通常能找出大多数表和索引的错误。

可以使用 REPAIR TABLE 命令来修复损坏的表，

减少索引和数据碎片

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

叶子节点在磁盘上的物理分布如果是顺序且紧密的，那么查询的性能无疑会更好。

表的数据存储也可能碎片化，数据存储的碎片化比索引碎片更加复杂。

• 行碎片 数据行被存储在多个地方的多个片段中，即使只访问一行记录，也会导致性能下降。

• 行间碎片 逻辑上顺序的页，在磁盘上不是顺序存储的。

• 剩余空间碎片 数据页中有大量剩余空间，导致服务器读取大量不需要的数据，浪费资源。

可以通过执行 OPTIMIZE TABLE 来重新整理数据。

对于不支持 OPTIMIZE TABLE 的存储引擎，可以使用不做任何操作的 ALTER TABLE 来重建表。

尾巴

在 MySQL 中，大多数使用 B-Tree 索引。在选择索引和利用索引查询时，请记住如下三个原则：

• 单行访问是很慢的，特别是在机械硬盘中。最好读取的块中包含尽可能多的需要的行，使用索引可以建立位置引用以提升效率。
• 顺序访问范围数据很快的。第一、顺序 I/O 不需要多次磁盘寻道，比随机 I/O 快很多。第二、服务器按照顺序读取数据，就无需额外排序。
• 索引覆盖查询是很快的，如果一个索引包含了查询的所有列，存储引擎就不用再回表查询，避免了单行访问。