Distinguir los predicados de acceso y de filtro

La base de datos MySQL usa tres diferentes maneras para evaluar los filtros where (predicados):

Predicado de acceso (columnas “key_len”, “ref”)

Los predicados de acceso expresan las condiciones de inicio y de fin del recorrido del nodo hoja.

Predicado de filtro sobre índice (“Using index condition”, desde la versión 5.6 MySQL)

Los predicados de filtro sobre índice se aplican únicamente sobre el recorrido del nodo hoja. No contribuyen a las condiciones de inicio y de fin y no limitan el rango escaneado.

Predicado de filtro a nivel de tabla (“Using where” en la columna “Extra”)

Los predicados sobre columnas que no son parte del índice se evalúan a nivel de tabla. Para que eso suceda, la base de datos debe cargar la fila desde la primera tabla.

Los planes de ejecución de MySQL no muestran qué tipo de predicados se usaron por cada condición; lista solamente los tipos de predicados en uso.

En el siguiente ejemplo, el filtro entero where se usa como predicado de acceso:

CREATE TABLE demo (
   id1 NUMERIC
 , id2 NUMERIC
 , id3 NUMERIC
 , val NUMERIC)

INSERT INTO demo VALUES (1,1,1,1)

INSERT INTO demo VALUES (2,2,2,2)

CREATE INDEX demo_idx
          ON demo
             (id1, id2, id3)

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id2=1

+------+----------+---------+-------------+------+-------+
| type | key      | key_len | ref         | rows | Extra |
+------+----------+---------+-------------+------+-------+
| ref  | demo_idx | 12      | const,const |    1 |       |
+------+----------+---------+-------------+------+-------+

No se muestra “Using where” ni “Using index condition” en la columna “Extra”. Sin embargo, el índice se usa (type=ref,key=demo_idx) así que se puede asumir que el filtro entero where se califica como predicado de acceso.

Es de resaltar que la columna ref indica que las dos columnas se usan desde el índice (en este ejemplo, ambas seleccionan constantes). Otra manera de confirmar la parte del índice que se usa es el valor key_len. Muestra los 12 primeros bytes de la definición del índice que utiliza la sentencia. Para mapear esto con los nombres de las columnas, “sólo” hace falta saber cuánto espacio necesita cada columna (ver “Data Type Storage Requirements” en la documentación MySQL). En la ausencia de una restricción NOT NULL, MySQL necesita un byte adicional por cada columna. En el ejemplo, después de todo, cada columna necesita 6 bytes. Por lo tanto, la longitud de 12 de la llave confirma que las dos primeras columnas del índice se emplean como predicado de acceso.

Cuando se filtra con la columna ID3 (en lugar de la columna ID2), MySQL 5.6 y las versiones superiores usan un predicado de filtro sobre índice (“Using index condition”):

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id3=1

+------+----------+---------+-------+------+-----------------------+
| type | key      | key_len | ref   | rows | Extra                 |
+------+----------+---------+-------+------+-----------------------+
| ref  | demo_idx | 6       | const |    1 | Using index condition |
+------+----------+---------+-------+------+-----------------------+

En este caso, ken_len=6 y el valor const en ref indica que una sola columna del índice se usa como predicado de acceso.

En las versiones anteriores, MySQL utilizaba predicados de filtro a nivel de la tabla para esta sentencia, identificada por “Using where” en la columna “Extra”:

+------+----------+---------+-------+------+-------------+
| type | key      | key_len | ref   | rows | Extra       |
+------+----------+---------+-------+------+-------------+
| ref  | demo_idx | 6       | const |    1 | Using where |
+------+----------+---------+-------+------+-------------+