Subs When Substring Sql : Optimiseur pour vos requêtes SQL

Comprendre le problème avec les requêtes SUBSTRING et LIKE

Requêtes qui interrogent le contenu d’une colonne à l’aide de fonctions de chaîne, notamment SUBSTRINGles opérateurs LIKE ou les expressions régulières représentent souvent des goulots d’étranglement en termes de performances. Lorsqu’une table contient des millions d’enregistrements, un simple WHERE column LIKE '%motif%' déclenche une analyse complète de la table. Le curseur ne trouve aucune information indexée qui pourrait le restreindre et la base de données finit par lire chaque ligne pour tester le modèle. Il devient alors crucial de savoir reformuler ou structurer les requêtes pour tirer parti des index, réduire les coûts d’E/S et éviter les croisières interlignes.

Pourquoi l’optimisation de ces requêtes est vitale

• Temps de réponse : Sur un serveur de production, une requête millisecondaire peut transformer un tableau de bord interactif en un charmant livre d’attente.
• Charge du serveur : Les analyses complètes augmentent la lecture du disque et le trafic réseau, saturant ainsi les ressources partagées.
• Coût des ressources : Dans les bases cloud, chaque Go lu peut engendrer un réel coût.
• Évolutivité : Une architecture qui ne prend pas en compte la complexité des requêtes texte est vouée à l’échec lorsque les données s’accumulent.

Gardez ces motivations à l’esprit pour justifier chaque technique que nous explorerons.

Analyse d’un scénario type

Supposons qu’un tableauproduits:

CREATE TABLE produits (
    id          INT PRIMARY KEY,
    code        VARCHAR(20) NOT NULL,
    description TEXT,
    catégorie   VARCHAR(50),
    prix        DECIMAL(10,2)
);

Une filiale souhaite afficher des articles dont code contient les caractères -01-.

SELECT id, code, description
FROM produits
WHERE code LIKE '%-01-%';

Un test simple montre qu’un indice dans code ne suffit pas, car le caractère d’initialisation % empêche l’utilisation de l’index. Comment contourner cette limitation ?

Stratégies d’indexation spécialisées

1. Index des expressions (expression) – les indices générés

Dans MySQL 8.0+, PostgreSQL ou SQL Server 2016+, vous pouvez créer un index sur l’expression qui extrait la partie pertinente d’une chaîne, par exemple:

CREATE INDEX idx_produits_code_partie
ON produits (SUBSTRING(code, 3, 3));

Cette technique permet à la requête ci-dessous d’utiliser l’index:

SELECT id, code
FROM produits
WHERE SUBSTRING(code, 3, 3) = '-01';

Réduire le motif à une longueur fixe optimise la recherche.

Avantages

• Recherche plus rapide (lecture d’index, pas de table).
• Il n’est pas nécessaire de modifier la requête d’origine si l’application ne peut pas modifier le code.

Limites

• L’index est statique : tout changement dans la structure de l’index code nécessite une mise à jour de l’index.

2. Index des suffixes (Gin, Gist, GiST) – index des types de texte

PostgreSQL propose l’index GIN de personnages à LIKE / ILIKE:

CREATE INDEX idx_description_gin
ON produits USING GIN (to_tsvector('simple', description));

Cette approche est utile pour les longues chaînes de texte, notamment lors de la recherche de mots spécifiques. La requête devient :

SELECT id
FROM produits
WHERE to_tsvector('simple', description) @@ plainto_tsquery('simple', 'motif');

Même si cela modifie un peu la logique métier, dans un contexte d’analyse de texte, ce gain de performance vaut l’investissement.

Optimiser les requêtes LIKE

1. Évitez les caractères génériques de début

Contrairement à l’indexation traditionnelle, un LIKE '%xyz' n’autorisera jamais l’utilisation d’un index. Deux solutions :

• Réécrire la requête : Si vous disposez d’un suffixe logique, une colonne dédiée peut stocker ce suffixe ou un hachage pour l’indexer.
• Pour utiliser RIGHT() : Pour les bases de données prenant en charge l’indexation fonctionnelle, créez CREATE INDEX idx_suffixe ON produits (RIGHT(code, 3));.

2. Prétraitement avec colonnes de précalcul

Une stratégie simple mais puissante consiste à ajouter une colonne stockée contenant la pièce recherchée:

