searchusermenu
  • 发布文章
  • 消息中心
点赞
收藏
评论
分享
原创

MySQL单表数据量最佳实践

2024-07-18 09:48:14
13
0
以mysql innodb引擎为例,innodb引擎定制化的页大小默认为16K
我们可以通过命令查询
SHOW VARIABLES LIKE 'innodb_page_size';
这个innodb_page_size=16384字节=16KB,就是innodb中最小的存储单位。
假设我们一条数据是1K大小,理想情况下innodb中的一页能存16条数据(实际能用于存储数据的不足16K),数据库中的每条记录都是以页的组织方式进行管理的。
通常来说我们还会使用主键对单表进行排序,而主键索引对应的B+Tree数据结构大概长这样:
B+Tree由非叶子节点和叶子节点组成,非叶子节点不存储实际数据,只存储主键列+索引号(其实就是页号);叶子节点存储实际的数据,且前后节点之间还会双向关联。
由于每个非叶子节点能存储的索引记录很多,树整体的高度就矮,高度矮意味着调用磁盘IO的次数少,毕竟磁盘查询一次10ms的开销是非常昂贵的。
B+Tree的数据存储量
当我们有一张按主键排序的单表,主键为BIGINT(业务最常用),且假设一条记录1KB,innodb引擎默认页16KB
  • B+Tree的高度为H
  • 非叶子节点每页能存储的索引量为I
  • 叶子节点的每页能存储的数据量为Y
  • B+Tree能存储的数据总量为N(最底层叶子节点层)
我们知道:数据总量 N = (I ^ (H-1)) * Y
其中,Y就按最理想的16条来算(实际更少)。
且由于BIGINT字段占8个字节,innodb索引号(页号)通常设为4字节,两者加起来12字节,就能算出
每页索引存储量 I = 16 * 1024 / (8+4) = 1365
套入公式,可以得到:
  • 如果B+Tree的高度为2
N = (1365^(2-1))*16 = 21840
  • 如果B+Tree的高度为3
N = (1365^(3-1))*16 = 29811600,2980w条记录——28G
  • 如果B+Tree的高度为4
N = (1365^(4-1))*16 = 40692834000,406亿条记录——38T
当然,以上的数据总量N是最大理想情况,实际存储量肯定更小。
 
再者,按业务常见的100qps并发请求为例,查询表记录的IO次数取决于树的高度H,当H=3,一次db查询可能耗时10ms*3=30ms,那一块磁盘能支撑的qps = 1000 / 30 = 33,如果高峰期所有请求同时都打到这个磁盘上,那么磁盘就已经满负荷了,最后一条记录的延迟至少有66 * 30ms = 20s。
当H=4情况会更加糟糕,且存满数据时,单机的磁盘也无法负载。
 
因此,通常情况下H=3是一个没得选的选择,当然,实际情况需要我们根据业务qps,单条记录平均大小,主键类型,数据库规格,操作系统配置等多个因素综合考量单表存储的数据上限。
0条评论
0 / 1000
胡****亮
4文章数
0粉丝数
胡****亮
4 文章 | 0 粉丝
胡****亮
4文章数
0粉丝数
胡****亮
4 文章 | 0 粉丝
原创

MySQL单表数据量最佳实践

2024-07-18 09:48:14
13
0
以mysql innodb引擎为例,innodb引擎定制化的页大小默认为16K
我们可以通过命令查询
SHOW VARIABLES LIKE 'innodb_page_size';
这个innodb_page_size=16384字节=16KB,就是innodb中最小的存储单位。
假设我们一条数据是1K大小,理想情况下innodb中的一页能存16条数据(实际能用于存储数据的不足16K),数据库中的每条记录都是以页的组织方式进行管理的。
通常来说我们还会使用主键对单表进行排序,而主键索引对应的B+Tree数据结构大概长这样:
B+Tree由非叶子节点和叶子节点组成,非叶子节点不存储实际数据,只存储主键列+索引号(其实就是页号);叶子节点存储实际的数据,且前后节点之间还会双向关联。
由于每个非叶子节点能存储的索引记录很多,树整体的高度就矮,高度矮意味着调用磁盘IO的次数少,毕竟磁盘查询一次10ms的开销是非常昂贵的。
B+Tree的数据存储量
当我们有一张按主键排序的单表,主键为BIGINT(业务最常用),且假设一条记录1KB,innodb引擎默认页16KB
  • B+Tree的高度为H
  • 非叶子节点每页能存储的索引量为I
  • 叶子节点的每页能存储的数据量为Y
  • B+Tree能存储的数据总量为N(最底层叶子节点层)
我们知道:数据总量 N = (I ^ (H-1)) * Y
其中,Y就按最理想的16条来算(实际更少)。
且由于BIGINT字段占8个字节,innodb索引号(页号)通常设为4字节,两者加起来12字节,就能算出
每页索引存储量 I = 16 * 1024 / (8+4) = 1365
套入公式,可以得到:
  • 如果B+Tree的高度为2
N = (1365^(2-1))*16 = 21840
  • 如果B+Tree的高度为3
N = (1365^(3-1))*16 = 29811600,2980w条记录——28G
  • 如果B+Tree的高度为4
N = (1365^(4-1))*16 = 40692834000,406亿条记录——38T
当然,以上的数据总量N是最大理想情况,实际存储量肯定更小。
 
再者,按业务常见的100qps并发请求为例,查询表记录的IO次数取决于树的高度H,当H=3,一次db查询可能耗时10ms*3=30ms,那一块磁盘能支撑的qps = 1000 / 30 = 33,如果高峰期所有请求同时都打到这个磁盘上,那么磁盘就已经满负荷了,最后一条记录的延迟至少有66 * 30ms = 20s。
当H=4情况会更加糟糕,且存满数据时,单机的磁盘也无法负载。
 
因此,通常情况下H=3是一个没得选的选择,当然,实际情况需要我们根据业务qps,单条记录平均大小,主键类型,数据库规格,操作系统配置等多个因素综合考量单表存储的数据上限。
文章来自个人专栏
数据库的那些事
4 文章 | 1 订阅
0条评论
0 / 1000
请输入你的评论
1
0