Explication détaillée des types d'index Mysql et de leurs avantages et inconvénients

IndexC'est un fichier spécial (les index des tables de données InnoDB font partie d'un espace de données), ils contiennent des pointeurs de référence vers tous les enregistrements de la table.
Remarque :
[1]L'index n'est pas omnipotent ! Les index peuvent accélérer les opérations de recherche de données, mais ralentissent les opérations de modification des données. Chaque modification d'enregistrement de données nécessite une mise à jour de l'index. Pour compenser quelque peu ce défaut, de nombreux commandes SQL ont un élément DELAY_KEY_WRITE. Cette option permet temporairement d'arrêter MySQL de mettre à jour l'index immédiatement après chaque insertion d'un nouveau enregistrement et chaque modification d'un enregistrement existant, et la mise à jour de l'index sera reportée jusqu'à ce que tous les enregistrements soient insérés/Effectuez la modification après cela. Dans les cas où il est nécessaire d'insérer de nombreux nouveaux enregistrements dans une table de données, l'option DELAY_KEY_WRITE joue un rôle très important.
[2De plus, les index occupent une grande quantité d'espace sur le disque. Par conséquent, seuls les colonnes fréquemment consultées et fréquemment triées devraient être indexées. Notez que si une colonne contient beaucoup de contenu répété, la création d'un index pour elle n'a pas d'effet réel.
Théoriquement, il est tout à fait possible de créer un index distinct pour chaque champ dans la table de données, mais MySQL limite le nombre total d'indices dans la même table à16un.

1. Index des tables de données InnoDB

Comparé aux tables de données MyISAM, l'index est beaucoup plus important pour les tables de données InnoDB. Sur les tables de données InnoDB, l'index est beaucoup plus important que sur les tables de données InnoDB. L'index joue non seulement un rôle dans la recherche des enregistrements de données, mais est également la base et l'essence du mécanisme de verrouillage au niveau des lignes de données. Le verrouillage au niveau des lignes de données signifie verrouiller les enregistrements individuels en cours de traitement pendant l'exécution de l'opération transactionnelle, afin qu'aucun autre utilisateur ne puisse y accéder. Ce verrouillage affecte (mais n'est pas limité à) les commandes SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE, et les commandes INSERT, UPDATE et DELETE.
Pour des raisons d'efficacité, le verrouillage au niveau des lignes des tables de données InnoDB se produit réellement sur leurs index,而非 sur la table elle-même. Il est évident que le mécanisme de verrouillage au niveau des lignes de données ne peut fonctionner efficacement que lorsque la table concernée dispose d'un index approprié pour le verrouillage.

2. Limite

Si la clause WHERE contient un inégalité (WHERE column != ...), MySQL ne peut pas utiliser l'index.
De même, si la clause WHERE utilise une fonction (WHERE DAY(column) = …), MySQL ne pourra pas utiliser l'index.
De même, dans l'opération JOIN (nécessitant deExtraire des données de plusieurs tables), MySQL ne peut utiliser l'index que lorsque les types de données de la clé principale et de la clé étrangère sont identiques.
Si la clause WHERE utilise des opérateurs de comparaison LIKE et REGEXP, MySQL ne peut utiliser l'index que si le premier caractère du modèle de recherche n'est pas un caractère générique. Par exemple, si la condition de recherche est LIKE 'abc%', MySQL utilisera l'index ; si la condition de recherche est LIKE '%abc', MySQL ne utilisera pas l'index.
Dans l'opération ORDER BY, MySQL n'utilise l'index que lorsque la condition de tri n'est pas une expression de condition de requête. (Cependant, dans les requêtes impliquant plusieurs tables, même si des indexes sont disponibles, ceux-ci n'ont pas grand effet sur l'accélération de l'ORDER BY).
Si une colonne de données contient de nombreuses valeurs répétées, même si elle est indexée, l'effet ne sera pas bon. Par exemple, si une colonne de données contient uniquement des valeurs telles que '0',/1ou 'Y'/N'aura pas besoin de créer un index pour une valeur égale à 'N'.　

Index normal, index unique et index principal

1Index normal

L'unique index (défini par la clé KEY ou INDEX) a pour unique tâche d'accélérer l'accès aux données. Par conséquent, il ne faut créer des indexes que pour les colonnes les plus souvent utilisées dans les conditions de requête (WHERE column = …) ou les conditions de tri (ORDER BY column). Si possible, il est préférable de choisir une colonne de données la plus ordonnée et la plus compacte (comme une colonne de type entier) pour créer des indexes.

2Index unique
L'index normal permet à la colonne de données indexée de contenir des valeurs répétées. Par exemple, en raison de la possibilité d'avoir des noms identiques, un même nom peut apparaître deux fois ou plus dans la table de données des informations personnelles des employés.
Si l'on peut déterminer qu'une colonne de données ne contiendra que des valeurs différentes, il faut utiliser la clé UNIQUE pour la définir comme un index unique lors de la création de cet index. Les avantages de cette méthode : d'une part, elle simplifie la gestion de cet index par MySQL, ce qui le rend plus efficace ; d'autre part, MySQL vérifie automatiquement si la valeur de ce champ dans un nouveau enregistrement existe déjà dans un champ de cet enregistrement ; si c'est le cas, MySQL refuse d'insérer cet enregistrement. Autrement dit, l'index unique garantit l'unicité des enregistrements. En réalité, dans de nombreux cas, le but de la création d'un index unique n'est pas d'accélérer l'accès aux données, mais plutôt d'éviter les répétitions de données.

3. Index primaire

Nous avons déjà souligné à plusieurs reprises : il est nécessaire de créer un index pour le champ primaire, c'est ce que l'on appelle l'"index primaire". La seule différence entre l'index primaire et l'index unique est que la clé utilisée lors de la définition de l'index primaire est PRIMARY au lieu de UNIQUE.

4. Index de clé étrangère

Si une contrainte de clé étrangère est définie pour un champ de clé étrangère, MySQL définira un index interne pour aider à gérer et utiliser la contrainte de clé étrangère de manière la plus efficace.

5. Index composite

Les index peuvent couvrir plusieurs colonnes de données, comme l'index INDEX(columnA, columnB). Une caractéristique de cet index est que MySQL peut choisir d'utiliser un tel index. Si l'opération de requête n'a besoin que de l'index d'une colonne columnA, on peut utiliser l'index composé INDEX(columnA, columnB). Cependant, cette utilisation n'est applicable qu'aux combinaisons de colonnes répertoriées en premier dans l'index composite. Par exemple, INDEX(A, B, C) peut être utilisé comme index pour A ou (A, B), mais pas pour B, C ou (B, C).

6. Longueur de l'index

Lors de la définition d'index pour des colonnes de type CHAR et VARCHAR, il est possible de limiter la longueur de l'index à un nombre de caractères donné (ce nombre doit être inférieur au nombre maximum de caractères autorisé par ce champ). L'avantage de cette méthode est de générer un fichier d'index de petite taille mais rapide en termes de vitesse de recherche. Dans la plupart des applications, les données de chaîne de caractères dans la base de données sont principalement des noms de divers types, et la longueur de l'index peut être réglée sur10~15caractères, ce qui est suffisant pour réduire la portée de la recherche à un très petit nombre d'enregistrements de données.
Lors de la création d'index pour des colonnes de type BLOB et TEXT, il est nécessaire de limiter la longueur de l'index; la longueur maximale d'index autorisée par MySQL est255caractères.
Index de texte complet

　　 Les index normaux sur les champs de texte ne peuvent accélérer que la recherche des chaînes de caractères apparaissant en premier dans le contenu du champ (c'est-à-dire le premier caractère du contenu du champ). Si le champ contient un texte plus long composé de plusieurs mots, ou même de plusieurs mots, les index normaux n'ont pas d'effet. Cette recherche se présente souvent sous la forme LIKE %word%, ce qui est complexe pour MySQL, et si la quantité de données à traiter est grande, le temps de réponse sera long.
Ces cas sont précisément les occasions où l'index de texte complet (full)-text index) peut être très utile. Lors de la création de ce type d'index, MySQL créera une liste de tous les mots apparaissant dans le texte, et les opérations de consultation utiliseront cette liste pour rechercher les enregistrements de données pertinents. Les index de texte complet peuvent être créés avec la table de données ou utilisés à une date ultérieure si nécessaire

La commande suivante ajoutera :
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

Avec l'index de texte complet, il est possible d'utiliser la commande SELECT pour rechercher les enregistrements de données contenant un ou plusieurs mots donnés. Voici la syntaxe de base de ces commandes de consultation :
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

La commande suivante mettra à jour column1et column2le champ contient word1、word2et word3Tous les enregistrements de données avec

Remarque：Les tables InnoDB ne supportent pas les index de texte complet.

L'optimisation des requêtes et des index

　　　　　 Seulement lorsque la base de données a suffisamment de données de test, les résultats du test de performance ont une valeur de référence réelle. Si dans la base de test de données il n'y a que quelques centaines d'enregistrements de données, ils sont souvent chargés dans la mémoire après l'exécution de la première commande de consultation, ce qui rend les commandes de consultation suivantes très rapides - peu importe s'ils utilisent des index ou non. Seulement lorsque les enregistrements de la base de données dépassent1000, et la quantité totale de données dépasse également la quantité totale de mémoire du serveur MySQL, les résultats du test de performance de la base de données ont un sens.

　　　　　 Lorsqu'il est incertain sur哪些数据列上应创建索引时，人们往往可以从EXPLAIN SELECT命令中获得一些帮助。这实际上只是给一条普通的SELECT命令加上一个EXPLAIN关键字作为前缀而已。有了这个关键字，MySQL将不会执行那条SELECT命令，而是对其进行分析。MySQL将以表格的形式列出查询的执行过程和使用的索引（如果有）等信息。
Dans les résultats de la commande EXPLAIN, le1La colonne est le nom de la table de données lue à partir de la base de données, elles sont classées dans l'ordre de lecture. La colonne type spécifie la relation de liaison (JOIN) entre cette table de données et d'autres tables de données. Parmi les différents types de relations de liaison, la plus efficace est systeme, suivie de const, eq_ref, ref, range, index et All (All signifie : pour chaque enregistrement de la table de données de niveau supérieur, tous les enregistrements de cette table doivent être lus - cette situation peut souvent être évitée par un index).

La colonne possible_keys donne les différents index que MySQL peut utiliser lors de la recherche de enregistrements de données. La colonne key est l'index réel utilisé par MySQL, et la longueur en octets de cet index est indiquée dans la colonne key_len. Par exemple, pour un index sur une colonne INTEGER, cette longueur en octets sera4Si une index composite est utilisé, vous pouvez voir dans la colonne key_len哪些部分MySQL具体使用了。一般来说，key_len列中的值越小越好（意思是越快）。
La colonne ref donne le nom de la colonne dans une autre table de la relation de liaison. La colonne row est le nombre de lignes que MySQL prévoit de lire à partir de cette table lors de l'exécution de cette requête. Le produit de tous les nombres dans la colonne row nous permet de comprendre approximativement le nombre de combinaisons que cette requête doit traiter.
Enfin, la colonne extra fournit plus d'informations sur l'opération JOIN. Par exemple, si MySQL doit créer une table de données temporaire lors de l'exécution de cette requête, vous verrez l'expression using temporary dans la colonne extra.

C'est tout le contenu de cet article. J'espère que cela aidera à votre apprentissage et que vous soutiendrez davantage le tutoriel d'alarme.

Déclaration : le contenu de cet article est issu du réseau, les droits d'auteur appartiennent aux auteurs, le contenu est apporté par les utilisateurs d'Internet et téléversé spontanément. Ce site ne détient pas de droits de propriété, n'a pas été édité par l'homme, et n'assume aucune responsabilité juridique connexe. Si vous trouvez du contenu suspect de violation de droits d'auteur, veuillez envoyer un e-mail à : notice#oldtoolbag.com (veuillez remplacer # par @ lors de l'envoi d'un e-mail pour signaler une violation, et fournir des preuves pertinentes. Une fois vérifié, ce site supprimera immédiatement le contenu suspect de violation de droits d'auteur.)