Introduction détaillée à l'indexation MySQL et à l'optimisation des requêtes

Cet article vous apporte une introduction détaillée à l'indexation MySQL et à l'optimisation des requêtes. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

L'article « Analyse des requêtes MySQL » décrit la méthode d'utilisation de la requête lente MySQL et de la commande d'explication pour localiser les goulots d'étranglement des performances MySQL. Après avoir localisé les instructions SQL des goulots d'étranglement des performances, vous devez analyser les instructions SQL inefficaces. optimisation. Cet article traite principalement des principes d'indexation MySQL et de l'optimisation des requêtes SQL couramment utilisées.

Un test de comparaison simple

Dans le cas précédent, la table c2c_zwdb.t_file_count n'a qu'un seul identifiant auto-incrémenté, et l'exécution de SQL sans indexer le champ FFileName est la suivante :

Dans l'image ci-dessus, tapez=all, key=null, rows=33777. Ce SQL n'utilise pas d'index et constitue une analyse de table complète très inefficace. Si des requêtes conjointes et d'autres contraintes sont ajoutées, la base de données consommera une mémoire folle et affectera l'exécution du programme frontal.

Ajoutez maintenant un index au champ FFileName :

alter table c2c_zwdb.t_file_count add index index_title(FFileName);

Exécutez à nouveau l'instruction de requête ci-dessus, le contraste est évident :

Dans cette figure, tapez=ref, key=index name (index_title), rows=1. Ce SQL utilise l'index index_title et il s'agit d'une analyse constante. Une seule ligne est analysée en fonction de l'index.

Par rapport à la situation sans indexation, après l'ajout d'un index, le contraste d'efficacité des requêtes est très évident.

Indice MySQL

Comme le montre le test de comparaison ci-dessus, l'index est la clé d'une recherche rapide. L'établissement d'un index MySQL est très important pour le fonctionnement efficace de MySQL. Pour une petite quantité de données, l’impact de ne pas avoir d’index approprié n’est pas grand, mais à mesure que la quantité de données augmente, les performances chuteront fortement. Si plusieurs colonnes sont indexées (index combiné), l'ordre des colonnes est très important et MySQL ne peut effectuer des recherches efficaces que sur le préfixe le plus à gauche de l'index.

Ce qui suit présente plusieurs types d'index MySQL courants.

Les index sont divisés en index à colonne unique et en index combinés. Un index à une seule colonne signifie qu'un index ne contient qu'une seule colonne. Une table peut avoir plusieurs index à une seule colonne, mais il ne s'agit pas d'un index combiné. Index combiné, c'est-à-dire qu'un index contient plusieurs colonnes.

1.Type d'index MySQL

(1) Index de clé primaire PRIMARY KEY

C'est un index unique spécial qui n'autorise pas les valeurs nulles. Généralement, l'index de clé primaire est créé en même temps lors de la création de la table.

Bien entendu, vous pouvez également utiliser la commande ALTER. N'oubliez pas : une table ne peut avoir qu'une seule clé primaire.

(2) Index unique UNIQUE

La valeur de la colonne d'index unique doit être unique, mais les valeurs nulles sont autorisées. Dans le cas d'un index composite, la combinaison des valeurs des colonnes doit être unique. Vous pouvez le spécifier lors de la création de la table, ou vous pouvez modifier la structure de la table, comme :

ALTER TABLE table_name ADD UNIQUE (column)

(3) Indice ordinaire INDEX

C'est l'index le plus basique, il n'a aucune restriction. Vous pouvez le spécifier lors de la création de la table, ou vous pouvez modifier la structure de la table, comme :

ALTER TABLE table_name ADD INDEX index_name (column)

(4 ) Index combiné INDEX

Index combiné, c'est-à-dire qu'un index contient plusieurs colonnes. Vous pouvez le spécifier lors de la création de la table, ou vous pouvez modifier la structure de la table, comme :

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

