从 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. 主查询统计两部分评论的总数：

3. 本身 ID 就在cte结果集中的评论（即根评论本身）。

4. 其父评论 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. 主查询优化索引：

3. id列：通常是主键，自带索引。

4. 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_acWHERE  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_commentWHERE 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_acWHERE  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_acJOIN 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_acJOIN CTE ON main_ac.ancestor_id = CTE.id -- 使用INNER JOINWHERE 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 有任何问题或意见，欢迎留言讨论。