前置学习
主键索引:通过主键创建的索引
二级索引(也叫辅助索引):除开主键索引之外的其他索引 如:联合索引,唯一索引 等
联合索引:为表中多个列创建的索引
唯一索引:被设置了唯一约束的列,对该列创建索引
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 这条数据,
然后拿着这条数据对应的主键索引,回表一次,查询出具体信息并返回