(5) Index de texte intégral FULLTEXT

L'index de texte intégral (également appelé recherche de texte intégral) est une technologie clé actuellement utilisée par les moteurs de recherche. Il peut utiliser divers algorithmes tels que la technologie de segmentation de mots pour analyser intelligemment la fréquence et l'importance des mots clés dans le texte, puis filtrer intelligemment les résultats de recherche souhaités selon certaines règles d'algorithme.

peut être spécifié lors de la création du tableau, ou la structure du tableau peut être modifiée, comme :

ALTER TABLE table_name ADD FULLTEXT (column)

2 . Structure de l'index Et principe

B+Tree est couramment utilisé comme index dans MySQL, mais l'implémentation diffère selon l'index clusterisé et l'index non clusterisé. Cet article ne discutera pas de ce point pour l'instant.

Introduction aux arbres b+

La photo de l'arbre b+ ci-dessous peut être vue à de nombreux endroits. La raison pour laquelle j'ai choisi cette photo ici est parce que je pense que cette photo. Cela peut très bien expliquer le processus de recherche dans l’index.

Comme indiqué ci-dessus, c'est un arbre b+. Le bloc bleu clair est appelé bloc de disque. Vous pouvez voir que chaque bloc de disque contient plusieurs éléments de données (affichés en bleu foncé) et des pointeurs (affichés en jaune). Par exemple, le bloc de disque 1 contient les éléments de données 17 et 35. Contient des pointeurs. P1, P2 et P3 représentent des blocs de disque inférieurs à 17, P2 représente des blocs de disque compris entre 17 et 35 et P3 représente des blocs de disque supérieurs à 35.

Les données réelles existent dans les nœuds feuilles, à savoir 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Les nœuds non-feuilles ne stockent pas de données réelles, mais uniquement les éléments de données qui guident la direction de recherche. Par exemple, 17 et 35 n'existent pas réellement dans la table de données.

Processus de recherche

Dans l'image ci-dessus, si vous souhaitez trouver l'élément de données 29, alors le bloc de disque 1 sera d'abord chargé du disque vers la mémoire, et une IO va se produire à ce moment, utilisez la recherche binaire en mémoire pour déterminer que 29 est compris entre 17 et 35, verrouillez le pointeur P2 du bloc disque 1, le temps mémoire est négligeable car très court (par rapport aux IO du disque ), et transmettez l'adresse disque du pointeur P2 du bloc de disque 1. Chargez le bloc de disque 3 du disque vers la mémoire, la deuxième E/S se produit, 29 est entre 26 et 30, verrouillez le pointeur P2 du bloc de disque 3, chargez le bloc de disque 8 dans la mémoire via le pointeur, le troisième IO se produit, et en même temps dans la mémoire Faites une recherche binaire pour trouver 29 et terminer la requête, un total de trois IO. La situation réelle est qu'un arbre b+ à 3 couches peut représenter des millions de données. Si des millions de recherches de données ne nécessitent que trois IO, l'amélioration des performances sera énorme s'il n'y a pas d'index, chaque élément de données devra avoir une IO. , alors un total de millions d'IO sont nécessaires, et le coût est évidemment très, très élevé.

Propriétés

(1) Le champ d'index doit être aussi petit que possible.

D'après le processus de recherche de l'arbre b+ ci-dessus, ou du fait que des données réelles existent dans les nœuds feuilles, nous pouvons savoir que le nombre d'IO dépend de la hauteur h du nombre b+.

Supposons que le volume de données de la table de données actuelle est N et que le nombre d'éléments de données dans chaque bloc de disque est m, alors la hauteur de l'arbre h=㏒(m+1)N, lorsque le volume de données N est constant, plus m est grand, plus h est petit ;

