Qu'est-ce que l'index MySQL et comment l'utiliser ? Organisé dans les moindres détails

Lorsque vous apprenez MySQL, vous rencontrez souvent des index. Qu'est-ce qu'un index ? Avant, je ne savais que vaguement comment l'utiliser, mais c'était un peu difficile pour moi de l'expliquer, alors j'ai profité de mon temps libre pour lire quelques livres, juste au cas où quelqu'un me le demanderait à l'avenir, j'ai fait le tri et Je l'ai écrit, ce qui m'a donné quelques impressions. C'est bien après tout, et c'est assez gênant de dire non. Après tout, j'y suis exposé depuis quelques années. Ne disons pas de bêtises. apache php mysql :

commencez !

1. Qu'est-ce qu'un index

1. Introduction à index
Index est en fait une structure de données stockée sur le disque sous la forme d'un fichier. Des opérations d'E/S sur disque de récupération d'index sont nécessaires. Contrairement à la mémoire principale, les E/S disque impliquent des coûts de déplacement mécanique, de sorte que la consommation de temps des E/S disque est énorme.

2. Introduction à IO
IO fait référence aux entrées et sorties dans les ordinateurs. Étant donné que les programmes et les données d'exécution résident en mémoire, ils sont exécutés par le cœur de calcul ultra-rapide du processeur, ce qui implique un échange de données. . Les emplacements, généralement des disques, des réseaux, etc., nécessitent des interfaces IO. Exemple de vie : toutes les choses clés dont il faut se souvenir doivent être écrites dans un cahier. Sortez-les et lisez-les si nécessaire. Chaque fois que vous allez dans le cahier pour lire les enregistrements, c'est IO si les personnes ayant une bonne mémoire le font. rappelez-vous cette question, ils peuvent le faire directement. Lisez-le, c'est le cache (il ne peut pas être enregistré tout le temps dans l'ordinateur).

2. Algorithme d'index

1 La base de données est essentiellement implémentée à l'aide de l'algorithme B+Tree

2 L'index de la base de données utilise le disque I/. O Le nombre de fois pour évaluer la qualité de la structure d'index

3.B-Tree
(1) La définition de B-Tree montre qu'il faut accéder à un maximum de nœuds h-1 pour une récupération (le nœud racine réside en mémoire). Le concepteur du système de base de données a intelligemment tiré parti du principe de lecture anticipée du disque et a défini la taille d'un nœud pour qu'elle soit égale à une page, de sorte que chaque nœud n'ait besoin que d'une seule E/S pour être complètement chargé

(2) La mise en œuvre réelle B-Tree doit également utiliser les compétences suivantes : chaque fois qu'un nouveau nœud est créé, il s'applique directement à une page d'espace. Cela garantit qu'un nœud est physiquement stocké dans une page. L'allocation de stockage est alignée sur les pages, et ceci est réalisé. Un nœud n'a besoin que d'une seule E/S

(3) En utilisant la structure de stockage B-Tree, le nombre d'E/S pendant la recherche ne dépasse généralement pas 3 fois. , donc utiliser B-Tree comme structure d'index est très efficace, mais les nœuds de B-tree peuvent contenir une grande quantité d'informations sur les mots clés et les branches en fonction de la situation réelle

4.B+Tree(1) La complexité de recherche de B-Tree est O(h)= O(logdN), donc plus le degré extérieur d de l'arbre est grand, plus la profondeur h est petite et moins le nombre d'E/S. B+Tree peut exactement augmenter la largeur du degré extérieur d, car la taille de chaque nœud est la taille d'une page, donc la limite supérieure du degré extérieur dépend de la taille de la clé et des données dans le nœud

(2) Depuis B+Tree Les données sont supprimées du nœud interne, elles peuvent donc avoir un degré de sortie plus grand et ainsi avoir de meilleures performances

3. 🎜>1. Index de clustering

(1) L'ordre de stockage physique des données d'index clusterisé est cohérent avec l'ordre des index, c'est-à-dire : tant que les index sont adjacents, les données correspondantes doivent également être stockés à côté sur le disque. Les index clusterisés sont beaucoup plus efficaces que les requêtes d'index non clusterisés

(3) Chaque table ne peut avoir qu'un seul index clusterisé, car les enregistrements d'une table ne peuvent être stockés que dans un seul ordre physique

( 4) Index par défaut d'Innodb

2. Index non clusterisé

(1) Index non clusterisé, semblable à l'annexe d'un livre, dans quel chapitre apparaît le terme professionnel, ces La technique les termes sont en règle, mais la position où ils apparaissent ne l’est pas. Cependant, une table peut avoir plus d'un index non clusterisé

(2) Le principe de mise en œuvre est d'utiliser des nœuds feuilles pour stocker les clés primaires des lignes de référence (on peut dire qu'il s'agit d'index clusterisés)

