Joins (SQL Server)Joins (SQL Server)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL Server выполняет операции сортировки, пересечения, объединения и поиска различий при помощи технологий хэш-соединений и сортировки в оперативной памяти.performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. При выполнении запросов этого типа SQL ServerSQL Server поддерживает вертикальное секционирование таблиц, иногда называемое хранилищем столбцов.Using this type of query plan, SQL ServerSQL Server supports vertical table partitioning, sometimes called columnar storage.

В SQL ServerSQL Server применяются операции соединения трех типов:SQL ServerSQL Server employs four types of join operations:

  • Соединения вложенных цикловNested Loops joins
  • Соединения слиянием.Merge joins
  • Хэш-соединения.Hash joins
  • Адаптивные соединения (начиная с SQL Server 2017 (14.x)SQL Server 2017 (14.x)).Adaptive joins (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x))

Основные принципы соединенияJoin Fundamentals

С помощью соединения можно получать данные из двух или нескольких таблиц на основе логических связей между ними.By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Соединения позволяют указать, как в SQL ServerSQL Server должны использоваться данные из одной таблицы для выбора строк из другой таблицы.Joins indicate how SQL ServerSQL Server should use data from one table to select the rows in another table.

Соединение определяет способ связывания двух таблиц в запросе следующим образом:A join condition defines the way two tables are related in a query by:

  • для каждой таблицы указываются столбцы, используемые в соединении.Specifying the column from each table to be used for the join. В типичном условии соединения указывается внешний ключ из одной таблицы и связанный с ним ключ из другой таблицы;A typical join condition specifies a foreign key from one table and its associated key in the other table.
  • указывается логический оператор (например, = или <>,) для сравнения значений столбцов.Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Внутренние соединения можно задавать в предложениях FROM и WHERE.Inner joins can be specified in either the FROM or WHERE clauses. Внешние соединения можно задавать только в предложении FROM.Outer joins can be specified in the FROM clause only. Условия соединения сочетаются с условиями поиска WHERE и HAVING для управления строками, выбранными из базовых таблиц, на которые ссылается предложение FROM.The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause.

То, что условия соединения задаются в предложении FROM, помогает отделить их от условий поиска, которые могут быть заданы в предложении WHERE. Объединение рекомендуется задавать именно таким способом.Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. Ниже приведен упрощенный синтаксис соединения с использованием предложения FROM стандарта ISO:A simplified ISO FROM clause join syntax is:

FROM first_table join_type second_table [ON (join_condition)]

join_type указывает на выполняемый тип соединения: внутреннее, внешнее или перекрестное.join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition определяет предикат, который будет вычисляться для каждой пары соединяемых строк.join_condition defines the predicate to be evaluated for each pair of joined rows. Ниже приведен пример предложения FROM с заданным соединением:The following is an example of a FROM clause join specification:

FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
     ON (ProductVendor.BusinessEntityID = Vendor.BusinessEntityID)

Ниже приведена простая инструкция SELECT, использующая это соединение:The following is a simple SELECT statement using this join:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'
GO

Инструкция возвращает наименование продукта и сведения о поставщике для всех сочетаний запчастей, поставляемых компаниями с названиями на букву F и стоимостью продукта более 10 долларов.The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.

Если один запрос содержит ссылки на несколько таблиц, то все ссылки столбцов должны быть однозначными.When multiple tables are referenced in a single query, all column references must be unambiguous. В предыдущем примере в обеих таблицах (ProductVendor и Vendor) есть столбец с именем BusinessEntityID.In the previous example, both the ProductVendor and Vendor table have a column named BusinessEntityID. Имена столбцов, совпадающие в двух или более таблицах, на которые ссылается запрос, должны уточняться именем таблицы.Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name. Все ссылки на столбец Vendor в этом примере являются уточненными.All references to the Vendor columns in the example are qualified.

