本文的原文地址在此:https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/,以下是译文。
-----------------------------------------------------------这是一条分割线-----------------------------------------------------------
我最近碰到了很多性能很糟糕的MySQL单表查询。原因很简单:索引创建得不正确,导致执行计划的性能低下。下面是一些能帮助你优化单表查询性能的要点。
免责声明:我会给出一些要点,但并不打算包含所有的可能情况。我100%相信你能够找到我的要点不适应的案例,但是我也相信大部分情况下,我写的这些要点会帮助到你。为了简单起见,我也不会讨论一些MySQL 5.6+版本的一些新特性,如Index Condition Pushdown。注意这些新特性会对响应时间有极大的影响(缩短或延长均有可能)。
索引能做什么?
索引主要做3件事:过滤(filter),排序或分组(sort/group),覆盖(cover)。前两个没什么好说的,但并不是每个人都知道什么叫“覆盖索引”。事实上这是个很简单的东西。
一个基本查询的工作流如下:
1. 使用索引以查找匹配的记录,并得到数据的指针。
2. 使用相关数据的指针。
3. 返回查询到的记录。
当可以使用覆盖索引时,索引将会覆盖查询中的所有字段,因此第二步将会被跳过,于是查询流程就变成了下面这样:
1. 使用索引以查找匹配的记录
2. 返回查询到的记录。
大部分情况下,索引都比较小,可以加载在内存中,而数据很大,无法全部存放在内存里:当使用覆盖索引时,可以避免很多的磁盘操作,因此对性能也会有极大的改善。
下面让我们来看一些常见的查询案例。
单个等于查询(Single equality)
这是最基本的情景:
SELECT * FROM t WHERE c = 100
毫无疑问这种情况下,要给c字段创建索引。要注意的是,如果查询条件不够精确(if the criteria is not selective enough,这句话我不理解),优化器很可能会选择全表查询,因为这样有可能性能更好。
这种单个等于查询也包括只查询部分字段,而不是所有字段,如:
SELECT c1, c2 FROM t WHERE c = 100
这里应该创建一个(c,c1,c2)的索引,因为这样是覆盖索引。注意不是创建(c1,c2,c)!这同样也是覆盖索引,但是对过滤没什么帮助(记住MySQL索引的最左原则)。
多个等于查询(Multiple equalities)
SELECT * FROM t WHERE c = 100 and d = 'xyz'
这种情况也很容易优化:创建索引(c,d)或(d,c)。
最常见的错误是建立两个索引:一个是c,一个是d。尽管MySQL根据index_merge算法能同时使用这两个索引,但这样依然是糟糕的选择(详情参见以下几篇文章:https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/,https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/,https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/)
等于与不等于并存的查询(Equality and inequality)
SELECT * FROM t WHERE c > 100 and d = 'xyz'
这种情况我们必须要小心,因为只要有一列使用了不等于计算,那么它将阻止其他列使用索引。
因此我们需要创建一个(d,c)的索引,这时候c和d两个条件都会走索引,这也是我们想要的结果。
而如果我们创建的是(c,d)索引,则只有c列的索引会被利用,这样效率会比较低。
因此,索引中字段的顺序对于这种等于/不等于并存的查询有极大的影响。
多个不等于查询(Multiple inequalities)
SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'
这里有两个不等于,前面已经说了不等于会终止索引查询,因此我们不可能做到b、c、d都被索引覆盖( 注释1)。因此我们必须要做出决定,到底是创建索引(d,b)还是索引(d,c)?
在不知道表里具体数据的情况下,创建上面任何一种都无所谓,最关键的是,一定要把等于条件(在这里是d)所在列,放在索引的最左侧。
注释1:事实上还是有一种“曲线救国”的方法,能同时满足所有条件,即按照字段b分区(partition on b),然后创建索引(d,c),或按照字段c分区(partition on
c
),然后创建索引(d,b)。这个的细节已经超出了本文的讨论范围,不过这也是这种情况下的一种解决方法。
多个等于与排序(Equalities and sort)
SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b
就像第一节中写的那样,索引可以过滤、排序,因此这个查询很容易优化。不过和不等于类似,我们对于索引中字段的顺序必须足够小心:
要求是先过滤后排序。
根据上面“先过滤后排序”的要求可知,(c,d,b)或(d,c,b)是不错的选择;而(b,c,d)或(b,d,c)则比较糟糕,因为他们只排序,不过滤。
如果是下面这种情况:
SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b
我们可以创建一个集过滤、排序、覆盖于一体的索引:(c,d,b,c1,c2)。
不等于与排序(Inequality and sort)
常见的情况有2种。下面是情况一(不等于、等于、排序都有):
SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b
这种情况有两种思路:(d,b)或(d,c)。至于哪种效率更高,这取决于你的数据,需要具体情况具体分析。
情况二如下(只有不等于和排序):
SELECT * FROM t WHERE c > 100 ORDER BY b
这种情况没有等于条件,因此b和c只能选一种,具体选哪一种同样和你的数据有关。通常情况下,选过滤的会多一些(即c字段)。
总结
本文并没有包含所有的情况,但同样指出了一些你必须要小心的地方。今后,我会列举一个看起来十分复杂的例子,不过只要你把这篇文章看懂了,它其实很简单。