深入InnoDB存储引擎最小存储单位剖析MySQL，其实索引真不难

最新推荐文章于 2022-11-03 20:22:57 发布

Java架构设计

最新推荐文章于 2022-11-03 20:22:57 发布

阅读量168

点赞数

文章标签： java 程序人生经验分享 mysql 开发语言

本文链接：https://blog.csdn.net/wdjnb/article/details/121557958

版权

本文从实际需求出发，详细介绍了MySQL InnoDB存储引擎的最小存储单位——页，以及B+树的原理。通过实例展示了如何通过页目录和B+树提高查询效率，解释了聚簇索引与非聚簇索引的区别，并讨论了磁盘预读对性能的影响。通过对索引原理的深入理解，有助于优化SQL查询，提升数据库性能。

摘要由CSDN通过智能技术生成

索引可以说是每个工程师的必备技能点，明白索引的原理对于写出高质量的 SQL 至关重要，今天我们就从 0 到 1 来理解下索引的原理，相信大家看完不光对索引还会对 MySQL 中 InnoDB 存储引擎的最小存储单位「页」会有更深刻的认识

从实际需求出发

假设有如下用户表:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` int(11) DEFAULT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
  `height` int(11) DEFAULT NULL COMMENT '身高',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

可以看到存储引擎使用的是 InnoDB，我们先来看针对此表而言工作中比较常用的 SQL 语句都有哪此，毕竟技术是要为业务需求服务的，

1. select * from user where id = xxx
2. select * from user order by id asc/desc
3. select * from user where age = xxx
4. select age from user where age = xxx
5. select age from user order by age asc/desc

既然要查询那我们首先插入一些数据吧，毕竟没有数据何来查询

insert into user ('name', 'age', 'height') values ('张三', 20, 170);
insert into user ('name', 'age', 'height') values ('李四', 21, 171);
insert into user ('name', 'age', 'height') values ('王五', 22, 172);
insert into user ('name', 'age', 'height') values ('赵六', 23, 173);
insert into user ('name', 'age', 'height') values ('钱七', 24, 174);

插入后表中的数据如下：

深入InnoDB存储引擎最小存储单位剖析MySQL，其实索引真不难

不知你有没发现我们在插入的时候并没有指定 id 值，但 InnoDB 为每条记录默认添加了一个 id 值，而且这个 id 值是递增的，每插入一条记录，id 递增 1，id 为什么要递增呢，主要是为了查询方便，每条记录按 id 由小到大的顺序用链表连接起来，这样每次查找 id = xxx 的值就从 id = 1 开始依次往后查找即可

深入InnoDB存储引擎最小存储单位剖析MySQL，其实索引真不难

现在假设我们要执行以下 SQL 语句，MySQL 会怎么查询呢

select * from user where id = 3

页

如前所述，首先从 id 最小的记录也就是 id = 1 读起，每次读一条记录，将其 id 值与要查询的值比较，连续读三次记录于是找到了记录 3，注意这个读的操作，是首先需要把存储在磁盘的记录读取到内存然后再比较 id 的，从磁盘读到内存算一次 IO，也就是说此过程中产生了三次 IO，如果只是几条记录还好，但如果要比较的条数多的话对性能是非常严重的挑战，如果我要查询为 id = 100 的记录那岂不是要产生 100 次 IO？既然瓶颈在 IO，那该怎么改进呢，很简单，我们现在的设计一次 IO 只能读一