Если имя столбца не дублируется в двух или более таблицах, указанных в запросе, то ссылки на него уточнять именем таблицы не обязательно.When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name. Это показано в предыдущем примере.This is shown in the previous example. Подобную инструкцию SELECT иногда трудно понять, поскольку в ней нет ничего, что указывало бы на таблицы, из которых берутся столбцы.Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. Запрос гораздо легче читать, если все столбцы указаны с именами соответствующих таблиц.The readability of the query is improved if all columns are qualified with their table names. Запрос будет читаться еще легче, если используются псевдонимы таблиц, особенно когда имена таблиц сами должны уточняться именами базы данных и владельца.The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names. Ниже приведен тот же пример, но чтобы упростить чтение, используются псевдонимы таблиц, уточняющие названия столбцов.The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv 
JOIN Purchasing.Vendor AS v
    ON (pv.BusinessEntityID = v.BusinessEntityID)
WHERE StandardPrice > $10
    AND Name LIKE N'F%';

В предыдущем примере условие соединения задается в предложении FROM, что является рекомендуемым способом.The previous examples specified the join conditions in the FROM clause, which is the preferred method. В следующем запросе это же условие соединения указывается в предложении WHERE:The following query contains the same join condition specified in the WHERE clause:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v
WHERE pv.BusinessEntityID=v.BusinessEntityID
    AND StandardPrice > $10
    AND Name LIKE N'F%';

Список выборки для соединения может ссылаться на все столбцы в соединяемых таблицах или на любое подмножество этих столбцов.The select list for a join can reference all the columns in the joined tables, or any subset of the columns. Список выборки не обязательно должен содержать столбцы из каждой таблицы в соединении.The select list is not required to contain columns from every table in the join. Например, в соединении из трех таблиц связующим звеном между одной из таблиц и третьей таблицей может быть только одна таблица, при этом список выборки не обязательно должен ссылаться на столбцы средней таблицы.For example, in a three-table join, only one table can be used to bridge from one of the other tables to the third table, and none of the columns from the middle table have to be referenced in the select list.

Хотя обычно в условиях соединения для сравнения используется оператор равенства (=), можно указать другие операторы сравнения или реляционные операторы, равно как другие предикаты.Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. Дополнительные сведения см. в разделах Операторы сравнения (Transact-SQL) и WHERE (Transact-SQL).For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).

При обработке соединений в SQL ServerSQL Server механизм запросов выбирает наиболее эффективный метод обработки из нескольких возможных.When SQL ServerSQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. При физическом выполнении различных соединений можно использовать много разных оптимизаций, поэтому их нельзя надежно прогнозировать.The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted.

Столбцы, используемые в условии соединения, не обязательно должны иметь одинаковые имена или одинаковый тип данных.Columns used in a join condition are not required to have the same name or be the same data type. Однако если типы данных не совпадают, то они должны быть совместимыми или допускать в SQL Server неявное преобразование.However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert. Если типы данных не допускают неявное преобразование, то условия соединения должны явно преобразовывать эти типы данных с помощью функции CAST.If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST function. Дополнительные сведения о подразумеваемых и явных преобразованиях см. в статье Преобразование типов данных (ядро СУБД).For more information about implicit and explicit conversions, see Data Type Conversion (Database Engine).

Большинство запросов, использующих соединение, можно переписать с помощью вложенных запросов и наоборот.Most queries using a join can be rewritten using a subquery (a query nested within another query), and most subqueries can be rewritten as joins. Дополнительные сведения о вложенных запросах см. в разделе Вложенные запросы.For more information about subqueries, see Subqueries.

Примечание

Таблицы невозможно соединять непосредственно по столбцам ntext, text или image.Tables cannot be joined directly on ntext, text, or image columns. Однако соединить таблицы по столбцам ntext, text или image можно косвенно, с помощью SUBSTRING.However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING.
Например, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) выполняет внутреннее соединение двух таблиц по первым 20 символам текстовых столбцов в таблицах t1 и t2.For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2.
Другая возможность сравнения столбцов ntext и text из двух таблиц заключается в сравнении длины столбцов с предложением WHERE, например: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

