MySQL 关于查询时扫描行数与索引的疑问

[andybest]

测试表：
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 是否会存在性能问题？

[coosir]

第二个用到了索引是id，因为limit所以5条就够了，type为index
第三个用到了索引category_id，虽然行数多，但是type是ref
按说type为index还是要扫全表，那么还是建议给category_id加索引