MySQL 8.0 CTE 递归查询：执行计划剖析与性能优化实战

cover

一、层级数据的查询困局：递归 CTE 如何破局

处理组织架构、评论嵌套、物料清单等层级数据时，传统 SQL 需要多次自连接或借助应用层递归，代码冗长且性能堪忧。MySQL 8.0 引入的 CTE（Common Table Expression）和递归 CTE，用声明式语法替代过程式递归，让层级遍历变得简洁。但简洁的语法背后，优化器如何执行递归查询？递归深度对性能的影响有多大？这些问题直接决定生产环境中的查询效率。

递归 CTE 分为锚定成员（非递归部分）和递归成员（引用自身的部分），优化器将它们拆分为迭代执行：先执行锚定查询得到初始行集，再反复将递归查询作用于前一轮结果，直到没有新行产生。理解这个执行模型，是性能优化的前提。

二、递归 CTE 的执行流程

flowchart TD
    A[WITH RECURSIVE cte AS] --> B[锚定查询: SELECT ... FROM table WHERE parent IS NULL]
    B --> C[初始结果集 R0]
    C --> D[递归查询: SELECT ... FROM table JOIN cte]
    D --> E[第 1 轮结果 R1]
    E --> F{R1 为空?}
    F -->|否| G[递归查询: SELECT ... FROM table JOIN cte]
    G --> H[第 2 轮结果 R2]
    H --> I{R2 为空?}
    I -->|否| J[继续迭代...]
    I -->|是| K[合并 R0 + R1 + R2 + ...]
    F -->|是| K
    J --> K
    K --> L[返回最终结果]

三、生产级代码实现与优化

3.1 递归 CTE 基础：组织架构层级查询

-- 员工组织架构表
CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    manager_id BIGINT DEFAULT NULL,
    level INT NOT NULL DEFAULT 1,
    INDEX idx_manager (manager_id)
);

-- 递归 CTE：查询某员工的所有下属（含层级深度）
WITH RECURSIVE subordinates AS (
    -- 锚定成员：起始员工
    SELECT id, name, manager_id, level, 1 AS depth
    FROM employees
    WHERE id = 1001  -- 从指定员工开始

    UNION ALL

    -- 递归成员：查找下一级下属
    SELECT e.id, e.name, e.manager_id, e.level, s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth, id;

3.2 执行计划分析

EXPLAIN ANALYZE
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 1001
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

执行计划关键信息解读：

  • 锚定查询走 eq_ref，命中主键索引，仅扫描 1 行
  • 递归查询走 ref，命中 idx_manager 索引，每轮迭代扫描的行数取决于每层的下属数量
  • 临时表：递归 CTE 的中间结果存储在内部临时表中，每轮迭代将新结果追加到临时表
  • 迭代终止条件：递归成员返回 0 行时停止，或达到 cte_max_recursion_depth 限制

3.3 性能优化策略

-- 优化 1：限制递归深度，防止无限递归
SET SESSION cte_max_recursion_depth = 100;

-- 优化 2：在递归成员中添加深度限制，提前终止
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 1001
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
    WHERE s.depth < 5  -- 只查 5 层深度
)
SELECT * FROM subordinates;

-- 优化 3：递归 CTE + 聚合：计算每层下属数量
WITH RECURSIVE subordinates AS (
    SELECT id, manager_id, 1 AS depth
    FROM employees WHERE id = 1001
    UNION ALL
    SELECT e.id, e.manager_id, s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
    WHERE s.depth < 10
)
SELECT depth, COUNT(*) AS count_at_level
FROM subordinates
GROUP BY depth
ORDER BY depth;

3.4 复杂场景：递归 CTE 处理多层级评论

-- 评论表：支持多级嵌套回复
CREATE TABLE comments (
    id BIGINT PRIMARY KEY,
    post_id BIGINT NOT NULL,
    parent_id BIGINT DEFAULT NULL,
    content TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_post (post_id),
    INDEX idx_parent (parent_id)
);

-- 查询某帖子下的评论树，按层级和创建时间排序
WITH RECURSIVE comment_tree AS (
    -- 锚定：顶级评论
    SELECT
        id, post_id, parent_id, content, created_at,
        1 AS depth,
        CAST(id AS CHAR(200)) AS path  -- 记录路径用于排序
    FROM comments
    WHERE post_id = 42 AND parent_id IS NULL

    UNION ALL

    -- 递归：子评论
    SELECT
        c.id, c.post_id, c.parent_id, c.content, c.created_at,
        ct.depth + 1,
        CONCAT(ct.path, '-', c.id)  -- 路径拼接
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_id = ct.id
    WHERE ct.depth < 20  -- 防止过深递归
)
SELECT
    id, content, depth, path, created_at,
    RPAD('', (depth - 1) * 2, '─') AS indent  -- 缩进展示
