La documentación MySQL 5.7 establece:

La columna filtered indica un porcentaje estimado de filas de la tabla que serán filtradas por la condición de la tabla. Es decir, rows muestra el número estimado de filas examinadas y rows × filtered / 100 muestra el número de filas que se unirán a las tablas anteriores.

Para intentar entender esto mejor, lo probé en una consulta usando la Base de datos de muestra MySQL Sakila. La tabla en cuestión tiene la siguiente estructura:

mysql> SHOW CREATE TABLE film \G
*************************** 1. row ***************************
       Table: film
Create Table: CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

Y este es el plan EXPLAIN para la consulta:

mysql> EXPLAIN SELECT * FROM film WHERE release_year=2006 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 10.00
        Extra: Using where

El conjunto de datos de muestra de esta tabla tiene 1,000 filas en total, y todas ellas tienen release_year establecido en 2006. Usando la fórmula en la documentación de MySQL:

rows x filtered / 100 = "número de filas que se unirán con tablas anteriores

Así que

1,000 x 10 / 100 = 100 = "100 filas se unirán con tablas anteriores"

¿Eh? ¿Qué "mesa anterior"? No hay JOIN pasando aquí.

¿Qué pasa con la primera parte de la cita de la documentación? "Porcentaje estimado de filas de la tabla que serán filtradas por la condición de la tabla". Bueno, la condición de la tabla es release_year = 2006, y todos registros tienen ese valor, por lo que filtered no debería ser 0.00 o 100.00 (dependiendo en lo que quieren decir con "filtrado")?

¿Tal vez se está comportando de manera extraña porque no hay índice en release_year? Entonces creé uno:

mysql> CREATE INDEX test ON film(release_year);

La columna filtered ahora muestra 100.00. Entonces, ¿no debería haber mostrado 0.00 antes de agregar el índice? Hm. ¿Qué sucede si hago que la mitad de la mesa tenga release_year en 2006 y la otra mitad no?

mysql> UPDATE film SET release_year=2017 ORDER BY RAND() LIMIT 500;
Query OK, 500 rows affected (0.03 sec)
Rows matched: 500  Changed: 500  Warnings: 0

Ahora la EXPLAIN se ve así:

mysql> EXPLAIN SELECT * FROM film WHERE release_year=2006 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref
possible_keys: test
          key: test
      key_len: 2
          ref: const
         rows: 500
     filtered: 100.00
        Extra: Using index condition

Y, dado que decidí confundirme aún más:

mysql> EXPLAIN SELECT * FROM film WHERE release_year!=2006 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: test
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 50.10
        Extra: Using where

Entonces, ¿una estimación de 501 filas será filtrada por la condición de la tabla y "unida a las tablas anteriores"?

Simplemente no lo entiendo.

Me doy cuenta de que es una "estimación", pero ¿en qué se basa esta estimación? Si un índice presente mueve la estimación a 100.00, ¿no debería ser su ausencia 0.00, no 10.00? ¿Y qué pasa con ese resultado 50.10 en la última consulta?

¿Es filtered útil para determinar si una consulta puede optimizarse aún más, o cómo para optimizarla más, o generalmente es solo "ruido" que puede ignorarse?

4
Michael Moussa 8 ene. 2017 a las 19:16

5 respuestas

La mejor respuesta

... número de filas que se unirán con tablas anteriores ...

En ausencia de uniones, creo que esto puede tomarse como el número medio de filas

ACTUALIZACIÓN : la documentación, ahora al menos, dice " siguiendo tablas" pero el punto sigue en pie, gracias @WilsonHauck


Para tomar cada uno de tus ejemplos por turno

1000 filas, todas de 2006, sin índice ...

EXPLAIN SELECT * FROM film WHERE release_year = 2006

key: NULL
rows: 1000
filtered: 10.00
Extra: Using where

Aquí el motor espera visitar 1000 filas, y espera devolver alrededor del 10% de estas

Como la consulta no usa un índice, tiene sentido predecir que se verificará cada fila, pero desafortunadamente la estimación filtrada es inexacta. No sé cómo el motor hace esta predicción, pero como no sabe, todas las filas son de 2006 (hasta que las verifica) ... no es la cosa más loca del mundo

Quizás en ausencia de más información, el motor espera cualquier condición simple = para reducir el conjunto de resultados al 10% de las filas disponibles

1000 filas, la mitad de 2006, con índice ...

EXPLAIN SELECT * FROM film WHERE release_year = 2006

key: test
rows: 500
filtered: 100.00
Extra: Using index condition

Aquí el motor espera visitar 500 filas y espera devolverlas todas

Ahora la consulta está utilizando el nuevo índice, el motor puede hacer predicciones más precisas. Puede ver muy rápidamente que 500 filas coinciden con la condición, y tendrá que visitar solo y exactamente estas para satisfacer la consulta

EXPLAIN SELECT * FROM film WHERE release_year != 2006

key: NULL
rows: 1000
filtered: 50.10
Extra: Using where

Aquí el motor espera visitar 1000 filas y devolver el 50.10% de ellas.

El motor ha optado por no usar el índice, tal vez la operación != no es tan simple como = en este caso, y por lo tanto tiene sentido predecir que se visitará cada fila

Sin embargo, el motor ha realizado una predicción bastante precisa sobre cuántas de estas filas visitadas serán devueltas. No sé de dónde proviene el .10%, pero quizás el motor ha utilizado el índice o los resultados de consultas anteriores para reconocer que alrededor del 50% de las filas coincidirán con la condición


Es un poco un arte oscuro, pero el valor filtered le brinda información bastante útil y una idea de por qué el motor ha tomado ciertas decisiones

