de Martin LE TARNEC.

Índices concatenados


Aunque la base de datos crea de manera automática el índice sobre la llave primaria, puede ser útil, para una afinación manual, crear un índice sobre todo si está compuesto por varias columnas. En este caso, la base de datos crea un índice sobre todas las columnas de la llave primaria, que se llama índice índice multi-columna (también conocido como índice compuesto o índice combinado). Apunten que el orden de las columnas en los índices concatenados tiene un impacto positivo sobre su uso, así que se debe escoger con cuidado.

Por el propósito de la demostración, vamos a suponer que nuestra empresa se fusiona con otra. Al agregar los empleados de la otra empresa a nuestra tabla de EMPLOYEES, esta crecerá 10 veces. Existe un solo problema: el EMPLOYEE_ID no es único entre las dos empresas. Debemos extender la llave primaria con un identificador adicional, un ID secundario. De este modo, la nueva llave primaria tendrá dos columnas: el EMPLOYEE_ID como antes y el SUBSIDIARY_ID para restablecer la unicidad.

El índice para la nueva llave primaria se define de la manera:

CREATE UNIQUE INDEX employee_pk
    ON employees (employee_id, subsidiary_id)

Una sentencia para un empleado particular usa la llave primaria completa dentro de una cuenta, lo que quiere decir que se usará también la columna SUBSIDIARY_ID:

SELECT first_name, last_name
  FROM employees
 WHERE employee_id   = 123
   AND subsidiary_id = 30

Cada vez que una sentencia utiliza la llave primaria completa, la base de datos puede usar un INDEX UNIQUE SCAN sin importar cuántas columnas tenga el índice. Pero, ¿qué pasa cuando se usa solamente una de las columnas de la llave, por ejemplo, cuando se buscan todos los empleados de la llave secundaria?

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

El plan de ejecución revela que la base de datos no usó el índice. En su lugar, se empleó TABLE ACCESS FULL. Como consecuencia, la base de datos lee toda la tabla y evalúa cada registro con el filtro where. El tiempo de ejecución crece con el tamaño de la tabla: si la tabla crece diez veces, TABLE ACCESS FULL tardará diez veces más. El peligro de esta operación es que se ejecute bastante rápidamente en un ambiente pequeño de prueba, pero que genere graves problemas de rendimiento en producción.

FULL TABLE SCAN

La operación TABLE ACCESS FULL, también conocida como FULL TABLE SCAN puede ser de todos modos más eficiente, en particular cuando se requiere devolver una parte importante de la tabla.

Eso se debe en parte a que una búsqueda sobre el índice origina una sobrecarga producida por la misma búsqueda sobre el índice, que no es generado con la operación TABLE ACCESS FULL. Generalmente es el caso porque la búsqueda sobre un índice lee un bloque tras el otro. Ello se debe a que la base de datos no sabe qué bloque será leído después procesar uno de ellos. Un FULL TABLE SCAN debe obtener de todas formas toda la tabla pero la base de datos puede leer varios bloques ("chunks") grandes durante un tiempo de lectura (lectura multi bloque). Aunque la base de datos lee más datos, podría necesitar menos operaciones de lectura.

La base de datos no usa el índice porque no puede emplear una columna sencilla desde un índice concatenado de forma arbitraria. Hacer un análisis detallado sobre la estructura del índice ayudará a aclarar el proceso.

Un índice concatenado es solamente un índice B-tree como cualquier otro conservando los datos indexados dentro de una lista ordenada. La base de datos considera cada columna de acuerdo con su posición en la definición del índice para ordenar las entradas del mismo. La primera columna es el primer criterio de ordenamiento y la segunda columna determina el orden solamente si dos entradas tienen el mismo valor en la primera columna.

Importante

Un índice concatenado es un solo índice compuesto por varias columnas.

El orden de un índice con dos columnas es por lo tanto como el de una guía telefónica: está ordenado primero por apellidos, y después por nombres. Eso quiere decir que un índice con dos columnas no soporta una búsqueda únicamente sobre la segunda columna; es como si no se pudiera buscar por nombres dentro de una guía telefónica.

Figura 2.1 índice concatenado

El fragmento del índice en la imagen de la Figura 2.1 muestra que las entradas para el ID secundario 20 no están almacenadas unas al lado de otro. Es evidente también que no hay entradas para SUBSIDIARY_ID = 20 dentro del árbol, aunque existe en los nodos hojas. El árbol es inútil para esta sentencia.

Sugerencia

Visualizar un índice ayuda a entender qué sentencias utilizará el mismo. Se puede consultar la base de datos para recuperar las entradas en el mismo orden que el índice. (para ver la sintaxis de SQL 2008 para las soluciones propietarias usando LIMIT, TOP or ROWNUM):

SELECT <INDEX COLUMN LIST> 
  FROM <TABLE>  
 ORDER BY <INDEX COLUMN LIST>
 FETCH FIRST 100 ROWS ONLY

Si viene la definición del índice y el nombre de la tabla dentro de la sentencia, obtendrás una muestra desde el índice. La pregunta ahí es saber si los registros solicitados son centralizados en un solo lugar. Si no es el caso, el árbol del índice no ayudará a encontrar este lugar.

Se podría, obviamente, agregar otro índice sobre la columna SUBSIDIARY_ID para mejorar el tiempo de respuesta. Existe, sin embargo, una solución mejor al menos si se supone que buscar solamente sobre la columna EMPLOYEE_ID no tiene sentido.

Se puede tomar ventaja del hecho de que la primera columna del índice es siempre utilizable para buscar. Nuevamente, es como una guía telefónica: no se necesita conocer el nombre para buscar por apellido. El truco es invertir el orden de las columnas del índice y así la columna SUBSIDIARY_ID se encontrará en primer lugar:

CREATE UNIQUE INDEX EMPLOYEES_PK 
    ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID)

Ambas columnas son todavía únicas así que para consultar sobre toda la llave primaria se puede usar aún un INDEX UNIQUE SCAN pero la secuencia de las entradas del índice es completamente diferente. La columna SUBSIDIARY_ID ya es el primer criterio de ordenamiento. Eso significa que todas las entradas para el ID secundario son consecutivas dentro del índice, así que la base de datos podrá usar el índice B-tree para encontrar su ubicación.

Importante

La consideración más importante cuando se define un índice concatenado es cómo escoger el orden de las columnas, así que se deberá elegir la que puede ser más usada.

El plan de ejecución confirma que la base de datos utiliza el índice invertido. La columna SUBSIDIARY_ID sola ya no es única, por lo que la base de datos debe seguir los nodos hojas en orden para encontrar todas las entradas correctas: se realiza usando la operación INDEX RANGE SCAN.

DB2

Explain Plan
-------------------------------------------------------------
ID | Operation               |                    Rows | Cost
 1 | RETURN                  |                         |  128
 2 |  FETCH EMPLOYEES        |  1195 of 1195 (100.00%) |  128
 3 |   RIDSCN                |  1195 of 1195 (100.00%) |   43
 4 |    SORT (UNQIUE)        |  1195 of 1195 (100.00%) |   43
 5 |     IXSCAN EMPLOYEES_PK | 1195 of 10000 ( 11.95%) |   43

Predicate Information
 2 - SARG (Q1.SUBSIDIARY_ID = +00002.)
 5 - START (Q1.SUBSIDIARY_ID = +00002.)
      STOP (Q1.SUBSIDIARY_ID = +00002.)

Este plan de ejecución se ve más complejo que el anterior. Sin embargo, las operaciones clave siguen aquí: IXSCAN representa el INDEX RANGE SCAN y FETCH para el acceso a la tabla. Entre estas operaciones existen una operación inesperada SORT y una operación RIDSCN : la operación SORT ordena las entradas devueltas desde el índice de acuerdo con la ubicación física de los registros dentro de la pila de datos de la tabla; RIDSCAN busca después todas las paginas de la base de datos afectadas (se leen varios bloques contiguos dentro de un sola operación I/O).

MySQL

+----+-----------+------+---------+---------+------+-------+
| id | table     | type | key     | key_len | rows | Extra |
+----+-----------+------+---------+---------+------+-------+
|  1 | employees | ref  | PRIMARY | 5       |  123 |       |
+----+-----------+------+---------+---------+------+-------+

El tipo de acceso de MySQL ref es equivalente a INDEX RANGE SCAN en las bases de datos Oracle.

Oracle

--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |  106 |   75 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |  106 |   75 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEE_PK |  106 |    2 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=20)

PostgreSQL

                 QUERY PLAN
----------------------------------------------
 Bitmap Heap Scan on employees
 (cost=24.63..1529.17 rows=1080 width=13)
   Recheck Cond: (subsidiary_id = 2::numeric)
   -> Bitmap Index Scan on employees_pk
      (cost=0.00..24.36 rows=1080 width=0)
      Index Cond: (subsidiary_id = 2::numeric)

La base de datos PostgreSQL usa en este caso dos operaciones: Bitmap Index Scan seguido por un Bitmap Heap Scan. Son muy similares a las operaciones de Oracle INDEX RANGE SCAN y TABLE ACCESS BY INDEX ROWID con una diferencia importante: se va primero a buscar todos los resultados desde el índice (Bitmap Index Scan), después se ordenan los registros según la ubicación física en el almacenamiento de los registros en la pila de los datos de la tabla (heap table) y finalmente buscará todos los registros desde la tabla (Bitmap Heap Scan). Este método reduce el número de accesos de I/O en la tabla.

SQL Server

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:employees_pk,
   |               SEEK:subsidiary_id=20
   |            ORDERED FORWARD)
   |--RID Lookup(OBJECT:employees,
                   SEEK:Bmk1000=Bmk1000
                 LOOKUP ORDERED FORWARD)

Generalmente, la base de datos puede usar un índice concatenado cuando se busca sobre la columna más destacada (la que está más a la izquierda). Un índice con tres columnas puede utilizarse cuando se busca sobre la primera columna, cuando se busca sobre las dos primeras columnas, y cuando se busca sobre todas las columnas.

Aunque la solución de los dos índices da muy buenos resultados para la operación select, es preferible la solución de un único índice. No es solamente para ahorrar espacio, sino también para el mantenimiento del segundo índice. Cuantos menos índices tenga una tabla, mejor rendimiento darán las instrucciones insert, delete y update.

Para definir un índice óptimo, se debe saber no sólo cómo funcionan los índices; también se tiene que aprender cómo consultan las sentencias los datos de la aplicación. Eso significa que se han de conocer todas las combinaciones posibles de las columnas, cuyas aparecen como filtro en el where.

Por eso, resulta muy difícil definir un índice óptimo para un consultor externo porque no tiene una visión de conjunto de los accesos de la aplicación. Los consultores generalmente pueden considerar una y solamente una sentencia. No pueden explotar el beneficio adicional; cómo podría ayudar el índice a las demás sentencias. El administrador de base de datos (DBA) está en la misma posición porque aunque conoce el esquema de la base de datos no tiene un conocimiento profundo acerca de los caminos de acceso.

El único lugar donde el conocimiento técnico de la base de datos y el conocimiento funcional del negocio convergen es en el departamento de desarrollo. Los desarrolladores tienen el conocimiento de los datos y conocen los caminos de acceso a los mismos. Son quienes pueden indexar correctamente para obtener el máximo beneficio de la aplicación y además lograrlo sin mucho esfuerzo.

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

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