前两天同事负责的订单模块查询出现了一个奇怪的问题,当加入筛选条件后会出现查询超时的问题,查询全部订单的时候没有问题,SQL如下(数据已脱敏,使用的是MySql):
SELECT
a.consumer_code AS orderCode,
a.rent_equipment_snid AS eqSn,
a.powerbank_snid AS pbSn,
a.rent_merchant_name AS rentMerchant,
a.rent_merchant_address AS merchantAddress,
a.rent_date AS rentTime,
a.close_date AS returnTime,
a.payment_money AS orderAmount,
a.order_status AS orderStatus,
a.consume_schema AS consumeSchema,
a.transaction_status AS transStatus,
a.rent_equipment_model AS eqModel
FROM
cp_consumer_order_2020_10 a
WHERE
a.agent_code = xxxx
# 下面两个条件就是筛选时才会加上
AND a.order_status = xxx
AND a.close_date IS NULL
ORDER BY
a.consumer_code desc
cp_consumer_order_2020_10是订单月表,差不多有1000多万数据,consumer_code是主键,agent_code 上有普通索引。 我拿到上面的SQL在数据库执行发现是走了agent_code索引的,并且查询效率也是正常的,然后删掉了筛选条件,执行结果也是一样。
上面分别是带条件和不带条件的执行计划,可以看到没有什么区别。这时我猜想是不是代码中有什么耗时的操作:
这个代码看起来也没有什么特别耗时的操作,getAgentOrderList就是执行那条SQL,getAgentStaffOrderList也试过查询很快,因为有分页,下面的for循环执行也不会特别慢。 难不成又出现“灵异事件”了?这时我突然想到会不会是分页导致的,我们都知道limit在offset非常大的情况下会导致查询慢,但我们这里还没有翻页,也就是第一页,所以不是这个问题。 除此之外我又想到之前看到过limit和order by连用会出现索引选择错误的问题,于是我在带上limit 0,30在数据库执行刚刚的SQL,果不其然,慢SQL出现了。这时我再看执行计划如下:
可以看到这时候Mysql使用了主键索引,即我们排序的字段,于是我建议同事用force index强制走普通索引,查询就恢复正常了。 到这里,SQL优化就结束了,但是为什么加上limit就会导致Mysql选错索引呢,而且为什么走主键索引就很慢呢,预估扫描行数明明更少了呀?本着“知其然还要知其所以然”的原则我查阅了很多资料,都没有完全能解决心中的疑惑,最终自己反复尝试,总算搞明白了。