MySQL Query 的优化—合理设计并利用索引

前言

索引，可以说是数据库相关优化尤其是在 Query 优化中最常用的优化手段之一了。但是很多人在大部分时候都只是大概了解索引的用途，知道索引能够让 Query 执行的更快，而并不知道为什么会更快。尤其是索引的实现原理，存储方式，以及不同索引之间的区别等就更不是太清楚了。正因为索引对我们的 Query 性能影响很大，所以我们更应该深入理解 MySQL 中索引的基本实现，以及不同索引之间的区别，才能分析出如何设计出最优的索引来最大幅度的提升 Query 的执行效率。

在 MySQL 中，主要有四种类型的索引，分别为：B-Tree 索引，Hash 索引，Fulltext 索引和 R-Tree 索引。

B-Tree 索引

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检索中有非常优异的表现。

在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（PrimaryKey），另外一种则是和其他存储引擎（如 MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index。下面我们通过图示来针对这两种索引的存放形式做一个比较。

图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在Root Node 和 Branch Nodes 方面都还是完全一样的。而 Leaf Nodes 就出现差异了。在 Primary Key中，Leaf Nodes 存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而 Secondary Index 则和其他普通的 B-Tree 索引没有太大的差异，只是在Leaf Nodes 除了存放索引键的相关信息外，还存放了 Innodb 的主键值。

所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来访问数据的话，Innodb 首先通过相应的索引键检索到 Leaf Node之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。

MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在 Leaf Nodes 上面除了存放索引键信息之外，再存放能直接定位到MyISAM 数据文件中相应的数据行的信息（如 Row Number），但并不会存放主键的键值信息。

Hash 索引

Hash 索引在 MySQL 中使用的并不是很多，目前主要是 Memory 存储引擎使用，而且在 Memory 存储引擎中将 Hash 索引作为默认的索引类型。这里不再做介绍。

Full-text 索引

Full-text 索引也就是我们常说的全文索引，目前在 MySQL 中仅有 MyISAM 存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有 CHAR VARCHAR 和 TEXT 这三种数据类型的列可以建 Full-text 索引。

一般来说，Fulltext 索引主要用来替代效率低下的 LIKE ‘%*%’ 操作，而且能通过多字段组合的 Full-text 索引一次全模糊匹配多个字段。

Full-text 索引和普通的 B-Tree 索引的实现区别较大，虽然他同样是以 B-Tree 形式来存放索引数据，但是他并不是通过字段内容的完整匹配，而是通过特定的算法，将字段数据进行分隔后再进行的索引。一般来说 MySQL 系统会按照四个字节来分隔。在整个 Full-text 索引中，存储内容被分为两部分，一部分是分隔前的索引字符串数据集合，另一部分是分隔后的词（或者词组）的索引信息。所以，Full-text 索引中，真正在 B-Tree 索引细细中的并不是我们表中的原始数据，而是分词之后的索引数据。在 B-Tree 索引的节点信息中，存放了各个分隔后的词信息，以及指向包含该词的分隔前字符串信息在索引数据集合中的位置信息。

此外，有一点是需要大家注意的，MySQL 目前的 Full-text 索引在中文支持方面还不太好，需要借助第三方的补丁或者插件来完成。而且 Full-text 的创建所消耗的资源也是比较大的，所以在应用于实际生产环境之前还是尽量做好评估。

R-Tree 索引

R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问题。这里不再做介绍。

索引的利弊与如何判定是否需要索引

索引的利处：索引能够提高数据检索的效率，降低数据库的 IO 成本；索引还有一个非常重要的用途，那就是降低数据的排序成本，极大的降低 CPU 资源的消耗。

索引的弊端：修改添加了索引的列数据时，需要同时修改索引信息。此外，索引是需要占用存储空间的。

