Повышение производительности при упорядочении по столбцу объединенной таблицы
У меня есть родительская таблица, которая содержит внешний ключ для таблицы поиска (упрощенный пример):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
В этом случае Parent
таблица содержит более 10 миллионов строк, а Lookup
таблица - около 5000. Реальная Parent
реализация имеет несколько таких ссылок внешнего ключа на другие таблицы, и каждый из этих столбцов может содержать NULL.
Обе таблицы в качестве примера имеют уникальные кластерные индексы для своих Id
столбцов, Parent
имеют некластеризованный индекс для LookupId
и Lookup
некластеризованный индекс для Name
.
Я запускаю запрос с разбивкой на страницы, в результаты которого я хочу включить поисковое значение: -
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
Это выполняется быстро, как и заказ P.LookupId
.
Однако если я попытаюсь выполнить заказ по Name
(или даже L.Id
), запрос будет выполняться значительно медленнее:
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
План запроса для второго запроса находится здесь: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Другие, казалось бы, связанные вопросы, по-видимому, включают упорядочение по столбцам в первой таблице, которое можно решить с помощью соответствующего индекса.
Я попытался создать индексированное представление для этого запроса, однако SQL Server не позволит мне проиндексировать представление, потому что оно содержит ЛЕВОЕ СОЕДИНЕНИЕ, которое мне требуется, поскольку оно LookupId
может быть NULL, и если я использую INNER JOIN, эти записи будут исключены.
Есть ли способ оптимизировать эту ситуацию?
РЕДАКТИРОВАТЬ
Ответ Роба Фарли (спасибо!) Великолепен и отлично подходит для вопроса, который я первоначально задал, в котором я подразумевал, что присоединяюсь к одной таблице.
Как бы то ни было, у меня есть несколько таких таблиц, и я не смог согласовать все, используя INNER JOINs, чтобы использовать это решение.
На данный момент я работал над этим, добавляя строку «NULL» в таблицы поиска, чтобы я мог использовать INNER JOIN без потери каких-либо строк слева.
В моем случае я использую uniqueidentifier
идентификаторы, поэтому я создаю индексированное представление следующим образом:
CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
P.Id,
L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id
Затем я добавляю в Lookup
таблицу строку со значением 00000000-0000-0000-0000-000000000000
for, Id
чтобы всегда было совпадение справа от соединения.
Затем я могу при необходимости создавать индексы для этого представления.
Кроме того, поскольку я не использую Enterprise, я обнаружил, что мне нужно использовать NOEXPAND
подсказку, чтобы убедиться, что эти индексы используются:
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Ответы
Давайте начнем с размышлений об этом первом запросе.
Вы соединяетесь между Parent и Lookup, но это внешнее соединение, поэтому Parents никогда не удаляются из результатов. Я собираюсь предположить, что Lookup.Id уникален, поэтому ни у одного Parent не будет нескольких запросов, к которым он присоединяется.
Следовательно, 50000-я строка в Parent (упорядоченная по Parent.Id) будет 50000-й строкой в результатах, если у нас нет предложения OFFSET.
Следовательно, запрос может пройти мимо 50000 строк для смещения, просмотреть следующие 50 строк и использовать это для присоединения к таблице поиска. Не имеет значения, если соединение ничего не находит, это левое внешнее соединение, и оно просто вернет NULL.
Если вы заказываете по другому столбцу в родительском элементе, который индексируется, он может так же быстро пройти мимо этих 50000 строк.
Теперь рассмотрим второй запрос.
Вы хотите, чтобы 50000 строк, которые вы игнорируете (по смещению), были первыми 50000 строками на основе результатов объединения. Эти 50000 строк могут включать в себя те, которые имеют значение NULL, где значение Parent.LookupId не существует в таблице поиска. Даже если у вас есть хороший индекс для Parent.LookupId, вам, вероятно, придется задействовать большую часть строк, потому что, если вы не найдете 50050 строк, которые не соединяются успешно, вам нужно будет продолжать. Даже 50050 - это намного больше, чем 50 строк, к которым вы присоединяетесь в первом запросе.
Теперь, если у вас есть внешний ключ, все может быть немного иначе. Затем механизм SQL должен знать, что если оно вообще имеет значение, Lookup.Name не будет иметь значение NULL. Таким образом, теоретически можно было бы начать с поиска тех, которые являются нулевыми, чтобы увидеть, есть ли их 50000. Но это все еще немного натянуто, и механизм SQL вряд ли сможет разработать такой план.
Но ты мог.
Итак, чтобы оценить производительность второго запроса, я бы сделал несколько вещей.
Начните с рассмотрения тех, которые не равны нулю. Это означает, что строки являются частью внутреннего соединения. Вы можете создать индексированное представление для этого, чтобы получить индекс в том порядке, в котором вы хотите.
Но вам также понадобятся те, у которых Parent.LookupID имеет значение null - за исключением того, что для них вам вообще не нужно соединение.
Если вы выполните UNION ALL для этих двух наборов (и, возможно, включите постоянный столбец в оба, чтобы убедиться, что строки NULL появляются перед строками NOT NULL в вашем порядке), вы должны увидеть некоторое улучшение.
Что-то вроде этого:
SELECT ID, Name
FROM
(
SELECT i.ID, i.Name, 2 as SetNumber
FROM dbo.MyIndexedView i
UNION ALL
SELECT p.ID, NULL, 1 as SetNumber
FROM dbo.Parent p
WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;
Надеюсь, ваш план будет включать в себя оператор слияния (конкатенации), чтобы он извлекал только те строки, которые ему нужны, из сканирования индекса в индексированном представлении (в порядке имен) и поиска индекса в родительском (для LookupID).