Méthode d'optimisation de l'index MySQL

Programmation des méditations                                                                                                 Cela ne se limite pas au partage de technologies dans le domaine du Web Java. J'espère que ce compte public pourra devenir un bon mentor et un ami utile sur. votre parcours de programmation.

                                                

Cet article partage principalement avec vous les méthodes d'optimisation des index Mysql, en espérant aider tout le monde. Un index est une structure de données utilisée par les moteurs de stockage pour rechercher rapidement des enregistrements. Surtout lorsque la quantité de données dans la table devient de plus en plus grande, des index corrects amélioreront considérablement les performances des requêtes. Mais dans le travail quotidien, l’indexation est souvent ignorée, voire mal comprise. Cet article présentera brièvement les principes et les précautions de l'optimisation des index Mysql.

1. Types d'index

1) Index B-Tree

L'index B-Tree est le type d'index le plus couramment utilisé, et la plupart des moteurs de stockage prennent en charge l'index B-Tree. .

B-Tree lui-même est une structure de données, qui est un arbre de recherche équilibré conçu pour les disques ou autres périphériques auxiliaires à accès direct. L'index B-Tree dans Mysql est généralement implémenté par B+Tree, une variante de B-Tree. Sa structure est la suivante :

La caractéristique de B+Tree est que les données sont stockées dans des nœuds feuilles, et les données de chaque nœud feuille sont dans le même ordre ( ordre croissant ou décroissant) Il est stocké dans un tableau et les nœuds feuilles adjacents sont connectés en un point avec des pointeurs. Cette structure est très adaptée aux recherches de plage.

L'index B-Tree peut accélérer considérablement l'accès aux données, car le moteur de stockage n'a plus besoin d'effectuer une analyse complète de la table pour obtenir les données requises, mais recherche couche par couche à partir du nœud racine de l'index, ce qui considérablement La portée des données analysées par le moteur de stockage est réduite, de sorte que la vitesse des requêtes est considérablement améliorée.

2) Index de hachage

L'index de hachage, comme son nom l'indique, est un index implémenté via une table de hachage. Sa particularité est qu'il n'est valable que s'il correspond exactement à toutes les colonnes de l'index. Pour chaque ligne de données, le moteur de stockage calcule un code de hachage pour toutes les colonnes d'index. L'index de hachage stocke le code de hachage dans l'index et enregistre un pointeur vers chaque ligne de données dans la table de hachage.

Dans Mysql, actuellement, seul le moteur de mémoire prend explicitement en charge l'index de hachage, et comme l'index de hachage ne prend pas en charge la recherche par plage, le tri ou la recherche de correspondance de colonne d'index partiel, l'index de hachage est rarement utilisé.

Ce qui suit se concentrera sur l'utilisation de l'index B-Tree.

Pour la commodité de la description suivante, nous supposerons qu'il existe une table utilisateur avec les champs suivants :

id : type bigint, clé primaire

nom : varchar tapez

age: int tapez

interest: varchar tapez

et créez un index conjoint index_1 sur le nom, l'âge, l'intérêt, le l'ordre d'index est (nom, âge,intérêt), cet ordre d'index est très important et sera mentionné plus tard.

2. Utilisation de l'index B-Tree

1) Correspondance de valeur complète

La correspondance de valeur complète fait référence à la correspondance avec toutes les colonnes de l'index, telles que The requête de table utilisateur ci-dessus où name='aaa' et age=20 et interest='basketball' peuvent utiliser toutes les colonnes de l'index.

2) Faire correspondre le préfixe le plus à gauche

Faire correspondre le préfixe le plus à gauche signifie utiliser uniquement les quelques colonnes de gauche de l'index multi-colonnes. Par exemple, pour la requête de table utilisateur ci-dessus où name = 'aaa', l'index peut être utilisé et seule la première colonne de l'index est utilisée.

3) Préfixe de colonne correspondant

Le préfixe de colonne correspondant signifie correspondre uniquement au début d'une certaine colonne. Par exemple, si vous interrogez la table utilisateur ci-dessus dont le nom est comme 'aaa%', vous pouvez. utilisez l'index. Notez qu'il correspond au début de la colonne. Si la requête porte un nom tel que '%aaa', l'index ne peut pas être utilisé.

4) Valeur de plage correspondante

Par exemple, si vous interrogez la table utilisateur ci-dessus où nom > 'aaa' et nom <

5) Correspond exactement à une certaine colonne et plage correspond à une autre colonne

Si vous interrogez la table utilisateur ci-dessus où name='aaa' et age >10, vous pouvez utiliser l'index et utiliser les 2 premières colonnes de l'index.

3. Limitations de l'index B-Tree

1) Si la recherche n'est pas lancée à partir de la colonne la plus à gauche de l'index, l'index ne peut pas être utilisé.

Si vous interrogez la table utilisateur ci-dessus où age=20, l'index ne peut pas être utilisé car l'âge n'est pas la colonne de données la plus à gauche de la colonne d'index.

2) Les colonnes de l'index ne peuvent pas être ignorées.

Si vous interrogez Where name='aaa' et interest='football' pour la table utilisateur ci-dessus, vous ne pouvez utiliser que la première colonne de l'index car la condition Where n'inclut pas âge.

Recommandations associées :

Comment utiliser l'optimisation de l'index MySQL

MySQL Advanced Thirteen— — Optimisation de SQL par index

Méthode d'optimisation de commande MySQL par index

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!