MySQL的索引覆盖和索引下推

在MySQL数据库中，索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点，索引就是为了提高数据查询的效率。今天我们来聊聊在MySQL索引优化中两种常见的方式，索引覆盖和索引下推

索引

索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。 索引最形象的比喻就是图书的目录了。注意这里的大量，数据量大了索引才显得有意义，如果想要在[1,2,3,4]中找到3这个数据，直接对全数据检索也很快，没有必要费力气建索引再去查找。

索引在mysql数据库中分三类：B+树索引、Hash索引、全文索引

索引覆盖
要了解索引覆盖，需要先了解几个索引的基础知识

B+树索引
B+树索引是InnoDB中的一种很常见的索引类型。关于B+树，这里不做深入的介绍，不太清楚的可以看单独介绍B树、B+树的文章。简单来说，是因为使用B+树存储数据可以让一个查询尽量少的读磁盘，从而减少查询时磁盘I/O的时间。

MyISAM索引

MySQL MyISAM索引类型分PRIMARY(主键)、UNIQUE(唯一)、INDEX(普通索引)、FULLTEXT(全文搜索)。

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

myisam 通过 key_buffer 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的原因

聚簇索引/非聚簇索引

MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。

聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

InnoDB索引

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
举例说明，假设我们有一张表，该表主键为id，且有用户名（user_name）和用户年龄(age)两个字段，其中age字段上有索引，所以建表语句如下图所示：

表中有5条数据

基于上面这种表设计和表中的数据，在InnoDB中的索引结构是下面这个样子的

根据叶子节点的内容，索引的类型分为主键索引和非主键索引

主键索引的叶子结点存的是整条记录，如上图紫色部分所示
非主键索引的叶子结点存的是主键的地址值，根据二级索引叶子结点中的地址可以找到主键索引中的这一条数据。所以非主键索引也被称为二级索引，如上图右半边黄色部分所示
那主键索引、二级索引的概念和我们今天介绍的内容有什么关系呢？我们下面来具体说说
回表
根据刚才提供的表结构，我们尝试来做两次次这样的查询：

select *from lyb_test where id = 2
select *from lyb_test where age = 12

第一条语句使用主键作为检索条件，即为主键查询，根据上图所示我们知道，如果是主键查询，我们只需要搜索左边这颗主键索引树即可快速查询到id=2的这条数据
第二条语句使用的是二级索引、即age作为检索条件，这和主键查询有什么区别呢？如果是二级索引查询，则需要先搜索右侧的age索引树，得到id的值为2，再到左侧的主键索引树搜索一次。
像第二种查询语句这样，通过非主键索引查询数据时，我们先通过非主键索引树查找到主键值,然后再在主键索引树搜索一次(根据rowid再次到数据块里取数据的操作)，这个过程称为回表，也就是说非主键索引查询会比主键查询多搜索一棵树
索引覆盖
结合上面的知识储备，我们进一步来优化一下刚才的SQL

select *from lyb_test where age = 12

当这条语句执行时，我们知道会进行两次索引树查询，第一次在二级索引上查询到主键索引的引用，然后到主键索引树中查询到所需要的数据，这个过程我们称之为回表。那为什么要有回表操作呢？由于查询的结果是所有字段，所需要的数据只有主键上才有，所以不得不回表。我们如果将sql改造为下面这种方式：

select id from lyb_test where age = 12

由于查询的值是ID，而id的值已经在age索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，当SQL语句的所有查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中，便可以直接使用索引查询而不需要回表。即在这个查询里，索引age已经“覆盖了”我们的查询需求，故称为索引覆盖。

索引下推
还是基于刚才的表结构和数据，我们现在针对user_name和age建立联合索引，索引建立之后，查询姓名以b开头且年龄大于等于13的用户信息，SQL语句如下：

select * from user_table where username like 'b%' and age >= 13

语句的执行过程有两种可能性：

根据(username，age)联合索引查询所有满足名称以"b"开头的索引，然后回表查询出相应的全行数据，再筛选出满足年龄大于等于13的用户数据。如果表中user_name以b开头的数据有n条，则需要回表n次
根据(username,age)联合索引查询所有满足名称以"b"开头的索引，然后直接再筛选出年龄大于等于13的索引，之后再回表查询全行数据。经过两次筛选之后，回表次数一定小于上述第一种情况
我们把第二种语句执行的过程称之为索引下推
在MySQL中，索引下推是默认启用的状态。在使用InnoDB存储引擎的数据表中，索引下推只能用于二级索引。我们可以通过修改MySQL系统变量来控制索引下推是否开启。设置如下：
SET optimizer_switch = 'index_condition_pushdown=off';// 关闭
SET optimizer_switch = 'index_condition_pushdown=on';// 开启
索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率

select * from t where a > 2 and b > 10

上述就是索引覆盖、回表、索引下推的相关概念和使用场景。

转载自：浅析MySQL的索引覆盖和索引下推_51CTO博客_mysql覆盖索引