Основные сведения о соединениях вложенных цикловUnderstanding Nested Loops joins

Если один вход соединения имеет небольшой размер (менее десяти строк), а другой вход сравнительно большой и индексирован по соединяемым столбцам, индексное соединение вложенных циклов является самой быстрой операцией соединения, так как для нее потребуется наименьшее количество операций сравнения и ввода-вывода.If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons.

Соединение вложенных циклов, называемое также вложенной итерацией, использует один ввод соединения в качестве внешней входной таблицы (на графической схеме выполнения она является верхним входом), а второй в качестве внутренней (нижней) входной таблицы.The nested loops join, also called nested iteration, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. Внешний цикл использует внешнюю входную таблицу построчно.The outer loop consumes the outer input table row by row. Во внутреннем цикле для каждой внешней строки производится сканирование внутренней входной таблицы и вывод совпадающих строк.The inner loop, executed for each outer row, searches for matching rows in the inner input table.

В простейшем случае во время поиска целиком просматривается таблица или индекс; это называется упрощенным соединением вложенных циклов.In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. Если при поиске используется индекс, то такой поиск называется индексным соединением вложенных циклов.If the search exploits an index, it is called an index nested loops join. Если индекс создается в качестве части плана запроса (и уничтожается после завершения запроса), то он называется временным индексным соединением вложенных циклов.If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. Все эти варианты учитываются оптимизатором запросов.All these variants are considered by the Query Optimizer.

Соединение вложенных циклов является особенно эффективным в случае, когда внешние входные данные сравнительно невелики, а внутренние входные данные велики и заранее индексированы.A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. Во многих небольших транзакциях, работающих с небольшими наборами строк, индексное соединение вложенных циклов превосходит как соединения слиянием, так и хэш-соединения.In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins. Однако в больших запросах соединения вложенных циклов часто являются не лучшим вариантом.In large queries, however, nested loops joins are often not the optimal choice.

Если для атрибута OPTIMIZED оператора соединения вложенными циклами задано значение True, это означает, что оптимизированные соединения вложенными циклами (или пакетная сортировка) используются для уменьшения количества операций ввода-вывода, когда внутренняя таблица имеет большой размер, независимо от того, выполняется ли ее параллельная обработка.When the OPTIMIZED attribute of a Nested Loops join operator is set to True, it means that an Optimized Nested Loops (or Batch Sort) is used to minimize I/O when the inner side table is large, regardless of it being parallelized or not. Такая оптимизация в этом плане выполнения может быть не слишком очевидна при анализе плана, если сама сортировка выполняется как скрытая операция.The presence of this optimization in a given plan may not be very obvious when analyzing an execution plan, given the sort itself is a hidden operation. Но изучив XML-код плана для атрибута OPTIMIZED, можно обнаружить, что соединение вложенными циклами, возможно, попытается изменить порядок входных строк, чтобы повысить производительность операций ввода-вывода.But by looking in the plan XML for the attribute OPTIMIZED, this indicates the Nested Loops join may try to reorder the input rows to improve I/O performance.

Основные сведения о соединениях слияниемUnderstanding Merge joins

Если два входа соединения достаточно велики, но отсортированы по соединяемым столбцам (например, если они были получены просмотром отсортированных индексов), то наиболее быстрой операцией соединения будет соединение слиянием.If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. Если оба входа соединения велики и имеют сходные размеры, соединение слиянием с предварительной сортировкой и хэш-соединение имеют примерно одинаковую производительность.If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. Однако операции хэш-соединения часто выполняются быстрее, если два входа значительно отличаются по размеру.However, hash join operations are often much faster if the two input sizes differ significantly from each other.

