[MYSQL] decimal的存储设计

原创

大大刺猬

发布于 2024-11-28 16:44:45

49300

代码可运行

文章被收录于专栏：大大刺猬大大刺猬

运行总次数：0

代码可运行

导读

昨天, 有大佬发现了ibd2sql对于decimal取值的BUG, 并提出了修改建议(已修复). 那正好, 今天就来~~水一篇~~聊聊mysql的decimal的存储设计.

介绍

先简单介绍下decimal数据类型, decimal就是用来存储含小数的数的, 能存小数的还有float和double, float和double差不多,只是精度问题, float(25-53)就相当于double了, 而且它们都是将整数和小数放一起的.而且是标准类型,各语言都有相应的包可以直接读. 而decimal的话, 其实并没得严格的标准, 各语言,各程序都可以自己定义其结构, 比如mysql中的decimal就分为整数部分和小数部分.

比如python中,可以使用struct包读.(struct.unpack('d',double_data))

我们直接来看一个实际的例子吧.

create table test_decimal(
id int primary key,
dt_decimal decimal(13,8)
);
insert into test_decimal values(1,-99999.99999999);
insert into test_decimal values(2,99999.99999999);

上述表结构中, decimal(13,8) 表示,该字段一共13位(整数个数+小数个数), 小数部分占8位, 那么整数部分就占5位. 能表示的范围就是-99999.99999999 -- 99999.99999999

mysql中numeric就是decimal, 是完全一样的. numeric的存在只是为了兼容性而已.

存储设计

以前我们讲过innodb存储设计的时候会尽可能的减少空间, 所以就不会直接当作字符串,或者直接当作bigint/int等来存储. 查询官方资料后, 我们知道其存储规则如下:

decimal最多支持65位(整数+小数). 估计是太大了,没意义.在存储上未限制,应该是server层做的限制
小数部分不能大于(小数+整数部分), 就是上面的8不能大于13 (这不废话? 估计是怕吧总位数搞错成整数部分.)
重点来了哈, 我们知道1字节能表示255, 就是一定能表示2位10进制(258之类的3位10进制无法表示); 4字节能表示的范围是4294967296, 也就是一定能表示9位10进制. 所以每9位10进制使用4字节, 不过的, 就每2位10进制使用1字节, 最后可能还剩1位10进制,那就被迫使用1字节了哦.

整数部分和小数部分完全一样

对于第三点如果比较难理解的话, 可以快速查表

10进制位数	要使用的字节数量
0	0
1–2	1
3–4	2
5–6	3
7–9	4

可能还是难以理解, 没事, 我们来看俩例子

decimal(13,8)
	整数部分: (13-8)  查表得到使用3字节
	小数部分: 8       查表得到使用4字节
一共3+4=7字节

decimal(33,10)
	整数部分: (33-10) 拆分为 9*2 + 5  就是 4*2 + 3 字节
	小数部分: 10      拆分为 9 + 1    就是 4 + 1 字节
一共(4*2 + 3) + (4 + 1) = 16字节

如果还是难以理解的话, 我们就使用py代码来计算吧

import re
TEST_DECIMAL_VAR = 'decimal(33,10)'
total_digits, decimal_digits = re.compile('decimal\((.+)\)').findall(TEST_DECIMAL_VAR ,)[0].split(',')
total_digits = int(total_digits)
decimal_digits = int(decimal_digits)
integer_p1_count = int((total_digits - decimal_digits)/9) #
integer_p2_count = total_digits - decimal_digits - integer_p1_count*9
integer_size = integer_p1_count*4 + int((integer_p2_count+1)/2)
decimal_p1_count = int(decimal_digits/9)
decimal_p2_count = decimal_digits - decimal_p1_count*9
decimal_size = decimal_p1_count*4 + int((decimal_p2_count+1)/2)
total_size = integer_size + decimal_size
size = total_size #decimal占用大小

print('整数部分字节数量:',integer_size)
print('小数部分字节数量:',decimal_size)
print('一共字节数量:',size )

如果还是难以理解的话, 就不用理解了, 我们使用现成的工具(ibd2sql)来查看.

python3 main.py /data/mysql_3314/mysqldata/db1/t20241128_3.ibd --sql --debug

我们可以看到这个字段一共占用了16字节, 位于该PAGE的194-->210位置.

decimal解析

存储结构就是那么个结构, 实际解析读取的时候, 还要注意的是zfill. (当然也有符号之类的问题,但不是本文的重点)

即比如使用4字节记录的9位10进制值可能是 123, 还不到9位, 即应该是000000123, 对于整数部分没啥影响, 但对于小数部分的话, 就有问题了, 本来 1.000000123的值如果不填充的话, 就会变成1.123, 显然不符合我们实际的值. 而对于什么时候应该填充呢? 填充多少呢? 我们使用代码来表示吧, 简单点..

# isend表示是否是最后一部分数据了.
# extra[2][1] 表示小数部分的长度
# t2.count 表示已经取了多少个9位10进制.
# _data表示本次解析的数据
if isend:
        if t2.count == 1:
                _data =  str(_data).zfill(extra[2][1])
        else:
                _data =  str(_data).zfill(extra[2][1]-9*t2.count)
else:
        if _data < 0:
                _data = "-" + str(_data)[1:].zfill(9)
        else:
                _data =  str(_data).zfill(9)

当我们全部解析出来,并填充之后, 直接当作字符串拼接即可.