SQL Server 查询连接优化

Question

我在网上寻找答案，但没有找到一个明确的答案。例如，你有两个连接子句：

1.

JOIN T2 ON T1.[ID] = T2.[ID]

2.

JOIN T2 ON T1.[ID] = REPLACE(T2.[ID],'A', '')

第二个执行效果不佳，原因是连接子句中的函数。确切的原因是什么？

例如，如果这段代码在存储过程中，最好优化它的方法是什么？将替换函数移除并添加到表级别，以便在任何连接之前完成所有操作吗？

如果您有任何建议或者更多信息的链接，那就太好了。谢谢。

- Ryan Gadsdon

---

第二个 JOIN 的参数不是sargable（可查询的）。这个词可能不在字典里，但它是你想要用来调查此问题的关键术语。 - Jeroen Mostert

当您将函数应用于连接参数或WHERE子句中的字段时，服务器无法使用该字段上的任何索引。索引是使用字段的值创建的，而查询想要使用不同的值。为了生成这些值，服务器必须扫描整个表。 - Panagiotis Kanavos

顺便问一下，为什么ID会包含任何需要替换的内容？也许它是两个字段伪装成一个？这是一个糟糕的设计——键不应该有任何可能或需要更改的业务含义。 - Panagiotis Kanavos

保留第一个连接，并添加一个条件来排除t2的'A'记录，然后使用所有t2的'a'行联合选择，再次直接连接--请参见我的答案。 - Stefanos Zilellis

Answer 1

在您的第二个示例中，您试图在T2中查找记录 - 但是与T1.ID值不同，您将一个函数应用于T2.ID - REPLACE(T2.[ID],'A', '') 如果T2.ID上有索引，则最好扫描索引而不是寻求它 - 这样会导致性能差异。

这就是解释更难的地方 - 索引存储为b+树，其中包含表上T2.ID的值。索引了解该字段并可以搜索/排序，但它不了解应用于它的任何逻辑。

它不知道REPLACE('A123','A', '') = 123 - 不在执行函数时在索引值上检查结果的相等性。

AAA123也相等，1A23，12A3，123A等，实际上有无数种组合可以匹配 - 但是只有在运行函数后才能找出单个索引条目是否匹配，然后检查相等性。

如果它只能通过运行索引值通过函数来确定 - 它只能在对索引中的每个条目进行这样做时正确回答查询 - 即扫描每个条目，传递到函数中并检查输出。

正如Jeroen所提到的术语是SARGable或SARGability，Search ARGumentABLE，尽管我个人更喜欢将其解释为Seek ARGumentABLE，因为这更接近查询计划运算符。

应该注意的是，这个概念与它是联接无关的，SQL中的任何谓词都有此限制 - 具有where谓词的单个表查询可能存在相同的问题。

可以避免这个问题吗？只有在某些情况下才能这样做，其中您可以反转操作。

考虑一个具有ID列的表，我可以构造这样的谓词： WHERE ID * 2 = @paramValue SQL Server知道ID条目乘以2是否为传递的值的唯一方法是处理每个条目，将其加倍并检查。因此，这是索引扫描场景。

在这种情况下，我们可以重写它： WHERE ID = @paramValue / 2.0 现在，SQL Server将执行数学运算一次，将传入的值除以2，然后可以以可寻址的方式检查它与索引的匹配项。编写的SQL的差异看起来可能是解释问题的微不足道的差异，但对数据库解决谓词的方式产生了非常大的影响。

- Andrew

Answer 2

SQL Server有四种基本的处理连接（join）的方法（其他数据库也有类似的方法）：

1. 无索引的嵌套循环。这类似于两个嵌套的for循环，通常是最慢的方法。
2. 索引循环（带索引的嵌套循环）。这是对一个表进行扫描并在第二个表中查找。
3. 合并连接。这假设两个表都是有序的，并同时遍历这两个表（也可以使用索引实现）。
4. 哈希连接。将两个表的键哈希化，并使用哈希表进行匹配。

