mysql ou échec d'index

L'instruction IN dans MySQL est une instruction de requête très couramment utilisée qui peut interroger plusieurs valeurs à la fois, par exemple :

SELECT * FROM table WHERE id IN (1,2,3,4);

Cette instruction de requête will Renvoie les lignes avec les ID 1, 2, 3 et 4. L'avantage de l'instruction IN est qu'elle nous permet d'interroger plusieurs valeurs à la fois sans avoir à utiliser plusieurs instructions OR. Cependant, lorsqu'il y a de nombreuses valeurs dans l'instruction IN, cela peut provoquer un échec de l'index, ce qui affectera sérieusement l'efficacité des requêtes.

Voyons pourquoi l'échec de l'index se produit et comment l'éviter.

1. Cause de l'échec de l'index

Lors de l'utilisation de l'instruction IN, MySQL comparera chaque valeur individuellement, ce qui entraînera un échec de l'index.

Supposons que nous ayons une table commandes, qui contient les détails de la commande, et que l'un des champs est customer_id, qui représente l'identifiant client de la commande. Nous souhaitons interroger toutes les commandes portant les identifiants client 1, 2, 3 et 4. Vous pouvez utiliser l'instruction de requête suivante :

SELECT * FROM commandes WHERE customer_id IN (1,2,3,4);

S'il y a un index sur le champ customer_id, alors MySQL utilisera cet index pour interroger. Cependant, lorsque MySQL doit effectuer une comparaison distincte pour chaque valeur de l'instruction IN, il devra analyser l'intégralité de l'index, ce qui rendra l'index invalide.

Supposons que nous ayons 1 000 commandes, dont 500 avec l'ID client 1, 200 avec l'ID client 2, 100 avec l'ID client 3 et 50 avec l'ID client 4. Si vous utilisez l'instruction de requête ci-dessus, MySQL doit analyser 500+200+100+50=850 lignes de l'index entier, ce qui aura évidemment un impact sérieux sur les performances.

2. Comment éviter l'échec de l'index

Afin d'éviter l'échec de l'index, nous pouvons utiliser certaines techniques pour réécrire l'instruction de requête. Voici quelques techniques courantes :

2.1 Utiliser plusieurs instructions OR

Si le nombre de valeurs dans l'instruction IN est faible, seulement quelques-unes, alors nous pouvons utiliser plusieurs instructions OR, par exemple :

SELECT * FROM commandes WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 3 OR customer_id = 4;

Cela forcera MySQL à utiliser l'index pour les requêtes sans provoquer de problèmes de performances. Cependant, cette approche n'est pas réalisable s'il existe de nombreuses valeurs dans l'instruction IN.

2.2 Utiliser l'instruction EXISTS

Une autre façon consiste à utiliser l'instruction EXISTS pour interroger :

SELECT *
FROM commandes o
WHERE EXISTS (
SELECT *
FROM (VALUES (1), (2), (3), ( 4)) AS c(customer_id)
WHERE c.customer_id = o.customer_id
);

Cette instruction de requête place la valeur de l'instruction IN dans une sous-requête, puis utilise l'instruction EXISTS pour interroger. Cette méthode peut éviter les problèmes d'échec d'index causés par les instructions IN.

2.3 Utiliser une table temporaire

Une autre méthode consiste à utiliser une table temporaire pour stocker les valeurs dans l'instruction IN, puis à utiliser l'instruction JOIN pour interroger :

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_customer_ids (
customer_id INT PRIMARY KEY
);

INSERT IGNORE INTO tmp_customer_ids (customer_id)
VALUES (1), (2), (3), (4);

SELECT *
FROM commandes o
JOIN tmp_customer_ids tci ON tci.customer_id = o. customer_id;

this La méthode créera une table temporaire pour stocker les valeurs dans l'instruction IN, puis utilisera l'instruction JOIN pour connecter la table temporaire à la table des commandes pour la requête. Cette méthode peut éviter le problème d'échec d'index causé par les instructions IN, et il est également plus facile de mettre en cache les résultats des requêtes (MySQL interrogera les instructions JOIN en cache).

3. Résumé

L'instruction IN est une instruction de requête très pratique, mais lorsqu'il y a de nombreuses valeurs dans l'instruction IN, cela peut provoquer un échec de l'index et affecter sérieusement les performances. Pour éviter ce problème, nous pouvons utiliser plusieurs instructions OR, utiliser des instructions EXISTS ou utiliser des tables temporaires pour réécrire l'instruction de requête. Ces méthodes peuvent éviter les échecs d’index et améliorer les performances des requêtes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!