Соединение слиянием требует сортировки обоих наборов входных данных по столбцам слияния, которые определены предложениями равенства (ON) предиката объединения.The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate. Оптимизатор запросов обычно просматривает индекс, если для соответствующего набора столбцов такой существует, или устанавливает оператор сортировки под соединением слиянием.The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join. В редких случаях может быть несколько предложений равенства, но столбцы слияния берутся только из некоторых доступных предложений равенства.In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.

Так как каждый набор входных данных сортируется, оператор Merge Join получает строку из каждого набора входных данных и сравнивает их.Because each input is sorted, the Merge Join operator gets a row from each input and compares them. Например, для операций внутреннего соединения строки возвращаются в том случае, если они равны.For example, for inner join operations, the rows are returned if they are equal. Если они не равны, строка с меньшим значением не учитывается, и из этого набора входных данных берется другая строка.If they are not equal, the lower-value row is discarded and another row is obtained from that input. Этот процесс повторяется, пока не будет выполнена обработка всех строк.This process repeats until all rows have been processed.

Операция соединения слиянием может быть как обычной, так и операцией типа «многие ко многим».The merge join operation may be either a regular or a many-to-many operation. Соединение слиянием «многие ко многим» использует временную таблицу для хранения строк.A many-to-many merge join uses a temporary table to store rows. При наличии дублирующих значений из каждого набора входных данных один из наборов должен будет сбрасываться на начало дубликатов по мере обработки каждого дубликата из другого набора данных.If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.

При наличии остаточного предиката все строки, удовлетворяющие предикату слияния, определяют остаточный предикат, и возвращаются только те строки, которые ему соответствуют.If a residual predicate is present, all rows that satisfy the merge predicate evaluate the residual predicate, and only those rows that satisfy it are returned.

Соединение слиянием — очень быстрая операция, но она может оказаться ресурсоемкой, если требуется выполнение операций сортировки.Merge join itself is very fast, but it can be an expensive choice if sort operations are required. Однако если том данных имеет большой объем, и необходимые данные могут быть получены из существующих индексов сбалансированного дерева с выполненной предварительной сортировкой, соединение слиянием является самым быстрым из доступных алгоритмов соединения.However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

Основные сведения о хэш-соединенияхUnderstanding Hash joins

Хэш-соединения могут эффективно обрабатывать большие, несортированные и неиндексированные входы.Hash joins can efficiently process large, unsorted, nonindexed inputs. Они полезны для получения промежуточных результатов в сложных запросах из-за следующего.They are useful for intermediate results in complex queries because:

  • Промежуточные результаты не индексированы (если только они явным образом не сохранены на диске, а затем проиндексированы) и часто отсортированы не так, как требуется для следующей операции в плане запроса.Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
  • Оптимизаторы запросов оценивают только размеры промежуточных результатов.Query optimizers estimate only intermediate result sizes. Так как для сложных запросов оценки могут быть очень неточны, алгоритмы обработки промежуточных результатов должны быть не только эффективными, но и правильно вырождаться, если объем промежуточных результатов оказался гораздо большим, чем ожидалось.Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.

Хэш-соединение позволяет уменьшить денормализацию.The hash join allows reductions in the use of denormalization. Денормализация обычно используется для получения более высокой производительности при уменьшении количества операций соединения, несмотря на издержки, вызываемые избыточностью данных, например несогласованных обновлений.Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Хэш-соединения снижают потребность в денормализацииHash joins reduce the need to denormalize. и позволяют осуществлять вертикальное секционирование (представляющее группы столбцов, содержащиеся в одной таблице, в отдельных файлах или индексах) в качестве доступной возможности при реализации физической структуры базы данных.Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design.

Хэш-соединение имеет два входа: конструктивный и пробный.The hash join has two inputs: the build input and probe input. Оптимизатор запросов распределяет роли таким образом, при котором меньшему входу присваивается значение «конструктивный».The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

