par Guillaume Lelarge.

Indexer les filtres LIKE


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)

SQL Server

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

Pour la base de données PostgreSQL, vous pouvez avoir besoin de spécifier une classe d'opérateur (par exemple, varchar_pattern_ops) pour utiliser les expressions LIKE comme prédicats accès. Référez-vous à « Operator Classes and Operator Families » dans la documen­tation PostgreSQL pour plus de détails.

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.

Indiquer les expressions LIKE pour la recherche plein texte

Lors de l'utilisation de l'opérateur LIKE pour une recherche plein texte, vous pouvez séparer les caractères joker du terme de la recherche :

WHERE colonne_texte LIKE '%' || ? || '%'
Les caractères joker sont directement écrits dans la requête SQL mais nous utilisons un paramètre lié pour le terme de la recherche. L'expression LIKE finale est construite par la base de données en utilisant l'opérateur de concaténation de chaînes de caractères || (Oracle, PostgreSQL). Bien qu'on utilise un paramètre lié, l'expression LIKE finale commencera toujours avec un caractère joker. Malheureusement, les bases de données ne reconnaissent pas ça.

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

DB2 supporte le mot-clé contains. Voir le document « DB2 Text Search tutorial » sur le site developerWorks d'IBM.

MySQL

MySQL propose les mots-clés match et against pour 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 « Full Text Search » 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') ?

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license