从40秒到11毫秒：TiDB环境下一次SQL深潜优化实战

作者： meathill 原文来源： https://tidb.net/blog/edb6061b

在数据库应用中，慢SQL是常见的性能瓶颈。本文将详细记录一次针对TiDB Cloud v7.5.2环境中复杂评论查询的SQL优化过程，如何通过分析执行计划、添加索引、改写SQL（使用 EXISTS 、 UNION ）等手段，将一个40多秒的查询逐步优化到11毫秒，希望能为读者提供有价值的实战参考。

不知道什么时候，TiDB Cloud 升级到 v7.5.2，于是我们的评论应用 RU 消耗开始起飞，达到以往月份的 3 倍左右。没办法，只好拖着病榻之躯来 Debug。还好 Gemini 2.5 Pro 给力，很快我就完成了这次优化，记录在这篇博客里。另外，这篇博客也是 Gemini 2.5 Pro 帮我写的，AI 之力，恐怖如斯 😱😱。

下文中的“我们”，是 Gemini 的自称。“用户”就是博主我。恐怖如斯，恐怖如斯。

一、问题的初现：一个耗时40秒的评论计数查询

故事的起点，是一个在TiDB Cloud v7.5.2版本上运行缓慢的SQL查询。其目的是统计符合特定条件的评论（ ac_comment 表）及其子评论的总数。原始SQL如下：

-- SQL查询一：原始计数查询
WITH `cte` AS (
  SELECT `id`
  FROM `ac_comment`
  WHERE `post_id` = ?  -- 参数绑定
    AND STATUS = ?     -- 参数绑定
    AND `ancestor_id` = ? -- 参数绑定 (例如0，代表根评论)
    AND `deleted_at` IS NULL -- 注意原始为 IS ?，但逻辑上应为 IS NULL
)
SELECT
  count('x') AS `num` -- 注意：count('x') 非标准，但功能类似 count(*)
FROM `ac_comment`
WHERE `id` IN (SELECT * FROM `cte`) -- 匹配CTE中的评论
  OR (
    `ancestor_id` IN (SELECT * FROM `cte`) -- 匹配父评论在CTE中的评论
    AND STATUS = ?     -- 参数绑定
    AND `deleted_at` IS NULL -- 参数绑定
  );

这个查询的逻辑是：

1. 通过一个公共表表达式（CTE） cte 筛选出特定帖子 ( post_id ) 下的“根评论”（假设 ancestor_id = 0 代表根评论，且状态正常、未删除）。
2. 主查询统计两部分评论的总数：
   • 本身ID就在 cte 结果集中的评论（即根评论本身）。
   • 其父评论ID ( ancestor_id ) 在 cte 结果集中，并且自身状态正常、未删除的评论（即这些根评论的直接子评论）。

初次拿到这个查询的执行计划（ EXPLAIN ANALYZE ），我们发现其耗时高达 40.5秒 。执行计划的核心问题点暴露无遗：

• 罪魁祸首： CARTESIAN left outer semi join ：执行计划中出现了刺眼的“CARTESIAN”字样。这意味着在处理 IN (SELECT * FROM cte) 时，优化器未能找到有效的连接键或索引来进行关联，而是退化到了类似笛卡尔积的低效操作。这通常是性能杀手。
• TableFullScan ：对 ac_comment 表进行了全表扫描，当表数据量大时，这无疑是雪上加霜。

初步诊断：查询结构中的 IN (CTE) 与 OR 条件的组合，使得优化器难以生成高效的执行计划。

二、初次尝试：索引的力量能拯救一切吗？

面对慢SQL，我们的第一反应通常是检查索引。合理的索引是数据库高性能的基石。针对上述查询，我们推荐了以下索引：

1. CTE优化索引 ：针对CTE的 WHERE 条件，创建复合索引 (post_id, status, ancestor_id, deleted_at, id) ，期望能覆盖CTE的查询。
2. 主查询优化索引 ：
   • id 列：通常是主键，自带索引。
   • ancestor_id 相关条件：为 (ancestor_id, status, deleted_at) 创建复合索引，以加速 OR 条件的第二部分。

用户反馈，索引添加后，"目测还是有 slow sql 产生"。我们再次获取了执行计划：

耗时从 40.5秒 降至 36.9秒 ，略有改善，但远未达到预期。仔细分析计划，发现：

• CARTESIAN 连接依然存在 ：核心问题未解决。
• 索引误用 ：在处理 id IN (CTE) 这部分逻辑时，优化器竟然选择对一个为 ancestor_id 设计的索引 ( ac_comment_ancestor_count_index ) 进行了全扫描 ( IndexFullScan_30 )，而非使用 id 列的主键索引。

