mysql覆盖索引详解

覆盖索引的定义:

如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。

只扫描索引而无需回表的优点:
    1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问量。
    2.因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
    3.一些存储引擎如myisam在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用
    4.innodb的聚簇索引,覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B-tree索引做覆盖索引。

当发起一个索引覆盖查询时,在explain的extra列可以看到using index的信息
mysql覆盖索引详解

覆盖索引的坑:mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖,假设索引覆盖了where条件中的字段,但不是整个查询涉及的字段,mysql5.5和之前的版本也会回表获取数据行,尽管并不需要这一行且最终会被过滤掉。
mysql覆盖索引详解

如上图则无法使用覆盖查询,原因:
    1.没有任何索引能够覆盖这个索引。因为查询从表中选择了所有的列,而没有任何索引覆盖了所有的列。

2.mysql不能在索引中执行LIke操作。mysql能在索引中做最左前缀匹配的like比较,但是如果是通配符开头的like查询,存储引擎就无法做比较匹配。这种情况下mysql只能提取数据行的值而不是索引值来做比较

优化后SQL:添加索引(artist,title,prod_id),使用了延迟关联(延迟了对列的访问)
mysql覆盖索引详解
说明:在查询的第一阶段可以使用覆盖索引,在from子句中的子查询找到匹配的prod_id,然后根据prod_id值在外层查询匹配获取需要的所有值。

5.5时API设计不允许mysql将过滤条件传到存储引擎层(是把数据从存储引擎拉到服务器层,在根据条件过滤),5.6之后由于ICP这个特性改善了查询执行方式

上一篇:Java 线程池框架核心代码分析--转


下一篇:BZOJ_1070_[SCOI2007]修车_费用流