orden - Optimización de consultas SQL eliminando el operador Sort en el plan de ejecución

orden de ejecucion sql

Question

Acabo de empezar a buscar la optimización de mis consultas a través de índices porque los datos SQL crecen a gran velocidad. Miré cómo el optimizador está procesando mi consulta a través del plan de ejecución en SSMS y noté que se está utilizando un operador de clasificación. He oído que un operador de Sort indica un diseño incorrecto en la consulta, ya que el ordenamiento se puede realizar prematuramente a través de un índice. Así que aquí hay una tabla de ejemplo y datos similares a lo que estoy haciendo:

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

Aquí hay una consulta de ejemplo:

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

Creé un índice no agrupado para ayudar a acelerar la consulta:

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

Para construir el índice IX_Store, comienzo con los predicados simples

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

Luego agrego la columna [Phone] para ORDER BY y para cubrir la salida SELECT

Por lo tanto, incluso cuando se genera el índice, el optimizador sigue utilizando el operador Ordenar (y no la clasificación de índice) porque [Phone] está ordenado DESPUÉS de [ParentStoreId] AND [Type] . Si elimino la columna [Type] del índice y ejecuto la consulta:

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

Entonces, por supuesto, el optimizador no utiliza el operador Ordenar porque [Phone] está ordenado por [ParentStoreId] .

Entonces, la pregunta es ¿cómo puedo crear un índice que cubra la consulta (incluido el predicado [Type] ) y que el optimizador no use un Sort?

EDITAR:

La tabla con la que estoy trabajando tiene más de 20 millones de filas

Answer 1

En primer lugar, debe verificar que el tipo es realmente un cuello de botella de rendimiento. La duración de la clasificación dependerá de la cantidad de elementos que se ordenarán, y es probable que el número de tiendas para una tienda principal particular sea pequeño. (Eso es suponiendo que se aplica el operador sort después de aplicar la cláusula where).

He oído que un operador de Sort indica un diseño incorrecto en la consulta, ya que el ordenamiento puede hacerse prematuramente a través de un índice

Eso es una generalización excesiva. A menudo, un operador de clasificación puede trasladarse trivialmente al índice y, si solo se captan las primeras dos filas del conjunto de resultados, puede reducir sustancialmente el costo de la consulta, porque la base de datos ya no tiene que buscar todas las filas coincidentes (y ordenarlas). todo) para encontrar los primeros, pero puede leer los registros en orden de conjunto de resultados, y detener una vez que se encuentran suficientes registros.

En su caso, parece que está buscando todo el conjunto de resultados, por lo que es poco probable que las cosas empeoren (a menos que el conjunto de resultados sea enorme). Además, en su caso podría no ser trivial construir un índice ordenado útil, porque la cláusula where contiene un o.

Ahora, si todavía desea deshacerse de ese operador de clasificación, puede intentar:

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

Alternativamente, puedes probar el siguiente índice:

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

intentar que el optimizador de consultas realice un escaneo de rango de índice solo en ParentStoreId , luego escanee todas las filas coincidentes en el índice y las emite si coincide el Type . Sin embargo, es probable que esto genere más E / S de disco y, por lo tanto, ralentice la consulta en lugar de acelerarla.

Editar : como último recurso, podrías usar

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]

con

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

y clasifique las dos listas en el servidor de aplicaciones, donde puede fusionar (como en tipo de combinación) las listas preordenadas, evitando así una clasificación completa. Pero eso es realmente una microoptimización que, si bien acelera la clasificación en un orden de magnitud, es poco probable que afecte al tiempo de ejecución total de la consulta, como era de esperar que el cuello de botella sea de red y de E / S de disco, especialmente a la luz del hecho de que el disco tendrá mucho acceso aleatorio ya que el índice no está agrupado.