结论 ：对于这个查询，仅添加索引不足以根治问题。查询本身的结构对TiDB优化器造成了困扰，使其无法有效利用索引并选择了低效的执行路径。

三、转换思路：用 EXISTS 改写SQL结构

既然索引效果不彰，我们需要从SQL结构本身入手。 IN (subquery) 在某些情况下确实容易引发性能问题，一个常见的优化思路是将其改写为 EXISTS 。 EXISTS 通常能更明确地向优化器表达半连接（semi-join）的意图。

我们将计数查询改写如下：

-- SQL查询一：EXISTS改写版本 (计数)
WITH CTE AS (
    SELECT id
    FROM ac_comment
    WHERE post_id=? AND status=1 AND ancestor_id=0 AND deleted_at IS NULL
)
SELECT COUNT(*) AS num -- 改为 COUNT(*)
FROM ac_comment AS main_ac
WHERE
  EXISTS (SELECT 1 FROM CTE WHERE CTE.id = main_ac.id)
  OR (
    EXISTS (SELECT 1 FROM CTE WHERE CTE.id = main_ac.ancestor_id)
    AND main_ac.status = 1
    AND main_ac.deleted_at IS NULL
  );

再次查看执行计划，惊喜出现了！

执行时间从 36.9秒 骤降至 498毫秒 ！提升约74倍。关键变化：

• CARTESIAN 连接消失了 ！取而代之的是 left outer semi join 。优化器正确理解了半连接的语义，执行效率大大提升。
• CTE本身执行仍然很快（ actRows: 7506 ，构建哈希表约 270ms ）。
• 美中不足 ：在处理 EXISTS (CTE.id = main_ac.id) 时，Probe端（ IndexReader_33 ）依然是对为 ancestor_id 设计的索引进行了全扫描。理想情况下，应使用 main_ac.id 的主键索引。但由于semi join的效率以及CTE结果集的大小（7506行），这个问题的影响已不像笛卡尔积时那么致命。

结论 ： EXISTS 改写是本次优化的一个重要转折点，它成功引导优化器避开了最低效的执行路径。

四、新的挑战：分页查询的优化

在解决了计数查询的性能问题后，用户提出了一个新的、结构类似的查询，但这次是获取实际数据并带有分页逻辑：

-- SQL查询二：原始分页查询
WITH CTE AS (
    SELECT id
    FROM ac_comment
    WHERE post_id=${post_id}
        AND status=1
        AND ancestor_id=0
        AND deleted_at IS NULL
    ORDER BY id DESC
    LIMIT ${start}, 21 -- 分页，取出21条
)
SELECT * -- 获取所有列
FROM ac_comment
WHERE id IN (SELECT * FROM CTE)
    OR (ancestor_id IN (SELECT * FROM CTE)
        AND status=1
        AND deleted_at IS NULL
    );

该查询的目的是获取某篇文章下“根评论”的一个分页结果（21条），以及这些根评论的直接子评论。

我们沿用之前的成功经验，将其用 EXISTS 改写：

-- SQL查询二：EXISTS改写版本 (分页)
WITH CTE AS (
    SELECT id FROM ac_comment
    WHERE post_id=${post_id} AND status=1 AND ancestor_id=0 AND deleted_at IS NULL
    ORDER BY id DESC LIMIT ${start}, 21
)
SELECT main_ac.*
FROM ac_comment AS main_ac
WHERE
  EXISTS (SELECT 1 FROM CTE WHERE CTE.id = main_ac.id)
  OR (
    EXISTS (SELECT 1 FROM CTE WHERE CTE.id = main_ac.ancestor_id)
    AND main_ac.status = 1 AND main_ac.deleted_at IS NULL
  );

执行计划显示，耗时为 1.01秒 ，最终返回42行。

分析结果：

• 依然没有 CARTESIAN 连接 ：优化器正确使用了 semi join 。
• CTE执行极快 ：由于 LIMIT 21 ，CTE部分仅耗时 2.4ms 左右，返回21行。
• 瓶颈转移 ：查询的主要耗时（约 917ms ）在于 HashJoin_47 的Probe端—— TableReader_49 ，它对 main_ac 表（即 ac_comment ）执行了 全表扫描 ( TableFullScan_48 ) 。 尽管CTE只有21行，优化器在处理 OR 条件，特别是 EXISTS (CTE.id = main_ac.id) 时，仍然选择全表扫描 main_ac 。这可能是因为它需要评估 OR 的另一半，或者 SELECT * 的成本考量。

虽然1.01秒相比最初已经好太多，但全表扫描的存在暗示着仍有优化空间。