Хэш-соединения используются во многих операциях совпадающих множеств: внутреннее соединение; левое, правое и полное внешнее соединение; левое и правое полусоединение; пересечение; соединение; разность.Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Дополнительно модификация хэш-соединения применяется для двойного удаления и группирования, например SUM(salary) GROUP BY department.Moreover, a variant of the hash join can do duplicate removal and grouping, such as SUM(salary) GROUP BY department. В указанной модификации используется общий вход как для конструктивной, так и для пробной ролей.These modifications use only one input for both the build and probe roles.

В представленных ниже разделах описываются различные типы хэш-соединений: хэш-соединения в памяти, поэтапные и рекурсивные хэш-соединения.The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.

Хэш-соединения в памятиIn-Memory Hash Join

Перед проведением хэш-соединения производится просмотр или вычисление входного конструктивного значения, а затем в памяти создается хэш-таблица.The hash join first scans or computes the entire build input and then builds a hash table in memory. Каждая строка помещается в сегмент хэша согласно значению, вычисленному для хэш-ключа.Each row is inserted into a hash bucket depending on the hash value computed for the hash key. В случае если конструктивное входное значение имеет размер, меньший объема доступной памяти, то все строки данных могут быть занесены в хэш-таблицу.If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. После описанного конструктивного этапа предпринимается пробный этап.This build phase is followed by the probe phase. Производится построковое считывание или вычисление пробного входного значения, для каждой строки вычисляется значение хэш-ключа, затем происходит сканирование сегмента хэша и поиск совпадений.The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.

Плавное хэш-соединениеGrace Hash Join

Если размер конструктивного входного значения превышает максимально допустимый объем памяти, то хэш-соединение проводится в несколько шагов.If the build input does not fit in memory, a hash join proceeds in several steps. Указанный процесс называется плавным хэш-соединением.This is known as a grace hash join. Каждый шаг состоит из конструктивной и пробной частей.Each step has a build phase and probe phase. Исходные конструктивные и пробные входные данные разбиваются на несколько файлов (для этого используются хэш-функции ключей).Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. При использовании хэш-функции для хэш-ключей обеспечивается гарантия нахождения соединяемых записей в общей паре файлов.Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Таким образом, задача соединения двух объемных входных значений разбивается на несколько более мелких задач.Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. Затем хэш-соединение применяется к каждой паре разделенных файлов.The hash join is then applied to each pair of partitioned files.

Рекурсивное хэш-соединениеRecursive Hash Join

Если объем информации, поступающей на конструктивный вход, настолько велик, что для использования обычного внешнего слияния требуется несколько уровней, то операцию разбиения необходимо проводить за несколько шагов на нескольких уровнях.If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. Дополнительные шаги разбиения используются только для секций большого объема.If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. Чтобы максимально ускорить проведение всех шагов разбиения, используются емкие асинхронные операции ввода-вывода, в результате чего один поток может занимать сразу несколько жестких дисков.In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.

Примечание

В случае незначительного превышения допустимого объема памяти конструктивными входными данными происходит совмещение элементов хэш-соединения в памяти и поэтапных хэш-соединений в общий этап. В результате получается гибридное хэш-соединение.If the build input is only slightly larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.

В процессе оптимизации не всегда удается определить тип используемого хэш-соединения.It is not always possible during optimization to determine which hash join is used. Поэтому в SQL Server в первую очередь используются хэш-соединения в памяти, а затем, в зависимости от объемов входной конструктивной информации, осуществляется переход на поэтапное или рекурсивное хэш-соединение.Therefore, SQL Server starts by using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.

В случае неверного определения конструктивного и пробного входов в оптимизаторе запросов их переключение осуществляется динамически.If the Query Optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. При использовании хэш-соединения осуществляется контроль использования меньшего файла в качестве конструктивного входа.The hash join makes sure that it uses the smaller overflow file as build input. Данная функция называется "переключением ролей".This technique is called role reversal. Переключение ролей происходит внутри хэш-соединения после сброса информации на диск.Role reversal occurs inside the hash join after at least one spill to the disk.

