MySQL limit与order by遇到的有趣问题_MySQL

bitsCN.com

MySQL limit与order by遇到的有趣问题

 

   相信大家都知道MySQL的limit语法 select/delete/update .... limit start, len； 就是现在查询或者更新几条数据。当然我们需要知道如果是更新语句带limit个，为了复制安全我们应该有 order by 主键/唯一键的语法，否则更新的行就不确定了。入正题，我这个场景是怎样的呢？

 

        业务反映有一条SQL突然查询特别慢：select matchid,uid from usermatch_create where status='nostart' and matchid%2 = 0 order by matchid asc limit 1;我explain看了下执行计划，也正常：

explain select matchid,uid from usermatch_create where status='nostart' and matchid%2 = 0 order by matchid asc limit 1;

+----+-------------+------------------+-------+----------------+---------+---------+------+------+-------------+

| id | select_type | table            | type  | possible_keys  | key     | key_len | ref  | rows | Extra       |

+----+-------------+------------------+-------+----------------+---------+---------+------+------+-------------+

|  1 | SIMPLE      | usermatch_create | index | idx_status_add | PRIMARY | 8       | NULL |    6 | Using where | 

+----+-------------+------------------+-------+----------------+---------+---------+------+------+-------------+

但是执行起来确实相当的慢，将近2min的查询，然后set  profiling=on,然后查看到耗时最长的阶段是sending data

这说明这个查询过程中扫描的数据特别大导致大量的数据传输，为什么会这样呢?不是优化器预估是扫描6行吗（当然这个值是预估的，不完全准确）？然后抱着尝试的心态将order by matchid asc改为order by matchid desc，再次执行查询，简直就是秒杀，瞬间执行完，查看这次的执行计划跟之前完全一样。心里当初还想btree对两种order by的查询有差异吗？想来想去觉得原理上是一样的，升序和降序只是扫描的方向不同。

 

接下来尝试将where条件中的status条件去掉，发现两条语句执行速度一样快。此时大概明白问题就出在status这个判断上了。猜测是由于数据分布的问题导致order by asc查询特别慢，当时猜测在 order by matchid asc这个条件时，前面大量的行由于status不满足‘nostart’这个条件所以相当于白扫描了很多行。而如果通过 order by matchid  desc的话，在前面刚开始的几行就找到status='nostart'的行，因此速度会很快。验证方式：

select status from usermatch_create where matchid%2 = 0 order by matchid asc limit 10000;

上面这个结果集显示前面10000行都是status不为‘nostart’

select status from usermatch_create where matchid%2 = 0 order by matchid asc limit 10000;

上面这个结果集显示前面10里面就有status为‘nostart'

相信此时对于两种order by的查询时间为什么会有这么大的差异的原因就很清楚了。

 

        那么到底是什么原因导致这种现象呢？为什么以前没有出现过这个查询慢？因为这周由于机器原因这个业务少了一个从库，为了减轻压力，我要业务停了每天的删除数据任务，而这个删除任务会将很多status不为’nostart'的删掉，因此删掉之后也就不会导致之前说的那么多没有作用的扫描。至此问题得到较好的解决，只能说这个问题很有意思。

 

bitsCN.com