SQL Server vu par Christian Robert

Optimiser : Utilisation des index – Améliorer une simple recherche

Vous l'aurez sans doute entendu fréquemment, pour améliorer les performances il faut indexer… Oui, mais pour améliorer quelle performance ? Et quels index ? Sur quels champs et dans quelles conditions le moteur les utilise ?

Le premier type d'optimisation utilisé dans le moteur est l'index simple d'un champ en vue d'améliorer une recherche.

Prenons l'exemple le plus simple au monde, une recherche sur 1 champ unique dans la clause WHERE.

Tout d'abord créons une table vide sans clef primaire (la raison est simple, la clef primaire étant systématiquement indexée par le moteur, çà fausserait tout ici) :

IF OBJECT_ID('dbo.MaTable') IS NOT NULL
    DROP TABLE dbo.Matable

CREATE TABLE dbo.MaTable
(
    Id int identity(1,1),
    Nom varchar(100)
)

Ici rien de très complexe, une table, 2 champs, l'un des 2 (le premier) est un compteur.

Puis j'alimente ma table avec des données (beaucoup, si çà vous semble trop réduisez le 18 surligné en jaune) :

DECLARE @var int
SET @var = 0

INSERT INTO dbo.MaTable(Nom)
VALUES('0000000000')

WHILE @var < 18
BEGIN

    INSERT INTO dbo.MaTable(Nom)
    SELECT CAST(@var as varchar(10)) FROM dbo.MaTable

    SET @var = @var + 1

END

On a environ 200000 enregistrements disponibles pour nos tests.

Premier test, on recherche une valeur dans n'importe lequel des 2 champs de la table :

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT *
FROM dbo.MaTable
WHERE Id = 10

SELECT *
FROM dbo.MaTable
WHERE Nom = '10'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

Pour rappel des commandes SET utilisés : http://blogs.codes-sources.com/christian/archive/2007/04/17/sql-server-comment-mesure-t-on-la-performance-d-une-requete.aspx

Les 2 requêtes lisent 616 pages, soit la totalité de la table, pour s'en convaincre un coup d'œil au plan d'exécution :

La « table scan » signifie lecture de la table complète. Le moteur n'a de toute façon pas d'autres possibilités. Prenons comme métaphore un livre, comment trouver toutes les occurrences d'un mot sans le lire totalement ? C'est la même chose dans SQL Server.

Deuxième test, on indexe le champ Id (possible aussi dans l'interface graphique), notez le que le nonclustered n'a pas d'importance dans cet exemple :

CREATE NONCLUSTERED INDEX IX_Matable_Id ON dbo.MaTable(Id)

On réexecute les mêmes requêtes : la première tombe de 616 à 4 pages tandis que la seconde reste stable à 616.

Notez le nouveau plan pour la première :

Vous obtenez à la place du « table scan » un « index seek », on utilise l'index (son nom est même indiqué) pour réaliser une recherche. La partie qui suit « RID LookUp » / « Nested Loop » est la phase de recherche de l'enregistrement en lui-même.

Reprenons notre métaphore. Dans un livre vous recherchez un mot, vous allez dans l'index, vous cherchez la première lettre du mot et dans cette section le mot lui-même. En face de ce mot figure la ou les pages où se situe(nt) le mot. La première phase est la recherche dans l'index, la seconde consiste à feuilleter la livre jusqu'à y trouver la bonne page ou le mot.

Ici sur 4 pages lues par SQL Server : les 3 premières sont dans l'index, tandis que la dernière est la recherche de l'enregistrement lui-même.

Troisième test, cette fois j'indexe le second champ :

CREATE NONCLUSTERED INDEX IX_Matable_Nom ON dbo.MaTable(Nom)

Même exécution de requête que tout à l'heure, mais la seconde requête scanne toujours les 616 pages. Essayons une autre valeur :

SELECT *
FROM dbo.MaTable
WHERE Nom = '0000000000'

L'enregistrement renvoyé est unique, résultat on lit 4 pages là aussi… Mais pourquoi donc dans un cas j'utilise l'index et dans l'autre non ?

La raison est simple, le moteur lors d'une recherche de valeur dans l'index va lire des pages pour localiser ce dernier et cela pour chaque enregistrement. Essayons de trouver la valeur limite à partir de laquelle le moteur n'utilise plus l'index.

Après quelques tests, cette requête est la première à se passer de l'index :

SELECT *
FROM dbo.MaTable
WHERE Nom = '9'

On renvoie 500 enregistrements environ, sur un total de 200000, soit environ 0,2% des données.

D'où vient se ratio, c'est simple, c'est la division de la taille en page de la table (616 pages) sur la nombre d'enregistrement de la table (218000 environ), cela fait 0,2 % ! Dès que l'on lit plus d'enregsitrements (approximativement) que de nombre de pages dans la table le moteur change donc de stratégie !
Pourquoi ? Parce que pour le moteur il est alors plus intéressat de lire la table complète, car rechercher via un index reviendrait à lire plus de données (on lira l'index en plus) qu'il n'en existe dans la table.

Donc notre moteur utilisera les index nonclustered (et ici c'est important) que lorsque l'on renvoie qu'un très faible pourcentage d'enregistrement de la table !

En passant pour tuer un mythe :

SELECT *
FROM dbo.MaTable
ORDER BY Id

Le plan est :

Et toujours 616 pages de lues. La plus grosse partie du plan est passé sur le tri et l'index n'est pas utilisé. Le parallélisme apparait ici, parce que mon portable est un dual Core et que j'ai une édition supportant cette fonctionnalité. Cela sert à accélérer le traitement pas plus !

Donc ici, l'index ne sert à rien pour le tri. Nous verrons plus tard que dans d'autres cas le moteur profite de l'index pour un tel cas.

Bonne recherche…