Примечание

Переключение ролей происходит независимо от указаний запроса или структуры запроса.Role reversal occurs independent of any query hints or structure. Событие «переключение ролей» не отображается в плане запроса, и сообщение о нем выдается пользователю непосредственно после выполнения.Role reversal does not display in your query plan; when it occurs, it is transparent to the user.

Аварийная остановка хэшаHash Bailout

Термин "аварийная остановка хэша" иногда используется для описания поэтапных и рекурсивных хэш-соединений.The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.

Примечание

Наличие рекурсивных хэш-соединений и аварийных остановок снижает производительность сервера.Recursive hash joins or hash bailouts cause reduced performance in your server. Если в трассировке содержится много "событий-предупреждений хэша", необходимо произвести обновление статистических данных соединяемых столбцов.If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.

Дополнительные сведения об аварийных остановках хэша см. в разделе Класс событий Hash Warning.For more information about hash bailout, see Hash Warning Event Class.

Основные сведения об адаптивных соединенияхUnderstanding Adaptive joins

Адаптивные соединения в пакетном режиме позволяют отложить выбор метода Хэш-соединение или Соединение вложенными циклами до завершения сканирования первых входных данных.Batch mode Adaptive Joins enable the choice of a Hash Join or Nested Loops join method to be deferred until after the first input has been scanned. Оператор адаптивного соединения определяет пороговое значение, по которому принимается решение о переключении на план вложенного цикла.The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Таким образом, во время выполнения план запроса может динамически переключаться на более эффективную стратегию соединения без перекомпиляции.A query plan can therefore dynamically switch to a better join strategy during execution without having to be recompiled.

Совет

Наиболее полезной эта функция будет для рабочих нагрузок с частыми переключениями между просмотрами входных данных мелких и крупных соединений.Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

Решение для среды выполнения зависит от следующего:The runtime decision is based on the following steps:

  • Если число строк во входных данных соединения сборки настолько мало, что соединение вложенными циклами будет эффективнее хэш-соединения, план переключается на алгоритм вложенных циклов.If the row count of the build join input is small enough that a Nested Loops join would be more optimal than a Hash join, the plan switches to a Nested Loops algorithm.
  • Если число строк во входных данных соединения сборки превышает порог, переключение не выполняется и план продолжает использовать хэш-соединение.If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash join.

Следующий запрос используется в качестве наглядного примера адаптивного соединения:The following query is used to illustrate an Adaptive Join example:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;

Этот запрос возвращает 336 строк.The query returns 336 rows. Если включить функцию Статистика активных запросов, отобразится следующий план:Enabling Live Query Statistics displays the following plan:

Результат запроса: 336 строк

Обратите внимание на следующие моменты в плане:In the plan, note the following:

  1. Просмотр индекса columnstore, используемый с целью предоставления строк для этапа сборки хэш-соединения.A columnstore index scan used to provide rows for the Hash join build phase.
  2. Новый оператор адаптивного соединения.The new Adaptive Join operator. Он определяет пороговое значение, по которому принимается решение о переключении на план вложенного цикла.This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. В этом примере порог равен 78 строкам.For this example, the threshold is 78 rows. Если число строк >= 78, будет использоваться хэш-соединение.Anything with >= 78 rows will use a Hash join. Если значение меньше порога, будет использоваться соединение вложенными циклами.If less than the threshold, a Nested Loops join will be used.
  3. Так как запрос возвращает 336 строк, порог превышен. Поэтому вторая ветвь представляет пробный этап стандартной операции хэш-соединения.Since the query returns 336 rows, this exceeded the threshold and so the second branch represents the probe phase of a standard Hash join operation. Обратите внимание, что статистика активных запросов показывает строки, передаваемые через операторы — в данном случае это "672 из 672".Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
  4. И последняя ветвь — поиск кластеризованного индекса, используемый соединением вложенными циклами, если порог не был превышен.And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded. Обратите внимание, что мы видим число строк "0 из 336" (ветвь не используется).Notice that we see "0 of 336" rows displayed (the branch is unused).

