de Martin LE TARNEC.

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 |
+------+----------+---------+-------+------+-------------+

Sugerencia

Acerca del autor

Foto de Markus Winand

Markus Winand enseña SQL eficiente, en casa y online. Mejora el tiempo de desarrollo utilizando SQL moderno y optimiza el tiempo de ejecución con indexación inteligente. Su libro Rendimiento SQL explicado se ha convertido en lectura obligada sobre el tema.

Adquiere tu libro en Amazon

Portada de “Rendimiento SQL explicado”: Ardilla corriendo en la hierba

La esencia del tuning de SQL en 200 páginas

Compra en Amazon
(solo en papel)

Libro y PDF también disponible en la tienda de Markus.

Contratar a Markus

La manera más rápida y fácil de beneficiarse de su extenso conocimiento y experiencia.
Aprende más »

“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