ALTER TABLE produits ADD COLUMN code_suffixe VARCHAR(3) GENERATED ALWAYS AS (SUBSTRING(code, 3, 3)) STORED;
CREATE INDEX idx_suffixe ON produits (code_suffixe);

Les requêtes deviennent alors :

SELECT ...
FROM produits
WHERE code_suffixe = '-01';

Le coût de stockage est négligeable (quelques octets par ligne seulement) tandis que la lecture est instantanée.

3. Utilisez l’opérateur RLIKE ou REGEXP de manière ciblée

Si vous avez besoin d’un modèle plus complexe que le LIKEles expressions régulières peuvent mieux fonctionner en combinaison avec des index de types FTS. Par exemple :

SELECT *
FROM produits
WHERE code REGEXP '^ABC[^0-9]{3}';

Bien que le moteur d’expression régulière puisse toujours effectuer une analyse complète dans certains SGBDR, sa combinaison avec un index fonctionnel peut permettre une extraction rapide des lignes potentielles.

Meilleures pratiques de conception de bases de données

1. Choisissez les types de données appropriés

• VARCHAR pour les chaînes de longueur variable;
• CHAR pour les colonnes de taille fixe (par exemple, les codes produits). Une colonne CHAR(10) est indexé plus efficacement que VARCHAR(10).

2. Normaliser les colonnes de recherche

Si votre candidature nécessite fréquemment LIKE dans un segment identique, stockez-le dans sa propre colonne. Cela crée un couplage plus étroit entre la structure des données et la requête métier.

3. Évaluer l’utilisation du partitionnement

Le partitionnement de la table par un champ fréquent (par exemple année, région) limite le nombre de lignes à analyser. Lorsque la requête inclut un critère de partition (par ex. WHERE date > '2023-01-01' AND code LIKE '%-01-%'), le moteur interroge uniquement la partition concernée.

Revue de cas pratiques (MySQL)

Stage	Demande	Coût (≈ E/S)	Commentaire
Maison	SELECT * FROM produits WHERE code LIKE '%-01-%';	Analyse de table à 100%	Aucun index utilisé
Indice fonctionnel	CREATE INDEX idx_subcode ON produits (SUBSTRING(code, 3, 3)); SELECT * FROM produits WHERE SUBSTRING(code,3,3)='-01';	5% du tableau	Utiliser l’index
Colonne stockée	ALTER TABLE produits ADD COLUMN code_suffix VARCHAR(5) AS (SUBSTRING(code, 3, 5)) STORED; CREATE INDEX idx_suffix ON produits(code_suffix);	3% du tableau	Frais généraux minimaux, requête simple

L’analyse montre que la combinaison de colonnes fonctionnelles ou stockées s’accompagne souvent d’une réduction spectaculaire des E/S, ce qui est essentiel pour des fondations évolutives.

Tests de performances pratiques

Un petit processus de test A/B vous donne une confiance empirique:

1. Installez deux copies de votre base de données (ou utilisez un environnement de test).
2. Exécutez la requête initiale sur la copie brute, notez l’heure X.
3. Appliquez la stratégie d’indexation décrite, réexécutez et comparez.
4. Vérifiez que le plan d’exécution EXPLAIN montre l’utilisation de l’index et ne contient pas Table Scan.

Les gains sont souvent mesurés de la même manière que la réduction du temps de réponse (par exemple, de 5 s à 0,5 s), mais les bénéfices s’étendent à l’ensemble des utilisateurs et de l’infrastructure globale.

Conclusion

L’optimisation des requêtes contenant des sous-chaînes ne doit pas être considérée comme un simple « double souris sur l’index ».
✔️ Connaître les limites de LIKE et SUBSTRING permet de repenser la structure des données.
✔️ L’utilisation d’index fonctionnels ou de colonnes dérivées offre une solution simple à implémenter dans pratiquement n’importe quel SGBD.
✔️ Le partitionnement, la normalisation des colonnes et l’utilisation de moteurs de recherche de texte (FTS) complètent la boîte à outils.

En appliquant ces techniques, vous transformez les requêtes lentes en filtres rapides, réduisez la charge du serveur et améliorez l’expérience utilisateur tout en contrôlant vos coûts. Prenez le temps d’analyser chaque requête de recherche, de les tester et de les peaufiner – votre base de données vous en remerciera.