Теперь давайте сравним план с таким же запросом, но для случая, когда значение Quantity будет иметь всего одну строку в таблице:Now contrast the plan with the same query, but when the Quantity value only has one row in the table:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;

Запрос возвращает одну строку.The query returns one row. Если включить статистику активных запросов, отобразится следующий план:Enabling Live Query Statistics displays the following plan:

Результатом запроса является одна строка.

Обратите внимание на следующие моменты в плане:In the plan, note the following:

  • При возврате одной строки видно, что теперь через поиск кластеризованного индекса передаются строки.With one row returned, the Clustered Index Seek now has rows flowing through it.
  • А так как этап сборки хэш-соединения не продолжается, никакие строки через вторую ветвь не передаются.And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

Примечания к адаптивным соединениямAdaptive Join remarks

Адаптивные соединения предъявляют более высокие требования к памяти, чем эквивалентный план соединения вложенными циклами индекса.Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. Дополнительная память запрашивается так, как если бы вложенный цикл был хэш-соединением.The additional memory is requested as if the Nested Loops was a Hash join. Существуют также издержки на этапе сборки, такие как стартстопная операция и эквивалентное потоковое соединение вложенными циклами.There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. Эти дополнительные затраты обеспечивают гибкость для сценариев, где количество строк во входных данных сборки может меняться.With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

Адаптивные соединения в пакетном режиме используются для первого выполнения инструкции. После компиляции последовательные выполнения остаются адаптивными с учетом порога скомпилированных адаптивных соединений и строк времени выполнения, передаваемых через этап сборки внешних входных данных.Batch mode Adaptive joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.

Если адаптивное соединение переключается на режим вложенного цикла, оно использует строки, уже считанные сборкой хэш-соединения.If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. Этот оператор не считывает повторно строки по внешней ссылке.The operator does not re-read the outer reference rows again.

Отслеживание операций адаптивного соединенияTracking Adaptive join activity

Оператор адаптивного соединения имеет следующие атрибуты оператора плана:The Adaptive Join operator has the following plan operator attributes:

Атрибут планаPlan attribute ОписаниеDescription
AdaptiveThresholdRowsAdaptiveThresholdRows Показывает пороговое значение, используемое для переключения с хэш-соединения на соединение вложенными циклами.Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType К какому типу, вероятнее всего, относится соединение.What the join type is likely to be.
ActualJoinTypeActualJoinType В фактическом плане показывает, какой итоговый алгоритм соединения был выбран на базе порогового значения.In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

Предполагаемый план показывает форму плана адаптивного соединения, а также определенное пороговое значение адаптивного соединения и предполагаемый тип соединения.The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.

Совет

Хранилище запросов захватывает и может принудительно применить план адаптивного соединения в пакетном режиме.Query Store captures and is able to force a batch mode Adaptive Join plan.

Допустимые инструкции адаптивного соединенияAdaptive join eligible statements

Чтобы логическое соединение стало допустимым для адаптивного соединения в пакетном режиме, должны выполняться следующие условия:A few conditions make a logical join eligible for a batch mode Adaptive Join:

  • Уровень совместимости базы данных имеет значение 140 или больше.The database compatibility level is 140 or higher.
  • Запрос является инструкцией SELECT (инструкции для изменения данных сейчас недопустимы).The query is a SELECT statement (data modification statements are currently ineligible).
  • Соединение может выполняться посредством как индексированного соединения вложенными циклами, так и физического алгоритма хэш-соединения.The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
  • Хэш-соединение использует пакетный режим — либо из-за наличия индекса columnstore во всем запросе, либо по той причине, что на таблицу индекса columnstore ссылается само соединение.The Hash join uses Batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  • Созданные альтернативные решения соединения вложенными циклами и хэш-соединения должны иметь одинаковый первый дочерний элемент (внешняя ссылка).The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).

