MySQL有可用合适索引却不使用，强制使用后性能剧变是否正常？

场景概述 #

所有关联列均已正确建立索引：所有主键（PK）与外键（FK）都对应后缀为_idx的单列索引。其中FigureTypeID对应索引FK_Figure_Artifact_idx，CategoryStandardID对应索引FK_Category_CatStandard_idx；唯一例外是CTE的TemplateID——它属于临时结果集，无任何索引。

查询语句 #

-- 选择的列来自各表（例如 SELECT t.Name, c.Label 等），用 SELECT * 时索引表现一致；所有关联的表都是必需的
SELECT *
FROM CTE
  INNER JOIN template T on T.TemplateID = CTE.TemplateID
  INNER JOIN member ME on ME.TemplateID = T.TemplateID
  INNER JOIN memberfigures MF on MF.MemberID = ME.MemberID
  INNER JOIN membercategories MC ON ME.MemberID = MC.MemberID
  INNER JOIN categories C ON C.CategoryID = MC.CategoryID
WHERE MF.FigureTypeID = 1 
  AND C.CategoryStandardID = 1;

CTE的两种形式 #

CTE可为递归公共表表达式，或存储为临时表：

-- 最多包含4条记录
CREATE TEMPORARY TABLE CTE (TemplateID INT);
INSERT INTO CTE (TemplateID) VALUES (1);

性能异常表现 #

无论采用JOIN、子查询（AND T.TemplateID IN (SELECT TemplateID FROM CTE)）还是WITH RECURSIVE CTE形式，执行结果的EXPLAIN输出几乎一致：

• 单独执行递归CTE或不含CTE的其他表关联均瞬间完成
• 将CTE结果直接写为AND T.TemplateID IN (<记录1>,<记录2>,...,<记录5>)也能瞬时执行
• 但关联CTE后性能骤降100-1000倍

临时表方式的EXPLAIN结果 #

table    type    possible_keys                                      key                     key_len     ref             rows    filtered    Extra
CTE      ALL     NULL                                               NULL                    NULL        NULL            1       100         Using where
T        eq_ref  PRIMARY                                            PRIMARY                 4           CTE.TemplateID  1       100         NULL
MF       ref     FK_Figure_Artifact_idx,FK_Figure_Member_idx        FK_Figure_Artifact_idx  2           const           500000  100         NULL
ME       eq_ref  PRIMARY,FK_Member_Template_idx                     PRIMARY                 4           MF.MemberID     1       100         Using where
MC       ref     FK_Category_Member_idx,FK_Category_CatStandard_idx FK_Category_Member_idx  4           MF.MemberID     2       100         NULL
C        eq_ref  PRIMARY,FK_Category_CatStandard_idx                PRIMARY                 2           MC.CategoryID   1       50          Using where

注：FK_Figure_Artifact_idx实际用于WHERE MF.FigureTypeID = 1；移除该WHERE子句时，优化器会选择关联用索引FK_Figure_Member_idx。不含CTE关联时的EXPLAIN结果基本一致。

手动强制索引后的优化效果 #

添加索引强制提示：

/*+ INDEX(ME FK_Member_Template_idx) INDEX(MF FK_Figure_Member_idx) */

查询耗时从30秒以上降至0.3秒以内，新的EXPLAIN结果如下：

table   type    possible_keys                                       key                     key_len     ref             rows    filtered    Extra
CTE     ALL     NULL                                                NULL                    NULL        NULL            1       100         Using where
T       eq_ref  PRIMARY                                             PRIMARY                 4           CTE.TemplateID  1       100         NULL
ME      ref     FK_Member_Template_idx                              FK_Member_Template_idx  5           CTE.TemplateID  300000  100         NULL
MF      ref     FK_Figure_Member_idx                                FK_Figure_Member_idx    4           ME.MemberID     60      1.33        Using where
MC      ref     FK_Category_Member_idx,FK_CatStandard_Category_idx  FK_Category_Member_idx  4           ME.MemberID     2       100         NULL
C        eq_ref  PRIMARY,FK_Category_CatStandard_idx                 PRIMARY                 2           MC.CategoryID   1       50          Using where

核心疑问 #

• 为何关联CTE（JOIN/子查询形式）会导致性能骤降？优化器的决策逻辑是什么？
• 我强制指定索引的做法是否合理？此前极少需要手动干预索引选择，此处为何必须这么做？

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