在使用索引的时,有时通过explain命令进行sql语句分析,会发现优化器没有选择索引去查找数据,而是通过扫描聚集索引(全表扫描)的方式。这种情况多发生于范围查找、JOIN链接等情况。
例如表tb_order:
使用Show index 查看索引:有orderId聚集索引和order_code 辅助索引;
用explain命令,查看索引的使用情况,发现是使用了辅助索引
explain select order_code from fcl_order where order_code >'1' and order_code < '9000000000000';
但条件改成
explain select * from fcl_order where order_code >'1' and order_code < '9000000000000';
或者
explain select * from fcl_order where order_code > '1'
此时没有使用辅助索引
即使存在辅助索引order_code, 优化器最后可能还是选择primary聚集索引。原因是:用户选取的数据是整行信息,而order_code索引不能覆盖到我们要查询的信息,因此在对order_code索引查询到指定数据后,还要再进行一次书签访问来查找整行的信息。虽然order_code索引中数据是顺序存放的,但是再进行一次书签查找的数据则是无序的,变成了磁盘上的离散读操作。如果访问的数据量很小,那优化器还是会选择辅助索引,但访问的数据占整个表蛮大一部分时(一般20%),优化器会选择通过聚集索引来查找数据,因为顺序读的操作会远大于离散读。
如果不能使用覆盖索引的情况,优化器只有数据量小的时候才会使用辅助索引。这是由传统的机械硬盘特性决定的。若使用固态硬盘,随机读操作很快,且有足够的自信能确认使用辅助索引可以带来更好的性能,可以使用Force index强制使用某个索引。
mysql技术内幕-InnoDB存储引擎