作者:洪斌
爱可生南区负责人兼技术服务总监,MySQL ACE,擅长数据库架构规划、故障诊断、性能优化分析,实践经验丰富,帮助各行业客户解决 MySQL 技术问题,为金融、运营商、互联网等行业客户提供 MySQL 整体解决方案。
本文来源:转载自公众号-玩转MySQL
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
前两天有人问到如何计算索引高度,分享之前的一篇笔记。
InnoDB 表是 B+tree 索引结构,数据即索引。最小存储单元为 page,默认 16KB。
有两类索引:
- clustered index:主键或唯一索引为 key,其他字段值作为 value。每张 InnoDB 表是一个 clustered index。
- secondary index:指定字段为 key,value 是 clustered index 的 key(即主键)。
index page 由三种组合在一起形成一个 B+Tree。
1. root page:在索引首次创建时分配,page id 保存在数据字典中,记录保存指向子页的指针。root page 填满时不会发生分裂,而是将值移动到新的子页上,并分裂成两个。
2. non-leaf page:存放指向 leaf page 或其他 non-leaf page 的指针,也称为 internal page。
3. leaf page:存放数据。
每个 page 都有一个 level,leaf page 的 level 是 0,root page 的 level 取决于整个 B+Tree 的高度。
对于 leaf page,每条记录包含一个 next-record 指针,记录下一条记录的偏移量。记录按升序排列,但物理上并非顺序存放。infimum 和 supremum 是伪记录,代表最小值和最大值。
![b1efeff671bd9e5d5ca6d78508af224e.png](https://i-blog.csdnimg.cn/blog_migrate/b7ea4c6ce04f7552f0c6fdf38f18932d.png)
non-leaf page 与 leaf page 结构类似,不同的是,每条记录 key 存放指向子页的最小值,value 部分存放子页编号。
![94e363d30f73c93707093864008387f9.png](https://i-blog.csdnimg.cn/blog_migrate/7af2f01cde7948940f29b0c650c041ad.png)
如果在同一个 level 下超过 1 个 page,page 间会使用双向链表方式连接,每个 page 的 FIL 头存放指向 next page 和 prev page 的指针。
![b2e534ab6b706f85980a0372f5f711da.png](https://i-blog.csdnimg.cn/blog_migrate/ef909a89d9ae379fe0940db97fb799cb.png)
一个完整的索引树结构:
![b4fba8c9ef6778acd5be9df0588ef415.png](https://i-blog.csdnimg.cn/blog_migrate/655b18e7b3b0e64366c63c4cbbf99229.jpeg)
观察下 3 个表定义不同数据类型的主键,其数据页中存储记录的情况,每张表 90w 条记录。
![632f2eb04d5cded34167dd5dcf4380eb.png](https://i-blog.csdnimg.cn/blog_migrate/ab4901abf08a4c57d07c601a25e711d3.jpeg)
表 1:int 主键
non-leaf page(level 1) 每页有 1203 条记录,每条记录 13 byte(data/records)。
![92ca0ed711607b8a003a945ad0e0e0dc.png](https://i-blog.csdnimg.cn/blog_migrate/884d1e0d5d39b91dcccd6aa25d3e785a.jpeg)
leaf page(level 0) 每页有 73 条记录,每条记录 206 byte。
![d9c7822d9b5f398a8838f8cda1dd4f79.png](https://i-blog.csdnimg.cn/blog_migrate/71c5130c527384f70739a9e1950717e4.jpeg)
表 2:bigint 主键
non-leaf page(level 1) 每页有 928 条记录,每条记录 17 byte。
![63c1373f814f2344e192a2985aec73e4.png](https://i-blog.csdnimg.cn/blog_migrate/16c6e8bd5b202765693c931c03c2a9aa.jpeg)
leaf page(level 0) 每页有 72 条记录,每条记录 210 byte。
![6da2db79f763cf8eac6a95ef822745ff.png](https://i-blog.csdnimg.cn/blog_migrate/4b6afaf7d770835f307d437ea7367e69.jpeg)
表 3:uuid 主键
non-leaf page(level 1) 每个页可存储 357 个记录,每条记录 45 byte。
![ae5b6981c92518680cca23c98567d36e.png](https://i-blog.csdnimg.cn/blog_migrate/adf109e7b9470b6ade2800efdc97e466.jpeg)
leaf page(level 0) 每个页可存储 63 个记录,每条记录 238 byte。
![68f7a5d031db55f13b102ed8628e10d8.png](https://i-blog.csdnimg.cn/blog_migrate/62eb61fcd7b5becd0b426e76962d959e.jpeg)
![6e6bc909c9c6316f0a21ac058210526d.png](https://i-blog.csdnimg.cn/blog_migrate/c163a966abc2ab8eaf905c82c6a871e0.jpeg)
索引树高度估算
表 1:int 主键
![87d50fb016de8821ce9132df2c911876.png](https://i-blog.csdnimg.cn/blog_migrate/084c10f16c83d1a98359c1b52b571a11.jpeg)
表 2:bigint 主键
![4d075ef8e1f83290f05b6d76dfd50cbb.png](https://i-blog.csdnimg.cn/blog_migrate/d3cc87510400b256a74594728cdae5ac.jpeg)
表 3:char(36) 主键
![0b91d112898f5b1744f12cf762bd39c4.png](https://i-blog.csdnimg.cn/blog_migrate/1ef39738c98ca52f74797e7caff5af13.jpeg)
参考: https:// blog.jcole.us/2013/05/0 2/how-does-innodb-behave-without-a-primary-key/