双INNER JOIN查询如何正确使用索引解决慢查询问题

核心原因是MySQL优化器选择了错误的表连接顺序，没有优先筛选过滤后结果集最小的表作为驱动表，反而对87万行的dysfonctionnements表做全表扫描作为查询起点，最终导致查询耗时暴涨。
具体触发优化器误判的因素有3个：

• 查询里存在冗余无效条件com.prestataireLAD REGEXP '.*'，该条件会匹配所有prestataireLAD非空的行，没有任何过滤作用，但会干扰优化器对表过滤率的成本估算
• pe_nom使用REGEXP做匹配且没有开头锚定，无法利用B树索引，同时优化器对pe表过滤后的行数估算严重偏差
• 表使用MyISAM引擎，统计信息更新不及时，进一步放大了优化器的行数估算误差

按优先级从高到低执行以下操作：

1. 清理冗余条件，优化过滤写法 #

• 直接删除无意义的com.prestataireLAD REGEXP '.*'条件，避免干扰优化器判断
• 如果你是要精确匹配列出的站点名称，把pe_nom REGEXP 'xxx'替换为IN()等值匹配，IN可以直接利用B树索引，性能远高于正则匹配：

pe.pe_nom IN ('bordeaux','chambéry-annecy','grenoble','lyon','marseille','metz','montpellier','nancy','nice','nimes','rouen','strasbourg','toulon','toulouse','vitry','vitry bis 1','vitry bis 2','vlg')

如果确实需要模糊匹配包含这些关键词的行，保留REGEXP写法即可，pe表数据量通常很小，全表扫描的成本可以忽略。

2. 强制优化器选择正确的连接顺序 #

正确的连接逻辑应该是：先扫描pe表拿到符合名称条件的pe_id → 关联commandes表过滤配送时间范围 → 最后关联dysfonctionnements表取业务字段，这个路径的总扫描行数最少。
调整SQL的表顺序，添加STRAIGHT_JOIN强制按书写顺序连接，避免优化器选错路径：

SELECT STRAIGHT_JOIN
  dys.dysfonctionnement, 
  dys.montant, 
  dys.listRembArticles, 
  CASE WHEN dys.reimputation IS NOT NULL THEN dys.reimputation ELSE dys.responsable END AS responsable_final
FROM 
  db.pe AS pe
  INNER JOIN db.commandes AS com ON com.code_pe = pe.pe_id
  INNER JOIN db.dysfonctionnements AS dys ON com.id_commande = dys.id_commande
WHERE 
  pe.pe_nom IN ('bordeaux','chambéry-annecy','grenoble','lyon','marseille','metz','montpellier','nancy','nice','nimes','rouen','strasbourg','toulon','toulouse','vitry','vitry bis 1','vitry bis 2','vlg')
  AND com.date_livraison BETWEEN '2022-06-11 00:00:00' AND '2022-07-08 00:00:00';

3. 补充联合索引，避免回表 #

现有单列索引无法同时覆盖过滤、关联、返回字段的需求，添加以下联合索引可以大幅提升查询效率：

• 给pe表添加过滤+关联的覆盖索引：

ALTER TABLE db.pe ADD INDEX idx_pe_nom_id (pe_nom, pe_id);

• 给commandes表添加关联+过滤的覆盖索引：

ALTER TABLE db.commandes ADD INDEX idx_com_codpe_date (code_pe, date_livraison, id_commande);

• 如果要进一步消除dysfonctionnements表的回表成本，可以添加覆盖索引（可选）：

ALTER TABLE db.dysfonctionnements ADD INDEX idx_dys_idcommande_cov (id_commande, dysfonctionnement, montant, listRembArticles, responsable, reimputation);

4. 更新表统计信息 #

MyISAM引擎的统计信息不会自动实时更新，执行以下命令让优化器拿到准确的行数估算：

ANALYZE TABLE db.commandes;
ANALYZE TABLE db.dysfonctionnements;
ANALYZE TABLE db.pe;

MyISAM引擎已经被官方淘汰多年，存在表锁粒度大、崩溃恢复能力差、统计信息不准等诸多问题，如果业务允许，建议将所有表的引擎切换为InnoDB，能大幅降低优化器误判概率，提升并发查询性能。

优化后重新执行EXPLAIN可以看到，执行计划会从pe表开始扫描，依次关联com、dys表，不会再出现dys表全表扫描的情况，总耗时应该能降到2秒以内。

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