仅在查询索引字段时获取 EXPLAIN 的"额外"列中的"Using where; Using index"

在执行以下查询时:

EXPLAIN SELECT 
belgarath.match_oc_history.id_, belgarath.match_oc_history.tour_id
FROM
belgarath.match_oc_history
JOIN
belgarath.tournament_oc ON belgarath.tournament_oc.tour_id = belgarath.match_oc_history.tour_id
AND belgarath.tournament_oc.orig_id = belgarath.match_oc_history.tournament_oc_orig_id;

得到如下表:

+----+-------------+------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+---------+-----------------------------------------------------------------+-------+----------+--------------------------+
| id | select_type |      table       | partitions | type  |                                                                                          possible_keys                                                                                           |                         key                          | key_len |                               ref                               | rows  | filtered |          Extra           |
+----+-------------+------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+---------+-----------------------------------------------------------------+-------+----------+--------------------------+
|  1 | SIMPLE      | tournament_oc    | NULL       | index | uq__tournament_oc__tour_id__orig_id,ix__tournament_oc__tour_id,ix__tournament_oc__orig_id                                                                                                        | uq__tournament_oc__tour_id__orig_id                  |       6 | NULL                                                            | 26550 |      100 | Using where; Using index |
|  1 | SIMPLE      | match_oc_history | NULL       | ref   | ix__match_oc_history__five_keys,ix__match_oc_history__tour_id,fk__match_oc_history__player_oc_p1_idx,fk__match_oc_history__player_oc_p2_idx,fk__match_oc_history__tour_id__tournament_oc_orig_id | fk__match_oc_history__tour_id__tournament_oc_orig_id |       5 | belgarath.tournament_oc.tour_id,belgarath.tournament_oc.orig_id |    54 |      100 | Using index              |
+----+-------------+------------------+------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+---------+-----------------------------------------------------------------+-------+----------+--------------------------+

我从阅读周围的理解是，如果查询只与索引字段工作，那么它不应该需要使用where。这是正确的吗?

如果是，那么为什么我看到它在EXPLAIN表中弹出?

如果没有，我是否应该考虑在这个实例中使用where?我一直认为where比index慢…

表组成:

CREATE TABLE `tournament_oc` (
`updated` timestamp NULL DEFAULT NULL,
`created` timestamp NULL DEFAULT NULL,
`id_` int NOT NULL AUTO_INCREMENT,
`tour_id` tinyint DEFAULT NULL,
`orig_id` int NOT NULL,
PRIMARY KEY (`id_`),
UNIQUE KEY `uq__tournament_oc__tour_id__orig_id` (`tour_id`,`orig_id`),
KEY `ix__tournament_oc__tour_id` (`tour_id`),
KEY `ix__tournament_oc__orig_id` (`orig_id`),
CONSTRAINT `fk__tournament_oc__tour_id` FOREIGN KEY (`tour_id`) REFERENCES `tour` (`id_`)
) ENGINE=InnoDB AUTO_INCREMENT=27788 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `match_oc_history` (
`updated` timestamp NULL DEFAULT NULL,
`created` timestamp NULL DEFAULT NULL,
`id_` int NOT NULL AUTO_INCREMENT,
`tour_id` tinyint NOT NULL,
`tournament_oc_orig_id` int NOT NULL,
`round_oc_id` tinyint NOT NULL,
`player_oc_orig_id_p1` int NOT NULL,
`player_oc_orig_id_p2` int NOT NULL,
PRIMARY KEY (`id_`),
KEY `ix__match_oc_history__five_keys` (`tour_id`,`tournament_oc_orig_id`,`round_oc_id`,`player_oc_orig_id_p1`,`player_oc_orig_id_p2`),
KEY `ix__match_oc_history__round_oc_id` (`round_oc_id`),
KEY `ix__match_oc_history__tour_id` (`tour_id`),
KEY `fk__match_oc_history__player_oc_p1_idx` (`tour_id`,`player_oc_orig_id_p1`),
KEY `fk__match_oc_history__player_oc_p2_idx` (`tour_id`,`player_oc_orig_id_p2`),
KEY `fk__match_oc_history__tour_id__tournament_oc_orig_id` (`tour_id`,`tournament_oc_orig_id`),
CONSTRAINT `fk__match_oc_history__player_oc_p1` FOREIGN KEY (`tour_id`, `player_oc_orig_id_p1`) REFERENCES `player_oc` (`tour_id`, `orig_id`),
CONSTRAINT `fk__match_oc_history__player_oc_p2` FOREIGN KEY (`tour_id`, `player_oc_orig_id_p2`) REFERENCES `player_oc` (`tour_id`, `orig_id`),
CONSTRAINT `fk__match_oc_history__round_oc_id` FOREIGN KEY (`round_oc_id`) REFERENCES `round_oc` (`id_`),
CONSTRAINT `fk__match_oc_history__tour_id` FOREIGN KEY (`tour_id`) REFERENCES `tour` (`id_`),
CONSTRAINT `fk__match_oc_history__tournament_oc` FOREIGN KEY (`tour_id`, `tournament_oc_orig_id`) REFERENCES `tournament_oc` (`tour_id`, `orig_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1516084 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

根据注释:

SHOW WARNINGS语句响应:

/* select#1 */ select `belgarath`.`match_oc_history`.`id_` AS `id_`,`belgarath`.`match_oc_history`.`tour_id` AS `tour_id` from `belgarath`.`match_oc_history` join `belgarath`.`tournament_oc` where ((`belgarath`.`match_oc_history`.`tournament_oc_orig_id` = `belgarath`.`tournament_oc`.`orig_id`) and (`belgarath`.`match_oc_history`.`tour_id` = `belgarath`.`tournament_oc`.`tour_id`))

Using index意味着整个查询(至少对于所讨论的表)不需要INDEX中的其他列。这比在索引的b树和数据的b树之间跳转要快。
优化器可以自由地重新排序JOINed表，因为它认为合适。EXPLAIN反映了优化器的决定。
"warning"显示它是如何决定执行查询的。
OR转换为WHERE。(您使用ON是正确的，因为这就是表的相关方式。)
如果没有真正的WHERE，优化器[通常]会选择较小的表，然后进行"嵌套循环连接"。(NLJ)伸手到下一张桌子。这通常是执行查询的更快的方法。我看不需要STRAIGHT_JOIN;
你有最优的复合索引，让优化器首先选择表，最优。
为什么你认为"错误"的桌子先放?
";没有多大意义;忽略它。
"比索引"慢——这没有道理。WHERE子句与一个合适的索引将使查询运行得更快。ON子句几乎总是需要一个索引来运行得更快。(你所拥有的是有效的ON)

相关内容

最新更新

热门标签：