前两天共事负责的订单模块查问呈现了一个奇怪的问题，当退出筛选条件后会呈现查问超时的问题，查问全副订单的时候没有问题，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 desccp_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选错索引呢，而且为什么走主键索引就很慢呢，预估扫描行数明明更少了呀？本着“知其然还要知其所以然”的准则我查阅了很多材料，都没有齐全能解决心中的纳闷，最终本人重复尝试，总算搞明确了。
首先为什么走一般索引更快，而主键索引更慢？
因为我这条SQL是查问主键索引倒序后果，索引人造有序，不必排序了，所以看到执行打算外面的Extra字段没有了Using filesort，这里比一般索引快，然而这条SQL是有where条件筛选的，那么在拿到有序后果后，还须要逐条取出agent_code和where条件进行匹配。看到这里置信读者应该根本明确了，如果没有limit，那么这条SQL会呈现全表扫描；而这里有limit 0,30，就会呈现上面的状况：一是和where条件匹配的30条记录刚好是排序后的前30条，那么mysql就只须要扫描30条；如果有余30条或者匹配的记录都在排序的开端，也会全表扫描。而应用一般索引agent_code没有这个问题，是因为筛选条件就是agent_code，能够很快的进行匹配。
为什么加了limit就会应用主键索引？
因为不加limit的时候如果应用主键索引，就会如上所说挨个匹配where条件，但此时没有限度返回的条数，就会进行全表扫描（能够用force index(primary)+explain看到row是表总行数（1000w条）），Mysql就认为应用一般索引更快，因为一般索引预估扫描行数只有不到1.8W条；然而加了limit之后走主键索引的预估扫描行数可能会少于走一般索引的预估扫描行数，导致索引抉择谬误。
————————————————
版权申明：本文为CSDN博主「夜勿语」的原创文章，遵循CC 4.0 BY-SA版权协定，转载请附上原文出处链接及本申明。
原文链接：https://blog.csdn.net/l610800…
发表回复