Conception d'index MySQL

Un index de base de données est une structure de données triée dans un système de gestion de base de données pour aider à interroger et mettre à jour rapidement les données dans les tables de base de données. L'implémentation de l'index utilise généralement B-tree et sa variante B-tree.

En plus des données, le système de base de données conserve également des structures de données qui satisfont des algorithmes de recherche spécifiques. Ces structures de données font référence (pointent vers) les données d'une manière ou d'une autre, afin que des algorithmes de recherche avancés puissent être implémentés sur ces structures de données. . Cette structure de données est un index.

Il y a des coûts à payer pour définir des index sur les tables : premièrement, l'espace de stockage de la base de données est augmenté, et deuxièmement, il faut plus de temps pour insérer et modifier les données (car l'index change également en conséquence).

L'image montre une méthode d'indexation possible. Sur la gauche se trouve un tableau de données avec un total de deux colonnes et sept enregistrements. Celui le plus à gauche est l'adresse physique de l'enregistrement de données (notez que les enregistrements logiquement adjacents ne sont pas nécessairement physiquement adjacents sur le disque). Afin d'accélérer la recherche de Col2, vous pouvez conserver un arbre de recherche binaire comme indiqué à droite. Chaque nœud contient la valeur de la clé d'index et un pointeur vers l'adresse physique de l'enregistrement de données correspondant. recherche binaire dans O(log2n) Les données correspondantes sont obtenues dans la complexité.

La création d'index peut grandement améliorer les performances du système.

Premièrement, en créant un index unique, l'unicité de chaque ligne de données dans la table de la base de données peut être garantie.

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 garantir 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 les requêtes peut également être considérablement réduit.

5. 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.

Il y a tellement d'avantages à ajouter un index, pourquoi ne pas créer un index pour chaque colonne du tableau ? Car l’ajout d’index présente également de nombreux inconvénients.

Premièrement, la création et la maintenance des index prennent du temps, et ce temps 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 si un index clusterisé doit être établi. sera plus grand.

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

Les indices sont construits sur certaines colonnes de la table de la base de données. Lors de la création d'un index, vous devez déterminer sur quelles colonnes peuvent être indexées et quelles colonnes ne peuvent pas être indexées.

De manière générale, les index doivent être créés sur ces colonnes :

1. Sur les colonnes fréquemment recherchées, la vitesse de recherche peut être accélérée

2. colonne de clé primaire, impose l'unicité de la colonne et la structure de disposition des données dans la table d'organisation

3. Sur les colonnes souvent utilisées dans les connexions, ces colonnes sont principalement des clés étrangères, ce qui peut accélérer établir la connexion. ;

4. Créez un 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 les colonnes qui doivent souvent être triées Créez un index, 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 la requête de tri

6. Créez un index ; index sur les colonnes qui sont souvent utilisées dans la clause WHERE pour accélérer le jugement des conditions.


De plus, les index ne doivent pas être créés pour certaines colonnes. De manière générale, ces colonnes qui ne doivent pas être indexées ont les caractéristiques suivantes :

1. Les index ne doivent pas être créés pour des 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 contenant 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, l'index ne doit pas être créé. 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.


Selon les fonctions de la base de données, trois types d'index peuvent être créés dans le concepteur de base de données : index unique, index de clé primaire et index clusterisé.

Indice unique

Un index unique est un index dans lequel deux lignes ne peuvent pas avoir la même valeur d'index. La plupart des bases de données ne permettent pas d'enregistrer les index uniques nouvellement créés avec la table lorsqu'il existe des valeurs de clé en double dans les données existantes. La base de données peut également empêcher l'ajout de nouvelles données qui créeraient des valeurs de clé en double dans le tableau. Par exemple, si un index unique est créé sur le nom de famille de l'employé (lname) dans la table des employés, deux employés ne peuvent pas avoir le même nom de famille.

Index de clé primaire

Les tables de base de données ont souvent une colonne ou une combinaison de colonnes dont les valeurs identifient de manière unique chaque ligne de la table. Cette colonne est appelée clé primaire de la table. La définition d'une clé primaire pour une table dans un diagramme de base de données crée automatiquement un index de clé primaire, qui est un type spécifique d'index unique. L'index exige que chaque valeur de la clé primaire soit unique. Il permet également un accès rapide aux données lorsqu'un index de clé primaire est utilisé dans les requêtes.

Index clusterisé

Dans un index clusterisé, l'ordre physique des lignes du tableau est le même que l'ordre logique (index) des valeurs clés. Une table ne peut contenir qu'un seul index clusterisé. Si un index n'est pas un index clusterisé, l'ordre physique des lignes de la table ne correspond pas à l'ordre logique des valeurs clés. Les index clusterisés offrent généralement un accès aux données plus rapide que les index non clusterisés.

Ce qui précède est le contenu de la conception de l'index MySql. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (m.sbmmt.com) !