是什么让 SQL 查询优化器在嵌套循环和散列连接之间做出决定 [英] What makes an SQL query optimiser decide between a nested loop and a hash join

问题描述

一般来说，是什么让 SQL 查询优化器在嵌套循环和散列连接之间做出决定.

In general what makes an SQL query optimiser decide between a nested loop and a hash join.

推荐答案

NESTED LOOPS 如果循环内的条件sargable，则可以使用索引限制记录数.

NESTED LOOPS are good if the condition inside the loop is sargable, that is index can be used to limit the number of records.

对于这样的查询:

SELECT  *
FROM    a
JOIN    b
ON      b.b1 = a.a1
WHERE   a.a2 = @myvar

，以a为前导，将取a中的每条记录，并找到b中所有对应的记录.

, with a leading, each record from a will be taken and all corresponding records in b should be found.

如果 b.b1 被索引并且具有高基数，那么 NESTED LOOP 将是首选方式.

If b.b1 is indexed and has high cardinality, then NESTED LOOP will be a preferred way.

在SQL Server中，它也是执行非等值连接的唯一方法(ON子句中的=条件除外)

In SQL Server, it is also the only way to execute non-equijoins (something other than = condition in the ON clause)

HASH JOIN 如果应该解析所有(或几乎所有)记录，则是最快的方法.

HASH JOIN is the fastest method if all (or almost all) records should be parsed.

它从 b 中获取所有记录，在它们之上构建一个哈希表，然后从 a 中获取所有记录并使用连接列的值作为键来查看向上哈希表.

It takes all records from b, builds a hash table over them, then takes all records from a and uses the value of the join column as a key to look up the hash table.

• NESTED LOOPS 需要这个时间:

Na * (Nb/C) * R,

其中Na和Nb分别是a和b中的记录数，C 是索引基数，R 是行查找所需的常量时间(1 是 SELECT 中的所有字段，WHERE 和 ORDER BY 子句被索引覆盖，如果没有，大约 10)

where Na and Nb are the numbers of records in a and b, C is the index cardinality, and R is constant time required for the row lookup (1 is all fields in SELECT, WHERE and ORDER BY clauses are covered by the index, about 10 if they are not)

HASH JOIN 需要这个时间:

Na + (Nb * H)

，其中 H 是构建和查找哈希表(每条记录)所需的常量总和.它们被编程到引擎中.

, where H is sum of constants required to build and lookup the hash table (per record). They are programmed into the engine.

SQL Server 使用表统计信息计算基数，计算并比较两个值并选择最佳计划.

SQL Server computes the cardinality using the table statistics, computes and compares the two values and chooses the best plan.

这篇关于是什么让 SQL 查询优化器在嵌套循环和散列连接之间做出决定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！