測試表：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是否會存在性能問題？