[博学谷学习记录]超强总结,用心分享|架构 MySql的索引实现

文章内容是学习过程中的知识总结，如有纰漏，欢迎指正

文章目录

前言

一、InnoDB索引

主键索引（聚簇索引）

辅助索引

组合索引

最左匹配原则

覆盖索引

二、MyIsam索引

辅助索引

总结

---

前言

上一篇文章介绍完了MySql索引数据结构，那肯定是要带入到Mysql里面看看真实的使用场景的，所以这里分析Mysql的两种存储引擎的索引实现：MyISAM索引和InnoDB索引

---

以下是本篇文章正文内容

一、InnoDB索引

• 主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

InnoDB创建索引的具体规则如下：

• 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

• InnoDB的数据和索引存储在t_user_innodb.ibd文件中，InnoDB的数据组织方式，是聚簇索引。
• 主键索引的叶子节点会存储数据行，辅助索引的叶子节点只会存储主键值。

 等值查询数据：

select * from user_innodb where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
2. 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
3. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO数量：3次。

• 辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址也非行记录。

以表user_innodb的age列为例，age索引的索引结果如下图。

• 辅助索引的底层叶子节点是按照（age，id）的顺序排序，先按照age列从小到大排序，age相同时按照id列从小到大排序。
• 使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后根据主键到主键索引中检索获得数据记录。

 辅助索引等值查询的情况：

select * from t_user_innodb where age=19;

 

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

磁盘IO数：辅助索引3次+获取记录回表3次

• 组合索引

以表abc_innodb为例，id列为主键索引，创建一个联合索引idx_abc(a，b，c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

show index from abc_innodb

-- 是否会匹配索引
-- （1）
explain select * from abc_innodb where  a = 1 and b = 2   是
explain select * from abc_innodb where  b = 2 and a = 1   是

--  (2)
explain select * from abc_innodb where  b = 2   否

--  (3)
explain select * from abc_innodb where a = 1 and b>2 and c=3   a,b走索引 c不走索引

组合索引的数据结构：

组合索引的查询过程： 

select * from abc_innodb where a = 13 and b = 16 and c = 4;

• 最左匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排序，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内有序递增；而c列只能在a和b两列值相等的情况下小范围内有序递增。

就像上面的查询，B+ 树会先比较a列来确定下一步应该检索的方向，往左还是往右。如果a列相同再比较b列，但是如果查询条件中没有a列，B+树就不知道第一步应该从那个节点开始查起。

可以说创建的idx_(a，b，c)索引，相当于创建了(a)、(a，b)、(a，b，c)三个索引。

组合索引的最左前缀匹配原则：

使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)等就会停止匹配。

• 覆盖索引

覆盖索引并不是一种索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到相应的主键值，想要获取最终的数据记录，还需要根据主键通过主键索引再去检索，最终获取到符合条件的数据记录。

在上面的abc_innodb表中的组合索引查询时，如果我们查询的结果只需要a、b、c这三个字段，那我们使用这个idx_index(a，b，c)组合索引查询到叶子节点时就可以直接返回了，而不需要再次回表查询，这种情况就是覆盖索引。

未使用索引覆盖的情况：

select * from abc_innodb where a=13 and b=16 and c=4;

索引覆盖的情况:

select a,b,c from abc_innodb where a=13 and b=16 and c=4;

---

二、MyIsam索引

以一个简单的user表为例。user表存在两个索引，id列为主键索引，age列为普通索引

CREATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

 MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

 表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。

简单分析下查询时的磁盘IO情况：

根据主键等值查询数据

select * from user where id = 28

 

• 第一次磁盘IO：先在主键索引树中从根节点开始检索，将根节点加载到内存中，比较28<75，所以走左子树。
• 第二次磁盘IO：将左子树节点加载到内存中，比较16<28<47，向下检索。
• 第三次磁盘IO：检索到叶子节点，将节点加载到内存中遍历，从16<28，18<28，28=28，查找到键值等于28的索引项。
• 第四次磁盘IO：从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。
• 将记录返回给客户端。

磁盘IO次数：3次索引检索+记录数据检索。

根据主键范围查询数据：

select * from user where id between 28 and 47;

 

• 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
• 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
• 检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。
• 根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
• 我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
• 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
• 最后得到两条符合筛选条件，将查询结果集返给客户端。

磁盘IO次数：4次索引检索+记录数据检索。

• 辅助索引

在MyISAM存储引擎中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点中data阈存储的都是行记录的磁盘地址。 主键列索引的键值是唯一的，而辅助索引的键值是可以重复的。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

---

总结

1.除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

2.根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

3.使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)等就会停止匹配