当前位置:网站首页>MySQL索引的B+树到底有多高?
MySQL索引的B+树到底有多高?
2022-08-09 10:18:00 【InfoQ】
一、问题
- h:统称索引的高度;
- h1:聚簇索引的高度;
- h2:二级辅助索引的高度;
- k:中间结点的扇出系数。
二、分析
2.1 索引高度h与页面I/O数的关系
SELECT * FROM USER WHERE id=1
- 点查询:
- 聚族索引:h1
- 二级索引:
- 覆盖索引:h2
- 回表查询:h2+h1
- 范围查询:这种情况相对比较复杂,但跟点查询的原理类似,读者可自行分析;
- 全表查询:B+树的叶子结点是通过链表连接起来的,对于全表查询,需要从头到尾将所有的叶子结点访问一遍。
2.2 索引高度h的理论计算
k^(h-1)
k^(h-1)*n
三、查看索引真实高度的方法
SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE a.table_id = b.table_id
AND a.space <> 0;
hexdump
$hexdump -C -s 49216 -n 10 user.ibd
0000c040 00 01 00 00 00 00 00 00 03 2b
16384*3+64
- 通过
information_schema.INNODB_SYS_INDEXES
和information_schema.INNODB_SYS_TABLES
找到对应索引Root页在ibd文件的页面号PAGE_NO(聚簇索引通常为3,其他索引往上累加);
- 通过
hexdump -C -s 16384*PAGE_NO+64 -n 10 user.ibd
,前两个字节+1即为索引的高度。
四、验证索引的真实高度
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`card_id` INT(11) NOT NULL DEFAULT '0' COMMENT '身份证号',
`name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '英文名字',
`age` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '年龄',
`content` VARCHAR(1024) NOT NULL DEFAULT '' COMMENT '附属信息',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_card_id` (`card_id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
card_id
name
content
hexdump -C -s 49216 -n 10 user.ibd && hexdump -C -s 65600 -n 10 user.ibd && hexdump -C -s 81984 -n 10 user.ibd
idx_name
name
五、总结
- 一次查询的页面I/O数跟索引高度h呈正相关,主要分为以下几种情况:
- 点查询:
- 聚族索引:h1
- 二级索引:
- 覆盖索引:h2
- 回表查询:h2+h1
- 范围查询:这种情况相对比较复杂,但跟点查询的原理类似,读者可自行分析;
- 全表查询:B+树的叶子结点是通过链表连接起来的,对于全表查询,需要从头到尾将所有的叶子结点访问一遍。
- 索引高度通常为2~4层。在高度h=3、主键为int类型、行记录大小为1KB时,可索引的总行数为1170^2*16=2190W。这也是很多大厂将2000W作为分库分表标准的原因;
- 查看索引真实高度的方法如下:
- 通过
information_schema.INNODB_SYS_INDEXES
和information_schema.INNODB_SYS_TABLES
找到对应索引Root页在ibd文件的页面号PAGE_NO(聚簇索引通常为3,其他索引往上累加);
- 通过
hexdump -C -s 16384*PAGE_NO+64 -n 10 user.ibd
,前两个字节+1即为索引的高度。
- 索引高度h也跟索引字段的数据类型有关。如果是int或short,扇出系数大,索引效率更好,整个索引看起来属于“矮胖”型;而如果是varchar(32)等,那扇出系数就低了,整个索引看起来属于“瘦高”型,索引效率自然要低些。所以我们在字段选取类型时,其类型越简单效率越好。
#聚簇索引(第一个)
hexdump -C -s 49216 -n 10 user.ibd
#第二个索引
hexdump -C -s 65600 -n 10 user.ibd
#第三个索引
hexdump -C -s 81984 -n 10 user.ibd
waterystone
边栏推荐
猜你喜欢
动态内存管理
拿下跨界C1轮投资,本土Tier 1高阶智能驾驶系统迅速“出圈”
深度学习--神经网络(基础讲解)
MySQL执行过程及执行顺序
Quick sort eight sorts (3) 】 【 (dynamic figure deduction Hoare, digging holes, front and rear pointer method)
Loop nesting and basic operations on lists
Electron application development best practices
【八大排序①】插入排序(直接插入排序、希尔排序)
Practical skills: a key for image information in the Harbor, quick query image
【八大排序②】选择排序(选择排序,堆排序)
随机推荐
极域Killer 1.0代码
实现下拉加载更多
【size_t是无符号整数 (-1 > 10) -> 1】
【Linux】宝塔面板设置MySQL慢查询日志,未走索引日志
Master-slave postition changes cannot be locked_Slave_IO_Running shows No_Slave_Sql_Running shows No---Mysql master-slave replication synchronization 002
OpengGL绘制立方体的三种方法
ArrayList和LinkedList
markdown转ipynb--利用包notedown
程序环境和预处理
Electron application development best practices
【八大排序③】快速排序(动图演绎Hoare法、挖坑法、前后指针法)
自启服务mock联调跨域问题
判断一段文字的width
Redis + NodeJS 实现一个能处理海量数据的异步任务队列系统
【MySQL】mysql因为字符集导致left join出现Using join buffer (Block Nested Loop)
Apache Log4j 2 远程代码执行漏洞详解
antd的Table列选择、列拓展
基于信号量与环形队列实现读写异步缓存队列
1003 我要通过! (20 分)
蓄电池建模、分析与优化(Matlab代码实现)