FROM comment_tree
ORDER BY path;

3.5 Python 封装：递归 CTE 查询工具

from dataclasses import dataclass
from typing import List, Optional
import pymysql

@dataclass
class TreeNode:
    """树形节点"""
    id: int
    parent_id: Optional[int]
    depth: int
    path: str
    children: List['TreeNode'] = None

    def __post_init__(self):
        self.children = []

class RecursiveCTEQuery:
    """递归 CTE 查询工具类"""

    CTE_TEMPLATE = """
    WITH RECURSIVE tree AS (
        SELECT {columns}, 1 AS depth, CAST({pk} AS CHAR(500)) AS path
        FROM {table}
        WHERE {root_condition}

        UNION ALL

        SELECT {columns}, t.depth + 1, CONCAT(t.path, '-', c.{pk})
        FROM {table} c
        INNER JOIN tree t ON c.{fk} = t.{pk}
        WHERE t.depth < %s
    )
    SELECT * FROM tree ORDER BY path
    """

    def __init__(self, conn: pymysql.Connection):
        self.conn = conn

    def query_tree(
        self,
        table: str,
        pk: str,
        fk: str,
        columns: str,
        root_condition: str,
        max_depth: int = 20
    ) -> List[TreeNode]:
        """执行递归 CTE 查询并构建树形结构"""
        sql = self.CTE_TEMPLATE.format(
            columns=columns,
            table=table,
            pk=pk,
            fk=fk,
            root_condition=root_condition
        )

        with self.conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, (max_depth,))
            rows = cursor.fetchall()

        # 将扁平结果构建为树形结构
        nodes = {}
        roots = []
        for row in rows:
            node = TreeNode(
                id=row[pk],
                parent_id=row.get(fk),
                depth=row['depth'],
                path=row['path']
            )
            nodes[node.id] = node
            if node.depth == 1:
                roots.append(node)
            elif node.parent_id in nodes:
                nodes[node.parent_id].children.append(node)

        return roots

四、递归 CTE 的边界分析与性能权衡

临时表的内存压力。递归 CTE 的中间结果存储在内部临时表中，深度递归或广度大的层级会产生大量临时数据。当临时表超过 tmp_table_size 或 max_heap_table_size 时，会从内存临时表转换为磁盘临时表，性能急剧下降。建议对深度超过 10 层或单层超过 10000 行的递归查询进行监控。

递归查询的索引依赖。递归成员的 JOIN 条件必须命中索引，否则每轮迭代都是全表扫描，复杂度从 O(N×D)（D 为深度）退化为 O(N²)。确保递归 JOIN 列上有索引是性能底线。

UNION ALL 与 UNION 的选择。递归 CTE 只支持 UNION ALL，不支持 UNION（去重）。如果递归数据中存在环（如 A 的上级是 B，B 的上级又是 A），会导致无限递归。必须在递归成员中通过路径检测或深度限制来避免环路。

适用边界：递归 CTE 适合层级深度可控（<20 层）、每层数据量适中（<10000 行）的场景。对于深度不确定或数据量巨大的图遍历，应考虑在应用层使用图数据库或专门的图算法。

五、总结

MySQL 8.0 递归 CTE 用声明式语法解决了层级数据查询的痛点，执行模型是锚定查询 + 迭代递归。性能优化的关键在于：确保递归 JOIN 列命中索引、限制递归深度、监控临时表内存使用。对于存在数据环路的场景，必须通过路径检测或深度限制防止无限递归。在层级深度可控的业务中，递归 CTE 是比应用层递归更高效的选择。

# 云原生 # 容器 # 微服务 # kubernetes # k8s
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

cover

海外动态代理行业彻底换代，AI 数据时代新标准已到来

avatar AtomGit开源社区
cover

2026年论文写作AI哪个好用？7款工具实测对比，轻松拿捏

avatar AtomGit开源社区
cover

2026年SEO+GEO优化指南：搜索排名机制解析与实用工具推荐

avatar AtomGit开源社区