mysql join 扫描行数_MySQL 关于查询时扫描行数与索引的疑问

测试表：

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