如何在MySQL8.0中排查公共表表达式CTE缺少索引导致的慢查询？-mysql教程-PHP中文网

CTE本身不建索引，慢查询主因是基础表关联字段缺失索引或JOIN方向错误；需用EXPLAIN确认执行路径，确保parent_id等关键字段有索引，并将CTE置于JOIN右侧作为被驱动表。

如何在mysql8.0中排查公共表表达式cte缺少索引导致的慢查询？

CTE本身不建索引，但驱动表必须有

MySQL 8.0 的 WITH 子句（包括 WITH RECURSIVE）定义的是临时结果集，它不生成物理索引，也不继承原表索引。慢的根本原因往往不是 CTE 写法本身，而是 CTE 中引用的**基础表在 JOIN 或 WHERE 条件中没走索引**。比如递归 CTE 里写 JOIN departments d ON d.parent_id = dt.id，如果 departments.parent_id 没索引，每轮递归都会全表扫描 departments，性能随层级指数恶化。

实操建议：

先确认 CTE 引用的所有基础表，在关联字段上是否已建索引：比如 parent_id、id、status 等被用于 ON 或 WHERE 的列
对递归方向敏感的字段单独建索引：向上查祖先（WHERE id = ? → parent_id = ?）要确保 parent_id 有索引；向下查子树（WHERE parent_id = ?）同样依赖 parent_id 索引
避免在 CTE 定义中对索引列做函数操作，如 DATE(created_at) 或 UPPER(name)，这会让关联失效

用EXPLAIN看清楚CTE里哪一步在全表扫描

EXPLAIN 能展示 CTE 执行时每个子查询的实际访问路径，但要注意：MySQL 8.0 对 CTE 的 EXPLAIN 输出是“展开后”的执行计划，不是按 WITH 块分层显示。你看到的 type=ALL 或 key=NULL 往往就藏在某次 JOIN 或子查询中。

实操建议：

把完整 CTE 查询复制出来，前面加 EXPLAIN FORMAT=TREE（推荐）或 EXPLAIN FORMAT=TRADITIONAL，重点看 table 列对应的基础表名，再核对 type 和 key
特别关注递归成员里的 JOIN：如果 table 是大表且 type=ALL，基本就是它没索引
非递归 CTE 同样适用：比如 WITH recent_orders AS (SELECT ... FROM orders WHERE order_date > '2025-01-01')，若 orders.order_date 无索引，主查询一 JOIN 就扫全表

cte_max_recursion_depth=0 会掩盖真实问题

很多慢 CTE 查询根本跑不到索引问题那步——因为递归功能被禁用了。云厂商或 DBA 常把 cte_max_recursion_depth 设为 0，此时 WITH RECURSIVE 会直接报错 ERROR 3636 或更隐蔽的 ERROR 1223，但有些应用层错误处理不完善，会静默重试或降级，反而表现为“查询卡住”或“超时”，让人误以为是索引慢。

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

实操建议：

排查前先执行 SELECT @@cte_max_recursion_depth;，值必须 > 0；若为 0，需用 SET SESSION cte_max_recursion_depth = 100; 临时启用（无需 SUPER 权限）
别只看慢日志里有没有 CTE 相关 SQL——禁用状态下它压根不会进慢日志，得靠 SHOW PROCESSLIST 看是否有长时间 Creating sort index 或 Copying to tmp table 状态
如果递归 CTE 执行时间随数据量非线性增长（比如 3 层 100ms，5 层 2s，7 层 30s），大概率是某张表缺失关键索引，而非递归深度设置问题

递归 CTE 的 JOIN 方向决定索引是否生效

递归 CTE 的执行是“上轮结果 → 驱动下轮 JOIN”，所以 JOIN 顺序直接影响索引使用效率。常见错误是把 CTE 当作驱动表（LEFT JOIN），导致每次都要拿膨胀中的 CTE 结果去扫大表。

实操建议：

正确写法：让 CTE 放在 JOIN 右侧，作为被驱动表，例如 FROM departments d INNER JOIN dept_tree dt ON d.parent_id = dt.id —— dt 每轮行数可控，d.parent_id 索引可高效定位
错误写法：FROM dept_tree dt INNER JOIN departments d ON dt.id = d.parent_id —— dt 每轮可能已达数千行，d 却无索引可用，变成嵌套循环全表扫描
验证方式：在 EXPLAIN 输出中看 rows 列，若某次 JOIN 的预估扫描行数远超基础表总行数，说明驱动方向反了