de Martin LE TARNEC.

Escaneado limitado al índice: Evitar el acceso a la tabla


El escaneado limitado a índice es una de las técnicas de optimización más importantes. No solamente evita tener acceso a la tabla para evaluar el filtro where, sino que evita tener acceso a la tabla por completo si la base de datos puede encontrar todas las columnas seleccionadas dentro el índice.

Para cubrir una sentencia completa, un índice debe contener todas las columnas de la sentencia SQL; especialmente las columnas de la cláusula select como se muestra en el siguiente ejemplo:

CREATE INDEX sales_sub_eur
    ON sales
     ( subsidiary_id, eur_value )
SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

Por supuesto, indexar el filtro where prevalece sobre las otras cláusulas. Por lo tanto, la columna SUBSIDIARY_ID está en primera posición así que cumple los requisitos para realizar un predicado de acceso.

El plan de ejecución muestra el escaneo del índice sin un acceso posterior a la tabla (TABLE ACCESS BY INDEX ROWID).

El índice cubre la sentencia completa así que también se le llama índice de cobertura (en inglés, covering index).

Nota

Si un índice evita el acceso a la tabla también se le denomina índice de cubertura.

Sin embargo, el término es engañoso porque suena como una propiedad del índice. La frase "escaneado limitado al índice" indica que es una operación del plan de ejecución.

El índice tiene una copia de la columna EUR_VALUE, asi que la base de datos puede usar el valor almacenado dentro del índice. No se requiere tener acceso a la tabla porque el índice tiene toda la información necesaria para satisfacer la sentencia.

Escanear solamente el índice puede mejorar muchísimo el rendimiento. Únicamente viendo la estimación del número de registros en el plan de ejecución, el optimizador espera combinar más de 40.000 registros. Eso significa que el escaneo de sólo índice evita 40.000 búsquedas en la tabla en el caso de que cada registro esté en un bloque diferente de la tabla. Si la tabla tiene un factor de agrupación bueno, esto es si las filas correspondientes están agrupadas en pocos bloques de la tabla, la ventaja puede ser considerablemente menor.

A parte del factor de agrupación, el número de registros seleccionados limita el beneficio potencial de rendimiento del escaneo de sólo índice. Por ejemplo, si se selecciona un único registro, se puede ahorrar únicamente un solo acceso a la tabla. Considerando que el recorrido del árbol necesita recuperar varios bloques, el ahorro del acceso a la tabla puede llegar a ser insignificante.

Importante

La ventaja de rendimiento del escaneo de sólo índice depende tanto del número de filas leídas y del factor de agrupación.

El escaneo de sólo índice es una estrategia de indexación agresiva. No se diseña un índice para "sólo escanear el índice" únicamente “por si acaso”, porque sino se usaría sin necesidad la memoria y se incrementaría el esfuerzo de mantenimiento necesario para las sentencias update. Ver en el Capítulo 8, “Modificando los datos. En la práctica, se podría indexar primero sin considerar la cláusula select y solamente extender el índice si se necesita.

También causar sorpresas desagradables, por ejemplo, si se quiere limitar la sentencia a las ventas recientes:

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?
   AND sale_date > ?

Sin ver el plan de ejecución, uno esperaría que la sentencia se ejecuta más rápido porque se seleccionan pocas filas. Sin embargo, el filtro where hace referencia a una columna que no está dentro del índice, así que la base de datos debe tener acceso a la tabla para cargar esta columna.

El acceso a la tabla incrementa el tiempo de respuesta a pesar de que la sentencia selecciona pocas filas. El factor pertinente no es cuántas filas devolverá la sentencia, sino cuántas filas deberá examinar la base de datos para encontrar lo que se busca.

Aviso

Extender el filtro where puede causar un comportamiento de rendimiento "ilógico". Validar el plan de ejecución antes de extender las sentencias.

Si un índice ya no puede ser usado para un escaneado de sólo índice, el optimizador escogerá el siguiente mejor plan de ejecución. Eso significa que el optimizador podría seleccionar un plan de ejecución completamente diferente, o, como anteriormente, un plan de ejecución similar con otro índice. En este caso, se usa un índice sobre SALE_DATE, que es un "resto" del capítulo anterior.

