mysql - 当连接到一个非常小/空的表时，为什么尽管我使用的是 "LIMIT"，MySQL 还是进行了全面扫描？

Question

编辑:我从示例查询中删除了 GROUP BY 子句，但同样的问题显示“当我将表 x 连接到空/1 行表 y MySQL 对表 x 进行全表扫描尽管我正在使用限制”

---

原始问题:我试图学习如何优化我的 SQL 查询，但我遇到了一种我无法理解的行为。有这样的架构

SQL fiddle

CREATE TABLE `country` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,   
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB ;


CREATE TABLE `school` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,   
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB ;


CREATE TABLE `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,
   `country_id` int(11) DEFAULT NULL,

   PRIMARY KEY (`id`),
   KEY `fk_country_idx` (`country_id`),
   CONSTRAINT `fk_users_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB ;


CREATE TABLE `user_school_mm` (
   `user_id` int(11) NOT NULL,
   `school_id` int(11) NOT NULL,
   PRIMARY KEY (`user_id`, `school_id`),
   KEY `fk_user_school_mm_user_idx` (`user_id`),
   KEY `fk_user_school_mm_school_idx` (`school_id`),
   CONSTRAINT `fk_user_school_mm_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
   CONSTRAINT `fk_user_school_mm_school` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
 ) ENGINE=InnoDB ;

INSERT INTO country (name) VALUES ('fooCountry1');
INSERT INTO school (name) VALUES ('fooSchool1'),('fooSchool2'),('fooSchool3');
INSERT INTO users (name, country_id) VALUES
('fooUser1',1),
('fooUser2',1),
('fooUser3',1),
('fooUser4',1),
('fooUser5',1),
('fooUser6',1),
('fooUser7',1),
('fooUser8',1),
('fooUser9',1),
('fooUser10',1)
;
INSERT INTO user_school_mm (user_id, school_id) VALUES
(1,1),(1,2),(1,3),
(2,1),(2,2),(2,3),
(3,1),(3,2),(3,3),
(4,1),(4,2),(4,3),
(5,1),(5,2),(5,3),
(6,1),(6,2),(6,3),
(7,1),(7,2),(7,3),
(8,1),(8,2),(8,3),
(9,1),(9,2),(9,3),
(10,1),(10,2),(10,3)
;

查询 1(快速)

-- GOOD QUERY (MySQL uses the limit and skip users table scan after 2 rows )
SELECT * 
FROM 
    users LEFT JOIN
    user_school_mm on users.id = user_school_mm.user_id
ORDER BY users.id ASC
LIMIT 2
-- takes about 100 milliseconds if users table is 3 million records  

解释

+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+-----------+
|id |select_type|table          | type | possible_keys                     | key      | key_len | ref           | rows | Extra     |
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+-----------+
|1  |SIMPLE     |users          |index |PRIMARY,fk_country_idx             | PRIMARY  |4        |               |2     |           |
|1  |SIMPLE     |user_school_mm |ref   |PRIMARY,fk_user_school_mm_user_idx | PRIMARY  |4        |tests.users.id |1     |Using index|
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+-----------+ 

查询 2(慢)

-- BAD QUERY (MySQL ignores the limit and scanned the entire users table )
SELECT * 
FROM 
    users LEFT JOIN
    country on users.country_id = country.id
ORDER BY users.id ASC
LIMIT 2
-- takes about 9 seconds if users table is 3 million records 

解释

+---+-----------+--------+------+------------------------+-----+---------+-----+------+---------------------------------------------------+
|id |select_type|table   | type | possible_keys          | key | key_len | ref | rows | Extra                                             |
+---+-----------+--------+------+------------------------+-----+---------+-----+------+---------------------------------------------------+
|1  |SIMPLE     |users   |ALL   | PRIMARY,fk_country_idx |     |         |     | 10   | Using temporary; Using filesort                   |
|1  |SIMPLE     |country |ALL   | PRIMARY                |     |         |     | 1    | Using where; Using join buffer (Block Nested Loop)|
+---+-----------+--------+------+------------------------+-----+---------+-----+------+---------------------------------------------------+

我不明白幕后发生了什么，我想如果我使用用户表的主键进行排序和分组，MySQL 将取用户表的前 2 行并继续连接，但它似乎并没有这样做，而是在查询 2 中扫描了整个表

为什么 MySQL 在 query2 中扫描了整个表，而在 query1 中只扫描了前 2 行？

MySQL 版本为 5.6.38

Answer 1

MySQL 优化器会先决定连接顺序/方法，然后检查对于选择的连接顺序，是否可以使用索引避免排序。对于这个问题中的慢查询，优化器决定使用 Block-Nested-Loop (BNL) join。

当其中一个表非常小(并且没有 LIMIT)时，BNL 通常比使用索引更快。

但是，对于 BNL，行不一定按照第一个表给出的顺序出现。因此，需要在应用 LIMIT 之前对连接结果进行排序。

您可以通过 set optimizer_switch = 'block_nested_loop=off'; 关闭 BNL