mysql > échec de l'index

MySQL est un système de gestion de bases de données relationnelles largement utilisé. Pendant le développement, les index peuvent améliorer l'efficacité des requêtes, mais vous rencontrez parfois des échecs d'index, ce qui ralentit les requêtes, voire rend impossible l'utilisation de l'index.

Cet article présentera les causes de l'échec de l'index, comment détecter l'échec de l'index, comment optimiser l'index et quelques cas pratiques.

1. La raison de l'échec de l'index

1. Une fonction est utilisée sur la colonne d'index

Par exemple : SELECT * FROM user WHERE DATE_FORMAT(create_time,'%Y-%m-%d')='2021- 01-01' ;

Solution : Stockez les résultats de l'opération de la fonction dans une table temporaire ou une nouvelle table, puis interrogez.

2. Incompatibilité de type de colonne d'index

Par exemple : SELECT * FROM user WHERE create_time='2021-01-01';

Si le champ create_time est de type datetime et que la condition de requête est de type caractère, cela entraînera un échec de l'index. .

Solution : gardez le type de condition de requête cohérent avec le type de colonne d'index.

3. Les opérateurs sont utilisés sur les colonnes d'index

Par exemple : SELECT * FROM user WHERE create_time + INTERVAL 1 DAY >

Solution : essayez d'éviter d'utiliser des opérateurs sur les colonnes d'index, vous pouvez d'abord calculer l'opération. Interrogez à nouveau les résultats.

4. IS NULL ou IS NOT NULL est utilisé sur la colonne d'index

Par exemple : SELECT * FROM user WHERE create_time IS NULL ;

Solution : utilisez un index conjoint ou modifiez l'instruction de requête.

5. Les valeurs de la colonne d'index sont inégalement réparties

Par exemple : pour une table contenant des millions de données, 90 % des valeurs d'un certain champ sont égales à ce moment, toute requête utilisant ceci. le champ échouera.

Solution : utilisez un index commun ou un index couvrant.

6. La quantité de données est trop importante

Par exemple : SELECT * FROM user WHERE name LIKE '%abc%' ;

Solution : Utilisez un index de texte intégral ou modifiez les conditions de requête.

2. Comment détecter l'échec de l'index

Vous pouvez afficher le plan d'exécution de l'instruction de requête via le mot-clé EXPLAIN.

Par exemple : EXPLAIN SELECT * FROM user WHERE create_time='2021-01-01';

Si Using Where apparaît dans les résultats de la requête, cela signifie que l'index n'est pas valide et doit être optimisé.

3. Comment optimiser l'index

1. Créer un index conjoint

Un index conjoint fait référence à un index composé de plusieurs colonnes, ce qui peut améliorer l'efficacité des requêtes.

Par exemple : CREATE INDEX idx_user ON user(create_time, name);

L'ordre de la requête doit être pris en compte. Par exemple, dans l'instruction ci-dessus, create_time est la première colonne et name est la deuxième colonne Create_time. dans la condition de requête. Cet index peut être utilisé.

2. Créer un index de couverture

Un index de couverture signifie que les résultats de la requête doivent uniquement être obtenus à partir de l'index, pas de la table de données.

Par exemple : CREATE INDEX idx_user ON user(create_time) INCLUDE(name);

Les colonnes qui doivent être incluses doivent être clairement spécifiées lors de l'interrogation, seuls les résultats de la requête doivent être obtenus à partir de l'index, ce qui peut réduire. le nombre d'accès à la table de données et améliorer l'efficacité des requêtes.

3. Utiliser l'index de texte intégral

L'index de texte intégral est un index spécial adapté aux scénarios de recherche de texte.

Par exemple : CREATE FULLTEXT INDEX idx_user ON user(name);

Il faut noter que MySQL ne prend en charge que les index de texte intégral en anglais et en chinois, d'autres langues nécessitent l'utilisation de plug-ins tiers.

4. Cas réel

1. L'échec de l'index entraîne un ralentissement de la requête

Un certain système a une table utilisateur, qui contient des millions de données, qui contient un nom de champ, qui est utilisé pour interroger les utilisateurs.

Au départ, le champ n'avait aucun index et la requête était très lente. Plus tard, un index a été ajouté à ce champ et la vitesse des requêtes a été considérablement améliorée.

Cependant, après que le système ait été mis en ligne pendant un certain temps, il a été constaté que la vitesse des requêtes avait de nouveau diminué et de nombreux enregistrements similaires aux suivants sont apparus dans le journal d'accès :

SELECT * FROM user WHERE name LIKE '%abc %';

Par mot-clé EXPLAIN Vérifiez le plan d'exécution et constatez que l'index n'est pas valide et doit être optimisé.

La solution finale consiste à créer un index de texte intégral pour le champ et à modifier l'instruction de requête. L'instruction de requête modifiée est la suivante :

SELECT * FROM user WHERE MATCH(name) AGAINST('abc');

2. L'échec de l'index rend la durée d'exécution de la requête trop longue

Un certain système a une commande table order, qui contient des millions de données, y compris un champ create_time, utilisé pour interroger les commandes.

Initialement, le champ était indexé et la vitesse de requête était acceptable. Cependant, à mesure que le nombre de commandes augmente, la durée d’exécution des requêtes augmente progressivement, entraînant même l’expiration de certaines requêtes.

Consultez le plan d'exécution via le mot-clé EXPLAIN et constatez que l'index n'est pas valide et doit être optimisé.

La solution finale consiste à créer un index commun pour le champ et à modifier l'instruction de requête. L'instruction de requête modifiée est la suivante :

SELECT * FROM order WHERE create_time='2021-01-01' AND status='SUCCESS';

En résumé, les index sont un moyen important d'améliorer l'efficacité des requêtes, mais en utilisation réelle , il est nécessaire d'éviter l'échec de l'index. En détectant et en optimisant les index, l'efficacité des requêtes peut être améliorée et des performances de base de données optimales peuvent être obtenues.

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!