Desde la perspectiva del optimizador, este índice tiene dos ventajas sobre SALES_SUB_EUR. El optimizador cree que el filtro sobre SALE_DATE es más selectivo que el filtro sobre SUBSIDIARY_ID. Se puede observar en la columna correspondiente "Rows" de los últimos dos planes de ejecución (desde 10,000 frente a 40,0000). Sin embargo, estas estimaciones son meramente arbitrarias porque la sentencia usa variables bind. Por ejemplo, la condición SALE_DATE podría seleccionar la tabla entera, si se provee la fecha de la primera venta.

La segunda ventaja del índice sobre SALES_DATE es que tiene un mejor factor de agrupación. Esa es una razón válida porque la tabla SALES solamente crece en orden cronológico. Siempre se agregan nuevas filas al final de la tabla mientras no se eliminen filas. Por lo tanto, el orden de la tabla corresponde al orden del índice porque ambos están ordenados aproximadamente en orden cronológico; por eso el índice tiene un factor de agrupación bueno.

Cuando se usa un índice con un factor de agrupación bueno, las filas seleccionadas de la tabla se almacenan casi juntas así que la base de datos sólo necesita leer pocos bloques de la tabla para obtenerlas todas. Usando este índice, la sentencia podría ser lo suficiente rápida sin usar un "escaneado de sólo índice". En este caso, nuevamente se deberían eliminar las columnas innecesarias del otro índice.

Nota

Algunos índices tienen un factor de agrupación bueno de forma automática así que la ventaja de rendimiento de escanear sólo el índice es mínima.

En este caso particular, no existe una feliz coincidencia. El nuevo filtro sobre SALE_DATE no solamente impide usar el escaneado limitado al índice, sino que al mismo tiempo abre un nuevo camino de acceso. El optimizador es capaz de limitar el impacto de rendimiento para este cambio. Sin embargo, también es posible evitar el escaneado limitado al índice agregando columnas sobre las otras cláusulas. De hecho, añadiendo una columna sobre la cláusula del select nunca se abrirá un nuevo camino de acceso que limite el impacto de la pérdida del "escaneado de sólo índice".

Sugerencia

Mantenimiento de tus "escaneados limitados al índice"

Escribe comentarios que te recuerden el escaneado limitado al índice y haz referencia a esta página, así cualquier persona podrá leerlo.

Los índices sobre expresiones también pueden causar sorpresas desagradables con el "escaneado limitado al índice". Un índice sobre UPPER(last_name) no puede ser usado por un "escaneado limitado al índice" cuando se selecciona la columna LAST_NAME. Según se vio en la sección anterior, se podría tener un índice sobre la columna LAST_NAME para soportar el filtro LIKE y permitir que sólo se escanee el índice cuando se selecciona sobre la columna LAST_NAME.

Sugerencia

Se ha de tener siempre el objetivo de indexar el dato original así como la información más útil dentro de un índice.

Evitar una índice sobre expresiones para las expresiones que no puedan ser usadas como predicado de acceso.

Combinar las sentencias LIKE en una sola, como se ha visto anteriormente, genera buenos candidatos para el escaneado limitado al índice. De este modo, se consultan muchas filas pero pocas columnas, lo que genera un índice pequeño y suficiente para soportar esta técnica. Cuantas más columnas se consultan, más columnas se tienen que agregar al índice para soportar el escaneado de sólo índice índice. Como desarrollador, se deben seleccionar solamente las columnas que sean realmente necesarias.

Sugerencia

Evitar select * y buscar solamente las columnas que se necesitan.

Indexar muchas filas requiere mucho espacio y, además, se puede alcanzar el límite de la base de datos. La mayoría de las bases de datos imponen un límite estático para el número de columnas dentro de un índice y para el tamaño total de entradas dentro de un índice. Eso significa que no pueden indexar un número caprichoso de columnas ni columnas de tamaño arbitrario. A pesar de todo, existen índices que cubren una tabla entera como veremos en la siguiente sección.

