El uso de índices y escaneos de tabla de rendimiento son dos enfoques fundamentales para acceder a los datos en una base de datos, cada una con distintas compensaciones dependiendo de la situación.
Un índice en una base de datos es una estructura de datos que permite una búsqueda más rápida al permitir que el sistema localice rápidamente filas sin escanear cada fila de la tabla. La mayoría de las bases de datos relacionales utilizan estructuras de árboles B+ para índices, que organizan claves y punteros en forma de árbol. Esto permite búsquedas, inserciones y deleciones en complejidad del tiempo logarítmico $$ o (\ log n) $$, que generalmente es mucho más rápido que escanear toda la tabla con una complejidad de $$ o (n) $$. Los índices pueden agruparse o no agruparse, con índices agrupados que almacenan datos en orden clasificado físicamente, mejorando el rendimiento del escaneo de rango a costa de sobrecarga adicional en las modificaciones de datos. Los índices también pueden ser compuestos, parciales, filtrados o basados en hash, sintonizados para patrones de consulta específicos.
Por el contrario, un escaneo de tabla (o escaneo de tabla completo) lee cada fila de la tabla secuencialmente, independientemente de la selectividad de la consulta. Esto implica escanear todos los bloques de datos de la tabla y a menudo se considera el método de acceso más costoso porque procesa más datos de los necesarios. Sin embargo, los escaneos de tabla pueden funcionar bien en ciertos casos. Por ejemplo, cuando las consultas recuperan un gran porcentaje de filas, la sobrecarga del uso de un índice (que a menudo requiere búsquedas adicionales para las filas reales) puede exceder el costo de escanear toda la tabla una vez. Los escaneos de tabla pueden hacer uso de lecturas de múltiples bloques, que permiten leer grandes fragmentos de datos con menos operaciones de E/S, reduciendo así la latencia en comparación con la lectura de muchos bloques individuales requeridos aleatoriamente por los escaneos de índice.
Una compensación importante implica la selectividad y el tamaño del conjunto de datos devuelto por la consulta. Si la consulta se filtra a un pequeño número de filas (alta selectividad), los índices generalmente superan a los escaneos de la tabla porque solo necesitan acceder a los datos relevantes. Pero a medida que aumenta el porcentaje de filas, aumenta el costo de los escaneos de índice ya que se pueden requerir múltiples búsquedas clave, y el motor de la base de datos debe realizar operaciones de E/S aleatorias adicionales. En algún umbral, a menudo alrededor del 10-20% de las filas de la tabla, pero dependen del ancho de los datos y el hardware, un escaneo de tabla completo se vuelve más eficiente. Esto se debe a que los costos de escaneo permanecen constantes independientemente de la selectividad, simplemente leyendo la tabla secuencialmente una vez.
Los escaneos de índice generalmente leen menos páginas que un escaneo de tabla cuando las columnas cubiertas son menos o más compactos que las filas de tabla completas. Por ejemplo, un índice puede incluir solo las columnas indexadas sin los datos completos de la fila de la tabla, haciéndolo más delgado y permitiendo que más filas se ajusten en cada página de la base de datos. Esto reduce la sobrecarga de E/S al escanear el índice en comparación con escanear los datos completos de la tabla. Además, algunos índices se pueden filtrar (índices parciales) para excluir filas irrelevantes, reduciendo aún más la huella de escaneo.
Por otro lado, los escaneos de mesa completos escriben menos carga en el lado de mantenimiento de la base de datos. Los índices introducen gastos generales durante las operaciones de modificación de datos, como insertar, actualizar y eliminar. Cada cambio en la tabla requiere actualizar los índices, a veces lo que lleva a una mayor latencia de escritura y sobrecarga de almacenamiento, particularmente si existen muchos índices en la tabla. Esta sobrecarga también puede afectar la concurrencia y conducir a la contención en entornos de escritura pesados. Por lo tanto, los escaneos de tabla, que simplemente leen los datos en su orden natural sin mantenimiento adicional de la estructura, eviten este costo.
Otra consideración importante es el efecto del almacenamiento en caché y las características del hardware. Los escaneos de tabla se benefician de la E/S secuencial y la captación previa, lo que permite que el sistema lea múltiples bloques contiguos de manera eficiente, a menudo de la memoria si se almacena en caché. Por el contrario, los escaneos de índice incurren en E/S aleatorias para obtener bloques de datos dispares, especialmente si el escaneo índice tiene que buscar punteros de fila en el almacenamiento de montón. Esto puede hacer que los escaneos de índice sean más lentos en los sistemas con un rendimiento de E/S aleatorio de disco más lento, aunque los SSD y los grandes grupos de memoria reducen esta brecha. La situación también puede depender de detalles como el paralelismo y las capacidades de subprocesos múltiples del motor de la base de datos, donde los escaneos de tabla paralelos pueden aumentar significativamente el rendimiento.
Además, la fragmentación interna y el diseño de almacenamiento físico influyen en las compensaciones de rendimiento. Los escaneos de tabla en las tablas organizadas por montón pueden sufrir registros reenviados, donde las filas se han movido a diferentes páginas debido a actualizaciones, empeorando la eficiencia del escaneo. Los índices agrupados, que almacenan datos ordenados por clave, pueden evitar este problema y, a veces, hacer que un "escaneo de tabla" sea equivalente a un escaneo de índice agrupado. Sin embargo, los beneficios vienen con el costo de los reordenos de la fila costosos durante la rotación de datos pesados.
Desde una perspectiva del optimizador de consulta, la decisión entre un escaneo índice y un escaneo de tabla se realiza típicamente mediante modelos de estimación basados en costos, teniendo en cuenta estadísticas sobre la distribución de datos, los recuentos de filas y los costos de hardware. El optimizador equilibra los costos de CPU, E/S y memoria para elegir la ruta de acceso más eficiente. Estas decisiones pueden verse influenciadas por factores como la memoria disponible, el estado de almacenamiento en caché y los patrones de consulta. No hay un umbral fijo entre cuándo usar uno u otro; El punto de cruce varía según el sistema y la carga de trabajo.
En resumen, las compensaciones entre el uso de índices y escaneos de tabla incluyen:
- Rendimiento versus volumen de datos: los índices superan al filtrarse a pocas filas; Los escaneos de tabla pueden ser mejores para la recuperación de datos grandes.
- Patrones de E/S: los escaneos de índice causan lecturas aleatorias de E/S; Los escaneos de tabla se benefician de la E/S secuencial y las lecturas de múltiples bloques.
- Gastos generales de mantenimiento: los índices aumentan los costos de operación de escritura debido a las actualizaciones de las estructuras de índice; Los escaneos de mesa no incurren en esto.
- Eficiencia de almacenamiento: los índices pueden ser compactos cubriendo menos columnas; Los escaneos de tabla procesan filas completas y potencialmente más datos.
- Efectos de almacenamiento en caché: los escaneos de tabla pueden utilizar el almacenamiento en caché de datos de manera efectiva, especialmente con grandes lecturas secuenciales; Es posible que los escaneos de índice no se beneficien tanto debido al acceso aleatorio.
- Decisión del optimizador: optimizadores de consultas basados en costos Elija dinámicamente entre estas opciones en función de las estadísticas de consulta y los detalles de la carga de trabajo.
- Impacto del diseño de datos: las tablas de montón pueden incurrir en sanciones como registros reenviados durante los escaneos; Los índices agrupados organizan datos físicamente pero aumentan los costos de actualización.