(3) Index clusterisé Il s'agit d'un index d'index non clusterisé, c'est-à-dire la méthode d'indexation d'index primaire + secondaire. L'avantage de cet index primaire + secondaire est que lorsqu'un mouvement de ligne de données ou une division de page se produit, l'arborescence d'index auxiliaire n'a pas besoin d'être mise à jour car l'arborescence d'index auxiliaire stocke le mot-clé clé primaire de l'index primaire, et non l'adresse physique spécifique des données

(4) Par conséquent, l'index non clusterisé doit accéder deux fois à l'index

4. Type d'index

1.UNIQUE (index unique) : la même valeur ne peut pas apparaître et la valeur NULL est autorisée

2.INDEX (index ordinaire) : le même contenu d'index est autorisé

3.PROMARY KEY (index de clé primaire) : la même valeur n'est pas autorisée

4.FULLTEXT INDEX (index de texte intégral) : il peut cibler un certain mot dans la valeur, mais l'efficacité est très faible

5 Index combiné : essentiellement, plusieurs champs sont intégrés dans un seul index et la combinaison de valeurs de colonne. ​​doit être unique

5. Compétences d'indexation

1 L'index n'inclura pas de colonnes NULL

(1) Tant que la colonne. contient des valeurs NULL, elle ne sera pas incluse dans l'index. Tant qu'il y a une colonne dans l'index composite qui contient des valeurs NULL, alors cette colonne ne sera pas incluse dans l'index. >

2. Utilisez un index court

(1) pour indexer la chaîne. Si possible, vous devez spécifier une longueur de préfixe. Par exemple, si vous avez une colonne de char(255), n'indexez pas la colonne entière si la plupart des valeurs sont uniques dans les 10 ou 20 premiers caractères. Les index courts améliorent non seulement la vitesse des requêtes, mais permettent également d'économiser de l'espace disque et des opérations d'E/S

3.

(1) La requête MySQL n'utilise qu'un seul index, donc si l'index a été utilisé dans la clause Where, les colonnes classées par n'utiliseront pas l'index. Par conséquent, n'utilisez pas d'opérations de tri lorsque le tri par défaut de la base de données peut répondre aux exigences. Essayez de ne pas inclure le tri de plusieurs colonnes, si nécessaire, il est préférable de créer des index composites pour ces colonnes

4. opérations d'instruction

(1) Généralement, l'utilisation d'opérations similaires est déconseillée. Si elles doivent être utilisées, faites attention à la manière correcte d'utiliser. comme '%aaa%' n'utilisera pas d'index, mais comme 'aaa%' peut utiliser des index

5 N'effectuez pas d'opérations sur les colonnes

6. > ;,! = opération, mais <,<=, =,>,>=,BETWEEN,IN peut utiliser index

7. L'index doit être établi sur les champs où les opérations de sélection sont souvent effectuées

(1) En effet, si ces colonnes sont rarement utilisées, la présence ou l'absence d'index ne modifiera pas significativement la vitesse des requêtes. Au contraire, du fait de l'ajout d'index, la vitesse de maintenance du système est réduite et les besoins en espace sont augmentés

8 Les index doivent être établis sur des champs avec des valeurs relativement uniques

9. Pour ceux définis comme colonnes de types de données texte, image et bits, ne doivent pas être indexées. Parce que la quantité de données dans ces colonnes est soit assez importante, soit a très peu de valeurs

10 Les colonnes apparaissant dans Where et Join doivent être indexées

11. connectez-vous dans la condition de requête de Where (where column != …), MySQL ne pourra pas utiliser l'index

12. Si une fonction est utilisée dans la condition de requête de la clause Where (telle que : Where DAY(column)=…), mysql ne pourra pas utiliser l'index

13. Dans l'opération de jointure (lorsque les données doivent être extraites de plusieurs tables de données), mysql ne peut utiliser l'index que lorsque le le type de données de la clé primaire et de la clé étrangère est le même, sinon l'index ne sera pas utilisé s'il est établi à temps

14.explain peut aider les développeurs à analyser les problèmes SQL. Expliquer montre comment MySQL utilise les index pour. traiter les instructions de sélection et les tables de connexion. Cela peut aider à choisir de meilleurs index et à écrire des instructions de requête plus optimisées

6 Index et verrous

1. verrouillage, c'est un verrou de ligne si l'index n'est pas utilisé, c'est un verrou de table, donc les données à exploiter doivent utiliser un verrou

(1) S'il n'y a pas d'index, de sélection de données ou de positionnement. se fera via une analyse complète de la table, qui formera un verrou de table, s'il y a un index, la ligne spécifiée sera directement localisée, c'est-à-dire qu'un verrou de ligne est formé ici. Si aucun index n'est utilisé lors de la mise à jour des données, le tableau entier sera numérisé

finLa plupart du contenu est comme ça J'accumule généralement des informations peu claires provenant d'Internet et des livres, alors s'il te plaît, pardonne-moi !

Articles connexes :

Comment utiliser le nom de l'index MySQL et quand l'utiliser

Qu'est-ce qu'un index ? Mysql propose actuellement plusieurs types d'index principaux

Vidéos associées :

Une brève introduction aux index - un didacticiel vidéo pour vous guider dans MySQL en six jours

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!