Les coûts des opérations d'E / S entre les analyses d'indice et les analyses de table diffèrent fondamentalement en termes de la façon dont les données sont accessibles, du nombre de pages lues et de l'efficacité basée sur la sélectivité des requêtes et l'organisation des données.
Les analyses d'index impliquent d'accéder aux données en traversant une structure d'index (souvent un arbre B). Le coût ici comprend principalement deux composants: le coût d'accès aux pages d'index et le coût de récupération des pages de table correspondantes. Les pages d'index ont généralement des modèles d'accès aléatoires car les nœuds de feuille d'index peuvent ne pas être physiquement contigus sur le disque, conduisant à des opérations d'E / S aléatoires. Chaque pas logique vers le bas de l'arbre d'index de la racine à la feuille nécessite des pages de lecture, et ce coût est estimé à l'aide du paramètre de coût de la page aléatoire du système. Après avoir localisé les entrées d'index pour les lignes pertinentes, le système récupère les pages de données de table correspondantes. Le nombre de telles réactions de page de données dépend de la sélectivité de la requête et de la corrélation physique entre les données stockées et l'ordre d'index (appelé corrélation). Une corrélation élevée signifie que les entrées d'index des emplacements des lignes correspondent étroitement à l'ordre des données physiques, en réduisant les E / S aléatoires et en rendant le scan d'index plus efficace. D'un autre côté, une faible corrélation conduit à de nombreuses réchauffages aléatoires, augmentant considérablement le coût des E / S.
Le coût du processeur dans les analyses d'index comprend le traitement de chaque ligne individuelle récupérée, mais la principale préoccupation des coûts est les E / S. Il y a aussi l'aspect de la visibilité: si la base de données a un suivi de la carte de visibilité quelles pages de données sont entièrement visibles pour toutes les transactions, le système peut parfois ignorer complètement la lecture de certaines pages pendant les analyses uniquement des index, réduisant considérablement les coûts d'E / S.
En revanche, les analyses de table ou les analyses de table complètes fonctionnent en lisant toutes les pages de données séquentiellement à partir du tableau. Les E / S ici sont principalement séquentielles, qui ont tendance à être plus rapides que les E / S aléatoires sur les disques de rotation ou moins pénalisés sur SSD. Le coût d'une analyse de table complète est relativement constant car il lit l'ensemble du tableau quel que soit le nombre de lignes qui satisfont la requête. Ce coût dépend du nombre total de pages dans le tableau plutôt que du nombre de lignes sélectionnées. Les analyses de table complètes ne bénéficient pas de la sélectivité; Ils doivent lire chaque page même si la requête filtre beaucoup les lignes. Cependant, avec les technologies de stockage modernes et les optimisations comme les lectures multi-blocs, les lectures parallèles et le déchargement de la couche de stockage, le coût des analyses de table complète peut être compétitive ou même moins que les analyses d'index pour les requêtes qui récupèrent une partie importante du tableau.
L'optimiseur basé sur les coûts (CBO) décide entre l'utilisation d'une analyse d'index et une analyse de table complète basée sur les coûts estimés. Pour les requêtes hautement sélectives, où seule une petite fraction de lignes doit être récupérée des analyses d'indexer ont tendance à avoir des coûts d'E / S inférieurs car moins de pages de table sont récupérées. À mesure que le seuil de sélectivité augmente (plus de lignes nécessaires), le coût d'E / S des analyses d'index augmente en raison d'un nombre plus élevé de récupérations de pages aléatoires et de traversées d'index, dépassant finalement le coût d'une analyse de table complète. À ce stade, le CBO favorise un scan de table car le coût des E / S séquentiel est inférieur à la charge d'E / S aléatoire de nombreuses recherches d'index.
Un autre facteur important ayant un impact sur la différence de coût des E / S est le «facteur de clustering» ou le regroupement physique des lignes correspondant aux clés d'index. Un facteur de clustering inférieur (meilleur clustering) signifie que les lignes accessibles via l'index sont situées physiquement les unes des autres, réduisant les E / S aléatoires et améliorant l'efficacité de la balayage de l'indice. Un facteur de clustering plus élevé conduit à des E / S plus aléatoires pendant le balayage d'index et réduit son avantage par rapport à un scan de table.
Les systèmes de bases de données modernes peuvent également implémenter des analyses parallèles à la fois des analyses d'index et des tableaux complètes où les ressources d'E / S et de CPU sont partagées entre plusieurs travailleurs, ce qui peut réduire le temps de requête total. Cependant, la nature fondamentale des E / S pour les analyses d'index (accès aléatoire sur les pages) par rapport aux analyses de table (principalement l'accès à la page séquentiel) reste un différenciateur clé.
Pour résumer les points clés sur les différences de coûts d'E / S:
- Les analyses d'index engagent les coûts d'E / S à partir des pages d'index de lecture (accès aléatoire) plus les pages de données correspondantes (accès potentiellement aléatoire). Le coût est sensible à la sélectivité, à la corrélation et au facteur de clustering.
- Les analyses de table complètes effectuent des E / S séquentielles en lisant toutes les pages, avec un coût d'E / S relativement stable, quelle que soit la sélectivité.
- Les analyses d'index peuvent surpasser lorsque les requêtes ciblent les petits sous-ensembles de données, mais souffrent de frais généraux d'E / S aléatoires à mesure que la sélectivité augmente.
- Les analyses de table complètes peuvent être plus efficaces lorsqu'une grande fraction du tableau a besoin d'accès en raison d'E / S séquentielles efficaces.
- Les cartes de visibilité et les analyses sur l'indice peuvent réduire les coûts d'E / S dans certains cas de balayage d'index en évitant les lectures de la page de données.
- Le parallélisme peut améliorer les deux types de scan mais ne modifie pas les caractéristiques fondamentales des E / S.
- L'optimiseur équilibre ces facteurs de coût pour choisir la méthode la plus efficace basée sur la requête et les caractéristiques de données.