L'opérateur SQL LIKE est la
cause très fréquente de comportements inattendus au niveau des
performances car certains termes de recherche empêchent une utilisation
efficace des index. Cela signifie que certains termes de recherche
peuvent être très bien indexés et d'autres non. La position des
caractères joker fait toute la différence.
L'exemple suivant utilise le caractère
joker % au milieu du terme de recherche :
SELECT prenom, nom, date_de_naissance
FROM employes
WHERE UPPER(nom) LIKE 'WIN%D'- MySQL
+----+----------+-------+---------+---------+------+-------------+ | id | table | type | key | key_len | rows | Extra | +----+----------+-------+---------+---------+------+-------------+ | 1 | employes | range | nom_emp | 767 | 2 | Using where | +----+----------+-------+---------+---------+------+-------------+- Oracle
--------------------------------------------------------------- |Id | Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYES | 1 | 4 | |*2 | INDEX RANGE SCAN | NOM_MAJ_EMP | 1 | 2 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(UPPER("NOM") LIKE 'WIN%D') filter(UPPER("NOM") LIKE 'WIN%D')- PostgreSQL
QUERY PLAN ------------------------------------------------------- Index Scan using nom_maj_emp on employes (cost=0.01..8.29 rows=1 width=17) Index Cond: (upper((nom)::text) ~>=~ 'WIN'::text) AND (upper((nom)::text) ~<~ 'WIO'::text) Filter: (upper((nom)::text) ~~ 'WIN%D'::text)
Les filtres LIKE peuvent
seulement utiliser les caractères avant le premier caractère
joker lors du parcours de l'arbre. Les caractères qui suivent
sont simplement des prédicats filtres qui ne peuvent pas diminuer
l'intervalle parcouru de l'index. Une expression LIKE seule
peut donc contenir deux types de prédicat : (1) la partie avant le
caractère joker comme prédicat accès ; (2) les autres caractères comme
prédicat filtre.
Prudence
L'opérateur LIKE fonctionne caractère par caractère
alors que les collations peuvent traiter plusieurs caractères en un seul
élément de tri. De ce fait, certaines collations empêchent l'utilisation
des index avec LIKE. Pour plus de détails, vous pouvez
lire l'article Indexing
“LIKE” in PostgreSQL and Oracle écrit par Laurenz Albe.
Plus le préfixe avant le première
caractère joker est sélectif, et plus petit sera l'intervalle parcouru
de l'index. Cela a comme conséquence de rendre la recherche via l'index
plus rapide. La Figure 2.4
illustre la relation entre trois expressions LIKE
différentes. Toutes les trois sélectionnent la même ligne mais
l'intervalle parcouru de l'index, et donc les performances, est très
différent.
Figure 2.4 Différentes recherches LIKE
La première expression dispose de deux caractères avant le
caractère joker. Ils limitent l'intervalle parcouru dans l'index à 18
lignes. Seule une d'entre elles correspond à l'expression
LIKE entière. Les 17 autres sont récupérées puis
abandonnées. La deuxième expression a un préfixe plus long qui diminue
l'intervalle à deux lignes. Avec cette expression, la base de données
lit seulement une ligne en trop, une ligne qui n'est pas pertinente pour
le résultat. La dernière expression n'a pas de prédicat accès du tout :
la base de données ne fait que lire l'enregistrement qui correspond à
l'expression LIKE complète.
Important
Seule la partie avant le premier caractère joker sert de prédicat accès.
Le reste des caractères ne peut pas diminuer l'intervalle parcouru dans l'index. Les enregistrements qui ne correspondent pas à la recherche sont juste ignorés pour le résultat final.
Le cas opposé est aussi possible : une expression
LIKE qui commence avec un caractère joker. Une telle
expression LIKE ne peut pas servir comme prédicat accès. La
base de données doit parcourir la table entière s'il n'y a pas d'autres
conditions qui permettent d'accéder à des prédicats accès.
Astuce
Évitez les expressions LIKE
ayant des caractères joker en début (par exemple,
'%TERM').
La position des caractères jokers affecte l'utilisation des index, au moins en théorie. En fait, l'optimiseur crée un plan d'exécution générique quand le terme de la recherche est fourni avec les paramètres liés. Dans ce cas, l'optimiseur doit deviner si la majorité des exécutions auront un caractère en tête ou non.
La plupart des bases de données supposent qu'il n'y aura pas de
caractère joker au début lors de l'optimisation d'une condition
LIKE avec un paramètre lié, mais cette supposition est
fausse si l'expression LIKE est utilisée pour la recherche
plein texte. Malheureusement, il n'existe pas de moyens directs pour
préciser que la condition LIKE sera utilisée pour de la
recherche plein texte. L'encart « Indiquer les expressions LIKE pour la recherche
plein texte » montre ce qui ne fonctionne pas. Ne
pas utiliser de paramètre lié est la solution la plus évidente, mais
cela augmente le travail de l'optimiseur et la vulnérabilité à une
injection SQL est toujours présente. Une solution efficace, sécurisée et
portable est de cacher volontairement la condition LIKE.
« Combiner des colonnes »
explique cela en détail.
Pour la base de données PostgreSQL, le
problème est différent parce que PostgreSQL suppose qu'il y a
toujours un caractère joker lors de l'utilisation
de paramètres liés pour une expression LIKE. PostgreSQL
n'utilise tout simplement pas d'index dans ce cas. La seule façon
d'obtenir un parcours d'index pour une expression LIKE est
de rendre le terme de recherche visible à l'optimiseur. Si vous
n'utilisez pas un paramètre lié, mais que vous placez le terme de
recherche directement dans la requête SQL, vous devez prendre d'autres
dispositions pour vous protéger contre les attaques par injection
SQL.
Même si la base de données optimise le plan d'exécution
pour un caractère joker au début, cela peut offrir des performances
insuffisantes. Vous pouvez utiliser une autre partie de la clause
where pour accéder efficacement aux données. Voir
aussi « Prédicats de filtre utilisés intentionnellement sur des
index ». S'il n'existe pas d'autres
chemins d'accès, vous pouvez utiliser une des solutions de recherche
plein texte suivantes.
- Db2 (LUW)
Db2 supporte le mot-clé
contains. Voir le document « Search functions for Db2 Text Search » .- MySQL
MySQL propose les mots-clés
matchetagainstpour la recherche plein texte. À partir de MySQL 5.6, vous pouvez créer des index de recherche plein texte pour les tables InnoDB. Auparavant, cela était seulement possible avec les tables MyISAM. Voir le chapitre « Full-Text Search Functions » de la documentation MySQL.- Oracle
La base de données Oracle propose le mot-clé
contains. Voir le document « Oracle Text Application Developer's Guide. »- PostgreSQL
PostgreSQL propose l'opérateur
@@pour ajouter les recherches plein texte. Voir le chapitre « Recherche plein texte » dans la documentation PostgreSQL.Une autre option revient à utiliser l'extension WildSpeed pour optimiser directement les expressions
LIKE. L'extension stocke le texte dans toutes les rotations possibles, pour que chaque caractère soit une fois au début du texte. Cela signifie que le texte indexé n'est pas stocké une fois, mais autant de fois qu'il y a de caractères. Il nécessite donc beaucoup d'espace disque.- SQL Server
SQL Server propose le mot-clé
contains. Voir le chapitre « Full-Text Search » dans la documentation de SQL Server.
Réflexion
Comment pouvez-vous indexer une recherche LIKE qui
a seulement un caractère joker au début du terme de recherche
('%TERME') ?
