mysql索引底层原理及联合索引

B+TREE

b+tree 是innodb存储引擎的底层结构, 如果想知道innodb如何存储数据, 首先需要掌握b+tree这个数据结构, 下面通过一张图来反映 : 

链接 : https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 

向b+tree中插入8条数据, 结果如上图 , 图中的叶子节点应该是双向指针

从上图可以看出, b+tree中的全部数据都在叶子节点中有所体现, 非叶子节点和叶子节点出现了数据冗余

---

接下来创建一个表, 根据表中的具体数据来展示索引的存储情况

CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t1 value(4,3,1,1,'d');
insert into t1 value(1,1,1,1,'a');
insert into t1 value(8,8,8,8,'h');
insert into t1 value(2,2,2,2,'b');
insert into t1 value(5,2,3,5,'e');
insert into t1 value(3,3,2,2,'c');
insert into t1 value(7,4,5,5,'g');
insert into t1 value(6,6,4,4,'f');

 

我们插入的数据是无序的, 但是当我们进行查询时, 默认根据主键值排序, 这就是b+tree在起作用,默认根据主键id进行排序, 插入到b+tree中

那我们思考一个问题, 当我们执行一个 select * from t1 a = 7这个sql语句时, innodb底层是如何查询的呢? 可能会想从a=1开始遍历, 一直遍历到 a=7, 但是还不能停止, 因为不确定后面还有没有a=7这种数据(a不是唯一索引), 一直遍历到最后一条数据,返回查询到的结果, 但是这种情况显然效率很慢,需要在磁盘上进行数据查询, mysql肯定不会这样做

实际上这里有一个页的概念, 一个页的大小是16k, 能够存16k的数据, 现在我们这个t1表中,一条数据大概占用 4 + 4 + 4 + 4 + 4 = 20b大小的空间, 数据库一次读取可以读取一个页, 将这个页中的数据加载到内存中, 之后再在内存中查找这一个页中的数据, 找到我们想要的数据

---

页中的结构

从上图可以看出  一个页主要由4部分组成 ( 并非如此, 可以查看mysql官网页的组成结构部分)

• prev 指针 : 指向上一页的地址
• next 指针 : 指向下一页的地址
• 页目录 : 将数据区进行分组, 提高查询效率
• 用户数据区 : 主要用来存放数据

当我们向数据库中插入数据时, 即使是无序插入的, innodb也会默认按照主键排序, 并且进行分组, 默认是六条数据一组, 之后将每一组的第一列数据存储到页目录中, 当我们进行数据查询时 , 首先查询的页目录, 找到指定的页目录, 再去找页目录下的数据, 页目录是一个典型的以空间换取效率的一个结构

但是当我们的数据量越来越大时 , 一页显然是装不下的, 因此就会开辟新的页,  结构如下 : 

上面的结构可以将每一页看做一个节点连接起来的一个链表, 我们每次查询数据都需要判断某页中是否有这条数据, 如果没有遍历下一个表, 依次到最后一个表 , 假如我们的数据量达到千万级别, 像这种页的数据会非常多, 遍历起来页非常耗时, 因此需要将这个结构进行如下优化 : 

大家不难看出, 上图的数据结构与b+tree类似, 将每一页的最小数据值放到一个新的页中, 当我们进行查询时, 先查询这个页, 通过这个页我们可以找到数据存储在哪一个具体的页中, 然后在这个也中进行单独的查询

---

由上面的结构我们可以推断出我们插入的数据的结构 : 

当我们查询 select * from t1 where a = 6 时, 我们知道会使用主键索引

当我们查询  select * from t1 where a > 6 时, 会使用索引吗? 

同样也是使用了主键索引, 因为b+tree是排序的, 并且是一个双向链表

---

联合索引 

首先在t1表中创建一个联合索引

联合索引是指, 数据按照联合索引中的字段排序, 生成一个b+tree

根据b,c,d字段生成的b+tree, 我们可能想象的数据结构如下图

但是这样可能会有问题, 因为数据又从新生成了一份, 如果一个表中有多个索引的话, 每生成的一个b+tree都有全部的数据, 那么会有大量的数据冗余,浪费空间, 并且进行update操作时, 需要更新多个b+tree, 造成效率下降, 因此我们可能想象数据结构如下图 : 

当我们执行 select * from t1 where b=1 and c=1 and d=1时 : 

 很显然用到了联合索引a_b_c

但是有这样一个疑问, 我们执行的是select *, 当我们根据联合索引a_b_c查找到"111"后, 怎么查询到全部的数据呢? 如何获取a,e的值呢? 因此上图中的结构也是不对的, 正确的结构如下 : 

在对应的联合索引下, 存储一个主键值, 我们获取了这个联合索引这个行, 然后根据这个行存储的主键值再去主键索引中获取全部的数据, 相当于一个回表的操作, 如何证明这一行中存储了

大家都知道联合索引遵循最左前缀匹配原则, 那么 select * from t1 where c=1 and b = 1 and d=1会走联合索引吗?

显然是会走的, mysql有优化器, 会自动调整位置(在都是等值判断的情况下) 

 

那 select * from t1 where b = 1 会走联合索引吗?

很显然, 确实走了联合索引a_b_c

那 select * from t1 where b > 1 会走联合索引吗?

很显然,  没有走联合索引, 相信很多人都会有所疑问, 当我们查询b>1时, 我们确实可以从联合索引获取b>1的值的组合, 但是由于我们查询的是select *, 切t1表中只有8条数据, 符合条件的有7条, 因此需要进行7次回表, 这个效率要低于全表扫描, 因此索引不会起效

那 select * from t1 where b > 6 会走联合索引吗?

很显然 , 确实走了联合索引, 因为满足条件的数据只有2条, 回表的效率要比全表扫描的快

那么 select a,b,c,d from t1 where b>1 会走联合索引吗?

很显然, 走了联合索引, 不需要回表, 因此也证明了上面的结构我们分析的是正确的

 也会走索引

---

接下来给e字段添加索引

判断一下语句是否会走索引 : 

重点看一下最后两行, 当数据类型不同时会不会走索引 ? 

想要解决上面的问题, 需要了解如下内容 : 

当数据类型不相同时, 首先会把字符串转化为数字, 数字字符串转化成对应的数字, 字母字符串转化成数字0,

当查询 select * from t1 where e=1 时, 首先将e中的字符全部转化成数字, 然后再对比where后面的条件是否成立, 由于b+tree中存储的是e字段对应的字符串, 但是由于数据的转化, 因此不会走索引

同理: 当我们查询 explain select * from t1 where a + 1=1; 时也不会走索引, 因为改变了a的值, 不能通过b+tree进行查找