MySQL中的索引：回表，覆盖，最左前缀初理解

我们知道，Mysql中InnoDB中索引结构为B+树，这是综合了B+树寻址次数少且查询耗时较稳定等优点决定的。

那B+树中存储的是什么东西呢？根据索引树就能直接找到对应的数据行吗？要说明这一点，就要详细地聊一下B+树的储存内容。

回表
实际上，InnoDB除了维护了一个索引B+树之外，还维护了一个主键B+树。索引B+树节点上存放的数据实际上是主键的值，而主键B+树上存放的才是真正的数据行本身(因为InnoDB是聚集索引，所以直接存储了数据行，如果是MyIsam非聚集索引，这里储存的只是数据行的一个地址，还需要去磁盘中获取数据行。这点具体可以看一下聚集索引与非聚集索引的区别)

那么这点弄清楚了，就可以分析一下，如果一条指令通过索引(同时不是主键)来检索数据，会发生什么呢？

例如在一个居民表中，有以下字段，其中id是主键，并且建立了name索引。

id	name	age	remark
1	aaa	20	2
2	bbb	14	3

当执行语句：

SELECT * FROM MyTable WHERE name=aaa;

首先MySQL分析到该次查询是通过name索引来检索的,所以就会去检索索引B+树。

而刚才我们也说了，索引树上存的实际上是主键，所以不出意外的话，我们能从索引树上获取到id=1这个主键。

于是这时如果想查询出整行数据，就要发生 回表：拿着id=1这个主键，再去主键树上进行查询，进而查询出真正的一条数据行。这个查询语句才算执行完成了。

回顾一下，在这个查询过程了，我们一共进行了两次B+树的检索，其中后面一次就是回表。就像我们猜测的一样，回标操作必然会导致查询时间的增加，开销的增大。

如果此时使用覆盖索引就可以避免回表操作，那啥是覆盖索引？考虑这个SQL语句：

SELECT id FROM MyTable WHERE name=aaa;

那这样执行流程是怎么样？我们发现，检索索引树的时候，直接就把id给查询出来，符合我们的预期结果了，不需要再进行一次回表。那这种操作就叫做覆盖索引，实际中也经常使用覆盖索引来进行性能的优化。

联合索引
基于刚说完的覆盖索引，我们再考虑一个问题，当在一个需要进行”根据name查询出age“操作很频繁的场景之下，按照我们刚才所建立的主键和索引，我们不难判断这个操作：

SELECT age FROM MyTable WHERE name=aaa;

或者这个操作

SELECT 一个其他属性 FROM MyTable WHERE name=aaaAND age=20;

每次执行时都需要进行回表操作(因为索引树中并没有age相关的数据)，这很明显是不太合理的。那咋办？把主键声明为age，利用覆盖索引避免回表操作？别傻了，不同的人可能会有相同的age，这怎么能作为主键呢？那这时，我们可以定义联合索引了。

取{name,age}作为一个联合索引，那么每次查询的时候，因为索引下推（会检索索引的所有值，只有都符合条件的时候，才会对该索引进行回表），MySQL除了查出name之外，还能查出age的值，这样我们就能从索引树直接获得age字段，而避免了一次回表操作。当然这样做也有缺点，联合索引因为比较复杂，通常来说维护起来也会产生比单个索引更大的开销，这个就要和实际情况相结合来进行取舍了。

最左前缀原则
回表的问题可算是解决了，建立一个联合索引{name,age}就可以。那问题又来了：

我不一定是通过name和age来检索数据啊，我们可能直接通过name来检索，那这时咋办，同时建立一个{name,age}联合索引来满足联合查询，和一个{name}单独索引来满足单独查询？是不是太繁琐了？

MySQL也考虑到了这个问题，所以MySQL有一个最左前缀原则。

拿{name,age}来举例，该索引中的最左边是name字段，根据最左前缀原则，该索引既可以用于name和age组合查询，也可以用于name的单独查询。那么这就好办了，只需要维护{name,age}就可以了，无需再多建立一个name索引。当然如果还需要一个单独的age索引，因为它不是在联合索引的最左边，所以还是需要额外建立age索引的，否则会进行全表扫描
 

参与资料：

https://blog.csdn.net/zhaohong_bo/article/details/90205979

https://blog.csdn.net/SCUTJAY/article/details/104150135