El mayor riesgo de rendimiento sobre un INDEX RANGE
SCAN es que sea recorrido el nodo
hoja. Por lo tanto, la regla de oro indexando es mantener el
rango escaneado del índice lo más pequeño posible. Se puede validar
viendo dónde empieza el escaneo y dónde termina.
La cuestión es fácil de contestar si la sentencia SQL menciona de manera explícita las condiciones de inicio y de fin:
SELECT first_name, last_name, date_of_birth
FROM employees
WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')Un índice sobre DATE_OF_BIRTH es escaneado solamente
dentro del rango especificado. El escaneo empieza a partir de la primera
fecha y termina en la segunda. No se puede reducir más el rango del
escaneo.
Las condiciones de inicio y de fin son menos evidentes si una segunda columna empieza de la condición:
SELECT first_name, last_name, date_of_birth
FROM employees
WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')
AND subsidiary_id = ?Por supuesto, un índice ideal puede cubrir ambas columnas, pero la pregunta entonces es ¿cuál es el orden?
A continuación se muestra el efecto del orden de las columnas sobre el rango escaneado por el índice. Desde esta figura, buscamos todos los empleados que tienen el ID secundario 27 y nacieron entre el 1ro de enero y el 9 de enero del 1971.
La Figura 2.2 expone el detalle del índice sobre
DATE_OF_BIRTH y SUBSIDIARY_ID en este orden.
¿Dónde empezará la base de datos a seguir la cadena del nodo hoja? O lo
expresará de otra manera: ¿dónde terminará el recorrido del árbol?
Figura 2.2 Escaneo de rango sobre el índice DATE_OF_BIRTH,
SUBSIDIARY_ID
El índice está ordenado primero por las fechas de cumpleaños.
Únicamente si dos empleados nacieron el mismo día, se usará la columna
SUBSIDIARY_ID para ordenar los registros. La sentencia, sin
embargo, cubre un rango de fechas. El ordenamiento
de SUBSIDIARY_ID es también inútil durante el recorrido a
través del índice. Eso es obvio si se tiene en cuenta que no existen registros
para el ID secundario 27 en el nodo rama, aunque existe uno dentro del
nodo hoja. El filtro sobre DATE_OF_BIRTH es también la
única condición que limita el rango escaneado dentro del índice. Empieza
desde el primer registro que cumple y termina en el último (los 5 nodos
hojas se muestran en la Figura 2.2).
La imagen aparece totalmente diferente cuando invertimos el orden
de las columnas. La Figura 2.3 ilustra el escaneo si el índice
empieza con la columna SUBSIDIARY_ID.
Figura 2.3 Escaneo de rango sobre el índice SUBSIDIARY_ID,
DATE_OF_BIRTH
La diferencia es que los operadores de igualdad limitan la columna
del primer índice a un solo valor. Dentro del rango para ese valor
(SUBSIDIARY_ID 27), el índice está ordenado de acuerdo con
la segunda columna -la fecha de cumpleaños. Aquí no hay necesidad de ver
el primer nodo hoja porque el nodo rama ya indica para el ID secundario
27 que no existen empleados, ya que estos nacieron después del 25 de
junio 1969.
El recorrido a través del árbol guía directamente hacia el segundo
nodo hoja. En este caso, todos los filtros where limitan el rango escaneado dentro del
índice así que el escaneo termina en el mismo nodo hoja.
Sugerencia
Regla general: indexa primero por igualdad - después por rangos.
La diferencia actual de rendimiento depende de los datos y del
criterio de búsqueda. La diferencia puede ser insignificante si el
filtro sobre DATE_OF_BIRTH es muy selectivo. Cuanto mayor
sea el rango, más diferencia de rendimiento se notará.
Con este ejemplo, se puede también refutar el mito que
dice que la columna más selectiva debe estar en la posición más a la
izquierda del índice. Si echamos un vistazo a la imagen y consideramos
la selectividad de la primera columna solamente, se ve que ambas
condiciones devuelven 13 registros. Es el caso donde se puede filtrar,
sin importar, por la columna DATE_OF_BIRTH sola o por la
columna SUBSIDIARY_ID sola. La selectividad no es útil
aquí, pero un orden de columna es mejor que el otro.
Para optimizar el rendimiento, es muy importante saber el rango
escaneado dentro del índice. En la mayor parte de las bases de datos éste
se puede ver en el plan de ejecución y se debe solamente entender cómo
buscarlo. El siguiente plan de ejecución de la base de datos de
Oracle indica, sin ambigüedad, que el escaneo del índice
EMP_TEST empieza con la columna
DATE_OF_BIRTH.
- Oracle
-------------------------------------------------------------- |Id | Operation | Name | Rows | Cost | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | |*1 | FILTER | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 4 | |*3 | INDEX RANGE SCAN | EMP_TEST | 2 | 2 | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:END_DT >= :START_DT) 3 - access(DATE_OF_BIRTH >= :START_DT AND DATE_OF_BIRTH <= :END_DT) filter(SUBSIDIARY_ID = :SUBS_ID)- PostgreSQL
QUERY PLAN ------------------------------------------------------------------- Index Scan using emp_test on employees (cost=0.01..8.59 rows=1 width=16) Index Cond: (date_of_birth >= to_date('1971-01-01','YYYY-MM-DD')) AND (date_of_birth <= to_date('1971-01-10','YYYY-MM-DD')) AND (subsidiary_id = 27::numeric)La base de datos PostgreSQL no indica los accesos de los índices y los filtros predicados en el plan de ejecución. Sin embargo, la sección
Index Condlista las columnas en el orden de la definición del índice. En este caso, se ve el predicadoDATE_OF_BIRTHprimero y despuésSUBSIDIARY_ID. Como ningún predicado siguiendo una condición de rango puede ser un predicado de acceso, entonces elSUBSIDIARY_IDdebe ser un filtro de predicado (ver “Distinguir los predicados de acceso y de filtro” para más detalles.- SQL Server
|--Nested Loops(Inner Join) |--Index Seek(OBJECT:emp_test, | SEEK: (date_of_birth, subsidiary_id) | >= ('1971-01-01', 27) | AND (date_of_birth, subsidiary_id) | <= ('1971-01-10', 27), | WHERE:subsidiary_id=27 | ORDERED FORWARD) |--RID Lookup(OBJECT:employees, SEEK:Bmk1000=Bmk1000 LOOKUP ORDERED FORWARD)SQL Server 2012 muestra el predicado "SEEK"(=acceso de predicados) usando la sintaxis del valor del registro..
La información de predicado para
INDEX RANGE SCAN da una “pista” esencial. Identificar las
condiciones de los filtros where como
un predicado de acceso o como un predicado de
filtro. Es la manera en la que la base de datos nos
indica cómo se usa cada operación.
Las condiciones sobre la columna DATE_OF_BIRTH son
solamente algunos de los predicados de acceso listados; éstos limitan el
rango del índice escaneado. DATE_OF_BIRTH es por lo
tanto la primera columna dentro del índice EMP_TEST. La
columna SUBSIDIARY_ID está usada sólo como
filtro.
Importante
El predicado de acceso es la condición de inicio y de fin para una búsqueda (“LOOKUP”) sobre el índice. Estos definen el rango escaneado dentro del índice.
Los predicados de filtros sobre índice se aplican solamente durante el recorrido a través del nodo hoja. No reducen el rango escaneado dentro del índice.
El apéndice explica cómo identificar los accesos de predicados en MySQL, SQL Server y PostgreSQL.
La base de datos puede usar todas las condiciones como predicados de acceso si cambiamos la definición del índice:
- Db2 (LUW)
----------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | FETCH EMPLOYEES | 3 of 3 (100.00%) | 13 3 | IXSCAN EMP_TEST2 | 3 of 10000 ( .03%) | 6 Predicate Information 3 - START (Q1.SUBSIDIARY_ID = ?) START ( TO_DATE(?, 'YYYY-MM-DD') <= Q1.DATE_OF_BIRTH) STOP (Q1.SUBSIDIARY_ID = ?) STOP (Q1.DATE_OF_BIRTH <= TO_DATE(?, 'YYYY-MM-DD'))- Oracle
--------------------------------------------------------------- | Id | Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | |* 1 | FILTER | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 | |* 3 | INDEX RANGE SCAN | EMP_TEST2 | 1 | 2 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:END_DT >= :START_DT) 3 - access(SUBSIDIARY_ID = :SUBS_ID AND DATE_OF_BIRTH >= :START_DT AND DATE_OF_BIRTH <= :END_T)- PostgreSQL
QUERY PLAN ------------------------------------------------------------------- Index Scan using emp_test on employees (cost=0.01..8.29 rows=1 width=17) Index Cond: (subsidiary_id = 27::numeric) AND (date_of_birth >= to_date('1971-01-01', 'YYYY-MM-DD')) AND (date_of_birth <= to_date('1971-01-10', 'YYYY-MM-DD'))La base de datos PostgreSQL no indica el acceso del índice y los filtros de los predicados en el plan de ejecución. Sin embargo, la sección
Index Condlista las columnas según la definición del índice. En este caso, vemos primero el predicadoSUBSIDIARY_IDy después dos sobreDATE_OF_BIRTH. Como no existen columnas filtradas después de la condición de rango sobreDATE_OF_BIRTH, sabemos que todos los predicados pueden utilizarse como predicados de acceso. Ver “Distinguir los predicados de acceso y de filtro” para más detalles.- SQL Server
|--Nested Loops(Inner Join) |--Index Seek(OBJECT:emp_test, | SEEK: subsidiary_id=27 | AND date_of_birth >= '1971-01-01' | AND date_of_birth <= '1971-01-10' | ORDERED FORWARD) |--RID Lookup(OBJECT:employees), SEEK:Bmk1000=Bmk1000 LOOKUP ORDERED FORWARD)
Finalmente, existe el operador
between que permite
especificar el límite inferior y superior dentro de una sola condición:
DATE_OF_BIRTH BETWEEN '01-JAN-71'
AND '10-JAN-71'Se debe tener en cuenta que between incluye siempre los valores
especificados, así como los operadores menor o igual a
(<=) y mayor o igual a (>=):
DATE_OF_BIRTH >= '01-JAN-71'
AND DATE_OF_BIRTH <= '10-JAN-71'Si te gusta mi manera de explicar, te encantará mi libro.
