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

Question

测试表：
CREATETABLE`table_1`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENT,
`title`textNOTNULL,
`category_id`int(10)unsignedNOTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDB;
其中id字段是自增主键
插入30行用于测试的数据：
insertintotable_1(`category_id`)values(1);
insertintotable_1(`category_id`)values(1);
insertintotable_1(`category_id`)values(1);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(2);
insertintotable_1(`category_id`)values(3);
insertintotable_1(`category_id`)values(3);
insertintotable_1(`category_id`)values(3);
insertintotable_1(`category_id`)values(4);
insertintotable_1(`category_id`)values(4);
insertintotable_1(`category_id`)values(4);
insertintotable_1(`category_id`)values(5);
insertintotable_1(`category_id`)values(5);
insertintotable_1(`category_id`)values(5);
执行查询：
mysql>explainselect*from`table_1`orderby`id`DESClimit0,5;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
|1|SIMPLE|table_1|index|NULL|PRIMARY|4|NULL|5||
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
1rowinset
这个很好理解，因为id是主键，查询中只使用了orderbyid，查询涉及记录行数rows5，因为limit0,5
mysql>explainselect*from`table_1`where`category_id`=2orderby`id`DESClimit0,5;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|1|SIMPLE|table_1|index|NULL|PRIMARY|4|NULL|5|Usingwhere|
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1rowinset
这个就无法理解了，为什么使用了wherecategory_id=2，用一个非索引字段where，该查询涉及的记录数仍然是5？将category_id=2改为任何数字，rows都为5，实际记录前几条并不是category_id=2，按理应该先跳过category_id!=2的然后筛选出符合的结果返回，这样涉及的行数应该大于5啊
更无法理解的是，如果使用该表category_id建立索引，同样该SQL执行结果：
mysql>explainselect*from`table_1`where`category_id`=2orderby`id`DESClimit0,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|Usingwhere|
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
1rowinset
也就是wherecategory_id=2涉及行数成了category_id=2记录的总数！也就是18条
那么如果数据库中有1千万条数据，均分至category_id1-10的话，这时候需要执行：
select*from`table_1`where`category_id`=2orderby`id`DESClimit0,5;
是否需要建立category_id索引呢？如果建立每次都要扫描100万条索引记录吗？如果不建立任何索引，该SQL是否会存在性能问题？

Answer 1

《高性能mysql》第三版p698ROWS列这一列是Mysql估计为了找到所需的行而要读取的行数。......根据表的统计信息和索引的选用情况，这个估算值可能很不准确。由于你的category_id没有索引，mysql认为他可能要把每一条都遍历一次，才可以找到，所以rows是5（因为是估计的）。category_id要不要加索引，我觉得和你category_id的数量有关。如果category_id就只有两条的话（比如sex性别，只有男和女），那么加索引完全是浪费资源，如果category过多，那就加上吧。

Answer 2

第一个问题为什么还是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的行后要整体进行orderby，所以预估的值就是category_id=2的的行数，因为前面说过explain不会真正执行sql，所以category_id=2的值应该是存在索引中的（猜的）。第三个问题，通过前面说的，可以了解到，mysql不会真正扫描100万条索引记录的（当然如果mysql不能使用orderby的索引还是要扫描100万条索引记录的），你可以先不建立索引，如果性能不够再建立索引，这要求你的mysql可以在线DDL