[MYSQL] mysql checksum table原理深度分析

[MYSQL] mysql checksum table原理深度分析

导读

之前我们简单介绍过checksum table的原理, 总结就是: 各行的校验值加起来就是最终的结果.

但实际会复杂一丢丢.(比如null bitmask,int类型等,甚至还有逻辑顺序和物理顺序之分). 所以我们来一点点分析.

深度分析

我们先要明白一点, checksum是server层做的, 也就是存储引擎得返回逻辑数据给server层去做计算. 所以只解析innodb数据来计算是不对的(主要是int的转换)

现象

先来看看nullable的影响, nullable即表示字段是否为空.

create table test_null(name varchar(20));
insert into test_null values('ddcw');
create table test_notnull(name varchar(20) not null);
insert into test_notnull values('ddcw');
checksum table test_null,test_notnull;

我们可以看到明明插入的一样的数据, checksum值居然不一样

分析

这就说明和是否为空有关系. 我们查看源码 发现会先对null mask做crc2校验

简单来说就是: 256 - (1<<最后一个null bitmask的位置(0-7)) 得到null_mask, 然后把null_bytes的最后一字节改为null_mask, 如果存在HA_OPTIO_PACK_RECORD(字符串) 就给null_bytes的第一字节+1. 最后对这个null_bytes做crc2, 作为第0个字段的crc2值.

比较绕, 也比较花里胡哨. 我们使用python代码来表示就清楚多了

def get_nullbitmask_crc2(null_count,null_bitmask,status=True):
        null_bitmask = bytearray(null_bitmask[::-1]) # 先来个两级反转
        if status:
                null_mask = 256 - (1 << (null_count+1)%8 )
                null_bitmask[-1] = null_mask
                null_bitmask[0] |= 1
        else:
                null_mask = 256 - (1 << null_count%8 )
                null_bitmask[-1] = null_mask
        return (bytes(null_bitmask))

null_count: 就是表有多少个字段可以为空.

null_bitmask 就是用来表示实际行是否为空的字节. (innodb是大端字节序, mysql是小端字节序, 所以要先反转一下, 后面的int类型也是这样)

status: 对应HA_OPTIO_PACK_RECORD 我们可以使用ibd2sdi查看

当存在varchar(可溢出字段)时, pack_record=1 否则pack_record=0 (8.0.28之前的checksum table的bug也是因为这玩意....)

status:True 表示没得varchar False:表示有varchar 使用的时候注意下

验证

那我们来验证一波

import binascii
null_count = 1
null_bitmask = b'\x00'
status = False
null_crc2 = get_nullbitmask_crc2(null_count,null_bitmask,status)
row_crc2 = (b'ddcw',null_crc2)
print('有nullable的表CRC2:',row_crc2)


row_crc2 = (b'ddcw',0)
print('not null的表CRC2:',row_crc2)

和上面checksum table的值对应上了. 说明逻辑没得问题.

接下来就是看数据类型了, 主要是int类型存在大小端问题, 字符串是没得这种问题的. 我们知道innodb是小端字节序, mysql 是大端自己序. 所以修改的时候我们只需要简单的反转一下再注意下符号即可.

现象

我们来看下一样数据的两个表的crc2值

create table test_int(id int);
insert into test_int values(1);
create table test_int_unsigned(id int unsigned);
insert into test_int_unsigned values(1);
checksum table test_int,test_int_unsigned;

我们看到数据是相同的.

分析

我们知道 innodb中有符号的1应该是b'\x80\x00\x00\x01' 没得符号的1应该是b'\x00\x00\x00\x01' 所以计算crc2之前需要转换成mysql的小端数据. 直接上python代码吧. 我这里就直接计算crc2值了.

def int2crc2(bdata,c=0,unsigned=False):
        """
        innodb的int计算crc2值(checksum是mysql server做的, 所以不能直接crc2 innodb的, 要先转换一下)
        bdata: innodb记录的int,bigint的二进制数据  其它的均直接(bdata,crc2)
        c: crc2值(int)
        unsigned: 是否有符号, 默认有符号
        """
        bdata = bytearray(bdata[::-1]) # mysql:小端好 innodb:大端好
        if not unsigned:
                bdata[-1] += 128 if bdata[-1] < 128 else -128
        return (bytes(bdata),c)

验证

然后我们来验证验证下

import binascii
signed_int   = b'\x80\x00\x00\x01'
unsigned_int = b'\x00\x00\x00\x01'
null_crc2 = get_nullbitmask_crc2(1,b'\x00',True)
print('有符号的int CRC:',int2crc2(signed_int,null_crc2,False))
print('无符号的int CRC:',int2crc2(unsigned_int,null_crc2,True))

我们可以看到也是和mysql校验的一样的.

innodb是按照 主键,普通字段存储的, 但表结构主键可能在普通字段后面. 但既然是server层校验, 那么就应该和索引位置无关, 即按照逻辑顺序来. 对于做过ISTAT DDL的也需要注意这个问题.

我们调整下索引位置即可.

create table test_logic_pos(aa varchar(200) primary key, bb varchar(200));
insert into test_logic_pos values('aa','bb');
create table test_physical_pos(aa varchar(200), bb varchar(200)  primary key);
insert into test_physical_pos values('aa','bb');
checksum table test_logic_pos,test_physical_pos;

我们看到校验值是一样的, 说明确实是按照逻辑顺序来的

校验的时候是否会考虑生成字段呢?

现象

create table test_base(id int);
create table test_gen_stored(id int,gen_stored IT GEERATED ALWAYS AS (id + 1) STORED);
create table test_gen_virtual(id int,gen_stored IT GEERATED ALWAYS AS (id + 1) virtual);
insert into test_base values(1);
insert into test_gen_stored(id) values(1);
insert into test_gen_virtual(id) values(1);
checksum table test_base,test_gen_stored,test_gen_virtual;

我们发现生成字段数据是一样的, 且是计算了生成字段的. (毕竟server层做的计算)

分析

对于stored和virtual是一样的就说明virtual也会占用null bitmask但不占用存储(花里胡哨的). stored不但占用null bitmask还会占用存储.

验证

然后我们使用python来验证下

null_crc2 = get_nullbitmask_crc2(2,b'\x00',True)
int2crc2(b'\x80\x00\x00\x02',int2crc2(b'\x80\x00\x00\x01',null_crc2,False),False)

计算值确实和checksum 一致. 而null count 我们均是按照2(含生成列)来计算的.

总结

从上面的各例子看, checksum table校验是数据是逻辑的二进制数据. 即存储引擎层返回数据给server层, server层按照表字段顺序做校验, 并将最终的校验值加起来作为最终checksum值.

既然知道了更深层的原理, 那么我们就可以写存储过程或者脚本来自己实现checksum table的校验了. 存储过程的话, 主要是得判断字段能否为空和是否为空, 然后先计算null bitmask的crc2值, 然后按照字段顺序校验. 比如:

测试了下, 自己写的脚本速度不如官方的(开到4并发都没追上,py的性能还是堪忧啊). 汇总如下

所以影响checksum table值的因数

1. nullable 字段能否为空 (空值不参与crc2计算,但空值的bitmask要)
2. 字段逻辑顺序

不影响的因素

1. 存储引擎(是server层计算的)
2. 生成列是否虚拟(按照逻辑数据, 不考虑是否存储)
3. unsigned 虽然有符号的1和无符号的1存储不同, 但不影响checksum
4. 行的顺序(是所有行的crc2加起来, 所以行的顺序不影响)