乐趣区

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

jiezi

前两天共事负责的订单模块查问呈现了一个奇怪的问题，当退出筛选条件后会呈现查问超时的问题，查问全副订单的时候没有问题，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 之后走主键索引的预估扫描行数可能会少于走一般索引的预估扫描行数，导致索引抉择谬误。
————————————————
版权申明：本文为 CSDN 博主「夜勿语」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。
原文链接：https://blog.csdn.net/l610800…

退出移动版