【MySQL系列】-回表、覆盖索引真的懂吗

在面试时常会被问一些概念性的东西。这些内容其实在开发中比较少用，但是为了显示你的知识储备你必须学习。博主最近在考Mysql认证时，也常碰到这样的问题。整理MySQL概念输出这篇博文。

一、MYSQL索引结构

1.1 索引的概念

MYSQL官方对索引的定义为：索引（Index）是帮助MySQL提高获取数据的数据结构。索引的本质是数据结构。可以简单理解为"预先排好一组能快速查询的数据结构"。这些数据结构以某种方式指向数据，可以通过这些数据结构实现高级查询算法。

1.2 索引的特点

1. 索引一个排序的数据结构可以加速数据库的检索速度。
2. 索引降低了数据库Insert、Update、Delete等维护任务的难度
3. MySQL索引只能创建在表上，不能创建在视图上。
4. 查询处理器执行SQL语句，一个表上，一次只能使用一个索引

1.3 索引的优点

1. 提高数据检索的效率，降低数据库的IO成本
2. 创建唯一性的索引，保证数据库表中每一行数据的唯一性。
3. 加速表和表之间的连接。
4. 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

1.4 索引的缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

二、B-Tree与B+Tree

2.1 B-Tree

B-Tree及为B树。B树是一种自平衡的树，能够保持数据有序。这种数据结构能够让查询数据，顺序访问、插入数据及删除的动作，都在对数时间内完成。B数概况来说是一个一般化的二叉查找树，可以拥有多于2个子节点。与自平衡二叉查找树不同，B树为系统大块数据的读写操作做了优化。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。

2.2 B+Tree

B+Tree是B-Tree的一种优化。节点上只存储键值，不存储数据。这样的设计在有限的节点空间（页空间）内可以存放更多的键值、指针。所有数据都存放在叶子节点中，所有叶子节点之间有链指针（双向循环列表），便于范围查询，也便于排序。

2.3 B-Tree 与B+Tree树的区别

1. B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。
2. B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
3. B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。

B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。

B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。

2.4 那么为什么InnoDB的主键最好要搞成有序的？

InnoDB中主键索引是聚集索引，所有数据都存在主键索引所在的聚集索引的B+Tree结构的叶子节点中。如果每次插入的主键是大小随机的话，每次数据进来找到的叶子节点的位置是随机的，这样的话，有些叶子节点所在页本来就排满了，结果又来了一条数据，就势必要引起页分裂，所以导致性能下降；但是如果主键是有序的话，每次进行都找到当前叶子前面的位置，一个一个叶子按顺序排满一个页再排一个页，就不会又页分裂的问题了。所以自增主键对于InnoDB这种使用B+Tree索引的存储引擎来说，性能更好。

三、回表查询

回表查询就是在数据查询过程中MySQL内部需要两次查询。既先定位查询数据所在表的主键值，在根据主键定位行记录。

要弄清楚回表查询，我们就要先从InnoDB的索引实现说起，InnoDB索引分为两大类：聚集索引（Clustered Index）和普通索引（Secondary Index）

3.1 InnoDB 聚集索引

聚集索引是索引结构和数据一起存放的索引。主键索引为聚集索引。

InnoDB聚集索引的叶子节点存储行记录，因此InnoDB 必须要有且只有一个聚集索引。

1. 如果表定义了 PK （Primary Key，主键），那么 PK 就是聚集索引；
2. 如果表没有定义 PK，则第一个 NOT NULL UNIQUE 的列就是聚集索引。
3. 否则 InnoDB 会另外创建一个隐藏的 ROWID 作为聚集索引。

由于这种机制是直接定位行记录，因此使得基于 PK 的查询速度非常快。

3.2 InnoDB非聚集索引

非聚集索引是索引结构和数据分开存在的索引。辅助索引就是非聚集索引。

非聚集索引的叶子节点不一定存储的是数据的指针（辅助索引的叶子节点存储的是就是主键，然后根据主键在回表查询数据。）

3.3 InnoDB回表

回表查询，就是先通过非聚集索引查询到对应的主键，在通过主键索引查询到对应的值。两次经过B+Tree索引。

四、覆盖索引

如果执行一个查询语句不经过两次B+Tree查询直接得到要查询的值，这个时候就不需要回表，也就是说在这个查询中，索引"覆盖了"查询，这个称为覆盖索引。

由于覆盖索引减少B+Tree是搜索次数，提高查询性能，所以使用覆盖索引是一个常用的索引手段。使用覆盖索引最常见的方法是创建联合索引，将需要查询的字段都放在联合索引上。

用explain sql，如果Extra中有using index，则证明使用到了覆盖索引。

五、最左前缀原则

最左前缀就是利用索引来加速检索，最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符，就是说你要查询N个字段就包含在某个联合索引的最左N个字段内，简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

最左前缀原则总结

1. 假设有三个字段(col1, col2, col3)，MySQL可以支持(col1), (col1, col2), 和(col1, col2, col3)的联合索引。
2. 比较有争议的(col1, col3) 是否支持联合索引，官方给的文档中是支持的，我们试验也是支持。
3. where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序。
4. where子句，若遇到范围查询（> < between, like）或未在总结1中创建的索引对时，就会停止匹配（遇到的范围查询还是参与索引）。

六、索引失效

建好索引后，但是一些不好的SQL会导致索引失效，有一下几种场景会导致失效。

1. 查询条件中有OR，即使有部分条件带索引也会失效；
2. LIKE查询时已%开头；
3. 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引；
4. 索引列上参与计算会导致索引失效；
5. 违背最左匹配原则；
6. 如果Mysql估计全表扫描要比使用索引要快，会不适用索引
7. B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走；
8. 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走

七、索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6以后的版本上推出，用于优化回表查询；在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 ；在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，

然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 ；

• 查看索引下推的状态

show VARIABLES like '%optimizer_switch%';
-------------------------------------------------------
optimizer_switch  index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

• 关闭索引下推

#索引下推是mysql 5.6优化查询回表的功能，在5.6之前都不支持索引下推
set optimizer_switch='index_condition_pushdown=off';

• 开启索引下推

set optimizer_switch='index_condition_pushdown=on';

• 总结

1. 索引下推功能是mysql 5.6推出优化回表的操作，只支持向上兼容，低版本是不支持的；
2. 索引下推优化的只是回表次数，扫描行数还是一样的。