de Martin LE TARNEC.

Mayor que, menor que y BETWEEN


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 Cond lista las columnas en el orden de la definición del índice. En este caso, se ve el predicado DATE_OF_BIRTH primero y después SUBSIDIARY_ID. Como ningún predicado siguiendo una condición de rango puede ser un predicado de acceso, entonces el SUBSIDIARY_ID debe ser un filtro de predicado (ver Distinguishing Access and Filter-Predicates 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.

Nota

El plan de ejecución se ha simplificado con claridad. explica los detalles de la sección "información de los predicados" en el plan de ejecución de Oracle.

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
-----------------------------------------------------
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 Cond lista las columnas según la definición del índice. En este caso, vemos primero el predicado SUBSIDIARY_ID y después dos sobre DATE_OF_BIRTH. Como no existen columnas filtradas después de la condición de rango sobre DATE_OF_BIRTH, sabemos que todos los predicados pueden utilizarse como predicados de acceso. Ver Distinguishing Access and Filter-Predicates 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.

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