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