L'utilisation des indices et la réalisation des tables sont deux approches fondamentales pour accéder aux données dans une base de données, chacune avec des compromis distincts en fonction de la situation.
Un index dans une base de données est une structure de données qui permet une recherche plus rapide en permettant au système de localiser rapidement les lignes sans scanner chaque ligne du tableau. La plupart des bases de données relationnelles utilisent des structures d'arbres B + pour les index, qui organisent les clés et les pointeurs sous forme d'arbre. Cela permet des recherches, des insertions et des suppressions dans la complexité temporelle logarithmique $$ o (\ log n) $$, qui est généralement beaucoup plus rapide que de scanner l'ensemble du tableau avec une complexité de $$ o (n) $$. Les index peuvent être cluster ou non cluster, avec des index en cluster stockant les données dans l'ordre trié physiquement, améliorant les performances de numérisation de la plage au coût des frais généraux supplémentaires sur les modifications de données. Les index peuvent également être composites, partiels, filtrés ou basés sur le hachage, réglés pour des modèles de requête spécifiques.
En revanche, une analyse de table (ou une analyse de table complète) lit chaque ligne du tableau séquentiellement, quelle que soit la sélectivité de la requête. Cela implique la numérisation de tous les blocs de données du tableau et est souvent considéré comme la méthode d'accès la plus coûteuse car elle traite plus de données que nécessaire. Cependant, les analyses de table peuvent bien fonctionner dans certains cas. Par exemple, lorsque les requêtes récupèrent un grand pourcentage de lignes, les frais généraux de l'utilisation d'un indice (qui nécessite souvent des recherches supplémentaires pour les lignes réelles) peut dépasser le coût de la numérisation de l'ensemble la table une fois. Les numérisations de table peuvent utiliser des lectures multi-blocs, qui permettent à la lecture de gros morceaux de données avec moins d'opérations d'E / S, réduisant ainsi la latence par rapport à la lecture de nombreux blocs individuels requis au hasard par les analyses d'index.
Un compromis majeur implique la sélectivité et la taille de l'ensemble de données renvoyés par la requête. Si la requête filtre à un petit nombre de lignes (sélectivité élevée), les index surpassent généralement les scans de table car ils n'ont qu'à accéder aux données pertinentes. Mais à mesure que le pourcentage de lignes revenait augmente, le coût des analyses d'index augmente, car plusieurs recherches de clés peuvent être nécessaires et le moteur de base de données doit effectuer des opérations d'E / S aléatoires supplémentaires. À un certain seuil, souvent environ 10 à 20% des rangées de la table, mais en fonction de la largeur des données et du matériel, une analyse complète de la table devient plus efficace. En effet
Les analyses d'index lisent généralement moins de pages qu'une analyse de table lorsque les colonnes couvertes sont moins compactes ou plus que les lignes de table complètes. Par exemple, un index peut inclure uniquement les colonnes indexées sans les données complètes des lignes de table, ce qui les rend plus minces et permettant à plus de lignes de s'adapter à chaque page de base de données. Cela réduit les frais généraux d'E / S lors du balayage de l'index par rapport à la numérisation de l'ensemble des données de la table. De plus, certains index peuvent être filtrés (index partiels) pour exclure les lignes non pertinentes, ce qui réduit encore l'empreinte de balayage.
D'un autre côté, les analyses de table complètes rédigent moins de chargement du côté de la maintenance de la base de données. Les index introduisent les frais généraux pendant les opérations de modification des données telles que l'insertion, la mise à jour et la suppression. Chaque modification de la table nécessite des index de mise à jour, conduisant parfois à une latence d'écriture et à des frais généraux d'écriture accrus, en particulier si de nombreux index existent sur le tableau. Ces frais généraux peuvent également affecter la concurrence et entraîner des affirmations dans des environnements d'écriture lourds. Ainsi, les scanneaux de table, qui lisent simplement les données dans son ordre naturel sans maintenance de structure supplémentaire, évitent ce coût.
Une autre considération importante est l'effet des caractéristiques de mise en cache et du matériel. Les analyses de table bénéficient d'E / S séquentielles et de pré-lutte, permettant au système de lire efficacement plusieurs blocs contigus, souvent de la mémoire s'ils sont mis en cache. Inversement, les scanneurs d'index engagent des E / S aléatoires pour récupérer des blocs de données disparates, surtout si la numérisation d'index doit rechercher les pointeurs de lignes dans le stockage du tas. Cela peut rendre les analyses d'index plus lentes sur les systèmes avec des performances d'E / S aléatoires de disque plus lents, bien que les SSD et les grands pools de mémoire réduisent cet écart. La situation peut également dépendre de spécificités telles que le parallélisme et les capacités multi-threading du moteur de base de données, où les scanneurs de table parallèles peuvent augmenter considérablement le débit.
De plus, la fragmentation interne et la disposition du stockage physique influencent les compromis de performance. Les tables de table sur des tables organisées par un tas peuvent souffrir d'enregistrements transmis, où les lignes se sont déplacées vers différentes pages en raison de mises à jour, aggravant l'efficacité de scan. Les index en cluster, qui stockent les données triés par clé, peuvent éviter ce problème et faire parfois un "scan de table" équivalent à une analyse d'index en cluster. Cependant, les avantages s'accompagnent du coût des réorganisations de lignes coûteuses lors du désabonnement des données lourdes.
Du point de vue de la requête, la décision entre une analyse d'index et une analyse de table est généralement prise par des modèles d'estimation basés sur les coûts, en tenant compte des statistiques sur la distribution des données, le nombre de lignes et les coûts matériels. L'optimiseur équilibre le processeur, les E / S et les coûts de mémoire pour choisir le chemin d'accès le plus efficace. Ces décisions peuvent être influencées par des facteurs tels que la mémoire disponible, l'état de mise en cache et les modèles de requête. Il n'y a pas de seuil fixe entre quand utiliser l'un ou l'autre; Le point de croisement varie selon le système et la charge de travail.
En résumé, les compromis entre l'utilisation des indices et des analyses de table comprennent:
- Performances vs volume de données: les index surperforment lors du filtrage à quelques lignes; Les analyses de table peuvent être meilleures pour la récupération des données importantes.
- Modèles d'E / S: les analyses d'index provoquent des lectures d'E / S aléatoires; Les analyses de table bénéficient d'E / S séquentielles et de lectures multi-blocs.
- Offres de maintenance: les indices augmentent les coûts de fonctionnement d'écriture dus aux mises à jour des structures d'index; Les scanners de table n'encourent pas cela.
- Efficacité de stockage: les index peuvent être compacts en couvrant moins de colonnes; Les analyses de table traitent les lignes complètes et potentiellement plus de données.
- Effets de mise en cache: les scanneurs de table peuvent utiliser la mise en cache de données efficacement, en particulier avec de grandes lectures séquentielles; Les analyses d'index peuvent ne pas bénéficier autant d'accès aléatoire.
- Décision Optimizer: les optimisateurs de requête basés sur les coûts choisissent dynamiquement entre ces options en fonction des statistiques de requête et des détails de charge de travail.
- Impact de la disposition des données: les tables de tas peuvent entraîner des pénalités comme les enregistrements transmis pendant les analyses; Les index en cluster organisent physiquement les données mais augmentent les coûts de mise à jour.