提高SQL CTE查询性能

Question

有没有办法提高以下CTE查询的性能（@E和@R是实际系统中具有索引的表）：

DECLARE @id bigint = 1

DECLARE @E TABLE
(
id bigint,
name varchar(50)
)

DECLARE @R TABLE
(
child_id bigint,
parent_id bigint
)

INSERT INTO @E SELECT 1, 'one'
INSERT INTO @E SELECT 2, 'two'
INSERT INTO @E SELECT 3, 'three'
INSERT INTO @E SELECT 4, 'four'
INSERT INTO @E SELECT 5, 'five'
INSERT INTO @E SELECT 6, 'six'
INSERT INTO @E SELECT 7, 'seven'

INSERT INTO @R SELECT 1, 2
INSERT INTO @R SELECT 1, 3
INSERT INTO @R SELECT 3, 4
INSERT INTO @R SELECT 5, 4
INSERT INTO @R SELECT 3, 6
INSERT INTO @R SELECT 7, 4

; WITH cte
(
child_id,
parent_id
)

AS (

SELECT * FROM @R R
WHERE R.child_id = @id

UNION ALL
SELECT R.* FROM @R R
INNER JOIN cte ON CTE.parent_id = R.child_id

)
SELECT * FROM @E E
WHERE e.id = @id
UNION ALL
SELECT P.* FROM @E E
INNER JOIN cte ON 1=1
INNER JOIN @E P ON P.id = cte.parent_id
WHERE e.id = @id
ORDER BY 1

预期结果：

id | name
1  | one 
2  | two
3  | three
4  | four
6  | six

在真实世界的数据中，我将在 @R 中处理数百万行，在 @E 中处理约十万行。所以我想知道是否有任何办法可以挤出更多的性能。

编辑：为了澄清和总结到目前为止的情况，R 上有一个聚集 pk 索引，其中包含 child_id、parent_id，添加一个索引到 @r.parent_id 也将提高连接性能。

还有其他方式可以改进吗？在 CTE 之后使用 inner join 1=1 的部分是否有任何可以改进的地方，还是已经达到最佳状态了？是否有任何其他模式设计可以实现类似的父子映射并提高性能？

- Seph

---

确保用于连接的任何列都被索引，例如child_id和parent_id等 - 这将提高JOIN性能。 - marc_s

你知道在现实世界的数据中，你可能会有多少层父子关系吗？ - user359040

@marc_s，这个表将会有一个基于[child_id, parent_id]的聚集索引。 - Seph

@MarkBannister：现实世界的数据可能只会下降到大约10个级别，最多可能达到20个级别，但没有直接的限制。 - Seph

@Seph：这还不够好-你应该在(child_id)和(parent_id)上分别建立索引以加快JOIN性能。如果你按照这个顺序在(child_id, parent_id)上建立复合索引，那么它可以用于child_id单独使用-但不能用于parent_id单独使用（而且它需要两者）。 - marc_s

@marc_s 确实，我完全没注意到，我不确定当递归运行时 CTE 如何处理索引。 - Seph

Answer 1

正如marc_s所指出的那样，对于这个表来说，一个以[child_id, parent_id]为键的集群索引并不够好——你应该有单独的索引在[child_id]和[parent_id]上来加速JOIN操作。如果你按照[child_id, parent_id]的顺序建立了一个复合索引，那么它可以用于[child_id]，但不能用于[parent_id]（需要两个索引）—— marc_s Dec 19 '11 at 12:35

这大大提高了查询性能，并帮助我理解CTE查询的内部工作原理。

- Seph

Answer 2

对于初学者：

DECLARE @E TABLE
(
    id BIGINT PRIMARY KEY,
    name varchar(50)
)

DECLARE @R TABLE
(
    child_id bigint,
    parent_id BIGINT,
    PRIMARY KEY(child_id, parent_id),
    UNIQUE (parent_id, child_id)
)

但是请记住，Sql Server在优化CTE方面非常差。

- Oleg Dok

---

你有关于“SQL Server 在优化CTEs方面非常差”的证据吗？ 这不是我的个人经验... - MatBailie

好的，我会更加礼貌一些 - 有时候SQL Server在优化公共表达式方面非常差劲。如果您想要适当的证据 - 我会尝试找出来，但我已经遇到过这种情况几次了。 - Oleg Dok

记住这个问题 - 如果你在CTE内部使用排名函数 - 例如ROW_NUMBER() - 那么CTE会变得非常缓慢。 - Oleg Dok

@Oleg：你确定不是因为你使用了ROW_NUMBER()并按非索引列排序吗？ - Andriy M