一般来说，第一个方法是最慢的，第二个方法——使用索引——是最快的。（当然也有例外情况）第二个方法通常是最快的。

当您在表中使用两列之间的相等比较时，SQL Server会有大量信息来决定使用最佳的连接算法：

• 它具有有关索引的信息。
• 它具有有关该列的统计信息。

如果没有这些信息，SQL Server通常会默认使用嵌套循环连接。我发现即使可以使用基于合并或哈希的连接表达式，它仍然会这样做。

顺便提一下，您可以通过使用计算列来解决这个问题：

alter table t2 add id_no_a as (replace(id, 'A', '')) persisted;

create index idx_t2_id_no_a on t2(id_no_a);

接下来的短语

on T1.[ID] = t2.id_no_a

- Gordon Linoff

Answer 3

使用联合来避免没有索引的搜索的示例：

DECLARE @T1 TABLE (ID VARCHAR(16), CODE INT)
DECLARE @T2 TABLE (ID VARCHAR(16), CODE INT)

INSERT INTO @T1 VALUES ('ASD',1)
INSERT INTO @T1 VALUES ('DFG',2)
INSERT INTO @T1 VALUES ('RTY',3)
INSERT INTO @T1 VALUES ('AZX',4)
INSERT INTO @T1 VALUES ('GTY',5)
INSERT INTO @T1 VALUES ('KKO',6)

INSERT INTO @T2 VALUES ('ASD',1)
INSERT INTO @T2 VALUES ('SD',2)
INSERT INTO @T2 VALUES ('DFG',3)
INSERT INTO @T2 VALUES ('RTY',4)
INSERT INTO @T2 VALUES ('AZX',5)
INSERT INTO @T2 VALUES ('ZX',6)
INSERT INTO @T2 VALUES ('GTY',7)
INSERT INTO @T2 VALUES ('GTYA',8)
INSERT INTO @T2 VALUES ('KKO',9)
INSERT INTO @T2 VALUES ('KKOA',10)
INSERT INTO @T2 VALUES ('AKKOA',11)



SELECT * FROM @T1 T1 INNER JOIN (SELECT ID FROM @T2 WHERE ID NOT LIKE '%A%')T2 ON T2.ID = T1.ID
UNION ALL 
SELECT * FROM @T1 T1 INNER JOIN (SELECT REPLACE(ID,'A','')ID FROM @T2 WHERE ID LIKE '%A%')T2 ON T2.ID = T1.ID

这是在不进行模式更改的情况下可以做的事情。 如果进行模式更改，则需要在T2中创建一个计算索引列并与其连接。这样做速度更快，大部分工作都放在插入/更新上，以维护额外列和其上的索引。

- Stefanos Zilellis

---

这个提议的替换对于 T1.ID = 'BNN' 和 T2.ID = 'BANANA' 是失败的。只有当我们知道 T2.ID 中唯一包含 A 的值确实是 A 而没有其他值时，才是正确的。 - Jeroen Mostert

Jeroen Mostert，我弄错了replace函数。但是我的解决方案始终使用索引搜索完成工作。 - Stefanos Zilellis

很遗憾，即使“ID”被完全索引，“LIKE '%A%'”也不是可搜索的，所以无论如何，在第二部分仍需进行完整的索引扫描。您可以通过在表中添加ID索引来验证这一点。总的来说，此查询比优化器单独生成的查询略慢一些——在这种情况下，拆分条件并没有增加多少效果。这种技术确实在某些情况下有一定的应用，例如其中一半（或两半）允许寻找，并且优化器不够聪明，无法看到它，但在这种情况下，它只是让事情更复杂了而没有获得任何收益。 - Jeroen Mostert

是的，没错，但这里的主要区别在于基数估算器无法正确估算JOIN T2 ON T1.[ID] = REPLACE(T2.[ID],'A', '')情况下的行数。在我的情况下，估算器可以更准确地估算返回的行数，这非常重要。行数的估算极大地影响执行计划的选择。我只是不想深入了解它。但你是对的 - 在处理LIKE时，没有比全索引搜索更好的方法。 - Stefanos Zilellis