MySQL 关于查询时扫描行数与索引的疑问-PHP中文网问答

测试表：

CREATE TABLE `table_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` text NOT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

其中 id 字段是自增主键

插入 30 行用于测试的数据：

insert into table_1 (`category_id`)values(1);
insert into table_1 (`category_id`)values(1);
insert into table_1 (`category_id`)values(1);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(2);
insert into table_1 (`category_id`)values(3);
insert into table_1 (`category_id`)values(3);
insert into table_1 (`category_id`)values(3);
insert into table_1 (`category_id`)values(4);
insert into table_1 (`category_id`)values(4);
insert into table_1 (`category_id`)values(4);
insert into table_1 (`category_id`)values(5);
insert into table_1 (`category_id`)values(5);
insert into table_1 (`category_id`)values(5);

执行查询：

mysql> explain select * from `table_1` order by `id` DESC limit 0,5;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | table_1 | index | NULL | PRIMARY | 4 | NULL | 5 | |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
1 row in set

这个很好理解，因为 id 是主键，查询中只使用了 order by id ，查询涉及记录行数 rows 5，因为 limit 0,5

mysql> explain select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | table_1 | index | NULL | PRIMARY | 4 | NULL | 5 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set

这个就无法理解了，为什么使用了 where category_id=2 ，用一个非索引字段 where ，该查询涉及的记录数仍然是 5 ？将 category_id=2 改为任何数字，rows 都为 5，实际记录前几条并不是 category_id=2 ，按理应该先跳过 category_id!=2 的然后筛选出符合的结果返回，这样涉及的行数应该大于 5 啊

更无法理解的是，如果使用该表 category_id 建立索引，同样该 SQL 执行结果：

mysql> explain select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
| 1 | SIMPLE | table_1 | ref | category_id | category_id | 4 | const | 18 | Using where |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set

也就是 where category_id=2 涉及行数成了 category_id=2 记录的总数！也就是 18 条

那么如果数据库中有1千万条数据，均分至 category_id 1-10 的话，这时候需要执行：

select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;

是否需要建立 category_id 索引呢？如果建立每次都要扫描 100 万条索引记录吗？如果不建立任何索引，该 SQL 是否会存在性能问题？

全部回复(2)

PHP中文网2017-04-17 12:01:37 2楼

《高性能mysql》第三版 p698

ROWS 列
这一列是Mysql估计为了找到所需的行而要读取的行数。
    ......
根据表的统计信息和索引的选用情况，这个估算值可能很不准确。

由于你的category_id没有索引，mysql认为他可能要把每一条都遍历一次，才可以找到，所以rows是5（因为是估计的）。

category_id 要不要加索引，我觉得和你category_id的数量有关。如果category_id 就只有两条的话（比如sex性别，只有男和女），那么加索引完全是浪费资源，如果category过多，那就加上吧。

赞 +0

添加回复

PHP中文网2017-04-17 12:01:37 1楼

第一个问题为什么还是5呢，因为explain后面的sql并没有真正执行，mysql只是根据这条sql预测的，所以这个值肯定是不准确的估计值，而此时mysql预估的值就是limit的5，最好情况5行就够，因为sql没有执行，mysql没法给你预估出别的值，只能是sql里存在的值。
第二个问题category_id 建立索引后预估的值变成了category_id=2的行数，这是因为mysql执行sql会按照sql中索引的顺序来使用，即这条sql会使用category_id的索引，而不会使用id的索引（新版本mysql可能两个索引都会使用），通过category_id的索引得到所有category_id=2的行后要整体进行order by，所以预估的值就是category_id=2的的行数，因为前面说过explain不会真正执行sql，所以category_id=2的值应该是存在索引中的（猜的）。
第三个问题，通过前面说的，可以了解到，mysql不会真正扫描 100 万条索引记录的（当然如果mysql不能使用order by的索引还是要扫描 100 万条索引记录的），你可以先不建立索引，如果性能不够再建立索引，这要求你的mysql可以在线DDL