InnoDB主键索引树和二级索引树

2023-11-09 18:23:08 浏览数 (3)

我们这里讨论InnoDB存储引擎，数据和索引存储在同一个文件student.ibd

场景1：主键索引树

uid是主键，其他字段没有添加任何索引

代码语言：javascript复制
select * from student;

如果是上面这样查询，这表示整表搜索，从左到右遍历叶子节点链表，从小到大访问

代码语言：javascript复制
select * from student where uid<5;

如果是上面这样查询，这表示范围查询，就直接在有序链表中遍历搜索就可以了，直到遍历到第一个不小于5的key结束遍历

代码语言：javascript复制
select * from student where uid=5;

如果是上面这样查询，这表示等值查询，在索引树上进行二分查找即可

由于name没有索引，于是做整表搜索

代码语言：javascript复制
select * from student where name='linfeng';
场景2：二级索引树

uid是主键，以name创建了普通索引（二级索引）

以name为索引构建的索引树，称为辅助索引树，也叫做二级索引树。key是辅助索引字段name的值，然后还有外加uid主键的值

在辅助索引树上，key是辅助索引的值，也就是name；data数据值是所在记录行的主键值（PRIMARY KEY），也就是uid（并不是表的一行数据），一行表数据只在主键索引树上存在

分析语句1：

代码语言：javascript复制
select name from student where name='linfeng';

因为过滤字段是name且 只select了name一个字段，name有索引，索引树上直接就有，所以从name的二级索引树上去等值匹配linfeng

分析语句2：

代码语言：javascript复制
select uid,name from student where name='linfeng';

这种情况select的是name和uid，而这些在二级索引树上也是直接就有，所以搜索二级索引树就完事了。

分析语句3：

代码语言：javascript复制
select * from student where name='linfeng';

这种情况下就涉及到回表了，这是一个很重要的概念。由于name字段有索引，所以我们会到name字段构建的二级索引树上去查找。但二级索引树没有linfeng这个人所有的信息，所以完整的查询过程应该是这样的：

  1. 用linfeng到二级索引树上进行匹配，拿到二级索引树上存储的uid
  2. 然后拿着这个uid去主索引树上去匹配，最后拿到linfeng的所有信息（回表）

而这个回表意味着更多的磁盘I/O，会影响效率，如果业务只需要uid、name，就别写select *了，这样可以避免回表（在二级索引树上查到主键，再去主键索引树上查找）

分析语句4：

我们删除name的索引后执行以下语句

代码语言：javascript复制
select * from student where age=20 order by name;

没有用到索引，还使用外部排序了。此外我们还看到using filesort，这时需要优化了。

我们的过滤条件是age，先给age添加索引，看看行不行

可以看到，age命中索引了，查询age所在的索引树。由于我们写的是select *，依然存在回表。还有using filesort，因为使用age=20查询到的结果是多个，然而name此时是没有顺序的，所以还需要再进行外部排序。

那能不能通过给name加载索引来解决问题呢？

不能，因为一次SQL执行只能用到1个索引，搜索了这个字段的索引树就不会再去搜索另一个字段的索引树了，因为加载索引是要耗费磁盘I/O的，查找多个索引树就太慢了！

分析：既然索引树上只能存自己建立的索引字段以及主键，那我们把需要查询的字段都设置成索引不就好了？

解决方法：我们可以在二级索引树上的key：age name，形成联合索引，先按age排序，age相同了，再按name排序

再次select *

这时候就使用到联合索引了，而且没有using filesort，这次是这样查询的：

先用age=20在辅助索引树上查找，如果数据足够会找到多个结果，这个结果就是已经排好序的，不需要再using filesort

我们现在直接用第二个字段name作为过滤条件

我们看到这里没有用到索引，因为我们用(age,name)创建索引，是先按age排序，再按name排序。如果我们只用name作为过滤条件，这就没有办法使用索引匹配了，因为是优先用age排序。

所以我们经常说：多列索引一定要使用到第1个字段，这样才能用到索引！

在建立(age,name)联合索引的情况下，以下操作不回表（到二级索引树上搜索，再去主索引树上搜索）：

  • select age
  • select age, name
  • select uid,age,name

以下操作要回表

  • select *
  • select age,name,sex

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

搜索 2023腾讯·技术创作特训营第三期 innodb 排序 数据

0 人点赞