已建索引且分区的MySQL偶发慢查询优化咨询

查询语句 #

SELECT * 
FROM product_related pr 
    LEFT JOIN product p ON (pr.related_id = p.product_id) 
    LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE 
pr.product_id = '" . (int)$product_id . "' AND 
p.status = '1' AND 
p.date_available <= NOW() AND 
p2s.store_id = '0'

表结构信息 #

• product_related：2个int类型字段，均已建索引，按HASH(product_id)分为10个分区，共120万条记录，总大小30MB
• product：包含48个不同类型字段（无大文本/二进制对象），product_id为主键且建索引，status和date_available字段也已建索引，按HASH(product_id)分为10个分区，共13万条记录，总大小61MB
• product_to_store：2个int类型字段，主键索引为PRIMARY(product_id, store_id)，无分区，共13万条记录，总大小3.4MB

现象 #

该查询多数场景下耗时低于0.05秒，但偶尔会突增至30-50秒，立即刷新重新执行则恢复毫秒级速度，其他表也出现过类似偶发慢查询情况。

1. 强制固定执行计划 #

偶发慢查询的核心原因通常是MySQL优化器临时选择了低效的执行计划（比如变更表连接顺序、选错索引）。可以通过以下方式强制稳定的执行逻辑：

• 用FORCE INDEX指定必须使用的索引：

SELECT * 
FROM product_related pr FORCE INDEX (product_id)
    LEFT JOIN product p FORCE INDEX (PRIMARY) ON (pr.related_id = p.product_id) 
    LEFT JOIN product_to_store p2s FORCE INDEX (PRIMARY) ON (p.product_id = p2s.product_id) 
WHERE 
pr.product_id = '" . (int)$product_id . "' AND 
p.status = '1' AND 
p.date_available <= NOW() AND 
p2s.store_id = '0'

• 用STRAIGHT_JOIN强制按书写顺序连接表（当前从product_related的小结果集开始，是高效顺序）：

SELECT * 
FROM product_related pr 
    STRAIGHT_JOIN product p ON (pr.related_id = p.product_id) 
    STRAIGHT_JOIN product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE 
pr.product_id = '" . (int)$product_id . "' AND 
p.status = '1' AND 
p.date_available <= NOW() AND 
p2s.store_id = '0'

2. 更新表统计信息 #

MySQL优化器依赖表统计信息生成执行计划，统计信息过时会导致错误决策。手动更新统计信息：

ANALYZE TABLE product_related, product, product_to_store;

对于MySQL 5.6+，默认开启innodb_stats_auto_recalc，但定期手动执行可避免统计信息滞后问题。

3. 移除不必要的分区 #

当前按HASH(product_id)分区，但查询均指定单一product_id，分区未带来性能收益，反而可能增加分区扫描的额外开销。建议移除分区后测试性能变化——小表分区通常收益甚微，甚至有反作用。

4. 优化缓存与内存配置 #

偶发慢查询可能是数据未命中InnoDB缓冲池，首次加载耗时，第二次命中缓存则加速：

• 调整innodb_buffer_pool_size至服务器内存的50%-70%（避免内存溢出），确保热点数据能常驻内存
• 应用层增加缓存（如Redis），对高频查询结果进行缓存，减少数据库直接查询次数；MySQL 5.6可开启query_cache（注意缓存失效规则）

5. 排查锁等待与资源竞争 #

慢查询发生时可能被其他事务锁阻塞：

• 开启慢查询日志：设置slow_query_log = 1、long_query_time = 1，捕获所有超过1秒的查询
• 实时查询锁状态：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

排查是否存在长事务占用锁导致当前查询阻塞的情况。

6. 优化查询语句与索引 #

• 避免SELECT *，只查询需要的字段，减少数据传输和内存开销：

SELECT pr.related_id, p.product_id, p.name, p.price -- 按需列出字段
FROM product_related pr 
    LEFT JOIN product p ON (pr.related_id = p.product_id) 
    LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE 
pr.product_id = '" . (int)$product_id . "' AND 
p.status = '1' AND 
p.date_available <= NOW() AND 
p2s.store_id = '0'

• 为product表创建覆盖索引，包含过滤条件和查询字段，避免回表：

CREATE INDEX idx_product_status_date ON product(product_id, status, date_available);

7. 升级MySQL版本 #

当前使用的MySQL 5.6.40存在部分优化器已知bug（比如分区表的执行计划选择问题），升级到MySQL 5.7或8.0可获得更稳定的优化器逻辑和更好的分区表支持。

内容的提问来源于stack exchange，提问作者Roman K.