MySQL升级至5.6版本后相同查询执行速度大幅变慢问题咨询

性能下降核心成因 #

MySQL 5.6版本对查询优化器做了大量逻辑调整，和5.5的执行计划生成逻辑差异是本次问题的核心诱因，具体可定位到以下几个常见场景：

• 优化器对ORDER BY + LIMIT场景的执行计划选择错误：5.5版本默认优先利用RECORDS表主键ID的有序性，倒序扫描主键索引的同时过滤WHERE条件，匹配到35000条符合条件的记录后直接关联剩余两张表返回结果，无需额外排序，执行效率高。5.6版本优化器误判全表过滤后排序的成本更低，会先扫描整张RECORDS表过滤出所有符合条件的记录，做完filesort排序后再关联另外两张表，若符合条件的记录量极大，排序和大表关联的耗时会直接拉长整体执行时间。
• 表统计信息过期：升级后未更新表统计信息，优化器拿到的表行数、索引区分度等参数偏差极大，进一步加大了执行计划选错的概率。
• 新优化特性适配问题：5.6默认开启的块嵌套循环连接（BNL）、半连接优化等新特性，在多表左连+模糊过滤的场景下反而会降低执行效率。

对应解决方案 #

优先验证执行计划 #

先分别在5.5和5.6版本执行以下命令，对比执行计划差异确认根因：

EXPLAIN SELECT SQL_NO_CACHE *
FROM RECORDS
LEFT OUTER JOIN AUTH ON RECORDS.`id` = AUTH.`id`
LEFT OUTER JOIN STAFFCOMMENTS ON RECORDS.`id` = STAFFCOMMENTS.`id`
WHERE (ODATE LIKE '%Jan%')
AND (ODATE LIKE '%2021%')
AND RECORDS.NAME <> 'CUSTOMER'
AND (RECORDS.NAME <> 'COURIER-ORDER')
ORDER BY RECORDS.ID DESC
LIMIT 35000

重点对比key、Extra列：若5.6版本key列没有显示PRIMARY、Extra列出现Using filesort，即可确认是执行计划选错的问题。

针对性修复方案 #

1. 强制走主键索引避免排序
   在RECORDS表后增加强制索引提示，让优化器复用5.5的执行逻辑：

FROM RECORDS FORCE INDEX(PRIMARY)

2. 优化查询逻辑减少数据处理量
   拆分查询为先过滤排序RECORDS表、再关联剩余表，避免大表关联后再排序：

SELECT SQL_NO_CACHE t.*, a.*, s.*
FROM (
    SELECT * FROM RECORDS
    WHERE ODATE LIKE '%Jan%' AND ODATE LIKE '%2021%'
    AND NAME <> 'CUSTOMER' AND NAME <> 'COURIER-ORDER'
    ORDER BY ID DESC LIMIT 35000
) t
LEFT JOIN AUTH a ON t.id = a.id
LEFT JOIN STAFFCOMMENTS s ON t.id = s.id

3. 更新表统计信息
   执行以下命令刷新统计信息，修正优化器判断依据：

ANALYZE TABLE RECORDS, AUTH, STAFFCOMMENTS;

4. 关闭不兼容的优化特性
   如果确认是新优化特性导致的性能下降，可以临时关闭对应开关验证效果：

SET optimizer_switch='block_nested_loop=off';

5. 长期优化建议
   将ODATE字段修改为DATE/DATETIME类型，替换原有字符串存储格式，同时为ODATE字段创建索引，把模糊匹配条件改为日期函数过滤，从根源上提升过滤效率。

内容的提问来源于stack exchange，提问作者Gayathri