我们很自豪,因为我们是MySQL专家,我们堪称MySQL的性能、索引专家,能够帮助我们的客户很好地运行MySQL。 昨天,我们有一名新客户,他的系统刚刚上线,今天我们有好几名数据库管理员就在他的古怪的SQL和查询优化器问题上忙活了一整天。大部分时间里,数据库服务器是很好的,但是当它有负荷的时候,所有8个CPU就超负荷了,我们扩大了CPU容量,但是InnoDB Row Reads(行阅读数)只有大约500K/秒。

最后,核心问题就是MySQL查询优化器如何选择索引策略,若没有在where语句中指定所有键的时候,针对于复合覆盖索引,应当如何选择索引策略,并且与小范围查询策略相比,多行索引扫描的效率究竟有多低。这些都是复杂的问题,我们把问题详细分解一下。

首先,设置最新的Percona5.6,这样我们可获得更优化的或比现有的查询更好的查询方法。独立250万行InnoDB表查询返回450行和一列。SELECT语句只包含主键,所以,ANY 索引可以包含这个查询,因为所有的InnoDB索引都包含主键(即使有些主键是您看不到的)。 这个表有许多索引(太多的索引),但是我们仅关注一个索引,这个索引有四个键(再加上一个隐藏的主键),前三个键的选择性/基数很低(大约36);第四个键(在一列的最后)在每行都是唯一的(我们不知道是什么原因),所以基数就很大。

Where语句有很多的IN语句,大约255个元素,但是我们不认为这是问题的根源。在这个系统的复合索引中,它包括了那四个键中的三个键。很重要的一点是,WHERE语句不含有全部的索引键,只包含了4个键中的3个键。通常情况下,也许您认为这样的索引是可以用的,但是这就是问题所在。

如果是像这样的索引的话,只提供了4个键中的3个,查询优化器会进行索引浏览或索引查询,大约花4秒钟。如果我们这样做,它就不会进行表扫描,表扫描也需花约4秒钟时间。

但是,如果从表中增加一列非索引列,我们便可去除覆盖(包含)选项,优化器就会对索引进行范围扫描,查询需消耗约100ms。范围查询要快许多许多。

现在主要的问题是,如果是覆盖索引的话,并且索引只包含四个键中的三个键的时候,为什么不采用范围查询的策略呢?如果不是范围索引的话,这样做是很好的,但是如果是范围索引的话,就会进行严格的索引浏览查询,查询速度要慢500多倍,我们是根据所有索引都会检查4个键值的浏览查询上限推算出查询速度的。

解决方案在哪呢?我们在第4个键值上增加了额外的WHERE查询语句,基本上是“ield4 > -9223372036854775808”-因为这是一个BigInt。这当然意味着任意一行,因为所有值都大于最小值,但是却是能够满足查询优化器的,因为它可以进行范围扫描,所有问题都解决了。

现在我们对于较低的InnoDB行阅读数也感到困惑,这通常表示有很差的SQL存在。但是在8核服务器上,速度只有400K/秒,这个数量并不高。有一点是肯定的,CPU扩大了,行阅读数也会扩大,但是我们根本没有IO。所以,应当是与SQL相关的问题,要么是行阅读问题要么是慢排序问题。我们一直都没有发现这是个慢索引扫描问题,因为扫描问题不是以行阅读统计数来进行衡量的。可能是其它一些我们没有认识到或没有发现的问题。

此外,查询还涉及了ORDER BY(排序) 和LIMIT(限制),我们在起初对这些也比较困惑,我们以为出现了文件排序或大型临时表格类的问题,当然,我们计算出排序427行不会超过4秒钟时间。请注意,如果LIMIT是不带有ORDER BY的话,那么一旦满足特定条件,LIMIT就会结束/终止查询,但是如果是附带ORDER BY的话,服务器必须先获取所有数据,然后进行数据排序,然后再等待直到满足限制条件,这样所消耗的时间要比不带ORDER BY的查询所消耗的时间多100倍。

那么,这还是一个bug吗?我们使用索引输出,可能直接输出到覆盖里面或从表格中获取数据,但是不论我们采用何种索引输出方法,都无法为变更索引策略提供合理的理由。看起来,在这个流程里,覆盖和非覆盖这两种方法是大相径庭的。

不管怎么说,在排除SQL查询故障时,我们度过了有趣的一天,虽然刚开始时出现些差错,但是所有参与查询故障排除的人员都获得了学习的机会。