1、如何判定是否需要创建索引：
并没有一个非常明确的定律可以清晰的定义出什么字段应该创建索引什么字段不该创建索引，但我们还是能够找到几点基本的判定策略来帮助我们分析是否需要创建索引。
1、较频繁的作为查询条件的字段应该创建索引
2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件（如状态字段）；
3、更新非常频繁的字段不适合创建索引
4、不会出现在 WHERE 子句中的字段不该创建索引；

2、单键索引还是组合索引
经常会有多个字段一起作为查询过滤条件存在于 WHERE 子句中。在这种时候，我们就必须要作出判断，是该仅仅为过滤性最好的字段建立索引还是该在所有字段（过滤条件中的）上面建立一个组合索引呢？

对于这种问题，很难有一个绝对的定论，我们需要从多方面来分析考虑，平衡两种方案各自的优劣，然后选择一种最佳的方案来解决。组合索引中因为有多个字段的存在，理论上被更新的可能性肯定比单键索引要大很多，这样可能带来的附加成本也就比单键索引要高。但是，当我们的 WHERE 子句中的查询条件含有多个字段的时候，通过这多个字段共同组成的组合索引的查询效率肯定比仅仅只用过滤条件中的某一个字段创建的索引要高。

可能有些朋友会说，那我们可以通过创建多个单键索引啊。MySQL Query Optimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或者更多的索引通过 INDEX_MERGE 来优化查询，可能所收到的效果并不会比选择其中某一个单键索引更高效。因为如果选择通过 INDEX_MERGE 来优化查询，就需要访问多个索引，同时还要将通过访问到的几个索引进行 merge操作，所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。

在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下都能过滤出90%以上的数据，而且其他的过滤字段会存在频繁的更新，我一般更倾向于创建组合索引，尤其是在并发量较高的场景下更是应该如此。因为当我们的并发量较高的时候，即使我们为每个 Query 节省很少的 IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

当然，我们创建组合索引并不是说就需要将查询条件中的所有字段都放在一个索引中，我们还应该尽量让一个索引被多个 Query 语句所利用，尽量减少同一个表上面索引的数量，减少因为数据更新所带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

此外，MySQL 还为我们提供了一个减少优化索引自身的功能，那就是前缀索引。在 MySQL 中，我们可以仅仅使用某个字段的前面部分内容做为索引键来索引该字段，来达到减小索引占用的存储空间和提高索引访问的效率。当然，前缀索引的功能仅仅适用于字段前缀比较随机重复性很小的字段。

3、Query 的索引选择

在有些场景下，我们的 Query 由于存在多个过滤条件，而这多个过滤条件可能会存在于两个或者更多的索引中。在这种场景下，MySQL Query Optimizer 一般情况下都能够根据系统的统计信息选择出一个针对该 Query 最优的索引完成查询，但是在有些情况下，可能是由于我们的系统统计信息的不够准确完整，也可能是 MySQL Query Optimizer 自身功能的缺陷，会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。在这种时候，我们就不得不通过认为干预，在 Query 中增加 Hint 提示 MySQL Query Optimizer 告诉他该使用哪个索引而不该使用哪个索引，或者通过调整查询条件来达到相同的目的。

4、下面是我对于选择合适索引的几点建议，并不一定在任何场景下都合适，但在大多数场景下还是比较适用的。

1. 对于单键索引，尽量选择针对当前 Query 过滤性更好的索引；
2. 在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好；
3. 在选择组合索引的时候，尽量选择可以能够包含当前 Query 的 WHERE 子句中更多字段的索引；
4. 尽可能通过分析统计信息和调整 Query 的写法来达到选择合适索引的目的而减少通过使用Hint 人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。

5、MySQL 中索引的限制
下面列出了目前 MySQL 中索引使用相关的限制。
1. MyISAM 存储引擎索引键长度总和不能超过 1000 字节；
2. BLOB 和 TEXT 类型的列只能创建前缀索引；
3. 过滤字段使用了函数运算后（如 abs(column)），MySQL 无法使用索引；
4. Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引；
5. 使用 LIKE 操作的时候如果条件以通配符开始（ ‘%abc…’）MySQL 无法使用索引；