Index du serveur SQL - ascendant ou descendant, quelle différence cela fait-il ?

Question

Lorsque vous créez un index sur une colonne ou un certain nombre de colonnes dans MS SQL Server (j'utilise la version 2005), vous pouvez spécifier que l'index sur chaque colonne soit ascendant ou descendant. J'ai du mal à comprendre pourquoi ce choix existe. En utilisant des techniques de tri binaire, une recherche ne serait-elle pas aussi rapide dans les deux cas ? Quelle différence cela fait-il de choisir l'ordre ?

Answer 1

Ceci est principalement important lorsqu'il est utilisé avec des indices composites :

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

peut être utilisé pour l'un ou l'autre :

SELECT  *
FROM    mytable
ORDER BY
        col1, col2 DESC

ou :

SELECT  *
FROM    mytable
ORDER BY
        col1 DESC, col2

mais pas pour :

SELECT  *
FROM    mytable
ORDER BY
        col1, col2

Un index sur une seule colonne peut être utilisé efficacement pour le tri dans les deux sens.

Voir l'article dans mon blog pour plus de détails :

• Index descendants

Mise à jour :

En fait, cela peut être important même pour un index à une seule colonne, bien que ce ne soit pas si évident.

Imaginez un index sur une colonne d'une table en cluster :

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

L'indice sur col1 conserve les valeurs ordonnées de col1 ainsi que les références aux rangs.

Comme la table est en cluster, les références aux lignes sont en fait les valeurs de l'attribut pk . Ils sont également ordonnés dans chaque valeur de col1 .

Cela signifie que les feuilles de l'index sont en fait ordonnées sur (col1, pk) et cette requête :

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk

ne nécessite aucun tri.

Si nous créons l'index comme suit :

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

alors les valeurs de col1 seront triés de manière descendante, mais les valeurs de pk dans chaque valeur de col1 seront triés par ordre croissant.

Cela signifie que la requête suivante :

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

peuvent être servis par ix_mytable_col1_desc mais pas par ix_mytable_col1 .

En d'autres termes, les colonnes qui constituent un CLUSTERED INDEX sur une table sont toujours les colonnes de queue de tout autre index sur cette table.

Answer 2

Pour un véritable index à une seule colonne, cela fait peu de différence du point de vue de l'optimiseur de requêtes.

Pour la définition du tableau

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

La requête

SELECT TOP 10 *
FROM T1
ORDER BY ID DESC

Utilise un balayage ordonné avec une direction de balayage BACKWARD comme on peut le voir dans le plan d'exécution. Il y a toutefois une légère différence dans la mesure où, actuellement, seuls les projets de l FORWARD Les balayages peuvent être mis en parallèle.

Cependant Cela peut faire une grande différence en termes de fragmentation logique. . Si l'index est créé avec des clés décroissantes mais que les nouvelles lignes sont ajoutées avec des valeurs de clés croissantes, vous pouvez vous retrouver avec chaque page dans un ordre logique différent. Cela peut avoir un impact important sur la taille des lectures d'E/S lors du balayage de la table, si celle-ci n'est pas dans le cache.

Voir les résultats de la fragmentation

                    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

pour le script ci-dessous

/*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 

Il est possible d'utiliser l'onglet des résultats spatiaux pour vérifier l'hypothèse selon laquelle cela est dû au fait que les pages ultérieures ont des valeurs clés ascendantes dans les deux cas.

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 %% )

Answer 3

L'ordre de tri est important lorsque vous souhaitez récupérer des lots de données triées, et non des enregistrements individuels.

Notez que (comme vous le suggérez dans votre question) l'ordre de tri est généralement beaucoup moins important que les colonnes que vous indexez (le système peut lire l'index en sens inverse si l'ordre est opposé à ce qu'il veut). J'accorde rarement de l'importance à l'ordre de tri de l'index, alors que j'agonise sur les colonnes couvertes par l'index.

@Quassnoi fournit un excellent exemple du moment où il hace matière.