Un aperçu de l'indexation de base de données pour les débutants

L'indexation de base de données est le moyen le plus courant connu et utilisé par les développeurs backend pour optimiser les requêtes de base de données. Dans cet article, nous discuterons en détail de l'indexation des bases de données.

Qu'est-ce qu'un index de base de données ?

Un index de base de données permet à une requête de récupérer des données d'une base de données de manière efficace. En termes plus simples, l'indexation est un moyen d'obtenir une table non ordonnée dans un ordre qui maximisera l'efficacité lors de la recherche d'un enregistrement.

Les index peuvent être liés à des tables spécifiques et se composer d'une ou plusieurs clés. De plus, une table peut avoir plusieurs index construits à partir de celle-ci.

Lorsqu'une table de base de données n'est pas indexée, il n'y aura pas d'ordre clair des lignes, donc, pour répondre à une requête, elle devra rechercher dans les lignes de manière linéaire, c'est-à-dire que la requête devra parcourir chaque ligne pour trouver les lignes avec la condition correspondante. Comme vous pouvez l'imaginer, ce n'est pas idéal et peut être un problème lorsque vous regardez à l'intérieur d'une table de base de données avec une énorme quantité de données.

Par exemple, nous avons un tableau comme indiqué ci-dessous :

Et puis, nous voulons exécuter une requête comme suit :

 SELECT company_id, units, unit_cost FROM index_test WHERE company_id = 18

Dans ce cas particulier, la base de données devrait rechercher dans les 17 enregistrements dans l'ordre dans lequel ils apparaissent dans le tableau, de haut en bas, un à la fois, pour rechercher toutes les instances potentielles de company_id comme 18.

Cela ne fera que prendre de plus en plus de temps à mesure que la taille de la table augmente. Comment l'indexation peut-elle aider ici ? L'indexation peut nous aider à configurer la colonne avec la condition de recherche sur ( company_id dans ce cas) de manière triée pour optimiser les performances de la requête.

Avec un index sur la colonne company_id , la table ressemblerait à ceci :

Désormais, la base de données peut simplement rechercher company_id égal à 18 et renvoyer toutes les colonnes demandées pour cette ligne, puis passer à la ligne suivante. Si la ligne suivante a également le company_id à 18, alors elle renverra également les colonnes de requête pour cette ligne, mais si la ligne suivante a le company_id à 18, la base de données sait qu'elle peut arrêter la recherche ici et terminer la réponse .

C'était une explication assez simple de ce que sont les index de base de données et de ce qu'ils peuvent faire, mais il se passe beaucoup plus de choses dans le processus. Examinons plus en détail le fonctionnement de l'indexation.

Comment fonctionne l'indexation de la base de données ?

En réalité, la table de la base de données ne se réorganise pas à chaque fois que les conditions de la requête changent afin d'optimiser les performances de la base de données, mais il se produit en fait que l'index oblige la base de données à créer une structure de données distincte qui devrait être facilement triable.

Il est important de noter que lorsqu'un index est créé sur une colonne dans une base de données, il crée une structure de données sur cette colonne spécifique et aucune autre colonne n'est stockée dans cette structure de données. Par exemple, dans l'exemple ci-dessus, notre structure de données ne contiendra que le company_id et aucune autre colonne telle que unit ou unit_cost .

Mais une question légitime apparaît ici - comment la base de données sait-elle quels autres champs de la table doivent être renvoyés pour une requête. Essayons de comprendre comment.

Les index de base de données stockent des pointeurs pour simplement référencer des informations pour l'emplacement des informations supplémentaires dans la mémoire. En d'autres termes, l'index contient le company_id et l'adresse de cette ligne particulière dans la mémoire. Dans cet exemple, l'index de la base de données ressemblera à ceci :

Avec cet index, la requête peut vérifier les lignes de la colonne company_id qui ont 18 comme valeur, puis à l'aide du pointeur, elle peut trouver les informations associées pour cet enregistrement.

Quelles structures de données sont utilisées pour l'indexation ?

Après avoir compris ce que nous attendons de l'index, regardons les structures de données communes qui peuvent être utilisées pour l'indexation de la base de données :

