Qu'est-ce qu'un index de base de données ? Explication détaillée de l'index de la base de données

Qu'est-ce qu'un index ?

Les opérations de recherche sont très courantes dans les bases de données, et l'indexation est un moyen d'améliorer la vitesse de recherche.

Classification d'index

1. Indice d'arbre B+
   C'est un indice au sens traditionnel, c'est l'indice le plus couramment utilisé et le plus efficace.

2. Index de hachage
   L'index de hachage est un index adaptatif La base de données générera automatiquement un index de hachage en fonction de l'utilisation de la table.

3. L'index de texte intégral
   est utilisé pour mettre en œuvre la recherche par mot clé. Mais il ne peut segmenter les mots qu'en fonction des espaces et ne prend donc pas en charge le chinois.
   Pour implémenter la fonction de recherche, vous pouvez choisir Lucene.

4. L'index RTree
   est rarement utilisé dans MySQL et ne prend en charge que le type de données géométriques par rapport à BTREE, l'avantage de RTREE est la recherche par plage ;

Index d'arborescence B+

La base de données utilise des pages comme unités de stockage. Une page fait 8 Ko (8192 octets) et une page peut stocker N enregistrements.
La page est divisée en : page de données et page d'index dans l'arborescence B+.
La hauteur de l'arborescence B+ est généralement de 2 à 4 niveaux, donc la recherche d'un enregistrement de ligne d'une certaine valeur clé ne nécessite que 2 à 4 fois IO, ce qui est très efficace.

Index clusterisé et index non clusterisé

Qu'il s'agisse d'un index clusterisé ou d'un index non clusterisé, leur structure logique est un arbre B+. La seule différence entre eux est :

• La page de données de l'index clusterisé stocke les enregistrements complets ; c'est-à-dire que l'index clusterisé détermine l'ordre de stockage physique de la table ;

• La page de données ; de l'index non clusterisé Seules les informations d'adresse pointant vers l'enregistrement sont stockées et ses données réelles ont été stockées dans l'index clusterisé.

Index conjoint et index de couverture

1. Index conjoint
   L'index conjoint peut être utilisé lorsque les conditions de requête impliquent plusieurs colonnes.

2. Index couvert
   Il vous suffit d'utiliser l'index auxiliaire pour obtenir les informations que vous souhaitez interroger, sans interroger à nouveau les informations d'enregistrement spécifiques via l'index clusterisé.
   Étant donné que l'index de couverture ne contient pas la ligne entière des enregistrements, sa taille est beaucoup plus petite que l'index clusterisé.
   Il est plus adapté pour effectuer certaines opérations statistiques.

Implémentation de l'index MyISAM

1. Index de clé primaire
   Dans l'index de clé primaire, la page d'index stocke la clé primaire et le pointage de décalage vers la page de données. Montant du décalage ; la page de données stocke la clé primaire et l'espace d'adressage de l'enregistrement de ligne auquel appartient la clé primaire.

2. Index secondaire
   Dans MyISAM, il n'y a pas de différence structurelle entre l'index primaire et l'index secondaire (Clé secondaire), sauf que l'index primaire nécessite que la clé soit unique, et la clé d'index secondaire peut être répétée.

Pour résumer, dans MyISAM, les fichiers d'index et les fichiers de données sont stockés séparément. Qu'il s'agisse d'un index de clé primaire ou d'un index auxiliaire, ce sont tous des index non clusterisés.

Implémentation de l'index InnoDB

1. Index de clé primaire
   La page d'index stocke toujours la clé primaire et le décalage pointant vers la page de données, mais la page de données stocke le dossier complet.
   C'est-à-dire que dans InnoDB, les données et les index de clé primaire sont stockés ensemble.

2. Index auxiliaire
   Le contenu stocké dans le nœud d'index est le même, ce sont toujours les informations de valeur clé et le décalage pointant vers la page de données mais la page de données stocke le ; Informations sur la valeur clé et le décalage de la page de données. La clé primaire correspondant à la valeur clé. Ensuite, l'enregistrement peut être trouvé en interrogeant l'index de clé primaire via la clé primaire.

Pour résumer :

