当前位置：首页 > article >正文

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

article 2025/9/20 22:54:07

作者： 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
)
SELECTcount('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 -- 参数绑定);

这个查询的逻辑是：

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

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

id	estRows	actRows	task	access object	execution info	operator info
HashAgg_33	1.00	1	root		time:40.5s, loops:2	funcs:count("x")->Column#33
└─Selection_35	28510.72	15053	root		time:40.5s, loops:16	or(Column#30, and(Column#32, ...))
└─HashJoin_36	35638.40	42854	root		time:40.5s, loops:44, probe:{max:40.5s,...}	CARTESIAN left outer semi join , other cond:...
├─...	...	...	...
└─TableFullScan_39	44548.00	44548	cop[tikv]	table:ac_comment

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

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

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

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

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

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

id	estRows	actRows	task	execution info	operator info
HashAgg_20	1.00	1	root	time:36.9s, loops:2	funcs:count("x")->Column#33
└─Selection_22	28520.96	15055	root	time:36.9s, loops:16	or(Column#30, and(Column#32, ...))
└─HashJoin_23	35651.20	42874	root	time:36.9s, loops:44, probe:{max:36.9s,...}	CARTESIAN left outer semi join , other cond:...
└─...	...	...	...
└─IndexReader_31	44564.00	44563	root		index:IndexFullScan_30 (ac_comment_ancestor_count_index)

耗时从 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 idFROM ac_commentWHERE 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
WHEREEXISTS (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 = 1AND main_ac.deleted_at IS NULL);

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

id	estRows	actRows	task	access object	execution info	operator info
HashAgg_22	1.00	1	root		time:498ms, loops:2	funcs:count("x")->Column#35
└─Selection_24	28520.96	15055	root		time:497.4ms, loops:16	or(Column#31, and(Column#34, ...))
└─HashJoin_25	35651.20	42874	root		time:492.2ms, loops:44, probe:{max:497.1ms,...}	left outer semi join , equal:[eq(example.ac_comment.ancestor_id, example.ac_comment.id)]
├─CTEFullScan_35	7452.00	7506	root	CTE:cte	time:275.5ms	data:CTE_0
└─...
└─HashJoin_27	44564.00	44563	root		time:469ms, loops:47, probe:{max:482.1ms,...}	left outer semi join , equal:[eq(example.ac_comment.id, example.ac_comment.id)]
├─CTEFullScan_34	7452.00	7506	root	CTE:cte	time:263.6ms	data:CTE_0
└─IndexReader_33	44564.00	44563	root		time:263.6ms	index:IndexFullScan_32 (ac_comment_ancestor_count_index)

执行时间从 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 idFROM ac_commentWHERE post_id=${post_id}AND status=1AND ancestor_id=0AND deleted_at IS NULLORDER BY id DESCLIMIT ${start}, 21 -- 分页，取出21条
)
SELECT * -- 获取所有列
FROM ac_comment
WHERE id IN (SELECT * FROM CTE)OR (ancestor_id IN (SELECT * FROM CTE)AND status=1AND deleted_at IS NULL);

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

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

-- SQL查询二：EXISTS改写版本 (分页)
WITH CTE AS (SELECT id FROM ac_commentWHERE post_id=${post_id} AND status=1 AND ancestor_id=0 AND deleted_at IS NULLORDER BY id DESC LIMIT ${start}, 21
)
SELECT main_ac.*
FROM ac_comment AS main_ac
WHEREEXISTS (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行。

id	estRows	actRows	task	access object	execution info	operator info
Projection_43	28521.60	42	root		time:1.01s, loops:2	...
└─Selection_44	28521.60	42	root		time:1.01s, loops:2	or(Column#31, and(Column#34, ...))
└─HashJoin_45	35652.00	42874	root		time:1.01s, loops:44, build:{total:2.5ms,...}	left outer semi join , equal:[eq(main_ac.ancestor_id, CTE.id)]
├─CTEFullScan_51	21.00	21	root	CTE:cte	time:2.44ms	data:CTE_0
└─Selection_46	35652.00	42874	root		time:1.01s, loops:43	or(Column#31, and(eq(main_ac.status,1),...))
└─HashJoin_47	44565.00	44565	root		time:930ms, loops:46, build:{total:2.33ms,...}	left outer semi join , equal:[eq(main_ac.id, CTE.id)]
├─CTEFullScan_50	21.00	21	root	CTE:cte	time:2.3ms	data:CTE_0
└─TableReader_49	44565.00	44565	root		time:917.6ms, cop_task:{max:917ms,...}	data:TableFullScan_48
└─TableFullScan_48	44565.00	44565	cop[tikv]	table:main_ac	tikv_task:{time:130ms,...}
CTE_0	21.00	21	root		time:2.44ms	Non-Recursive CTE

分析结果：

依然没有 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 idFROM ac_commentWHERE post_id=${post_id} AND status=1 AND ancestor_id=0 AND deleted_at IS NULLORDER 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毫秒 ！

id	estRows	actRows	task	access object	execution info	operator info
HashAgg_53	46.14	42	root		time:11.4ms, loops:6	group by:all_columns..., funcs:firstrow(...)
└─Union_54	46.14	42	root		time:7.4ms, loops:3
├─Projection_55	13.44	21	root		time:6.76ms, loops:2	...
│ └─IndexHashJoin_63	13.44	21	root		time:6.59ms, loops:2	inner join, inner:TableReader_58, outer key:main_ac.id, inner key:CTE.id
│ ├─...CTE...	16.80	21	root	CTE:cte	time:2.8ms	data:CTE_0
│ └─TableReader_58	13.44	21	root		time:2.88ms	data: TableRangeScan_57 (range decided by main_ac.id)
└─Projection_72	32.70	21	root		time:4.99ms, loops:2	...
└─IndexHashJoin_80	32.70	21	root		time:4.97ms, loops:2	inner join, inner: IndexLookUp_77 , outer key:CTE.id, inner key:main_ac.ancestor_id

关键变化：

全表扫描彻底消失 ：
- 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毫秒的优化之旅，充满了抽丝剥茧的分析和柳暗花明的尝试，带给我们几点深刻的启示：

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

七、结语

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

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