Arbres B

Les arbres B sont les structures de données d'index les plus souvent utilisées car ils sont rapides pour les recherches, les suppressions et les insertions. Toutes ces opérations sont possibles en temps logarithmique et les données contenues dans un B-tree peuvent être facilement triées.

Tables de hachage

Les index de hachage sont couramment utilisés pour décrire les index qui utilisent des tables de hachage . Étant donné que les tables de hachage sont particulièrement efficaces pour rechercher des données, les requêtes qui recherchent une correspondance exacte peuvent être traitées rapidement. La clé d'un index de hachage est la valeur de colonne et la valeur d'une table de hachage est une référence aux données de ligne de la table.

Les tables de hachage, en revanche, ne sont pas des structures de données ordonnées ; par conséquent, ils peuvent être inefficaces pour d'autres types de recherches.

R-Tree

R-tree est fréquemment utilisé dans les bases de données spatiales, généralement utilisées pour indexer des informations multidimensionnelles telles que des coordonnées géographiques, des rectangles, des polygones, etc. Il est utile pour des recherches telles que "trouver tous les cafés à moins de 2 miles de ma position".

Index des bitmaps

Les index bitmap sont utiles pour les colonnes qui ont un grand nombre d'occurrences de ces valeurs, c'est-à-dire les colonnes avec une faible sélectivité. Par exemple, considérons une colonne ayant des valeurs booléennes.

Quand utiliser les index

Les index sont conçus pour augmenter les performances de la base de données ; ainsi, l'indexation peut être utilisée chaque fois que nous avons besoin d'améliorer considérablement les performances de la base de données. Plus votre base de données s'agrandit, plus il est probable que l'indexation vous sera bénéfique.

Cependant, la première chose à retenir est que l'index occupe de l'espace supplémentaire ; par conséquent, plus la table est grande, plus l'indice est élevé. Chaque fois que vous effectuez une opération d'ajout, de suppression ou de mise à jour, la même opération devra également être exécutée sur l'index.

Quand ne pas utiliser les index

Lorsque des données sont écrites dans la base de données, la table d'origine est mise à jour en premier, suivie des autres index basés sur cette table. Lorsqu'une écriture est effectuée dans la base de données, les index deviennent inopérants jusqu'à ce qu'ils soient mis à jour. Les index ne seront jamais fonctionnels si la base de données reçoit continuellement des écritures.

C'est pourquoi les index sont souvent appliqués aux bases de données dans les entrepôts de données qui obtiennent de nouvelles données de manière planifiée (pendant les heures creuses) plutôt qu'aux bases de données de production qui peuvent recevoir de nouvelles écritures tout le temps.

Comment créer un index ?

L'extrait de code suivant montre comment créer un index sur une seule colonne dans une base de données SQL :

 CREATE INDEX name_index ON Employee (Employee_Name);

Si vous souhaitez créer un index sur plusieurs colonnes, la commande SQL ressemblera à ceci :

 CREATE INDEX name_index ON Employee (Employee_Name, Employee_Age);

Conclusion

Nous avons donc discuté en détail de l'indexation de la base de données dans cet article et avons également découvert les structures de données utilisées pour implémenter l'indexation de la base de données et également quand il est conseillé d'utiliser des index et autres.

Pour tout résumer, voici un petit résumé :

• L'indexation de la base de données peut aider à réduire considérablement le temps des requêtes.
• L'indexation comprend une structure de données avec des colonnes pour les critères de recherche ainsi qu'un pointeur.
• Le pointeur est l'adresse sur le disque mémoire de la ligne contenant les informations restantes.
• Pour améliorer les performances des requêtes, la structure des données d'index (B-Tree, R-Tree, Hash Table ou Bitmap) est triée.
• La requête recherche dans l'index la ligne spécifiée ; l'index fait référence au pointeur qui découvrira le reste de l'information.

C'est tout pour cet article. L'indexation de base de données est un sujet vaste et un peu compliqué, j'espère que cet article sera utile pour comprendre les bases du concept.

Continue de lire!