Influence du type d’index sur un Select

Pour améliorer les performances des requêtes SQL la solution la plus souvent avancée est de « créer un index ». Cela n’est malheureusement pas aussi simple, les index n’optimisant pas certains types de requêtes, sans compter qu’il existe deux types majeurs d’index dont les avantages et les inconvénients varient. Il faut donc toujours procéder à une réflexion complète sur l’intérêt même de ces index par rapport aux problèmes qu’ils peuvent poser, mais aussi effectuer une série de tests afin de vérifier que les index ont bien une influence positive sur les performances des requêtes utilisées. Même une simple requête Select peut ne pas bénéficier d’un index.

Pour illustrer cela j ‘ai effectué un micro-benchmark sur SQLServer 2005 pour comparer les performances d’une table sans index (heap), d’un index ordonné (clustered) et d’un index non ordonné (nonclustered) sur la requête suivante :

Select count(*) from R_Temp.dbo.EMAIL where Email like ('frederic%')

La table compte 8 555 648 lignes, pèse 550 Mo et est constituée de 8 colonnes. Le champ Email est unique.

Voici le plan d’exécution estimé de cette requête sur la table sans index :

SQLServer doit ici parcourir toute la table (Table Scan) pour trouver les lignes correspondant à la requête et le coût est donc très élevé : elle s’exécute en 2,5 s sur mon serveur. 

J’ai ensuite créé un index non ordonné sur la table et voici le plan d’exécution estimé correspondant :

La requête est ici considérablement optimisée car passant par un Index Seek : elle s’exécute en 15 ms. L’intérêt de l’index est ici parfaitement vérifié.

Le champ Email étant unique il semble plus avantageux de créer un index ordonné. Voici le plan d’exécution estimé correspondant :

On le voit, le coût de la requête est ici plus élevé de plus de 50% qu’avec un index non ordonné ! Faut-il pour autant immédiatement éliminer cette solution ? C’est là que l’intérêt du test en situation se révèle important : la requête s’exécute en moyenne en 15 ms, soit exactement aussi rapidement qu’avec un index non ordonné.

Pour le moment on peut retenir une chose : la requête profite énormément de l’existence d’un index, qu’il soit ordonné ou non. Dans les deux cas le plan d’exécution passe bien par un Index Seek, ce qui est de nature à faire plaisir à tout administrateur de bases de données.

Passons maintenant à une requête très légèrement différente : au lieu de rechercher tous les emails commençant par frederic, cherchons ceux dont le nom de domaine est hotmail.com.

Select count(*) from R_Temp.dbo.EMAIL where Email like ('%@hotmail.com')

Sur la table sans index on obtient le plan d’exécution suivant :

Le coût est quasi identique au like précédent et la requête s’exécute en moyenne en 2,5 s également.

La solution utilisant un index non ordonné donne le plan suivant :

On remarque immédiatement que la requête ne profite plus d’un Index Seek mais passe à présent par un Index Scan beaucoup plus coûteux. On obtient néanmoins un coût estimé 40% inférieur à la table sans index. Si l’on exécute la requête on s’aperçoit que la requête demande un temps d’exécution de 2,5 s, identique au précédent.

Enfin, la solution utilisant un index ordonné donne le plan suivant :

La requête utilise ici également un Index Scan coûteux aboutissant à un coût estimé identique à celui avec la table sans index. L’exécution de la requête se fait en moyenne en 2,6s.

Récapitulons les résultats obtenus :

De cette série de tests on peut retirer deux enseignements :

• un plan d’exécution estimé est, comme son nom l’indique, une estimation du coût de la requête étudiée. Seule la mesure du temps d’exécution véritable de la requête permettra de trancher entre plusieurs possibilités d’optimisation, l’intérêt du plan d’exécution estimé étant de vérifier rapidement si les index seront utilisés correctement et de s’assurer que l’on n’introduit pas de traitement supplémentaire coûteux.
• le fait d’écrire un Select ne signifie pas systématiquement que l’index sera utilisé de manière optimale (Index Seek).