今天重新看了一遍索引的原理及如何避免索引失效的办法,详见:
索引原理
索引原理延展
如何避免索引失效
原本想着只是温故,突然想到线上有个列表加载速度一直很慢,大概5s左右。这还是优化过的,还没优化前10s左右。因为也不是非常重要的功能,就一直放着,今天看完也就想着实践一下的想法去尝试优化,最后优化到了1s内,也是…惊喜,优化过程如下:
有or_mainten表和or_mainten_inner_evaluate表,后面都用M和E代替
业务场景是M表需要关联E表,E表为评论表,所以可能存在M表中的一条数据对应E表中多条评论,而查询的时候,M只要E表中最新的那一条评论。
刚开始的实现方式是通过M表关联 (E表自身排序查询最新一条评论),发现E表自身排序查询太慢了,拖了整个查询的时间。后面想到在评论表中新增一个状态值,最新的那条用1标志,其它的重置为0。这样直接通过inner join和where就能查询,省去了查询最新的步骤,如下:
SELECT
M.ID
FROM
OR_MAINTEN M
INNER JOIN or_mainten_inner_evaluate omie ON omie.RELATED_ID = M.ID
WHERE omie.IS_LAST = '1'
优化第一次后,列表加载从原来的十几秒降到了5s左右。
初始加载的SQL语句如下:
SELECT
M.ID,
M.MAINTEN_NO,
M.LOT_ID,
M.LOT_NAME,
M.FAULT_DESC,
M.INFORMATION,
M.REAL_MAINTEN_PERSON,
M.BILL_TYPE,
M.STATUS,
M.FINISH_TIME,
M.DATA_SOURCES,
M.ADDRESS,
M.PROBLEM_LEVEL,
M.DEAL_MODE,
M.CUSTOM_INITIATE,
M.BILL_TYPE AS billTypeName
FROM
OR_MAINTEN M
INNER JOIN or_mainten_inner_evaluate omie ON omie.RELATED_ID = M.ID
WHERE omie.IS_LAST = '1' AND omie.EVALUATE_STATUS ='0' AND M.STATUS = '2' AND M.FINISH_TIME >= '2021-09-18 00:00:00' AND M.FINISH_TIME <= '2021-10-18 23:59:59'
ORDER BY M.FINISH_TIME DESC
此时两表的索引:
M表
E表
此时的查询:
explain
查询时间
explain相关分析可以看这篇 关于mysql inner join 连接查询的优化
仔细看了SQL查询和索引后发现,这边走的索引是INDEX_STATUS_DATA_SOURCES
根据最左前缀匹配原则,M表根据status去匹配索引,自然就是上面这个了,但是初始SQL中还有和finish_time没有,于是我把
改成了
再执行
查询时间明细提升。
要理解上面所讲,需要理解索引的原理,建议详细阅读开头有关索引原理及避免失效的文章