背景
最近在生产环境遇到一个慢查询,形式如下:
SELECT *
FROM coupon
WHERE type = 'redeem' AND status = 'new' AND user_id=1
ORDER BY end_time ASC
LIMIT 1;
该SQL执行时间大概3s左右，但令人惊奇的是当limit x(x>2)之后， 如下SQL执行时间只要50ms左右：
SELECT *
FROM coupon
WHERE type = 'redeem' AND status = 'new' AND user_id=1
ORDER BY end_time ASC
LIMIT 2;
limit 1 比 limit 2取更少数据，为什么执行时间却更长？
分析
先看两个SQL的查询计划分析
limit 1对应的执行计划如下：
| select_type | table | type | key | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| SIMPLE | coupon | index | idx_endtime_status | 1029 | 0 | Using where |
limit 2对应的执行计划如下:
| select_type | table | type | key | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| SIMPLE | coupon | ref | idx_status_type | 1106 | 0 | Using index condition; Using where; Using filesort |
从两个执行计划可以看出两种SQL选择了不同的索引：
limit 1 => idx_endtime_status 这种情况下：
- 遍历索引树每个叶节点，
- 然后回表查找数据行，比对剩余的where条件
最坏情况可能扫描全表；而整个表行数有110万行
limit 2 => idx_status_type 这种情况下：
- 覆盖索引叶节点匹配status='new' 和 type='redeem'
- 取叶节点对应数据行where过滤user_id
- 按end_time排序取前2行
而status='new' 和 type='redeem'对应数据行只有1100行，加上排序，按O(NlogN)复杂度大致也只需要1万行数据的操作
猜测
从让可以看到是因为索引选择导致了执行时间差异。那问题变成索引为什么这选择？ MySQL索引选择是基于成本估计的搜索优化
对于limit 2;聚合索引叶节点存有对应数据行的准确行数rows, 可以直接读取。
然而limit 1;是需要遍历索引树。显然不可能在做代价估计时遍历所有数据；那又如何做呢？
答案是通过表维护的统计数据拟合表数据真实分布，然后估计where条件的数据所在位置；
对于MySQL就是默认采集20个page的数据做统计。 这种统计很快只要20-30ms，但在数据行比较大，则每个page对应的数据行相应就少；再加上表数据行很多，这种采样的比例相对整个表就较小，数据分布估计会有较大偏差造成成本估计不准。
在线上coupon表中
...
WHERE type = 'redeem' AND status = 'new' AND user_id=1
...
对应的end_time就位于表数据页的最后几页。
总结
所以limit 1比limit 2执行慢；主要就是数据倾斜， 要查的数据太靠后， 而idx_endtime_status按照均匀分布估计执行成本远少于真实成本；导致选择了错误索引
参考
具体解决方案最后是升级阿里云MySQL内核