MySQL索引底层原理

前言

MySQL的索引底层数据结构采用的是 B+树，一个索引对应一个B+树

特点

• 一个节点里可以存储多个元素，减少树的深度
• 非叶子节点所有元素都会在叶子节点冗余一份
• 叶子节点从左到右是升序的
• 叶子节点有指向相邻叶子节点的指针，方便顺序遍历数据

图例

MySQL数据存储

当我们往表里加入数据的时候，MySQL是将表存储在文件里的
当我们读取表中数据的时候，MySQL需要从磁盘中将数据读取出来，放入内存中

磁盘和内存的数据交互涉及到IO，操作系统存在一个局部性原理，局部性原理是指当操作系统发现我们要取 1 kb 数据的时候，操作系统其实是会取出这一页（通常来说是4kb）的数据，也就是说这 1 kb 相邻的数据也会同时取出来

在InnoDB引擎里也存在 页 的概念，不同于操作系统，InnoDB里 一页 = 16 kb（可手动修改）

简化版页结构

数据索引过程

真正在执行sql的时候，有一个查询优化器会估算各方法的查询效率，选择最优解

创建表

CREATE TABLE `demo_table` (
  `id` int(20) NOT NULL,
  `name` varchar(45),
  `age` int(11),
  `score` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入八条数据后，索引结构如下

全表扫描

执行

explain SELECT * FROM demo_table;

解释

• type 为 ALL，表示 全表扫描，即从左至右扫描主键索引的叶子节点
  

主键索引

执行

explain SELECT * FROM demo_table where id = 4;

解释

• type 为 const，表示语句执行速度近似等于常量
• key 为 PRIMARY，表示主键索引
  

如果一张表没有主键，InnoDB会寻找有没有唯一索引，如果有则将唯一索引列作为主键，如果没有，则会添加一个隐含字段作为主键字段（rowid）

辅助索引

除开主键索引，其他索引都可以称之为辅助索引，也叫联合索引

创建辅助索引（非唯一的联合索引）

CREATE INDEX `idx_name_age` ON demo_table(`name`, `age`);

建立索引实际是对数据排序，辅助索引生成的B+树叶子节点顺序是根据索引列表排序。
本例中，排序规则：先将name列排序，如果name相等则再根据 age 进行排序
（字符集的排序规则为 collation 指定，本例为 utf8mb4_0900_ai_ci）

主键索引B+树的叶子节点会包含该行数据的全部字段，但辅助索引B+树的叶子节点只保存该行对应的主键。
根据主键去主键索引B+树中找出该行全部字段数据，这个步骤叫做 回表
执行

explain SELECT * FROM demo_table where name = 'ming' and age = 18;

解释

• key 为 idx_name_age，表示走的索引 idx_name_age

一条sql能够走索引是因为给出的条件能够根据B+树一步步缩小结果集，所以有 最左前缀原则

根据最左前缀原则，这条语句将不能成功走索引

explain SELECT * FROM demo_table where age = 18;

进阶

(1) 分析这条sql，是否能命中索引？

explain SELECT * FROM demo_table where name > 'ming';

答案是不能

解释

• type 为 ALL，表示全表扫描
• possible_key 为 idx_name_age，表示可能命中的索引

按照之前所说的逻辑，只要在B+树中找到 name = ming 的叶子节点，那么右边的叶子节点都是结果集，但为什么不能走索引呢？
原来这条sql的执行过程是

• 第一步也是找 name = ming，即MySQL对于范围查询，第一步也是找 等于条件 的叶子节点
• “select * ” 意味着当根据索引找到结果集后要根据结果集进行回表
• 回表是涉及到磁盘IO的，不管是单个主键结果还是多个主键结果集，MySQL每次回表都只能查一个主键。这就涉及到很多的磁盘IO，如果结果集比较多的话，再加上本身 name = ming 也涉及磁盘IO
• 这种情况下走全表扫描竟然比走索引进行的磁盘IO更少一点，所以MySQL进行了全表扫描

结论：如果根据索引找出的结果集比较少的话，会命中索引；结果集比较多的话，那么会放弃索引走全表扫描

(2) 分析这条语句，是否能命中索引？

explain SELECT name,age FROM demo_table where name > 'ming';

答案是可以

解释

• Extra 多了一个 Using index，表示覆盖索引（不需要回表），原来是辅助索引上已有 name,age 的值，并且由于辅助索引叶子节点不是完整的数据，导致辅助索引叶子节点单个体积比主键索引叶子节点要小，那么辅助索引的页数可能比主键索引要少，最终辅助索引涉及到的磁盘IO会更加少

(3) 以字段更多的rank_report表为例，该表主键为id，并以 cate_id 列建立辅助索引，表中数据有上万条。分析这条sql，是否能命中索引？

explain SELECT * FROM rank_report order by cate_id;

答案是 不能

解释

• order by 逻辑（即Using filesort逻辑）：MySQL每条语句都有一个线程，为每个线程开辟一个内存空间（大小可设置），然后将数据全部放到这个内存空间中去，然后CPU会根据你指定的顺序（DESC 或者 ASC）排序。如果内存空间放不下要排序的数据，则只能利用某个临时文件进行排序，这就是Using filesort的名字由来

(4) 根据主键来排序，则放进内存空间的数据已经是排序的好的，省略了CPU排序这一步，Extra 字段为空，能够命中索引

explain SELECT * FROM rank_report order by id;

(5) 将 select * 换成 select cate_id，那么也能走索引

explain SELECT cate_id FROM rank_report order by cate_id;

(6) 以 demo_table为例，分析这条语句，是否能命中索引？

explain SELECT name,age FROM test.demo_table order by name,age desc;

答案是不能

那要怎么优化这条sql语句呢？
原来 建立索引的时候也是有一个默认的顺序的，即ASC。MySQL里可以指定索引的顺序是ASC还是DESC