de Martin LE TARNEC.

Índices Lentos, Parte II


La sección anterior explica cómo ganar un beneficio adicional desde un índice existente cambiando el orden de las columnas, pero el ejemplo considera solamente dos sentencias. Cambiar un índice, sin embargo, podría afectar todas las sentencias de la tabla indexada. Esta sección explica la manera en que la base de datos escoge un índice y muestra los posibles efectos cuando se cambian índices ya existentes.

El índice EMPLOYEE_PK ya aceptado mejora el rendimiento de todas las sentencias que buscan solamente por el ID secundario. Además es útil para las sentencias que buscan por SUBSIDIARY_ID, a pesar de los criterios adicionales para la búsqueda. Eso significa que el índice llega a ser utilizable para las sentencias que son usadas por otros índices con una otra parte del filtro where. En este caso, si hay varios caminos de acceso disponibles, el trabajo del optimizador es escoger el mejor.

El optimizador de sentencias

El optimizador de sentencias u organizador de sentencias, es un componente de la base de datos que convierte la sentencia SQL en un plan de ejecución. Este proceso es conocido también como compilador o análisis sintácticamente. Existen dos tipos de optimizadores.

Optimizadores basados en el costo (CBO) generan muchas variantes del plan de ejecución y calculan un valor de costo por cada plan. El cálculo del costo se basa en las operaciones en uso y estima el número de registros. Al final, el valor del costo sirve como referencia para escoger el “mejor” plan de ejecución.

Optimizadores basados en reglas (RBO) genera un plan de ejecución usando un grupo de reglas programadas de forma rígida. Los optimizadores basados en reglas son menos flexibles y a día de hoy suelen emplearse sólo excepcionalmente.

Cambiar un índice puede tener un efecto indeseado. En nuestro ejemplo, tenemos una guía telefónica que llegó a ser muy lenta después de la fusión. El primer análisis identifica la siguiente sentencia como causa de la lentitud:

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

El plan de ejecución es el siguiente:

Ejemplo 2.1 El plan de ejecución con la llave primaria ya corregida

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |   30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |   30 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK |   40 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

El plan de ejecución utiliza un índice y tiene un costo global de 30. Por ahora, todo bien. Sin embargo, es sospechoso porque usa el índice que apenas ha cambiado, una razón suficiente para presumir que nuestro índice cambia el problema de rendimiento provocado, especialmente cuando el índice anterior empezó con la columna EMPLOYEE_ID que no es parte del filtro where. La sentencia no puede usar el índice como estaba antes.

Para un análisis más profundo, sería bueno comparar el plan de ejecución antes y después del cambio. Para obtener el primer plan de ejecución, se podría hacer uso nuevamente del índice anterior. Sin embargo, la mayoría de las bases de datos ofrecen un método para evitar que una sentencia use un índice particular. El siguiente ejemplo utiliza una sugerencia (“hint”) con este propósito.

SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEE_PK) */ 
       first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

El plan de ejecución que presuntamente se utilizó antes del cambio sobre el índice no usó el índice en absoluto:

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)

Aunque TABLE ACCESS FULL debe leer y procesar la tabla entera, en este caso parece ser más rápido usar un índice. Eso es particularmente inusual porque la sentencia devuelve solamente un registro. utilizar un índice para encontrar un solo registro puede ser más rápido que un escaneo entero de la tabla, pero en este caso no lo es. El índice parece ser lento.

En estos casos, es mejor ir por cada etapa problemática del plan de ejecución. La primera etapa es INDEX RANGE SCAN sobre el índice EMPLOYEES_PK. Este índice no cubre la columna LAST_NAME. INDEX RANGE SCAN puede considerar que la columna SUBSIDIARY_ID es solamente un filtro; la base de datos Oracle lo muestra en las informaciones de predicados (“predicate information”) y tiene la entrada “2” en el plan de ejecución. Ahí es donde se pueden ver las condiciones que se aplicaron por cada operación.

Sugerencia

Apéndice A, “Planes de ejecución, explica como encontrar la información de los predicados para las otras bases de datos

El INDEX RANGE SCAN con el ID de operación “2” (Ejemplo 2.1) aplica solamente el filtro SUBSIDIARY_ID=30. Eso significa que atraviesa el árbol del índice para encontrar la primera entrada de SUBSIDIARY_ID con 30. Luego, sigue el nodo hoja para encontrar las otras entradas por este ID secundario. El resultado del INDEX RANGE SCAN es una lista de ROWIDs que cumplen con la condición SUBSIDIARY_ID: dependiendo del tamaño del ID secundario, es posible que sean solamente algunos o varios centenares.

