MySQL 5.7.36生产环境DERIVED查询慢于本地5.7.35的问题排查

问题背景 #

• 本地MySQL 5.7.35：该SQL执行耗时0.0471秒，执行计划为SIMPLE类型，用到了index_merge优化
• 生产MySQL 5.7.36：同样的SQL执行耗时超5.0589秒，执行计划为DERIVED（派生表），未使用index_merge
• 数据库通过mysqldump从生产同步至本地，数据结构与内容完全一致，两台服务器配置基本相同

慢查询SQL #

SELECT culture_prices.*
FROM   (SELECT culture_prices.*,
               ( culture_prices.nds_grn_cents - IF(distance_m.distance > 50000,
(
Nds_distance_cost(distance_m.distance)
),
                                 10000)
) AS
nds_grn_cents_with_delivery,
distance_m.distance
AS distance_meters
FROM   `culture_prices`
INNER JOIN `cultures`
ON `cultures`.`id` = `culture_prices`.`culture_id`
INNER JOIN `elevators`
ON `elevators`.`id` = `culture_prices`.`elevator_id`
INNER JOIN `distance_m`
ON `distance_m`.`elevator_id` = `elevators`.`id`
INNER JOIN `cities`
ON `cities`.`id` = `distance_m`.`city_id`
LEFT JOIN org_cultures cul_1
ON culture_prices.org_culture_id = cul_1.id
WHERE  ( culture_prices.nds_grn_cents > 0 )
AND `culture_prices`.`is_searchable` = 1
AND ( cul_1.is_active = '1' )
AND ( cities.id = 1503 )
ORDER  BY is_check_price ASC,
nds_grn_cents_with_delivery DESC) AS culture_prices
WHERE  `culture_prices`.`culture_id` = 24 

核心差异原因 #

1. MySQL小版本优化器逻辑调整 #

MySQL 5.7系列的小版本更新会频繁调整优化器规则，尤其是子查询与派生表的处理逻辑。5.7.35中优化器能将嵌套查询合并为SIMPLE类型（无需生成临时表），但5.7.36可能收紧了派生表合并的触发条件，导致优化器无法合并查询，只能先执行完整子查询生成临时表，再过滤culture_id=24的数据，数据量较大时性能急剧下降。

2. 表统计信息不一致 #

虽然数据同步完成，但生产环境的表统计信息可能与本地存在差异：

• 生产环境表可能存在更多历史数据（同步后生产新增数据未同步至本地）
• MySQL自动统计信息的采样率、更新时机在生产与本地不同，优化器基于不准确的统计数据选择了低效执行计划，本地能通过index_merge快速过滤，生产却走了全表扫描或低效索引。

3. 关键配置细节差异 #

即使整体配置一致，部分影响优化器的参数可能不同：

• optimizer_switch中的derived_merge开关：5.7.36可能默认关闭该开关，或对合并条件更严格
• 内存参数如join_buffer_size、sort_buffer_size：生产环境因负载高，实际可用内存不足，排序、连接操作只能使用磁盘临时表，速度大幅降低

优化解决方案 #

1. 调整SQL结构，提前过滤条件 #

将外层的WHERE culture_id=24移至内层子查询，让优化器在查询初期就过滤掉大部分数据，避免生成大临时表：

SELECT culture_prices.*,
       ( culture_prices.nds_grn_cents - IF(distance_m.distance > 50000, Nds_distance_cost(distance_m.distance), 10000) ) AS nds_grn_cents_with_delivery,
       distance_m.distance AS distance_meters
FROM   `culture_prices`
INNER JOIN `cultures` ON `cultures`.`id` = `culture_prices`.`culture_id`
INNER JOIN `elevators` ON `elevators`.`id` = `culture_prices`.`elevator_id`
INNER JOIN `distance_m` ON `distance_m`.`elevator_id` = `elevators`.`id`
INNER JOIN `cities` ON `cities`.`id` = `distance_m`.`city_id`
LEFT JOIN org_cultures cul_1 ON culture_prices.org_culture_id = cul_1.id
WHERE  ( culture_prices.nds_grn_cents > 0 )
AND `culture_prices`.`is_searchable` = 1
AND ( cul_1.is_active = '1' )
AND ( cities.id = 1503 )
AND `culture_prices`.`culture_id` = 24 
ORDER  BY is_check_price ASC, nds_grn_cents_with_delivery DESC

2. 更新生产环境表统计信息 #

执行ANALYZE TABLE命令让MySQL重新收集表的统计数据，确保优化器基于准确数据选择最优执行计划：

ANALYZE TABLE culture_prices, cultures, elevators, distance_m, cities, org_cultures;

3. 检查并开启派生表合并开关 #

查看生产环境optimizer_switch配置：

SELECT @@optimizer_switch;

若derived_merge=off，临时开启测试：

SET SESSION optimizer_switch='derived_merge=on';

测试有效后，在my.cnf（或my.ini）中全局配置：

optimizer_switch='derived_merge=on'

4. 添加针对性组合索引 #

为以下字段添加组合索引，帮助优化器快速过滤数据：

• culture_prices：(culture_id, is_searchable, nds_grn_cents)
• distance_m：(elevator_id, city_id)
• org_cultures：(id, is_active)（左连接后过滤is_active='1'，实际等效内连接，该索引可加速匹配）

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