记一次线上SQL索引优化及索引选择错误原理分析

前两天同事负责的订单模块查询出现了一个奇怪的问题，当加入筛选条件后会出现查询超时的问题，查询全部订单的时候没有问题，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选错索引呢，而且为什么走主键索引就很慢呢，预估扫描行数明明更少了呀？本着“知其然还要知其所以然”的原则我查阅了很多资料，都没有完全能解决心中的疑惑，最终自己反复尝试，总算搞明白了。

• 首先为什么走普通索引更快，而主键索引更慢？
  因为我这条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之后走主键索引的预估扫描行数可能会少于走普通索引的预估扫描行数，导致索引选择错误。