解释选择..，为什么输入= ALL？

Question

有以下3张表：

用户

CREATE TABLE `users` (
    `user_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(64) NOT NULL,
    `last_name` VARCHAR(64) NOT NULL,
    PRIMARY KEY (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

帖子

CREATE TABLE `posts` (
    `post_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` MEDIUMINT(8) UNSIGNED NOT NULL,
    `author_id` MEDIUMINT(8) UNSIGNED NOT NULL,
    `title` VARCHAR(128) NOT NULL,
    `text` TEXT NOT NULL,
    PRIMARY KEY (`post_id`),
    INDEX `FK_posts__category_id` (`category_id`),
    INDEX `FK_posts__author_id` (`author_id`),
    CONSTRAINT `FK_posts__author_id` FOREIGN KEY (`author_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE,
    CONSTRAINT `FK_posts__category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

类别

CREATE TABLE `categories` (
    `category_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    PRIMARY KEY (`category_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

表中的数据：

INSERT INTO `users` (`user_id`, `first_name`, `last_name`) VALUES
    (1, 'John', 'Doe'),
    (2, 'Pen', 'Poe'),
    (3, 'Robert', 'Roe');

INSERT INTO `categories` (`category_id`, `name`) VALUES
    (1, 'Category 1'),
    (2, 'Category 2'),
    (3, 'Category 3'),
    (4, 'Category 4');

INSERT INTO `posts` (`post_id`, `category_id`, `author_id`, `title`, `text`) VALUES
    (1, 1, 1, 'title 1', 'text 1'),
    (2, 1, 2, 'title 2', 'text 2');

我想做一个简单的选择(让MySQL解释一下)：

EXPLAIN SELECT p.post_id, p.title, p.text, c.category_id, c.name, u.user_id, u.first_name, u.last_name
FROM posts AS p
JOIN categories AS c
ON c.category_id = p.category_id
JOIN users AS u
ON u.user_id = p.author_id
WHERE p.category_id = 1

我拿到了这个：

​

​

我不明白的是，为什么MySQL要在u (users)做一个完整的表扫描。我的意思是只有两个用户需要检索数据(使用id 1和2)，这两个用户可以通过主键user_id找到。有更多经验的人能帮我理解这一点吗？是否有更好的方法来创建索引，这样MySQL就不必对users表进行全面扫描才能检索有关post作者的数据？

谢谢你！

Answer 1

因此，在如此小的数量下，索引搜索将比顺序搜索慢。因此，MySQL选择使用简单的表读。

这与这里的运作效率有关。让我们简单地使用MySQL使用索引读取整个表vs所必须执行的操作。

全文如下：

1. 开桌
2. 一次只读一行，并匹配标准。
3. 返回结果集

这就是5次行动。

索引读取

1. 开桌
2. 将标准改为每一行的索引。
3. 使用索引指针为每行定位磁盘上的行
4. 返回结果集

在本例中，有8个操作。

这是非常简单的，但除非您有足够的数据，您的索引会减慢您的速度。随着表的增长，MySQL可能会选择不同的查询路径。这就是为什么您不强制使用索引。

Answer 2

根据测试数据和解释报告，您的users表中只有3行。

如果表中的行太少，优化器可以产生扭曲的结果。它可以对一个很小的表进行表扫描，即使它会使用一个索引来对几百或几千行相同的表进行相同的查询。

因此，在进行开发时，如果您想获得精确的优化器报告，那么在表中有大量的测试数据是非常重要的。