Строки адаптивного порогового значенияAdaptive threshold rows

На приведенной ниже диаграмме показан пример пересечения между показателем затрат хэш-соединения и таким показателем альтернативного ему соединения вложенными циклами.The following chart shows an example intersection between the cost of a Hash join versus the cost of a Nested Loops join alternative. В этой точке пересечения определяется пороговое значение, что, в свою очередь, определяет фактический алгоритм, используемый для операции соединения.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

Пороговое значение соединения

Отключение адаптивных соединений без изменения уровня совместимостиDisabling Adaptive joins without changing the compatibility level

Адаптивные соединения можно отключить в области базы данных или инструкции, сохранив уровень совместимости базы данных 140 или более высокий.Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
Чтобы отключить адаптивные соединения для всех запросов, поступающих из базы данных, выполните следующую команду в контексте соответствующей базы данных:To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Когда этот параметр включен, он будет иметь соответствующее состояние в представлении sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Чтобы снова включить адаптивные соединения для всех запросов, выполняемых из базы данных, выполните следующую команду в контексте соответствующей базы данных:To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;

Вы также можете отключить адаптивные соединения для определенного запроса, назначив DISABLE_BATCH_MODE_ADAPTIVE_JOINS в качестве указания запроса USE HINT.Adaptive joins can also be disabled for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. Пример:For example:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
       ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

Примечание

Указание запроса USE HINT имеет приоритет над конфигурацией, областью действия которой является база данных, или флагом трассировки.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Значения NULL и соединенияNull Values and Joins

Если в столбцах, по которым производится соединение таблиц, есть значение NULL, значения NULL друг с другом совпадать не будут.When there are null values in the columns of the tables being joined, the null values do not match each other. Наличие таких значений в столбце одной из соединяемых таблиц возможно только при использовании внешнего соединения (если только предложение WHERE не исключает значение NULL).The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).

Ниже приведены две таблицы, каждая из которых может содержать NULL в столбце, по которому проводится соединение:Here are two tables that each have NULL in the column that will participate in the join:

table1                          table2
a           b                   c            d
-------     ------              -------      ------
      1        one                 NULL         two
   NULL      three                    4        four
      4      join4

Соединение, сравнивающее значения в столбце a со значениями столбца c, не создает совпадений, если столбцы имеют значение NULL:A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:

SELECT *
FROM table1 t1 JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Возвращена только одна строка со значением 4 в столбцах a и c:Only one row with 4 in column a and c is returned:

a           b      c           d      
----------- ------ ----------- ------ 
4           join4  4           four   

(1 row(s) affected)

Значения NULL, возвращаемые из базовой таблицы, также сложно отличить от значений NULL, возвращаемых при внешнем соединении.Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. Например, следующая инструкция SELECT выполняет левое внешнее соединение этих двух таблиц.For example, the following SELECT statement does a left outer join on these two tables:

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Ниже приводится результирующий набор.Here is the result set.

a           b      c           d      
----------- ------ ----------- ------ 
NULL        three  NULL        NULL 
1           one    NULL        NULL 
4           join4  4           four   

(3 row(s) affected)

В результате сложно определить, какие значения NULL получены из данных, а какие означают неуспешное соединение.The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. Если в соединениях данных присутствуют значения NULL, чаще всего желательно исключить их из результатов с помощью обычного соединения.When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.

См. также:See Also

Справочник по логическим и физическим операторам Showplan Showplan Logical and Physical Operators Reference
Операторы сравнения (Transact-SQL) Comparison Operators (Transact-SQL)
Преобразование типов данных (ядро СУБД) Data Type Conversion (Database Engine)
Вложенные запросы Subqueries
Адаптивное соединениеAdaptive Joins