欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-03-29 22:14:33
...
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
相关标签: orderby