一次 Mysql 慢查询引发的故障
近日订单履约系统发生了一起故障，导致下发的订单积压。究其原因是慢查询导致的，现在痛定思痛看看为什么发生了慢查询。
发生问题的查询语句长得很正经：
# 仅作示例，此处省略一长串
SELECT id,order_id,flag,order_sn,old_order_sn,wms_flag,warehouse,...
FROM xxx_order_xxx
WHERE wms_flag=1 AND warehouse IN ('ABC', 'XYZ') AND flag IN ( 90 , 3 )
ORDER BY id ASC
limit 350该数据库的表索引如下：
PRIMARY KEY (`id`),
...
KEY `idx_flag` (`wms_flag`,`flag`),
KEY `warehouse` (`warehouse`),
...
KEY `warehouse_2` (`warehouse`,`extra2`,`flag`)这么多索引，以上查询语句该使用哪个索引呢？我们先根据wms_flag，warehouse看看数据分布吧。
对于符合 wms_flag=1 的记录个数:
select count(*) as count1
from xxx_order_xxx
where wms_flag=1；
查询结果如下：
| count1 |
|--------|
| 127132 |对于符合 warehouse in (xxx) 的小仓记录个数:
select count(*) as count2
from xxx_order_xxx
where warehouse in ('小仓列表');
查询结果如下：
| count2 |
|--------|
| 105180 |对于符合 warehouse in (xxx) 的大仓记录个数:
select warehouse, count(*) as count2大仓1 | 105 |
from xxx_order_xxx where warehouse in ('大仓列表');
查询结果如下：
| warehouse| count2 |
|----------|--------|
|
| 大仓2 | 4943267|
这样会产生两种情况：
场景1:
count1 > count2, 此刻应该走warehouse索引，可选索引 warehouse, warehouse_2，比如以上小仓的场景。场景2：
count1 < count2, 此刻可选索引为 idx_flag & primary key，比如大仓的场景。
为什么这样选择索引呢？索引的目的就是尽量缩小结果集，这样才能做到快速查询。
当时线上为场景2, 那么慢查询是如何产生的呢？
在测试环境下，场景2的执行计划如下。我们继续看看查询语句中的 “order by id asc limit 350"。
如果去掉order by id的执行结果怎样呢？
也就是扫描了 idx_flag 索引的数据 13903行，找到了350行符合 warehouse in ('大仓列表') and flag in (90,3) 的数据。
如果去掉limit的执行结果怎样呢？
也就是扫描了符合 idx_flag 的索引数据 149152行，终于找到了 22058行符合warehouse warehouse in ('大仓列表') and flag in (90,3)的数据。
如果组合 order by id 加 limit 350 就是线上故障了。可这是为什么呢？
从索引统计信息看，主键索引的过滤性更高，目前limit是350，按照mysql统计信息的行为，每次SQL解析随机该表扫描32个页的数据，发现满足 wms_flag=1 的极少，都是 wms_flag=0，为此放弃了 idx_flag 的索引，而使用了主键索引。
那么使用主键索引mysql数据库是怎么扫描的呢？
数据库里的id是自增id，也是主键索引。故障发生时数据库里 wms_flag=0 的记录有 29091485条，wms_flag=1 的只有 127132条。wms_flag=0 的数据占了从 idx=0 开始的绝大多数。
order by id asc 的扫描从 id=0 开始扫描到尾，直到扫描到符合条件的350条记录。由于扫描量巨大，因此引发了慢查询，消耗的时间长达20S。如果把 asc 改成 desc, 那么结果秒出。
综上所述，慢查询的根源是使用了主键索引，因此修复方案是去掉主键索引。在紧急上线的临时方案里，调整了sql语句如下。执行时间从 20s 降到了 0.6s。
select id,order_id,flag,order_sn,old_order_sn,wms_flag,warehouse,...
from xxx_order_xxx
where wms_flag = 1 and warehouse in ('ABC','XYZ') and flag in (90,3)
order by id+0 asc
limit 350为什么+0就能不走主键索引呢？ 让我们看看 <<高性能mysql>> 里是怎么解释的吧。
就此，紧急方案上线以后，解决了订单积压的问题。
PLUS: 从履约系统的业务角度分析，不需要对数据排序，所以终极修复方案里去掉了 order by id asc。
长按以下二维码，关注本公众号，更多原创等着你！