Mysql（11）——InnoDb的主键索引树和二级索引树

前言

上一节我们学习了B+树的索引底层的相关知识，先来复习一下，再进入今天的内容：

假设存在如下的表：

注意想想B+索引树的存储结构

场景一：uid是主键

select * from student;

对于上面的sql，在建立了主键索引树后，在底层叶子结点上会进行整表遍历。

场景二：uid是主键的等值查询

select * from student where uid=5;

对于上面的sql，在建立了主键索引树后，会在叶子节点上二分查找到目标结点。

场景三：uid是主键的范围查询

select * from student where uid<5;

对于上面的sql，在建立了主键索引树后，会在叶子节点上依次遍历前四个结点。

场景四：uid是主键，但是查询条件中未使用

select * from student where name='lifeng';

对于上面的sql，在建立了主键索引树后，只会进行整表搜索。

场景五：uid是主键，name创建了普通索引（二级索引）

select * from student where name='lifeng';

上述的场景五就是我们今天要学习的内容：
我们先来看看主键索引树和二级索引树上存储的内容的区别：
1、对于之前的主键索引树而言：

• key是uid，data存储的行记录的其他数据成员

2、对于辅助索引树（二级索引树）而言;

• key就是辅助索引的值，data存放的所在记录行的主键值（也就是uid）

如下的两种情况，均可在二级索引树上完成查询工作：

select name from student where name='linfeng';
select uid,name from student where name='linfeng';

然而，如下的情况：

select * from student where name='linfeng';

这里会回表，因为*的全部内容在二级索引树上查找并不能完成要求，所以上述搜索的步骤是：

1. 搜索name的二级索引树，找到linfeng对应的主键uid是4；
2. 然后再以uid=4为条件在主键索引树上搜索uid那一行的记录

这也给我们提供启示，在业务上，我们尽量搜索我们需要的字段，避免回表，从而提升搜索效率。

再来看下面的情境：

select * from student where age = 20 order by name;

上述查询语句中，如果只有age建立了索引，会涉及到外部排序，explain显示using filesort，因此为了提高查询效率，需要建立age和name的多列索引。

注意：如果已经建立了多列索引，我们必须使用到age，如果过滤条件只有name是不会使用索引的，你想一想B+树上建立的二级索引，是先按照age排序，再按照name排序，如果只拿name匹配，就无法匹配。