五、终极一击： UNION 改写带来的极致性能

OR 条件有时确实会给优化器带来困扰，使其难以对 OR 的各个分支独立采用最优访问路径。一种更彻底的优化策略是将 OR 条件拆分为多个独立的查询，然后用 UNION （或 UNION ALL ）合并结果。

我们对分页查询进行了如下 UNION 改写：

-- SQL查询二：UNION改写版本 (分页)
WITH CTE AS (
    SELECT id
    FROM ac_comment
    WHERE post_id=${post_id} AND status=1 AND ancestor_id=0 AND deleted_at IS NULL
    ORDER BY id DESC LIMIT ${start}, 21
)
-- Part 1: 匹配根评论 (id IN CTE)
SELECT main_ac.*
FROM ac_comment AS main_ac
JOIN CTE ON main_ac.id = CTE.id -- 使用INNER JOIN，因为EXISTS隐含了匹配

UNION -- UNION会自动去重，确保与OR逻辑一致

-- Part 2: 匹配子评论 (ancestor_id IN CTE AND status=1 AND deleted_at IS NULL)
SELECT main_ac.*
FROM ac_comment AS main_ac
JOIN CTE ON main_ac.ancestor_id = CTE.id -- 使用INNER JOIN
WHERE main_ac.status = 1 AND main_ac.deleted_at IS NULL;

这次改写带来了惊人的效果，执行计划显示耗时仅为 11.4毫秒 ！

关键变化：

• 全表扫描彻底消失 ：
  • UNION 的第一个分支（匹配根评论）： IndexHashJoin_63 的Probe端显示 TableRangeScan_57 ，其 range: decided by [example.ac_comment.id] 。这意味着它利用 main_ac.id 上的索引（主键）对CTE返回的21个ID进行了高效的 点查 。
  • UNION 的第二个分支（匹配子评论）： IndexHashJoin_80 的Probe端显示 IndexLookUp_77 。这意味着它利用了 main_ac.ancestor_id 和 status 、 deleted_at 上的复合索引来查找匹配的子评论，然后回表获取数据。
• HashAgg_53 ：顶部的 HashAgg 操作是 UNION （等同于 UNION DISTINCT ）为了去除重复行而执行的。由于总共最多处理42行，这个开销极小。

通过 UNION 将复杂的 OR 逻辑分解为两个独立的、更简单的查询，使得TiDB优化器能够为每个子查询都选择最优的、基于索引的访问路径，从而达到了极致的性能。

六、优化总结与启示

这次从40多秒到11毫秒的优化之旅，充满了抽丝剥茧的分析和柳暗花明的尝试，带给我们几点深刻的启示：

1. EXPLAIN ANALYZE 是你的导航仪 ：没有它，我们就像在黑暗中航行。它是诊断慢SQL、理解优化器行为、验证优化效果不可或缺的工具。务必学会阅读和理解它的输出。
2. 索引是基础，但并非万能 ：合理的索引是高性能的起点。但如此案例所示，当SQL查询结构本身对优化器不友好时，仅靠索引可能无法解决根本问题。
3. 警惕 IN (subquery) 的陷阱 ：尤其当子查询返回较多行或与 OR 等复杂条件结合时， IN 子查询有时会被优化器错误处理。考虑使用 EXISTS 或 JOIN 作为更稳健的替代方案。
4. OR 条件有时是优化的“硬骨头” ：复杂的 OR 条件可能让优化器难以抉择，导致选择次优的全局计划（如全表扫描）。通过 UNION 或 UNION ALL 将 OR 的各个分支拆解，让每个分支独立优化，往往能奇效。
5. 迭代优化，不断试错 ：SQL优化往往不是一蹴而就的。它需要我们根据执行计划的反馈，不断调整索引策略、改写SQL，是一个分析、假设、验证、再分析的迭代过程。
6. 理解业务逻辑与数据特性 ：例如，CTE中 LIMIT 21 使得CTE结果集极小，这对后续的JOIN策略有很大影响。了解数据分布和业务场景有助于做出更合理的优化判断。

七、结语

SQL优化是一门技术，更是一门艺术。它要求我们不仅要理解SQL的语法，更要洞察数据库优化器的工作原理。通过这次实战，我们不仅解决了一个棘手的性能问题，更重要的是深化了对TiDB查询优化机制的理解。希望这个案例能为你未来的SQL优化工作带来一些启发和帮助。记住，每一个慢查询背后，都可能隐藏着一次学习和提升的绝佳机会。

总之，希望大家所有收获，如果大家对 SQL、Web 全栈开发、AI 使用、Gemini 有任何问题或意见，欢迎留言讨论。