DB2

DB2 LUW limita un índice a 64 columnas con una longitud de clave máxima a 25% del tamaño de la página.

DB2 también soporta el INCLUDE clause para agregar columnas sin clave así que el índice puede ser usado por un "escaneado limitado al índice" sin cambiar la semántica de la clave única.

MySQL

MySQL con InnoDB limita la longitud total de clave (todas las columnas) a 3,072 bytes. Además, la longitud de cada columna se limita a 767 bytes si innodb_large_prefix no se habilita o si el formato de filas usado es diferente de DYNAMIC o COMPRESSED. Esos eran los valores por defecto hasta la versión MySQL 5.6. Los índices MyISAM se limitan a 16 columnas y una longitud máxima de clave a 1,000 bytes..

MySQL tiene una característica única denominada "prefijo de índice" (aveces también denominada "indexación parcial"). Eso significa que se indexan solamente los primeros caracteres de una columna, aunque no tiene nada que ver con los índices parciales descritos en el Capítulo 2. Si se indexa una columna que supera la longitud autorizada por columna (767, 1,000 o 3,072 bytes como se ha descrito antes), dependiendo del modo SQL y del formato de fila, MySQL podría truncar la columna. En este caso, el comando create index se ejecutará correctamente pero con un aviso "Clave especificada demasiada larga; la longitud máxima de la clave es ... bytes". Eso significa que el índice ya no tiene una copia completa de la columna; seleccionar la columna impide un 398escaneado limitado al índice (parecido a la función basada en índices).

Se puede utilizar un prefijo MySQL indexando de manera explícita para evitar superar el límite de la longitud total de la clave si se obtiene el aviso "Clave especificada demasiada larga; al longitud máxima de la clave es ... bytes". El siguiente ejemplo solo indexa los diez primeros caracteres de la columna LAST_NAME.

CREATE INDEX .. ON employees (last_name(10))
Oracle

La longitud máxima de una clave dentro del índice depende también del tamaño del bloque y de los parámetros de almacenamiento del índice (75% del tamaño de bloque de la base de datos menos el costo adicional). Un índice B-tree se limita a 32 columnas.

Cuando se usa Oracle 11g con todos los parámetros por defecto (bloques de 8k), la longitud máxima de una clave dentro del índice es de 6.398 bytes. Superado este límite aparece el mensaje de error “ORA-01450: longitud de clave máxima (6398) superada.”

PostgreSQL

La base de datos PostgreSQL soporta escaneado limitado al índice desde la versión 9.2 .

La longitud de clave de los índices B-tree se limita a 2,713 bytes (almacenado estáticamente, aproximadamente BLCKSZ/3). El mensaje de error específico “tamaño de fila del índice ... supera el máximo para el B-tree, 2,713” aparece solamente cuando se ejecuta un insert o update que exceda el limite. Los índices B-tree pueden incluir más de 32 columnas .

SQL Server

SQL Server limita la longitud de la clave a 900 bytes y 16 columnas. Sin embargo, SQL Server tiene una característica para permitir que se agreguen de forma arbitraria muchas columnas para un índice con la única finalidad de soportar escaneado limitado al índice. Para eso, SQL Server diferencia las columnas con clave y las que no.

Las columnas con clave ya se han explicado. Las columnas sin clave son columnas adicionales que están almacenadas dentro de los nodos hoja del índice. Pueden ser arbitrariamente grandes, pero no pueden usarse como predicados de acceso (predicados de búsqueda).

Las columnas sin claves se definen con la palabra clave include dentro del comando create index:

 CREATE INDEX empsubupnam
     ON employees
       (subsidiary_id, last_name)
INCLUDE(phone_number, first_name)

Si te gusta mi manera de explicar, te encantará mi libro.

Piénsalo

Las sentencias que no seleccionan ninguna columna de la tabla generalmente son ejecutadas mediante escaneado de sólo el índice.

¿Se puede pensar en un ejemplo más comprensible?

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia