这些使用OR的T-SQL查询有什么区别？

Question

我可以为您翻译。以下是您需要翻译的内容：

我使用的是Microsoft SQL Server 2008（SP1，x64版）。我有两个查询，它们执行相同的操作（或者我认为是这样），但是它们具有完全不同的查询计划和性能。

查询1：

SELECT c_pk
FROM table_c
WHERE c_b_id IN (SELECT b_id FROM table_b WHERE b_z = 1)
  OR  c_a_id IN (SELECT a_id FROM table_a WHERE a_z = 1)

查询2：

SELECT c_pk
FROM table_c
LEFT JOIN (SELECT b_id FROM table_b WHERE b_z = 1) AS b ON c_b_id = b_id
LEFT JOIN (SELECT a_id FROM table_a WHERE a_z = 1) AS a ON c_a_id = a_id
WHERE b_id IS NOT NULL
  OR  a_id IS NOT NULL

查询1的速度很快，符合我的预期，而查询2非常慢。这两个查询的查询计划看起来非常不同。

我希望查询2的速度能像查询1一样快。我有使用查询2的软件，无法将其更改为查询1。但我可以更改数据库。

一些问题：

• 为什么查询计划不同？
• 我能否以某种方式“教”SQL Server查询2等于查询1？

所有表都有（聚集）主键和所有列上的适当索引。

CREATE TABLE table_a (
  a_pk   int NOT NULL PRIMARY KEY,
  a_id   int NOT NULL UNIQUE,
  a_z    int
)
GO
CREATE INDEX IX_table_a_z ON table_a (a_z)
GO

CREATE TABLE table_b (
  b_pk   int NOT NULL PRIMARY KEY,
  b_id   int NOT NULL UNIQUE,
  b_z    int
)
GO
CREATE INDEX IX_table_b_z ON table_b (b_z)
GO

CREATE TABLE table_c (
  c_pk   int NOT NULL PRIMARY KEY,
  c_a_id int,
  c_b_id int
)
GO
CREATE INDEX IX_table_c_a_id ON table_c (c_a_id)
GO
CREATE INDEX IX_table_c_b_id ON table_c (c_b_id)
GO

这些表在最初填充后不会被修改。只有我一个人在查询它们。它们包含数百万条记录（table_a: 5M, table_b: 4M, table_c: 12M），但只使用1%就可以得到类似的结果。

编辑：我尝试为c_a_id和c_b_id添加外键，但这只会使查询1变慢...

希望有人可以查看查询计划并解释差异。请参考此处的查询计划。

- Michel de Ruiter

---

这是出于什么动机？在SQL Server中，“IN / EXISTS”通常比“OUTER JOIN ... NULL”更有效，而且第一个查询对我来说似乎更清晰，那为什么不只使用第一个呢？ - Martin Smith

@Martin “我有使用查询2的软件，我无法更改它” - Michel de Ruiter

我99%确信它们在这种情况下似乎具有相同的语义。但这并不意味着查询优化器具有将一个转换为另一个的必要转换规则。通常查询的编写方式会影响计划。您是否尝试在查询上使用计划指南（使用USE PLAN提示），以尝试让第二个查询使用第一个查询的计划？ - Martin Smith

@Martin，我没有使用计划指南的经验。看起来这些也无法帮助我，因为我有许多其他类似于a_z和b_z（比如a_y和b_y）的列，都存在完全相同的问题。 - Michel de Ruiter

我刚试了一个计划指南，但它拒绝运行，所以SQL Server似乎无法为其生成该计划。 - Martin Smith

Answer 1

联接查询速度慢，这是出于 设计原因。第一个查询使用子查询（可缓存）来过滤记录，从而产生更少的数据（并减少对每个表的访问次数）。

你有阅读过这些吗：

• http://www.sql-server-performance.com/2006/tuning-joins/
• http://blogs.msdn.com/b/craigfr/archive/2006/12/04/semi-join-transformation.aspx

我想说的是，在使用 IN 时，数据库可以进行更好的优化，比如去重、在第一个匹配项处停止等（这些都源自于学校记忆，所以我相信它会做得更好）。因此，我猜问题不在于 QP 为什么不同，而在于优化能够达到多么智能和深入。

- Adriano Repetti

---

"IN" 是半连接。不确定您所说的可缓存子查询是什么意思。 - Martin Smith

SQL Server在优化JOIN和子查询方面非常出色，并且会使用最快的查找方式。但在这种情况下不是这样的。我了解索引，我认为你的链接没有添加任何相关内容。 - Michel de Ruiter

增加了一些我所指的解释。 - Adriano Repetti

Answer 2

您正在比较不相等的查询，而且您使用了非常不寻常的左连接方式。 通常，如果您的意图是选择表格C中所有具有与表格A或表格B关联记录的条目，则应使用exists语句：

SELECT c_pk 
FROM table_c 
WHERE  Exists( 
 SELECT 1
 FROM table_b 
 WHERE b_z = 1 and c_b_id = b_id 
) OR  Exists( 
 SELECT 1 
 FROM table_a 
 WHERE a_z = 1 and c_a_id = a_id
) 

- Vitaliy Kalinin

---

如果您发布代码、XML或数据示例，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（{}）以使其格式化和语法高亮！ - marc_s

Answer 3

既然您无法更改查询，那么至少可以改善查询的环境。

1. 在SSMS中突出显示查询，右键单击并选择“在数据库引擎调整顾问中分析查询”。
2. 运行分析以查看是否需要构建任何其他索引或统计信息。
3. 遵循SQL Server的建议。

- JeffO

---

我在我的SSMS中没有看到任何“调整顾问”。估计的执行计划没有显示任何缺失的索引。所有列都已经建立了索引，你认为还有什么需要添加的吗？ - Michel de Ruiter

@MicheldeRuiter - 疑惑你没有其他可以添加的内容了。你需要重写查询或接受这种性能。在这种情况下，SQL Server 似乎无法将 OR 转换为 UNION，因此它正在处理 table_c 中的所有行外连接到其他两个表，然后在最后进行过滤。 - Martin Smith

你可能使用的是免费版本，该功能不可用或者你没有安装它。 - JeffO