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,5mysql>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》第三版p698
ROWS列
这一列是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