Si el número de filas es alto y la estimación de filas filtradas es baja (y precisa), puede ser una buena indicación de que un índice cuidadosamente aplicado podría acelerar la consulta

2
Arth 14 ago. 2019 a las 09:05

De la documentación 5.7 existente hoy en url https://dev.mysql.com/doc/refman/ 5.7 / es / explicar-salida.html

Filtrado (nombre JSON: filtrado)

La columna filtrada indica un porcentaje estimado de filas de la tabla que serán filtradas por la condición de la tabla. El valor máximo es 100, lo que significa que no se produjo ningún filtrado de filas. Los valores que disminuyen de 100 indican cantidades crecientes de filtrado. filas muestra el número estimado de filas examinadas y filas × filtradas muestra el número de filas que se unirán con la siguiente tabla. Por ejemplo, si las filas son 1000 y las filtradas son 50.00 (50%), el número de filas a unir con la siguiente tabla es 1000 × 50% = 500.

2
Wilson Hauck 6 ago. 2019 a las 15:09

Por lo tanto, debe escribir uno de estos para comprenderlo perfectamente, pero la estimación no se basa en los contenidos sino en metadatos sobre los contenidos y las estadísticas.

Permíteme darte un ejemplo inventado específico. No digo que ninguna plataforma sql haga lo que describo aquí, este es solo un ejemplo:

Tiene una tabla con 1000 filas y el valor máximo para la columna del año es 2010 y el valor mínimo para la columna del año es 2000; sin ninguna otra información, puede "adivinar" que donde year = 2007 tomará el 10% de todos los elementos suponiendo una distribución promedio .

En este caso, devolvería 1000 y 10.

Para responder a su pregunta final, filtered podría ser útil si (como se muestra arriba) solo tiene un valor "predeterminado" que está descartando todo; puede decidir usar say null en lugar de un valor predeterminado para obtener sus consultas Desempeñar mejor. O puede ver que las estadísticas deben ejecutarse en sus tablas con más frecuencia porque los rangos cambian mucho. Esto depende mucho de una plataforma determinada y de su modelo de datos.

0
Hogan 8 ene. 2017 a las 16:44

Encuentro que la columna "filtrada" es inútil.

EXPLAIN (hoy) utiliza estadísticas crudas para derivar muchos de los números que muestra. "Filtrado" es un ejemplo de lo mal que pueden ser.

Para profundizar aún más en los números, ejecute EXPLAIN FORMAT=JSON SELECT ... Esto, en las versiones más recientes de MySQL, proporcionará el "costo" para cada posible plan de ejecución. Por lo tanto, le da pistas sobre qué opciones pensó y la "base de costos" para el plan que se eligió. Desafortunadamente, utiliza una constante para recuperar una fila, sin dar importancia a si la fila proviene del disco o ya está en caché.

Se puede obtener una métrica más precisa de qué trabajo se realizó después del hecho a través de los valores STATUS "Handler%". Discuto eso, además de técnicas simples de optimización en http://mysql.rjweb.org/doc.php / index_cookbook_mysql.

Los histogramas existen en 8.0 y 10.0; Proporcionarán más precisión. Probablemente ayuden a que el "filtrado" sea algo útil.

0
revo 6 ago. 2019 a las 04:53

¿Cómo puedo usarlo?

Los números altos (idealmente filtered: 100.00) indican que la consulta está usando un índice "bueno", o un índice sería inútil.

Considere una tabla con una columna deleted_at TIMESTAMP NULL (eliminación suave) sin un índice, y al igual que el 99% de las filas contienen NULL (no se eliminan). Ahora con una consulta como

SELECT * FROM my_table WHERE deleted_at IS NULL

Es posible que vea

filtered: 99.00

En este caso, un índice en deleted_at sería inútil, debido a la sobrecarga de una segunda búsqueda (encontrar las filas filtradas en el índice agrupado). En el peor de los casos, el índice podría incluso dañar el rendimiento, si el optimizador decide usarlo.

Pero si consulta filas "eliminadas" con

SELECT * FROM my_table WHERE deleted_at IS NOT NULL

Deberías obtener algo como

filtered: 1.00

El número bajo indica que la consulta podría beneficiarse de un índice. Si ahora crea el índice en (deleted_at), EXPLAIN le mostrará

filtered: 100.00

Yo diría: Cualquier cosa> = 10% no vale la pena crear un índice. Eso al menos para condiciones de una sola columna.

Una historia diferente es cuando tienes una condición en varias columnas como

WHERE a=1 AND b=2

Asumiendo 1M de filas en la tabla y una cardinalidad de 10 para ambas columnas (cada columna contiene 10 valores distintos) distribuidas al azar, con un índice en (a), el motor analizaría 100K filas (10% debido al índice en {{ X1}}) y devuelve 10K filas (10% de 10% debido a la condición en b). EXPLICAR debería mostrarle rows: 100000, filtered: 10.00. En este caso, extender el índice de una sola columna en (a) a un índice compuesto en (a, b) debería mejorar el tiempo de consulta por el factor 10. Y EXPLICAR debería mostrarle rows: 10000, filtered: 100.00.

Sin embargo, eso es más una teoría. La razón: a menudo veo filtered: 100.00 cuando debería ser más bien 1.00, al menos para columnas de baja cardinalidad y al menos en MariaDB. Eso puede ser diferente para MySQL (no puedo probar eso ahora), pero su ejemplo muestra un comportamiento similar (10.00 en lugar de 100.00). En realidad, no recuerdo cuándo el valor de filtered me ha ayudado alguna vez. Lo primero que miro son: el orden de las tablas (si es un JOIN), la clave utilizada, la longitud de la clave utilizada y el número de filas examinadas.

1
Arth 12 ago. 2019 a las 08:18