• Cette méthode de mise en œuvre de l'index clusterisé rend la recherche par clé primaire très efficace, mais la recherche par index auxiliaire doit récupérer deux passes d'index : récupérez d'abord l'index auxiliaire pour obtenir la clé primaire, puis utilisez la clé primaire pour récupérer les enregistrements de l'index primaire.

• L'index auxiliaire d'InnoDB contiendra également la colonne de clé primaire, donc si la clé primaire est définie relativement grande, les autres index seront également grands. Si vous souhaitez définir plusieurs index sur la table, essayez de définir la clé primaire aussi petite que possible. InnoDB ne compresse pas les index.

Avantages de l'index

• Tout d'abord, en créant un index unique, le caractère unique de chaque ligne de données dans la table de la base de données peut être garanti.

• Deuxièmement, cela peut considérablement accélérer la récupération des données, ce qui est également la principale raison de la création d'un index.

• Troisièmement, cela peut accélérer la connexion entre les tables, ce qui est particulièrement important pour atteindre l'intégrité référentielle des données.

• Quatrièmement, lors de l'utilisation de clauses de regroupement et de tri pour la récupération de données, le temps de regroupement et de tri dans la requête peut également être considérablement réduit.

• Cinquièmement, en utilisant des index, vous pouvez utiliser des caches d'optimisation pendant le processus de requête pour améliorer les performances du système.

Inconvénients des index

• Premièrement, la création et la maintenance des index prennent du temps, ce qui augmente à mesure que la quantité de données augmente.

• Deuxièmement, les index doivent occuper de l'espace physique en plus de l'espace de données occupé par la table de données, chaque index occupe également une certaine quantité d'espace physique. index clusterisé, alors vous avez besoin L'espace sera plus grand.

• Troisièmement, lors de l'ajout, de la suppression et de la modification de données dans le tableau, l'index doit être maintenu dynamiquement, ce qui réduit la vitesse de maintenance des données.

Quelles situations nécessitent une indexation ?

• Sur les colonnes fréquemment recherchées, vous pouvez accélérer la recherche

• Sur la colonne qui est la clé primaire, forcer la colonne ; être unique La structure de disposition des données dans la table de structure et d'organisation

• est souvent utilisée dans les colonnes connectées. Ces colonnes sont principalement des clés étrangères, ce qui peut accélérer la connexion ;

• Créez des index sur les colonnes qui doivent souvent être recherchées en fonction de la plage, car l'index a été trié et sa plage spécifiée est continue. Créez un index sur la colonne, car l'index ; a été trié, afin que la requête puisse utiliser le tri de l'index pour accélérer le temps de requête de tri

• Créer un index sur la colonne qui est souvent utilisée dans la clause WHERE pour accélérer La vitesse de jugement des conditions.

• Quelles situations ne nécessitent pas d'indexation ?

Premièrement, les index ne doivent pas être créés pour les colonnes rarement utilisées ou référencées dans les requêtes. En effet, ces colonnes étant rarement utilisées, l’indexation ou non n’améliore pas 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.

• Deuxièmement, les index ne doivent pas être augmentés pour les colonnes avec peu de valeurs de données. En effet, comme ces colonnes ont très peu de valeurs, comme la colonne sexe de la table personnel, dans les résultats de la requête, les lignes de données du jeu de résultats représentent une grande proportion des lignes de données de la table, c'est-à-dire : les données qui doivent être recherchées dans le tableau. La proportion de lignes est énorme. L'augmentation de l'index n'accélère pas significativement la récupération.

• Troisièmement, les index ne doivent pas être ajoutés aux colonnes définies comme types de données texte, image et bits. En effet, le volume de données de ces colonnes est soit assez important, soit très peu de valeurs.

  Quatrièmement, lorsque les performances de modification sont bien supérieures aux performances de récupération, les index ne doivent pas être créés. En effet, les performances de modification et les performances de récupération sont contradictoires. Lors de l'ajout d'index, les performances de récupération seront améliorées, mais les performances de modification seront réduites. Lors de la réduction des index, les performances de modification augmenteront et les performances de récupération diminueront. Par conséquent, lorsque les performances de modification sont bien supérieures aux performances de récupération, les index ne doivent pas être créés.

• 
  

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!