Et m = la taille du bloc de disque/la taille de l'élément de données. La taille du bloc de disque est la taille d'une page de données. , qui est fixe ; si l'espace occupé par la donnée est plus grand, Small, plus le nombre m de données est grand, plus la hauteur h de l'arbre est faible. C'est pourquoi chaque élément de données, c'est-à-dire le champ d'index, doit être aussi petit que possible. Par exemple, int occupe 4 octets, soit la moitié de moins que bigint 8 octets.

(2) La caractéristique correspondante la plus à gauche de l'index.

Lorsque les éléments de données de l'arbre b+ sont des structures de données composites, telles que (nom, âge, sexe), le numéro b+ est utilisé pour construire l'arbre de recherche dans l'ordre de gauche à droite, par exemple quand ( Zhang San ,20,F) Lors de la récupération de données comme celle-ci, l'arbre b+ donnera la priorité à la comparaison des noms pour déterminer la prochaine direction de recherche. Si les noms sont identiques, l'âge et le sexe seront comparés en séquence, ainsi que les données récupérées. sera finalement obtenu ; quand (20,F ) Lorsque des données sans nom arrivent, l'arbre b+ ne sait pas quel nœud vérifier ensuite, car le nom est le premier facteur de comparaison lors de la construction de l'arbre de recherche, et vous devez d'abord rechercher en fonction du nom. pour savoir où aller ensuite. Par exemple, lors de la récupération de données comme (Zhang San, F), l'arbre b+ peut utiliser le nom pour spécifier la direction de recherche, mais l'âge du champ suivant est manquant, il ne peut donc trouver que toutes les données dont le nom est égal à Zhang San, puis correspond au sexe. Ce sont les données de F. Il s'agit d'une propriété très importante, c'est-à-dire la caractéristique de correspondance la plus à gauche de l'index.

Plusieurs principes pour la création d'index

(1) Principe de correspondance des préfixes les plus à gauche

Pour les index multi-colonnes, commencez toujours par le début de l'index Le champ commence et continue, et le milieu ne peut être ignoré. Par exemple, si vous créez un index multi-colonnes (nom, âge, sexe), le champ du nom sera mis en correspondance en premier, puis le champ de l'âge, et enfin le champ du sexe ne peut pas être ignoré. MySQL continuera à correspondre vers la droite jusqu'à ce qu'il rencontre une requête de plage (>,

Généralement, lors de la création d'un index multi-colonnes, la colonne la plus fréquemment utilisée dans la clause Where est placée à gauche.

Regardez un exemple comparatif dans lequel le complément est conforme au principe de correspondance des préfixes les plus à gauche et est conforme à ce principe.

Exemple : La table c2c_db.t_credit_detail a un index (Flistid,Fbank_listid)

ne correspond pas à l'extrême gauche préfixe L'instruction sql du principe de correspondance :

select * from t_credit_detail où Fbank_listid='201108010000199'G

Le sql utilise directement le deuxième champ d'index Fbank_listid, en sautant le premier champ d'index Flistid , fait ne respecte pas le principe de correspondance des préfixes les plus à gauche. Utilisez la commande expliquer pour afficher le plan d'exécution de l'instruction sql, comme indiqué ci-dessous :

Comme le montre la figure ci-dessus, l'instruction sql n'utilise pas d'index et constitue une analyse de table complète peu efficace.

Instruction SQL conforme au principe de correspondance des préfixes les plus à gauche :

select * from t_credit_detail Where Flistid='2000000608201108010831508721' et Fbank_listid='201108010000199'G

Ce SQL utilise d'abord le premier champ Flistid de l'index, puis utilise le deuxième champ Fbank_listid de l'index. Il n'y a pas de saut au milieu, ce qui est conforme au principe de correspondance de préfixe le plus à gauche. Utilisez la commande expliquer pour afficher le plan d'exécution de l'instruction sql, comme indiqué ci-dessous :

Comme le montre la figure ci-dessus, l'instruction sql utilise des index et analyse uniquement une ligne.

La comparaison montre que l'efficacité des instructions SQL qui respectent le principe de correspondance des préfixes les plus à gauche est grandement améliorée par rapport aux instructions SQL qui ne respectent pas ce principe, de l'analyse complète de la table à l'analyse constante.

(2) Essayez de choisir des colonnes avec une différenciation élevée comme index.

Par exemple, nous sélectionnerons le numéro d'étudiant comme index, mais pas le sexe.

(3) = et in peuvent être dans le désordre

Par exemple, a = 1 et b = 2 et c = 3, l'index (a, b, c) peut être créé dans n'importe quel ordre, requête mysql L'optimiseur vous aidera à l'optimiser sous une forme que l'index peut reconnaître.

(4) La colonne index ne peut pas participer au calcul, gardez la colonne "propre"

Par exemple : Flistid+1>'2000000608201108010831508721'. La raison est très simple. Si la colonne d'index participe au calcul, alors à chaque fois que l'index est récupéré, l'index sera calculé une fois puis comparé. Évidemment, le coût est trop élevé.

(5) Développez l'index autant que possible et ne créez pas de nouvel index.

Par exemple, il y a déjà un index de a dans la table, et maintenant vous souhaitez ajouter un index de (a, b), il vous suffit alors de modifier l'index d'origine.

Inconvénients des index

Bien que les index puissent améliorer l'efficacité des requêtes, les index ont également leurs propres inconvénients.

Surcharge supplémentaire de l'index :

(1) Espace : l'index nécessite de l'espace

(2) Temps : l'interrogation de l'index prend du temps ; ( 3) Maintenance : les index doivent être maintenus (lorsque les données changent)

Il n'est pas recommandé d'utiliser des index :

(1) Tables avec un petit volume de données

(2) L'espace est restreint

Résumé des optimisations couramment utilisées

Il existe de nombreuses déclarations d'optimisation et de nombreuses choses auxquelles il faut prêter attention. Voici quelques points basés sur l'habituel. situation :

1. Il existe un index mais il n'est pas utilisé (non recommandé)

(1) Lorsque le paramètre Like commence par un caractère générique

Essayez de. évitez le paramètre Like commençant par un caractère générique, sinon le moteur de base de données abandonnera l'utilisation de l'index et effectuera une analyse complète de la table.

Instructions SQL commençant par des caractères génériques, par exemple : sélectionnez * depuis t_credit_detail où Flistid comme '%0'G

C'est L'analyse complète de la table n'utilise pas d'index et n'est pas recommandée.

Instructions SQL qui ne commencent pas par des caractères génériques, par exemple : sélectionnez * depuis t_credit_detail où Flistid comme '2%'G

Très évidemment, cela utilise un index, qui est une recherche étendue, et est beaucoup plus efficace que les instructions SQL commençant par des caractères génériques.

(2) Lorsque la condition Where n'est pas conforme au principe du préfixe le plus à gauche

Des exemples ont été donnés dans le contenu du principe de correspondance du préfixe le plus à gauche.

(3) Utilisez ! Essayez d'éviter d'utiliser

lorsque vous utilisez les opérateurs = ou = ou , sinon le moteur de base de données abandonnera l'utilisation de l'index et effectuera une analyse complète de la table. Il est plus efficace d’utiliser > ou <.>

sélectionnez * depuis t_credit_detail où Flistid != '2000000608201108010831508721'G

(4) Les colonnes d'index participent au calcul

Vous devriez essayer d'éviter d'effectuer des opérations d'expression sur les champs de la clause Where, ce qui entraînerait l'abandon du moteur par l'utilisation de l'index et une analyse complète de la table.

sélectionnez * depuis t_credit_detail où Flistid +1 > '2000000608201108010831508722'G

(5) Effectuer un jugement de valeur nulle sur les champs

Essayez d'éviter de porter des jugements de valeur nuls sur les champs de la clause Where, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table, telle que : Inefficacité : sélectionnez * from t_credit_detail où Flistid est nul ;

Vous pouvez définir la valeur par défaut 0 sur Flistid, vous assurer qu'il n'y a pas de valeur nulle dans la colonne Flistid du tableau, puis interroger comme ceci : Efficace : sélectionnez * from t_credit_detail où Flistid =0 ;

(6) Utiliser ou pour connecter les conditions

devrait essayer d'éviter d'utiliser ou dans la clause Where pour connecter les conditions, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table. , tels que : Inefficacité : sélectionnez * dans t_credit_detail où Flistid = '2000000608201108010831508721' ou Flistid = '10000200001' ;

Vous pouvez utiliser la requête suivante pour remplacer la requête ci-dessus : Efficace : sélectionnez

from t_credit_detail où Flistid = '2000000608201108010831508721' union all select

from t_credit_detail où Flistid = '10000200001';

2. Évitez de sélectionner *

Pendant le processus d'analyse, '*' sera converti à tour de rôle en tous les noms de colonnes. se fait en interrogeant le dictionnaire de données, ce qui signifie que cela prendra plus de temps.

Vous devez donc développer une bonne habitude de prendre tout ce dont vous avez besoin.

3. Optimisation de l'instruction Order by

Tous les éléments non indexés ou expressions de calcul dans l'instruction Order by ralentiront la vitesse de la requête.

Méthode : 1. Réécrire l'instruction order by pour utiliser l'index

  2.为所使用的列建立另外一个索引

  3.绝对避免在order by子句中使用表达式。

4 Optimisation de l'instruction GROUP BY

Améliorer l'efficacité de l'instruction GROUP BY par. ajout Les enregistrements inutiles sont filtrés avant GROUP BY

Inefficacité :

SELECT JOB, AVG(SAL)

FROM EMP

GROUP by JOB

AVOIR JOB = 'PRESIDENT'

OU JOB = 'MANAGER'

Efficace :

SÉLECTIONNER JOB, AVG(SAL)

DEPUIS EMP

OÙ JOB = 'PRESIDENT'

OU JOB = 'MANAGER'

GROUPE par JOB

5. L'utilisation existe à la place de dans

Souvent, c'est un bon choix d'utiliser exist au lieu de in : sélectionnez num à partir de a où num in (sélectionnez num à partir de b) Remplacez par l'instruction suivante : sélectionnez num à partir de a où existe (sélectionnez 1 à partir de b où num = a .num)

6. Utilisez varchar/nvarchar au lieu de char/nchar

Utilisez autant que possible varchar/nvarchar au lieu de char/nchar, car d'abord, l'espace de stockage des champs de longueur variable. est petit, ce qui permet d'économiser de l'espace de stockage. Deuxièmement, pour les requêtes, la recherche dans un champ relativement petit est évidemment plus efficace.

7. Si vous pouvez utiliser DISTINCT, vous n'avez pas besoin de GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

peut être modifié en :

SELECT DISTINCT OrderID FROM Détails WHERE UnitPrice > 10

8 Si vous pouvez utiliser UNION ALL, n'utilisez pas UNION

UNION ALL n'exécute pas SELECT DISTINCT. fonction, ce qui réduira beaucoup de ressources inutiles.

9. Utilisez des types d'exemples équivalents lors de la jointure de tables et indexez-les

Si l'application a de nombreuses requêtes JOIN, vous devez confirmer que les champs de jointure dans les deux tables sont créés indexés. De cette façon, MySQL lancera un mécanisme pour optimiser l'instruction Join SQL pour vous.

De plus, les champs utilisés pour Join doivent être du même type. Par exemple : si vous joignez un champ DECIMAL à un champ INT, MySQL ne peut pas utiliser leurs index. Pour ces types STRING, ils doivent également avoir le même jeu de caractères. (Les jeux de caractères des deux tables peuvent être différents)

Cet article se termine ici Pour plus de connaissances sur MySQL, vous pouvez faire attention à la colonne Tutoriel MySQL du site php chinois ! ! !

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!