L'indexation de la base de données SQL est le processus de catégorisation et d'organisation des enregistrements de données. Quiconque travaille avec une base de données apprécierait le gain de temps que cette fonctionnalité offre lors de la recherche de données spécifiques. Dans cet article, nous avons exploré la définition d'un index de base de données SQL, les types et l'importance d'un index de base de données. Appréciez la balade!
Qu'est-ce que l'index de base de données ?
Les index de base de données sont des structures de données qui facilitent des recherches de table plus rapides. La base de données fonctionnera toujours sans index, mais cela peut prendre plus de temps pour que les requêtes renvoient des résultats. Chaque index contient une clé composée de données d'une ou plusieurs colonnes de sa table correspondante. La base de données peut choisir d'utiliser un index pour accélérer la récupération d'enregistrement lorsqu'une requête nécessite l'accès à une table avec un index.
Lorsqu'un utilisateur entre une requête, la requête effectue généralement une recherche linéaire sur chaque ligne, en vérifiant si l'une d'entre elles contient les informations souhaitées. Ceci est simplifié avec l'utilisation d'un index, qui augmente la productivité car il raccourcit le temps nécessaire pour accomplir les tâches.
Types d'index de base de données
Il existe différents types d'index de base de données. Voici les types d'index de base de données :
#1. Index primaire dans le SGBD
L'index de la base de données principale est un fichier à deux champs, ordonné et de longueur fixe. Le premier champ fonctionne comme une clé primaire et le second champ indique quel bloc de données contient les informations spécifiées. Il existe toujours une correspondance un à un entre les entrées d'index de la base de données primaire. Il existe deux variétés distinctes d'indexation principale utilisée dans le SGBD.
- Indice dense
- Index clairsemé
#2. Index secondaire dans le SGBD
Une clé candidate dans un système de gestion de base de données (SGBD) est un champ qui a une valeur unique pour chaque entrée. C'est un type d'index qui ne regroupe pas les données en clusters. Cette méthode d'indexation d'une base de données sur deux niveaux permet de minimiser la taille du niveau de mappage initial. Pour cette raison, une large gamme d'entiers est choisie pour l'étape initiale, tandis que la taille de la cartographie elle-même reste fixement modeste.
Que fait l'indexation ?
L'indexation permet d'obtenir une table non triée dans un ordre qui améliore l'efficacité de la recherche via des requêtes. Les tables non indexées nécessitent une recherche linéaire sur les lignes car la requête ne peut pas connaître l'ordre des lignes et s'optimiser en conséquence. C'est-à-dire que les requêtes devront parcourir chaque ligne pour trouver celles qui répondent aux critères. Cela peut, comme vous vous en doutez, prendre un certain temps.
Comment fonctionne l'index de base de données ?
Il n'est pas pratique de s'attendre à ce que la table de base de données se réorganise chaque fois que les circonstances de la requête changent pour améliorer les performances de la requête. En fait, ce qui se passe, c'est que la base de données construit un Structure de données en réponse à l'indice. Un B-Tree est probablement le type de structure de données utilisé. Le B-Tree a plusieurs avantages, mais le fait qu'il puisse être facilement trié est le plus important pour nos besoins. En raison de ce qui précède, il n'est pas surprenant qu'une structure de données triée améliore l'efficacité de notre recherche.
Notez que lorsque l'index de la base de données construit une structure pour une colonne donnée, il stocke uniquement les informations sur cette colonne ; aucune autre colonne n'est incluse.
Vous pouvez également lire:
Conseils pour créer un index de base de données
Voici les conseils pour créer un index de base de données :
#1. Index selon la charge de travail
Un index de base de données efficace peut être conçu en réponse au volume de données traitées. Au lieu de baser le processus de création d'index sur le nombre de tables dont vous disposez (ce qui peut rendre l'accès plus difficile), concentrez-vous sur la création d'un index de base de données qui facilite la recherche de certains termes.
#2. Vous devriez utiliser des prédicats
La création d'un index de base de données avec des prédicats est une autre option viable. Un prédicat est une sorte d'expression de condition utilisée pour déterminer la valeur de vérité d'une déclaration. En plus de simplifier le développement d'un index de base de données, cela aide l'utilisateur à faire des choix éclairés sur les données à récupérer.
Afin de trouver les outils et les fournitures nécessaires à un projet de construction, une base de données conçue pour cette industrie peut utiliser des prédicats dans son index. Un employé peut l'utiliser pour vérifier si oui ou non les mêmes matériaux ont été utilisés dans un projet similaire quelques mois plus tôt.
#3. Créer un index de base de données des recherches les plus populaires
Un index adapté aux requêtes SQL d'index de base de données les plus populaires est également généralement construit pour une efficacité optimale. Cela pourrait inclure un aperçu de toutes les requêtes SQL d'index de base de données possibles et leur fréquence d'utilisation estimée, avec les plus cruciales mises en évidence.
Cela peut être utile lorsqu'une école doit effectuer une recherche standard dans la base de données, par exemple pour déterminer quels enfants ont été le plus souvent appelés à la maison ou qui ont été absents pendant une longue période pour cause de maladie. Ceci est utile en cas d'urgence, car cela facilite la récupération rapide et facile des informations de contact de leurs parents.
#4. Éviter le besoin de trier en créant un index
La création d'un index qui ne nécessite pas de tri peut accélérer la capacité d'une requête à récupérer les données souhaitées. Un utilisateur de base de données peut souvent trier manuellement les enregistrements à l'aide des clauses 'Group by' et 'Order by'. Vous pouvez optimiser la vitesse de la base de données en créant un index sur les colonnes utilisées par ces deux tris. Puisqu'aucun tri des enregistrements n'est nécessaire, les requêtes peuvent être optimisées davantage.
#5. Créer des index uniques
Les employés sont souvent chargés de créer un certain nombre d'index uniques lors du développement d'une base de données efficace. Ceci est crucial car il empêche la corruption des données en éliminant la possibilité de doublons dans les colonnes d'une table. Les contraintes, ou les règles que les données d'une table doivent toujours suivre, sont utilisées par la grande majorité des systèmes de bases de données pour générer des index uniques. Une clé primaire est également utilisée pour faciliter l'identification des données dans une table.
#6. Considérez les clés étrangères
Un employé peut optimiser les performances de la base de données en créant des index liés à chaque clé étrangère utilisée par la base de données. Les clés étrangères sont un groupe de colonnes dans une base de données qui permettent d'accéder aux tables d'une autre base de données. Ces index ne sont pas requis dans la plupart des systèmes de base de données, mais peuvent quand même augmenter la productivité. La base de données peut ne pas contenir toutes les informations dont un employé a besoin, mais cette clé peut le conduire au bon résultat dans une autre base de données.
#7. Insérer des colonnes exclusivement pour l'utilisation de l'index de base de données
Un employé peut trouver utile d'ajouter plusieurs colonnes d'index de base de données uniquement à sa base de données dans certaines circonstances. Un travailleur peut désormais récupérer rapidement des informations d'index uniquement. Ainsi, toutes les requêtes saisies par l'utilisateur n'auront qu'à rechercher dans ces colonnes d'index nouvellement ajoutées. Cela accélère le processus de fourniture des données spécifiques dont un employé a besoin. Mais il convient de noter que le travailleur doit maintenant vérifier les autres requêtes pour voir si les nouvelles colonnes affecteront sa capacité à faire son travail.
#8. Éviter une limite sur le nombre d'index
Il est recommandé de ne pas limiter le nombre d'index dans une base de données lors de sa création. Pour optimiser correctement une base de données, il est préférable d'avoir autant d'index que possible car chaque index agit comme un optimiseur qui accélère le processus de récupération des données. Il convient de noter que ce n'est pas seulement utile à la personne en question ; dans la plupart des lieux de travail, les bases de données et les informations qu'elles contiennent sont partagées avec les employés. Cela signifie qu'il a le potentiel d'augmenter la production dans une organisation.
#9. Vérifiez s'il existe de nombreuses implications de modification des données
Pour gagner du temps, un travailleur peut préférer ignorer la saisie manuelle des données. Ils peuvent y parvenir en réfléchissant aux résultats de toute modification apportée aux données. Certaines options existent pour y parvenir. Un employé, par exemple, peut consulter les index de la table. Il est fort possible que l'ajout d'un autre index de base de données n'affecte pas de manière significative les performances s'il existe déjà plus d'index que de colonnes. Un employé peut également vérifier le temps d'exécution d'une requête en l'exécutant sur la table. La valeur de la création d'un index de base de données dépend du temps nécessaire à l'exécution de la requête.
Si un agent doit ajouter ou supprimer des données pour générer ses propres index, il peut le faire après avoir pris en compte l'impact sur le reste de la base de données. Il serait insensé, par exemple, de se débarrasser des numéros de téléphone qui relient l'entreprise à un employé spécifique si la base de données regorge de coordonnées de fournisseurs.
Fonctionnement interne d'un index de base de données
L'index de base de données SQL est structuré en interne sous forme d'arbres. Les index de base de données sont représentés graphiquement sous forme d'arbres, les enregistrements individuels remplaçant les feuilles. Plusieurs nœuds connectés par des pointeurs constituent la structure de l'arborescence d'index d'une base de données.
Lorsque la commande CREATE INDEX est exécutée, une arborescence d'index est générée. Un algorithme dans le programme de base de données est responsable de la création de l'arborescence d'index. La procédure de construction d'index commence par une étape de tri des enregistrements par clé d'index. Une fois que tous les nœuds de l'index de l'arborescence ont été créés, la structure est construite au-dessus d'eux. Lorsqu'il existe de nombreux enregistrements dans une base de données, la création d'une arborescence d'index peut prendre du temps.
Afin de trouver une ligne spécifique dans la table, nous devons commencer notre recherche d'index au nœud feuille. A chaque embranchement de la route, une sélection doit être faite quant au chemin à prendre. Cela nécessite une analyse approfondie des critères de recherche et des valeurs comprises dans chaque fourchette. Prenons le cas de la recherche du nombre 12 dans un arbre à deux branches comme illustration très basique. Les chiffres 0 à 10 sont situés dans la branche X, tandis que 11 à 20 sont situés dans la branche Y. Puisque 12 est supérieur à 11, vous devriez regarder là-bas.
Le B-tree est un exemple d'arbre d'index spécialisé. Les arbres équilibrés, ou arbres B, ont le même nombre de nœuds de la racine à chaque nœud feuille.
Comment fonctionne un index de base de données sous le capot
Un index est simplement une structure de données qui permet d'accéder facilement aux informations pertinentes en stockant des références à des éléments individuels. En l'absence d'index, les lignes de données d'une base de données sont souvent conservées dans un « tas » non organisé. Microsoft SQL Server et Azure SQL Database disposent tous deux d'un commutateur que vous pouvez utiliser pour activer ou désactiver cette fonctionnalité.
En réalité, les données sont rarement conservées dans un état entièrement non structuré. Les clés primaires, qui dans MySQL peuvent être identiques aux index, sont généralement utilisées à la place ; ils peuvent prendre la forme d'un entier auto-incrémenté. L'efficacité "binaire" du tri (avec des valeurs uniques) est toutefois limitée à une requête qui filtre sur la colonne ordonnée, car une seule colonne peut être utilisée pour trier les données. Une table avec un index peut être recherchée efficacement à l'aide de la recherche binaire car elle peut être triée sur plusieurs colonnes.
Lorsque vous indexez une colonne, vous créez en réalité une nouvelle table avec deux colonnes : la colonne que vous avez indexée et un pointeur vers l'emplacement de l'élément que vous recherchez. L'index aura la même longueur que la table d'origine, mais sa largeur sera nettement plus petite, nécessitant moins de blocs de disque à stocker et à parcourir. Dans MySQL, les pointeurs ont généralement une taille inférieure à 5 octets. Pour ceux qui souhaitent plonger plus profondément, le post "légendaire" Stack Overflow référencé ci-dessus explique l'arithmétique derrière le nombre nécessaire de blocs pour le stockage.
À moins que vous ne les ayez construits à partir de rien, votre base de données actuelle contient probablement de nombreux index.
Importance d'un index de base de données
L'importance d'un index de base de données ne peut jamais être négligée, mais ils entraînent des coûts sous la forme d'écritures supplémentaires et le besoin d'espace de stockage supplémentaire pour maintenir à jour la copie en double des données. Une autre importance d'un index de base de données est qu'il nécessite une maintenance régulière pour garantir des performances optimales. Cependant, les avantages de l'utilisation d'index l'emportent largement sur les inconvénients mineurs de leur entretien. Les index nécessitent une maintenance régulière pour fonctionner correctement. Les reconstructions d'index, les réorganisations et les vérifications de fragmentation relèvent de cette catégorie de tâches.
Le coût d'avoir des index
Comme la plupart des choses dans la vie, les indices coûtent de l'argent. Tous les index d'une table doivent être renouvelés si l'une des lignes de cette table est modifiée (via INSERT, UPDATE ou DELETE). Ces modifications coûtent du temps et, lorsque plusieurs index existent dans une base de données, les performances d'un processus qui modifie de manière intensive les tables peuvent en souffrir.
Nous devons peser les avantages par rapport aux coûts de construction d'un indice avant de continuer. Pouvons-nous anticiper une augmentation notable de l'efficacité ? S'agit-il d'un tableau statistique ou a-t-il été largement manipulé ? Pouvons-nous vivre avec une baisse de performance pendant que nous peaufinons la table ? Les résultats de cet examen nous diront si oui ou non l'index doit être fait. Certaines bases de données ont même la capacité de construire et d'éliminer automatiquement des index selon les besoins.
Ce qu'il ne faut pas oublier à propos de l'index de la base de données
Voici les éléments à ne pas oublier concernant l'index de la base de données :
- L'indexation implique l'ajout d'une structure de données avec des colonnes de critères de recherche et un pointeur.
- Le pointeur est l'emplacement du disque mémoire de la ligne contenant les données restantes.
- Le tri de la structure des données d'index améliore les performances des requêtes.
- Une requête localise la ligne souhaitée dans l'index ; une fois localisé, l'index fournit une référence au reste des données.
- Avec l'index, la requête n'a besoin de regarder que quatre lignes au lieu de dix-sept.
Qu'est-ce qu'un index pour les nuls ?
Un indice est une valeur numérique utilisée comme jauge. C'est un terme utilisé pour décrire une analyse quantitative du mouvement d'un marché de titres. Les indices boursiers et les indices obligataires des marchés financiers consistent chacun en un portefeuille fictif de titres censés représenter l'ensemble du marché ou un sous-ensemble de celui-ci.
Quel est le but de l'indexation ?
L'indexation est un outil statistique permettant de surveiller des indicateurs économiques tels que l'inflation, le chômage, la croissance du PIB, la productivité et les rendements boursiers.
Réflexions finales
Un index est beaucoup plus efficace que de demander aux employés de réorganiser constamment la base de données en réponse à de nouvelles requêtes. L'index, d'autre part, génère sa propre hiérarchie de base de données interne. Un index de base de données peut utiliser une structure B-Tree, par exemple. De plus, les nœuds avec deux branches ou plus sont pris en charge, ainsi les travailleurs peuvent s'étendre au-delà des simples recherches binaires.
Articles Relatifs
- DÉVELOPPEUR DE BASE DE DONNÉES : Description du poste, tâches, salaire et certification
- GESTION DES BASES DE DONNÉES : définition, types et avantages
- Top 27 des logiciels de systèmes de gestion de base de données (SGBD) en 2023
- OUTILS D'ANALYSE DE DONNÉES : 13+ meilleurs outils d'analyse de données 2023
Bibliographie
