mysql的limit的分页使用(获取 MySQL innodb B+tree 的高度的方法)
mysql的limit的分页使用
获取 MySQL innodb B+tree 的高度的方法前言
MySQL 的 innodb 引擎之所以使用 B+tree 来存储索引,就是想尽量减少数据查询时磁盘 IO 次数。树的高度直接影响了查询的性能。一般树的高度在 3~4 层较为适宜。数据库分表的目的也是为了控制树的高度。那么如何获取树的高度呢?下面使用一个示例来说明如何获取树的高度。
示例数据准备
建表语句如下:
|
CREATE TABLE ` user ` ( `id` int (11) NOT NULL AUTO_INCREMENT, ` name ` varchar (100) CHARACTER SET latin1 DEFAULT NULL , `age` int (11) DEFAULT NULL , PRIMARY KEY (`id`), KEY ` name ` (` name `), KEY `age` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
表中插入100万条数据。数据如下:
|
mysql> select * from user limit 2\G *************************** 1. row *************************** id: 110000 name : ab age: 100 *************************** 2. row *************************** id: 110001 name : ab age: 100 2 rows in set (0.00 sec) |
通过查询相关数据表获取树的高度
以 MySQL5.6 版本为例说明如何获取树的高度。
首先获取 page_no
|
mysql> 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 and b. name = 'test/user' ; + -----------+---------+----------+------+-------+---------+ | name | name | index_id | type | space | PAGE_NO | + -----------+---------+----------+------+-------+---------+ | test/ user | PRIMARY | 22 | 3 | 6 | 3 | | test/ user | name | 23 | 0 | 6 | 4 | | test/ user | age | 24 | 0 | 6 | 5 | + -----------+---------+----------+------+-------+---------+ 3 rows in set (0.00 sec) |
page_no 是索引树中Root页的序列号。其它各项的含义可以参照:
https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html
再读取页的大小
|
mysql> show global variables like 'innodb_page_size' ; + ------------------+-------+ | Variable_name | Value | + ------------------+-------+ | innodb_page_size | 16384 | + ------------------+-------+ 1 row in set (0.00 sec) |
最后读取索引树的高度
|
$ hexdump -s 49216 -n 10 . /user .ibd 000c040 0200 0000 0000 0000 1600 000c04a |
可以发现 PAGE_LEVEL 为 0200,表示这棵二级索引树的高度为 3。后面的 1600 是索引的 index_id 值。十六进制的 16 转换为十进制数字是 22。这个 22 正好就是上面主键的 index_id。
上面 hexdump 命令中 49216 是怎么算出来的?公式是 page_no * innodb_page_size + 64。
3*16384+64=49216
我们在用这个方式查看下其他两个索引的高度。
|
$ hexdump -s 65600 -n 10 . /user .ibd 0010040 0100 0000 0000 0000 1700 001004a $ hexdump -s 81984 -n 10 . /user .ibd 0014040 0200 0000 0000 0000 1800 001404a |
可见,name 索引的高度是 2,age 索引的高度是 3。
根据索引的结构估算
如果你没有数据库服务器的权限。自己也可以根据数据库索引结构进行估算树的高度。
根据 B+Tree 结构,非叶子节点存储的是索引数据,叶子节点存储的是每行的所有数据。
非叶子节点每个索引项的大小是,数据大小+指针大小。假设指针大小为 8 个字节。每页不会被占满,预留1/5的空隙。下面我们估算下 name 和 age 两个索引的高度。
name 索引高度估算
非叶子节点每页存放的索引项数量。每页大小是 16k。name 的值为 ab。占2个字节。每项数据大小是 2+8=10字节。每页能存放的索引项数量是 16384 * 0.8 / 10 = 1310 个。
叶子节点每页存放的索引数量。每页大小是 16k。每项数据大小是 4+2+8=14 个字节。没页能存放的索引数量是 16384 * 0.8 / 14 = 936 个。
两层能存放 1310*936=1226160 个数据记录。可见120万条记录以下,树的高度为2。
age 索引高度估算
非叶子节点每页存放的索引项数量。每页大小是 16k。age 的类型为 int。占4个字节。每项数据大小是 4+8=12字节。每页能存放的索引项数量是 16384 * 0.8 / 12 = 1092 个。
叶子节点每页存放的索引数量。每页大小是 16k。每项数据大小是 4+4+8=16 个字节。没页能存放的索引数量是 16384 * 0.8 / 16 = 819 个。
两层能存放 1092*819=894348 个数据记录。可见90万条记录以下,树的高度为2。100万条为 3 层。
其它工具
还有一个小工具可以查看。InnoDB 表空间可视化工具innodb_ruby
以上就是获取 MySQL innodb 的 B+tree 的高度的示例的详细内容,更多关于MySQL innodb 的 B+tree 的资料请关注开心学习网其它相关文章!
原文链接:https://www.bo56.com/%e5%a6%82%e4%bd%95%e8%8e%b7%e5%8f%96-mysql-innodb-%e7%9a%84-btree-%e7%9a%84%e9%ab%98%e5%ba%a6/
- mysql数据库调优技术大全(Mysql数据库性能优化三分表、增量备份、还原)
- mac更改mysql密码(Mac下mysql 8.0.22 找回密码的方法)
- mysql顺序排序(Mysql 中文排序规则说明)
- 怎么将mysql彻底卸载(如何把Mysql卸载干净亲测有效)
- 分享mysql设计规范(MySQL 可扩展设计的基本原则)
- 如何使java与sqlserver数据库连接(java连接mysql数据库 java连接sql server数据库)
- mysql字符串的表示方法(详解mysql中的字符集和校验规则)
- mysql索引知识点总结(MySQL 8.0 之索引跳跃扫描Index Skip Scan)
- docker怎么连接mysql(docker如何安装mysql)
- mysql新增字段并添加备注(mysql添加备注信息的实现)
- mysql拆分成多行(mysql单字段多值分割和合并的处理方法)
- mysql查询性能优化详解(实例讲解MySQL 慢查询)
- mysql中innodb四种事务级别(MySQL InnoDB 锁的相关总结)
- mysql简单查询基本语句(详解MySQL 查询语句的执行过程)
- mysql意外查不到数据(MySQL 丢失数据的原因及解决)
- mysql将字符串转换为日期的函数(Mysql中时间戳转为Date的方法示例)
- 终于来了,淘宝更改账户名测试中,快去看看你能不能修改(淘宝更改账户名测试中)
- 淘宝支持账号名修改,网友 终于可以 重新做人 了(淘宝支持账号名修改)
- 盘点那些年让人称奇的年终奖 最后一个赢辣条毫无悬念(盘点那些年让人称奇的年终奖)
- 你还没有升职吗 他竟因为几套激励理论,升职了(你还没有升职吗)
- 某知名企业绩效管理体系及薪酬分配体系操作手册(某知名企业绩效管理体系及薪酬分配体系操作手册)
- 职场人改不掉这4个习惯,只会越混越穷,一辈子也翻不了身(职场人改不掉这4个习惯)
热门推荐
- html5炫酷代码(HTML5实现视频弹幕功能)
- javascript中还原append代码(JS实现jQuery的append功能)
- python分割字符串要用哪一个语句(python使用threading.Condition交替打印两个字符)
- submit与button的区别
- h5前端入门(前端H5 Video常见使用场景简介)
- python制作彩色字符(Python3利用print输出带颜色的彩色字体示例代码)
- sqlserver去除空格函数(sqlServer实现去除字符串空格)
- mysql主键为什么用varchar(Mysql中varchar类型一些需要注意的地方)
- docker端口映射和暴露端口的区别(解决docker指定udp端口号的问题)
- css鼠标点击效果怎么样(CSS实现鼠标滑过鼠标点击代码写法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9