mysql - MySQL EXPLAIN 中的 "filtered"列告诉我什么，我该如何使用它？

Question

MySQL 5.7 documentation 指出:

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables.

为了更好地理解这一点，我在使用 MySQL Sakila Sample Database 的查询中进行了尝试。有问题的表具有以下结构:

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

这是查询的 EXPLAIN 计划:

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

该表的示例数据集总共有 1,000 行，并且所有行都将 release_year 设置为 2006。使用 MySQL 文档中的公式:
rows x filtered / 100 = "将与先前表连接的行数

所以，
1,000 x 10 / 100 = 100 = "100 行将与之前的表连接"

嗯？什么“上一张 table ”？这里没有 JOIN。

文档中引用的第一部分怎么样？ “将由表条件过滤的表行的估计百分比。”好吧，表条件是 release_year = 2006 ，并且所有记录都具有该值，所以 filtered 不应该是 0.00 或 100.00 (取决于“过滤”的含义)？

也许它的行为很奇怪，因为 release_year 上没有索引？所以我创建了一个:

mysql> CREATE INDEX test ON film(release_year);

filtered 列现在显示 100.00 。那么，在我添加索引之前它不应该显示 0.00 吗？嗯。如果我让一半表的 release_year 是 2006，另一半不是怎么办？

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

现在 EXPLAIN 看起来像这样:

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

而且，因为我决定进一步混淆自己:

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

那么，估计 501 行将被表条件过滤并“与以前的表连接”？

我简直不明白。

我意识到这是一个“估计”，但这个估计基于什么？如果存在的索引将估计值移动到 100.00 ，那么它的缺失不应该是 0.00 ，而不是 10.00 吗？最后一个查询中的 50.10 结果是什么？
filtered 在确定查询是否可以进一步优化，或者如何进一步优化它，或者它通常只是可以忽略的“噪音”方面是否有用？

Answer 1

…number of rows that will be joined with previous tables…

在没有任何连接的情况下，我相信这可以理解为行数

更新 - 至少现在文档说“ 跟随 表”，但重点仍然存在，谢谢@WilsonHauck

依次拿你的每个例子

1000 行，全部来自 2006 年，没有索引...

EXPLAIN SELECT * FROM film WHERE release_year = 2006

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

这里引擎期望访问 1000 行，并期望返回其中的 10% 左右

由于查询未使用索引，因此预测每一行都将被检查是有意义的，但不幸的是过滤后的估计是不准确的。我不知道引擎是如何做出这个预测的，但因为它不知道所有的行都是 2006 年的(直到它检查它们......这不是世界上最疯狂的事情

也许在没有更多信息的情况下，引擎期望任何简单的 = 条件将结果集减少到可用行的 10%

1000 行，2006 年的一半，带有索引...

EXPLAIN SELECT * FROM film WHERE release_year = 2006

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

这里引擎期望访问 500 行并期望返回所有行

现在查询使用的是新索引，引擎可以做出更准确的预测。它可以很快看到 500 行与条件匹配，并且必须仅访问这些行才能满足查询

EXPLAIN SELECT * FROM film WHERE release_year != 2006

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

这里引擎期望访问 1000 行并返回其中的 50.10%

引擎选择不使用索引，可能在这种情况下 != 操作不像 = 那么简单，因此预测每一行都将被访问是有意义的

然而，引擎已经对将返回多少这些访问过的行进行了相当准确的预测。我不知道 .10% 来自哪里，但也许引擎已经使用索引或先前查询的结果来识别大约 50% 的行将匹配条件

这有点像黑暗艺术，但 filtered 值确实为您提供了一些相当有用的信息，以及对引擎做出某些决定的原因的一些见解

如果行数很高并且过滤的行估计值很低(并且准确)，这可能是一个很好的迹象，表明精心应用的索引可以加快查询速度