sql - que - mongodb español

Índices de SQL Server: ascendente o descendente, ¿qué diferencia hace?

Question

Cuando crea un índice en una columna o número de columnas en MS SQL Server (estoy usando la versión 2005), puede especificar que el índice en cada columna sea ascendente o descendente. Me cuesta entender por qué esta elección está aquí. Usando técnicas de clasificación binarias, ¿no sería una búsqueda igual de rápida en ambos sentidos? ¿Qué diferencia hace qué orden elijo?

Answer 1

El orden de clasificación importa cuando desea recuperar muchos datos ordenados, no registros individuales.

Tenga en cuenta que (como está sugiriendo con su pregunta) el orden de clasificación suele ser mucho menos significativo que las columnas que indexa (el sistema puede leer el índice en reversa si el orden es opuesto a lo que quiere). Raramente le doy un orden de clasificación de índice a cualquier pensamiento, mientras que agonizo sobre las columnas cubiertas por el índice.

@Quassnoi proporciona un excelente ejemplo de cuándo importa.

Answer 2

Esto es primordialmente importante cuando se usa con índices compuestos:

CREATE INDEX ix_index ON mytable (col1, col2 DESC);

se puede usar para:

SELECT *
FROM mytable
ORDER BY
col1, col2 DESC

o:

SELECT *
FROM mytable
ORDER BY
col1 DESC, col2

, pero no para:

SELECT *
FROM mytable
ORDER BY
col1, col2

Un índice en una sola columna se puede usar de manera eficiente para clasificar en ambos sentidos.

Ver el artículo en mi blog para más detalles:

• Índices descendentes

Actualizar:

De hecho, esto puede importar incluso para un índice de columna individual, aunque no es tan obvio.

Imagine un índice en una columna de una tabla agrupada:

CREATE TABLE mytable (
pk INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)

El índice en col1 mantiene los valores ordenados de col1 junto con las referencias a filas.

Como la tabla está agrupada, las referencias a filas son en realidad los valores de pk . También se ordenan dentro de cada valor de col1 .

Esto significa que las hojas del índice están actualmente ordenadas (col1, pk) y esta consulta:

SELECT col1, pk
FROM mytable
ORDER BY
col1, pk

no necesita clasificación.

Si creamos el índice de la siguiente manera:

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

, entonces los valores de col1 se ordenarán descendentemente, pero los valores de pk dentro de cada valor de col1 se ordenarán de forma ascendente.

Esto significa que la siguiente consulta:

SELECT col1, pk
FROM mytable
ORDER BY
col1, pk DESC

puede ser servido por ix_mytable_col1_desc pero no por ix_mytable_col1 .

En otras palabras, las columnas que constituyen un CLUSTERED INDEX en cualquier tabla son siempre las columnas finales de cualquier otro índice en esa tabla.

Answer 3

Para un verdadero índice de una sola columna, hay poca diferencia desde el punto de vista del Optimizador de consultas.

Para la definición de la tabla

CREATE TABLE T1( [ID] [int] IDENTITY NOT NULL,
[Filler] [char](8000) NULL,
PRIMARY KEY CLUSTERED ([ID] ASC))

La consulta

SELECT TOP 10 *
FROM T1
ORDER BY ID DESC

Utiliza un escaneo ordenado con la dirección de escaneo BACKWARD como se puede ver en el Plan de ejecución. Sin embargo, existe una ligera diferencia en el hecho de que actualmente solo los escaneos FORWARD se pueden paralelizar.

Sin embargo , puede marcar una gran diferencia en términos de fragmentación lógica . Si el índice se crea con teclas que descienden, pero las nuevas filas se anexan con valores de clave ascendentes, puede terminar con cada página fuera de orden lógico. Esto puede afectar seriamente el tamaño de las lecturas de E / S al escanear la tabla y no está en la memoria caché.

Ver los resultados de fragmentación

 avg_fragmentation avg_fragment
name page_count _in_percent fragment_count _size_in_pages
------ ------------ ------------------- ---------------- ---------------
T1 1000 0.4 5 200
T2 1000 99.9 1000 1

para el script a continuación

/*Uses T1 definition from above*/
SET NOCOUNT ON;
CREATE TABLE T2( [ID] [int] IDENTITY NOT NULL,
[Filler] [char](8000) NULL,
PRIMARY KEY CLUSTERED ([ID] DESC))
BEGIN TRAN
GO
INSERT INTO T1 DEFAULT VALUES
GO 1000
INSERT INTO T2 DEFAULT VALUES
GO 1000
COMMIT
SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id(''T1''), 1, NULL, ''DETAILED'')
WHERE index_level = 0
UNION ALL
SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id(''T2''), 1, NULL, ''DETAILED'')
WHERE index_level = 0

Es posible usar la pestaña de resultados espaciales para verificar la suposición de que esto se debe a que las páginas posteriores tienen valores de clave ascendentes en ambos casos.

SELECT page_id,
[ID],
geometry::Point(page_id, [ID], 0).STBuffer(4)
FROM T1
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
UNION ALL
SELECT page_id,
[ID],
geometry::Point(page_id, [ID], 0).STBuffer(4)
FROM T2
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )