Fonction d'indexation de la série MySQL (5)

Les index sont des fichiers spéciaux (les index de la table de données InnoDB sont un composant de l'espace table). Ils contiennent des pointeurs de référence vers tous les enregistrements de la table de données. Les index ne sont pas une panacée. Les index peuvent accélérer les opérations de récupération de données, mais ils peuvent ralentir les opérations de modification des données. Chaque fois qu'un enregistrement de données est modifié, l'index doit être actualisé. Pour compenser dans une certaine mesure cette lacune, de nombreuses commandes SQL ont une entrée DELAY_KEY_WRITE. La fonction de cette option est d'empêcher temporairement MySQL de rafraîchir l'index immédiatement après que chaque nouvel enregistrement soit inséré et que chaque enregistrement existant soit modifié par cette commande. L'actualisation de l'index attendra que tous les enregistrements aient été insérés/modifiés. Le rôle de l'option DELAY_KEY_WRITE sera très évident lorsque de nombreux nouveaux enregistrements devront être insérés dans une table de données. De plus, les index occupent un espace considérable sur le disque dur. Par conséquent, seules les colonnes de données les plus fréquemment interrogées et les plus fréquemment triées doivent être indexées. Notez que si une colonne de données contient beaucoup de contenu en double, son indexation n’aura pas beaucoup d’effet pratique.

Théoriquement, il est possible de construire un index pour chaque champ de la table de données, mais MySQL limite le nombre total d'index dans la même table de données à 16.

1． Index des tables de données InnoDB

Par rapport aux tables de données InnoDB, les index sur les tables de données InnoDB sont beaucoup plus importants. Sur les tables de données InnoDB, les index jouent non seulement un rôle dans la recherche d'enregistrements de données, mais constituent également la base du mécanisme de verrouillage au niveau des lignes de données. Le « verrouillage au niveau des lignes de données » signifie le verrouillage des enregistrements individuels en cours de traitement pendant l'exécution des opérations de transaction, empêchant ainsi les autres utilisateurs d'y accéder. Ce verrouillage affecte (mais sans s'y limiter) les commandes SELECT, LOCKINSHAREMODE, SELECT, FORUPDATE et INSERT, UPDATE et DELETE. Pour des raisons d'efficacité, le verrouillage au niveau des lignes des tables InnoDB se produit en réalité sur leurs index, et non sur la table elle-même. De toute évidence, le mécanisme de verrouillage au niveau des lignes de données ne peut être efficace que lorsque la table de données concernée possède un index approprié pour le verrouillage.

2． Restrictions

S'il y a un signe d'inégalité (WHERE coloum !=) dans la condition de requête de la clause WHERE, MySQL ne pourra pas utiliser l'index. De même, si la fonction (WHERE DAY (column) =) est utilisée dans la condition de requête de la clause WHERE, MySQL ne pourra pas utiliser l'index. Dans une opération JOIN (lorsque les données doivent être extraites de plusieurs tables de données), MySQL ne peut utiliser des index que lorsque le type de données de la clé primaire et de la clé étrangère est le même.

Si les opérateurs de comparaison LIKE et REGEXP sont utilisés dans la condition de requête de la clause WHERE, 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 requête est LIKE 'abc%', MySQL utilisera l'index ; si la condition de requête est LIKE '�c', MySQL n'utilisera pas l'index.

Dans une opération ORDER BY, MySQL n'utilise l'index que si la condition de tri n'est pas une expression de condition de requête. (Cependant, dans les requêtes impliquant plusieurs tables de données, même si des index sont disponibles, ces index auront peu d'effet sur l'accélération de ORDER BY). Si une colonne de données contient de nombreuses valeurs en double, même si elle est indexée, elle n'obtiendra pas de bons résultats. Par exemple, si une colonne de données contient uniquement des valeurs telles que « 0/1 » ou « O/N », il n'est pas nécessaire de créer un index pour celle-ci.

Catégorie d'index

1. Index ordinaire

La seule tâche d'un index ordinaire (un index défini par le mot-clé KEY ou INDEX) est d'accélérer l'accès aux données. Par conséquent, les index ne doivent être créés que pour les colonnes de données qui apparaissent le plus fréquemment dans les conditions de requête (colonne WHERE =) ou les conditions de tri (colonne ORDER BY). Dans la mesure du possible, vous devez choisir une colonne de données contenant les données les plus soignées et les plus compactes (telles qu'une colonne de données de type entier) pour créer un index.

2． Index unique

L'index ordinaire permet à la colonne de données indexée de contenir des valeurs en double. Par exemple, étant donné que les personnes peuvent avoir le même nom, le même nom peut apparaître deux fois ou plus dans la même table de données « Profil de l'employé ».

Si vous pouvez être sûr qu'une certaine colonne de données ne contiendra que des valeurs différentes les unes des autres, vous devez utiliser le mot-clé UNIQUE pour la définir comme un index unique lors de la création d'un index pour cette colonne de données. . Les avantages de ceci sont les suivants : premièrement, cela simplifie la gestion de cet index par MySQL, et l'index devient plus efficace ; deuxièmement, MySQL vérifiera automatiquement la valeur de ce champ dans le nouvel enregistrement lorsqu'un nouvel enregistrement est inséré dans la table de données. ce champ est déjà apparu dans un enregistrement ; si c'est le cas, MySQL refusera d'insérer le nouvel enregistrement. En d’autres termes, un index unique peut garantir l’unicité des enregistrements de données. En fait, dans de nombreux cas, le but de la création d’index uniques n’est pas d’augmenter la vitesse d’accès, mais simplement d’éviter la duplication des données.

3. Index primaire

Cela a été souligné à plusieurs reprises auparavant : un index doit être créé pour le champ de clé primaire. Cet index est ce qu'on appelle « l'index primaire ». La seule différence entre un index primaire et un index unique est que le premier est défini à l'aide du mot-clé 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 l'aider à gérer et à utiliser les contraintes de clé étrangère de la manière la plus efficace.

5． L'index composite

peut couvrir plusieurs colonnes de données, telles que l'index INDEX (colonneA, colonneB). La particularité de ce type d'index est que MySQL peut utiliser un tel index de manière sélective. Si l'opération de requête nécessite uniquement un index sur la colonne de données columnA, vous pouvez utiliser l'index composite INDEX(columnA, columnB). Toutefois, cette utilisation s'applique uniquement à la combinaison de colonnes de données classées en premier dans l'index composite. Par exemple, INDEX (A, B, C) peut être utilisé comme index dans A ou (A, B), mais pas comme index dans B, C ou (B, C).

Longueur de l'index

Lors de la définition des index pour les colonnes de données de type CHAR et VARCHAR, vous pouvez limiter la longueur de l'index à un nombre donné de caractères (ce nombre doit être inférieur au nombre maximum de caractères autorisé pour ce champ). L’avantage de ceci est qu’il peut générer un fichier d’index de plus petite taille et plus rapide en termes de vitesse de récupération. Dans la plupart des applications, les données de chaîne dans la base de données sont principalement basées sur divers noms. Définir la longueur de l'index sur 10 à 15 caractères suffit pour limiter la portée de la recherche à quelques enregistrements de données. Lors de la création d'index pour les colonnes de données de type BLOB et TEXT, des restrictions doivent être placées sur la longueur de l'index ; l'index maximum autorisé par MySQL est un index normal sur un champ de texte d'index de texte intégral qui ne peut qu'accélérer la chaîne qui apparaît à le début du contenu du champ (c'est-à-dire les caractères au début du contenu du champ) pour effectuer l'opération de recherche. Si le champ stocke un texte plus volumineux composé de plusieurs, voire de plusieurs mots, les index ordinaires seront de peu d'utilité. Cette récupération se présente souvent sous la forme de , ce qui est complexe pour MySQL et peut entraîner des temps de réponse longs si la quantité de données à traiter est importante.

C'est dans ce type de situation que l'index de texte intégral (full-textindex) peut montrer ses talents. Lors de la génération de ce type d'index, MySQL créera une liste de tous les mots qui apparaissent dans le texte, et les opérations de requête utiliseront cette liste pour récupérer les enregistrements de données pertinents. L'index de texte intégral peut être créé avec la table de données, ou vous pouvez utiliser la commande suivante pour l'ajouter si nécessaire à l'avenir :

ALTER TABLE tablename ADD FULLTEXT (column1, column2) Avec le full- index de texte, vous pouvez utiliser les commandes SELECT Query pour récupérer des enregistrements de données contenant un ou plusieurs mots donnés. Voici la syntaxe de base de ce type de commande de requête :

SELECT * FROM tablename

WHERE MATCH (column1,column2) AGAINST('word1','word2','word3')

La commande ci-dessus interrogera tous les enregistrements de données avec mot1, mot2 et mot3 dans les champs colonne1 et colonne2.

Remarque : les tables de données InnoDB ne prennent pas en charge l'indexation en texte intégral.

Requête et index

Seulement lorsqu'il y a suffisamment de données de test dans la base de données, ses résultats de tests de performances auront une valeur de référence réelle. S'il n'y a que quelques centaines d'enregistrements de données dans la base de données de test, ils sont souvent tous chargés en mémoire après l'exécution de la première commande de requête, ce qui permettra aux commandes de requête suivantes d'être exécutées très rapidement, que les index soient utilisés ou non. Les résultats des tests de performances de la base de données ne sont significatifs que lorsque le nombre d'enregistrements dans la base de données dépasse 1 000 et que la quantité totale de données dépasse la mémoire totale du serveur MySQL.

Lorsque les gens ne savent pas sur quelles colonnes de données doivent être indexées, ils peuvent souvent obtenir de l'aide grâce à la commande EXPLAIN SELECT. Il s'agit en fait simplement de préfixer une commande SELECT ordinaire avec le mot-clé EXPLAIN. Avec ce mot-clé, MySQL n'exécutera pas la commande SELECT, mais l'analysera. MySQL répertoriera le processus d'exécution des requêtes et les index utilisés dans une table.

Dans la sortie de la commande EXPLAIN, la colonne 1 est le nom de la table de données lue dans la base de données, et elles sont classées dans l'ordre dans lequel elles sont lues. La colonne type spécifie la relation (JOIN) entre cette table de données et d'autres tables de données. Parmi les différents types de relations, la plus efficace est système, suivie de const, eq_ref, ref, range, index et All (All signifie : correspondant à chaque enregistrement de la table de données de niveau supérieur, ces données Tous les enregistrements de la table doivent être lu une seule fois - cela peut souvent être évité avec un index).

La colonne de données possible_keys donne les différents index que MySQL peut utiliser lors de la recherche d'enregistrements de données. La colonne de données clés est l'index réellement sélectionné par MySQL. La longueur de cet index en octets est donnée dans la colonne de données key_len. Par exemple, pour un index sur une colonne de données INTEGER, la longueur en octets serait de 4. Si un index composite est utilisé, vous pouvez également voir quelles parties de celui-ci sont utilisées par MySQL dans la colonne de données key_len. En règle générale, plus la valeur de la colonne de données key_len est petite, mieux c'est.

ref data column donne le nom de la colonne de données dans une autre table de données de la relation. La colonne de données de ligne correspond au nombre de lignes de données que MySQL s'attend à lire dans cette table de données lors de l'exécution de cette requête. Le produit de tous les nombres de la colonne de données de ligne vous donne une idée du nombre de combinaisons que la requête doit traiter.

Enfin, la colonne de données supplémentaires 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 les mots usingtemporary dans la colonne supplémentaire.

Ce qui précède est le contenu de la fonction d'indexation de la série Mysql (5). Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !