Los costos de las operaciones de E/S entre los escaneos de índice y los escaneos de tabla difieren fundamentalmente en términos de cómo se accede a los datos, el número de páginas se lee y la eficiencia basada en la selectividad de consultas y la organización de datos.
Los escaneos de índice implican acceder a datos atravesando una estructura de índice (a menudo un árbol B). El costo aquí incluye principalmente dos componentes: el costo de acceder a las páginas de índice y el costo de obtener las páginas de tabla correspondientes. Las páginas de índice generalmente tienen patrones de acceso aleatorios porque los nodos de hoja índice pueden no ser físicamente contiguos en el disco, lo que lleva a operaciones de E/S aleatorias. Cada paso lógico por el árbol índice de la raíz a la hoja requiere páginas de lectura, y este costo se estima utilizando el parámetro de costo de página aleatorio del sistema. Después de localizar las entradas de índice para las filas relevantes, el sistema obtiene las páginas de datos de la tabla correspondientes. El número de tales ganancias de la página de datos depende de la selectividad de la consulta y la correlación física entre los datos almacenados y el orden de índice (conocido como correlación). Una alta correlación significa que las ubicaciones de las hileras de las entradas de índice coinciden estrechamente con el orden de datos físicos, reduciendo las E/S aleatorias y haciendo que el índice sea más eficiente. Por otro lado, una baja correlación conduce a muchas recuperaciones aleatorias, aumentando el costo de E/S sustancialmente.
El costo de la CPU en escaneos de índice incluye procesar cada fila individual recuperada, pero la principal preocupación de costos es la E/S. También existe el aspecto de la visibilidad: si la base de datos tiene un seguimiento de mapas de visibilidad qué páginas de datos son completamente visibles para todas las transacciones, el sistema a veces puede omitir completamente la lectura de ciertas páginas durante los escaneos solo de índice, reduciendo drásticamente los costos de E/S.
En contraste, los escaneos de tabla o los escaneos de tabla completos funcionan leyendo todas las páginas de datos secuencialmente de la tabla. La E/S aquí es principalmente secuencial, lo que tiende a ser más rápido que la E/S aleatoria en los discos giratorios o menos penalizados en los SSD. El costo de un escaneo de tabla completo es relativamente constante porque lee la tabla completa independientemente del número de filas que satisfagan la consulta. Este costo depende del número total de páginas en la tabla en lugar del número de filas seleccionadas. Los escaneos de tabla completos no se benefician de la selectividad; Deben leer cada página incluso si la consulta filtra en gran medida las filas. Sin embargo, con las tecnologías de almacenamiento modernas y las optimizaciones como las lecturas de múltiples bloques, las lecturas paralelas y la descarga de la capa de almacenamiento, el costo de los escaneos de mesa completos puede ser competitivo o incluso menos que los escaneos de índice para consultas que recuperan una parte significativa de la tabla.
El optimizador basado en costos (CBO) decide entre usar un escaneo índice y un escaneo de tabla completo basado en costos estimados. Para consultas altamente selectivas, donde solo una pequeña fracción de filas debe recuperarse, los escaneos de índice tienden a tener costos de E/S más bajos porque se obtienen menos páginas de mesa. A medida que aumenta el umbral de selectividad (se necesitan más filas), el costo de E/S de los escaneos de índice aumenta debido a un mayor número de recuperaciones de páginas aleatorias y recorridos de índice, lo que eventualmente supere el costo de un escaneo de tabla completo. En este punto, el CBO favorece un escaneo de tabla porque el costo de E/S secuencial es menor que la carga de E/S aleatoria de muchas búsquedas de índice.
Otro factor importante que afecta la diferencia de costos de E/S es el "factor de agrupación" o la agrupación física de las filas correspondientes a las teclas de índice. Un factor de agrupación más bajo (mejor agrupación) significa que las filas a las que se accede a través del índice se encuentran físicamente entre sí, reduciendo las E/S aleatorias y mejorando la eficiencia del escaneo del índice. Un factor de agrupación más alto conduce a una E/S más aleatoria durante el escaneo índice y reduce su beneficio en comparación con un escaneo de tabla.
Los sistemas modernos de bases de datos también pueden implementar escaneos paralelos, tanto el índice como los escaneos de mesa completos donde los recursos de E/S y CPU se comparten entre múltiples trabajadores, lo que puede reducir el tiempo total de consulta. Sin embargo, la naturaleza fundamental de E/S para escaneos de índice (acceso aleatorio a la página) versus escaneos de tabla (en su mayoría acceso a la página secuencial) sigue siendo un diferenciador clave.
Para resumir los puntos clave sobre las diferencias de costos de E/S:
- Los escaneos de índice incurren en costos de E/S de las páginas de índice de lectura (acceso aleatorio) más obteniendo las páginas de datos correspondientes (acceso potencialmente aleatorio). El costo es sensible a la selectividad, la correlación y el factor de agrupación.
- Los escaneos de mesa completos realizan E/S secuenciales leyendo todas las páginas, con un costo de E/S relativamente estable, independientemente de la selectividad.
- Los escaneos de índice pueden superar cuando las consultas se dirigen a pequeños subconjuntos de datos, pero sufren de sobrecarga de E/S aleatoria a medida que aumenta la selectividad.
- Los escaneos de tabla completos pueden ser más eficientes cuando una fracción grande de la tabla necesita acceso debido a la E/S secuencial eficiente.
- Los mapas de visibilidad y los escaneos solo índices pueden reducir los costos de E/S en algunos casos de escaneo índice evitando las lecturas de la página de datos.
- El paralelismo puede mejorar ambos tipos de exploración, pero no cambia las características fundamentales de E/S.
- El optimizador equilibra estos factores de costo para elegir el método más eficiente basado en las características de consulta y datos.