Оптимизация SQL-запросов путем удаления оператора сортировки в плане выполнения

Я только начал изучать оптимизацию своих запросов с помощью индексов, потому что данные SQL растут большими и быстрыми. Я посмотрел, как оптимизатор обрабатывает мой запрос через план выполнения в SSMS и замечает, что используется оператор Sort. Ive слышал, что оператор Sort указывает на плохой дизайн в запросе, поскольку сортировка может быть сделана преждевременно через индекс. Итак, вот пример таблицы и данных, похожих на то, что я делаю:

IF OBJECT_ID('dbo.Store') IS NOT NULL DROP TABLE dbo.[Store]
GO

CREATE TABLE dbo.[Store]
(
    [StoreId] int NOT NULL IDENTITY (1, 1),
    [ParentStoreId] int NULL,
    [Type] int NULL,
    [Phone] char(10) NULL,
    PRIMARY KEY ([StoreId])
)

INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '2223334444')
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '3334445555')
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '0001112222')
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '1112223333')
GO

Вот пример запроса:

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
AND ([Type] = 0 OR [Type] = 1)
ORDER BY [Phone]

Я создаю некластеризованный индекс, чтобы ускорить запрос:

CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])

Чтобы построить индекс IX_Store, я начинаю с простых предикатов

[ParentStoreId] = 10
AND ([Type] = 0 OR [Type] = 1)

Затем я добавляю столбец [Phone] для ORDER BY и закрываю вывод SELECT

Таким образом, даже когда индекс построен, оптимизатор по-прежнему использует оператор Sort (а не сортировку индекса), потому что [Phone] сортируется ПОСЛЕ [ParentStoreId] И [Type]. Если я удалю столбец [Type] из индекса и запустим запрос:

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
--AND ([Type] = 0 OR [Type] = 1)
ORDER BY [Phone]

Тогда, конечно, оператор Sort не используется оптимизатором, потому что [Phone] сортируется [ParentStoreId].

Итак, вопрос в том, как создать индекс, который будет охватывать запрос (включая предикат [Type]) и не использовать оптимизатор для сортировки?

EDIT:

В таблице, в которой я работаю, содержится более 20 миллионов строк

Ответы

Ответ 1

Во-первых, вы должны убедиться, что сортировка на самом деле является узким местом производительности. Продолжительность сортировки будет зависеть от количества отсортированных элементов, а количество магазинов для определенного родительского хранилища, вероятно, будет небольшим. (Предполагается, что оператор сортировки применяется после применения предложения where).

Ive слышал, что оператор Sort указывает на плохой дизайн в запросе, поскольку сортировка может быть сделана преждевременно через индекс

Это чрезмерное обобщение. Часто оператор сортировки может тривиально перемещаться в индекс, и, если извлекаются только первые пары строк результирующего набора, может существенно снизить стоимость запроса, поскольку базе данных больше не нужно извлекать все соответствующие строки (и сортировать их все), чтобы найти первые, но может читать записи в порядке набора результатов и останавливаться, как только будет найдено достаточно записей.

В вашем случае вы, кажется, извлекаете весь набор результатов, поэтому сортировка вряд ли сделает вещи намного хуже (если набор результатов не огромен). Кроме того, в вашем случае не может быть тривиально создавать полезный отсортированный индекс, потому что предложение where содержит a или.

Теперь, если вы все еще хотите избавиться от этого sort-оператора, вы можете попробовать:

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
AND [Type] in (0, 1)
ORDER BY [Phone]

В качестве альтернативы вы можете попробовать следующий индекс:

CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Phone], [Type])

чтобы попытаться заставить оптимизатор запросов выполнить проверку диапазона индекса только на ParentStoreId, а затем проверить все соответствующие строки в индексе, выводя их, если Type соответствует. Однако это, скорее всего, вызовет больше дискового ввода-вывода и, следовательно, замедлит ваш запрос, а не ускорит его.

Изменить. В качестве последнего средства вы можете использовать

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
AND [Type] = 0
ORDER BY [Phone]

UNION ALL

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
AND [Type] = 1
ORDER BY [Phone]

с

CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])

и сортировать два списка на сервере приложений, где вы можете объединить (как в сортировке слияния) прессованные списки, тем самым избегая полного сортировки. Но это действительно микро-оптимизация, которая, хотя и ускоряет сортировку на порядок, вряд ли повлияет на общее время выполнения запроса, так как я ожидаю, что узким местом станет сетевое и дисковое ввода/особенно в свете того факта, что диск будет делать много произвольного доступа, поскольку индекс не кластеризуется.