mysql 回表 索引覆盖 索引下推

前置学习

主键索引：通过主键创建的索引

二级索引(也叫辅助索引)：除开主键索引之外的其他索引 如：联合索引，唯一索引 等
联合索引：为表中多个列创建的索引
唯一索引：被设置了唯一约束的列，对该列创建索引


mysql的innodb引擎 会为每一个索引生成一颗B+树 
如：一个表有三个索引 一个主键索引 一个唯一索引 一个普通索引 
那么innodb会为这三个索引生成B+树
 一颗主键索引树 
 一颗唯一索引树 
 一颗普通索引树
 
B+树结构 ： 最底层叫叶子节点 ，除了叶子节点其他的都是叶节点，
叶子节点通过双向指针关联，便于范围查询

在主键索引B+树中：叶节点存放主键索引，叶子节点存放行数据
在二级索引B+树中：叶节点存放索引值，叶子节点存放主键索引

因此走二级索引只能查询出主键索引，
需要具体的行数据，
必须再拿着这个主键索引去主键索引树里查询

一个问题：如果走二级索引只查询二级索引对应的值而不查询行数据呢？

回表 二级索引树找到主键+再去主键索引树里找值

所谓的回表就是 通过二级索引查询到主键索引，再通过主键索引去查询对应的行数据

对name创建了一个普通索引
select * from table where name = 'dc'
这儿需要查询行数据，因此先查询普通索引树找到主键索引，再拿着主键索引去主键索引树查询name = 'dc'这条行数据;

缺点：查询两次才能拿到准确数据

索引覆盖 二级索引树里已经有我要查询的所有数据 不用再回表

目的：避免回表 

描述：查询的数据正好是索引

比如 ： 对name创建了一个普通索引
查询语句为 
select name from table where name = 'dc'
这儿只需要name,并且在普通索引树里本身就有name的值，因此直接返回查询到的name值就行，不用再去回表;

索引下推

使用了联合索引，索引的第一列正常使用，后边列失效的情况下

在5.6版本前，联合索引 ‘第一列’ 找数据 + 回表

在5.6版本后，联合索引 ‘第一列 + 其他失效的索引列 ’+ 回表

目的：在联合索引树中尽可能查出最精确的数据，把数据范围缩小，减少回表的次数

表结构 
id	name	age		address
1	xd		16		北京
2	xf		18		北京
3	gh		17		上海
4	hg		15		成都

比如：
对 address列 和 name列 创建 联合索引 index_a_n
查询语句 ： select name ,age,address from table where address= '北京' and name like '%d'

分析：
联合索引的第一列address正常使用了，但第二列name因为用了以'%'开头的模糊查询，导致name索引列失效

重点： 
  在5.6版本前，是这样查询的，先通过联合索引里的有效索引address找到北京相关的，
找到了name 为xd 和 name为xf 这两条数据，然后拿着这两条数据对应的主键索引，回表两次，查询出具体信息并返回

  在5.6版本后，是这样查询的，先通过联合索引里的有效索引address找到北京相关的，
然后再看其他索引信息，查询还用到了哪个索引，一看还有索引name[即使索引name已经失效]，
于是通过address是北京和name 以d结尾的条件，找到了 name 为 xd 这条数据,
然后拿着这条数据对应的主键索引，回表一次，查询出具体信息并返回