La siguiente etapa es la operación TABLE ACCESS BY INDEX ROWID. Usa los ROWIDs devueltos por la etapa anterior para traer los registros (todas las columnas) de la tabla. Una vez que la columna LAST_NAME está disponible, la base de datos puede evaluar la parte restante del filtro where. Eso significa que la base de datos tiene que traer todos los registros del SUBSIDIARY_ID=30 antes de aplicar el filtro sobre LAST_NAME.

El tiempo de respuesta de la sentencia no depende del tamaño del resultado sino del número de empleados con el ID secundario particular. Si el ID secundario tiene solamente algunos miembros, INDEX RANGE SCAN provee un mejor rendimiento. Sin embargo, un TABLE ACCESS FULL puede ser más rápido para una cantidad gigante de ID secundario porque puede leer una parte amplia de la tabla de una sola vez (ver FULL TABLE SCAN).

La sentencia es lenta porque la búsqueda (“LOOKUP”) del índice devuelve muchos ROWIDs, uno por cada empleado de la empresa original, y la base de datos debe traerlos de forma individual. Es la combinación perfecta para crear un índice lento: la base de datos lee un rango amplio del índice y tiene que devolver muchos registros de forma individual.

Escoger la mejor solución depende de la distribución de los datos en la tabla y la manera en que el optimizador usa las estadísticas acerca del contenido de la base de datos. En nuestro ejemplo, se usa un histograma, que contiene la distribución de los empleados por cada ID secundario. Eso permite al optimizador estimar el número de registros devueltos desde la búsqueda (“LOOKUP”) del índice (el resultado está usado para el cálculo del costo).

Estadísticas

Un optimizador basado en el costo usa las estadísticas acerca de las tablas, columnas y índices. La mayoría de las estadísticas son recolectadas a nivel de columna: el número de valores distintos, el valor más pequeño y el más grande (rango de datos), el número de NULL y los histogramas de las columnas (distribución de datos). La estadística más importante para la tabla es su tamaño (en registros y bloques).

La estadística más importante para el índice es la profundidad del árbol, el número de nodos hojas, el número de llaves diferentes y el factor de clúster (“clustering factor”). (ver Capítulo 5, “Agrupación de datos).

El optimizador usa esos valores para estimar la selectividad de los predicados del filtro where.

Si no existen estadísticas disponibles, por ejemplo porque la información se ha borrado, el optimizador usa valores por defecto. Los valores por defecto de las estadísticas para la base de datos Oracle sugieren un índice pequeño con una selectividad mediana. Se guían para la estimación por el hecho de que INDEX RANGE SCAN devolverá 40 registros. El plan de ejecución muestra esta estimación en las columnas de los registros (nuevamente, ver Ejemplo 2.1). Por supuesto, se trata de una subestimación enorme, porque hay 1 000 empleados trabajando para este ID secundario.

Si proveemos estadísticas correctas, el optimizador hace un trabajo mejor. El siguiente plan de ejecución muestra una nueva estimación: 1,000 registros para el INDEX RANGE SCAN. En este caso, el optimizador calcula un valor del costo mayor para el acceso a la tabla.

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |  680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |  680 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK | 1000 |    4 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

El costo con valor “680” es más alto que el valor del costo del plan de ejecución usando el FULL TABLE SCAN (477). Por lo tanto, al optimizador le gustará el FULL TABLE SCAN.

Este ejemplo de índice lento no oculta el hecho de que el índice correcto es la mejor solución. Por supuesto, buscar un apellido lo soporta mejor un índice si se indexa la columna LAST_NAME:

CREATE INDEX emp_name ON employees (last_name)

Al usar el nuevo índice, el optimizador calcula un valor del costo de 3:

Ejemplo 2.2 Plan de ejecución con un índice dedicado

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUBSIDIARY_ID"=30)
   2 - access("LAST_NAME"='WINAND')

El acceso al índice devuelve (de acuerdo con la estimación del optimizador) solamente un registro. La base de datos tiene que traer solamente este registro desde la tabla: es definitivamente más rápido que FULL TABLE SCAN. Un índice correctamente definido es aún mejor que el escaneo entero de la tabla propuesto originalmente.

Los dos planes de ejecuciones propuestos en el Ejemplo 2.1 y Ejemplo 2.2 son casi idénticos: la base de datos realiza las mismas operaciones y el optimizador calcula valores similares para el costo. Sin embargo el segundo plan es mejor. La eficiencia de INDEX RANGE SCAN puede variar en un amplio rango, especialmente cuando requiere a continuación un acceso a la tabla. Usar un índice no implica necesariamente que la sentencia se ejecute de la mejor manera posible.

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