关于一个sql执行时间而引发的讨论

关于一个sql执行时间而引发的讨论

今天下午在QQ群中有个网友问到一个SQL执行时间疑惑的问题，引发了精彩的讨论，其中还解决了我的一个理论知识错误的问题，将过程大致描述一下，方便以后学习。

问题SQL如下：

SQL1:

SELECT sql_no_cache * FROM `news`.`v9_news` WHERE `catid` = '15' AND `id`<'201846' AND `status`=99 ORDER BY id DEsc limit 1;

执行时间为0.8s左右
SQL2:

SELECT sql_no_cache * FROM `news`.`v9_news` WHERE `catid` = '15' AND `id`<'201846' AND `status`=99 ORDER BY catid,status,id DEsc limit 1;

执行时间为0.2s左右

如果将排序更改成ASC，那么SQL1会快一点。

v9_news的表索引如下：

Explain查看到的结果：

 

从上面两张图上分析，第一个语句能用到索引排序，但是速度却慢那么多，比较奇怪。于是又让该网友使用profiling看看到底哪一步慢，发现SQL1在Sending data消耗了大部分的时间，而SQL2却很快。

讨论了很久仍然没有解释清楚，最后牛B的王大哥给出了合理的解释。他的解释是这样的，他认为是MYSQL优化器的问题，SQL1走了组合索引但是id< 201846没走索引，因为后面有个order by desc，优化器认为不走索引会快一点。这里可以解释为什么SQL1在Sending data消耗时间那么多，因为它进行大量的磁盘扫描。而SQL2用了三个组合索引，id那列也走了索引，过滤了大部分数据，即使后面的order by没有走索引，反而比SQL1快。而当将排序更改成ASC以后，优化器走的正常了。所以第一个要比第二个快。这是因为选择的索引一样，第一个SQL使用索引排序，而第二个是通过磁盘排序。

      为了验证王大哥的这种猜想，我让该网友执行了如下强制指定索引的语句，避免了优化器的干扰：

SELECT sql_no_cache * FROM `news`.`v9_news`  force index(idx_catid_status_id) WHERE `catid` = '15' AND `id`<'201846' AND `status`=99 ORDER BY id DEsc limit 1;

这个执行时间和升序差不多，时间显示为0。
SELECT sql_no_cache * FROM `news`.`v9_news`  force index(idx_catid_status_id) WHERE `catid` = '15' AND `id`<'201846' AND `status`=99 ORDER BY  catid,status,id DEsc limit 1;

这个执行时间和升序差不多，时间显示为0.07。

从执行时间可以看出排除优化器的干扰以后，执行就对了，第二个用到的文件排序就慢一点。哈哈。下面是该网友提供的截图，更说明了是优化器没有用到ID列的索引所致：

SQL1强制索引和通过优化器的执行计划差异：

SQL2强制索引和通过优化器的执行计划差异：

从上面3张图可以看出，当使用强制索引的时候key_len为6，而让优化器自己选择索引的时候使用的索引长度为3，因此可以看出id列没有使用索引。

从网友的SQL1和SQL2的截图上看，用王大哥的解释也不通，因为他的解释是SQL1的id列没有走索引而SQL2的id列走了。现在两个SQL的id列都没走索引，我就想不明白为什么SQL1的Sending data为什么和SQL2的Sending data相差如此之大了。现在功力太浅了，哈哈，先记录下这个问题以后功力深厚了再回头来看看。

新进展：网友反馈有问题的mysql版本是5.5.9，刚该网友使用5.5.28测试发现优化器又正常了，不存在SQL1和SQL2时间相差很大的问题。看来mysql版本之间的差异优化器工作也有差异，哈哈！
见下面的截图：




最终结论：是因为那个表的碎片太多了，optimize优化一下表后优化器就正确了，哈哈！