四、主键和普通索引叶子节点的行数据在存储上有哪些区别?
下面我先总结一下:
- 主键会包含全部的字段,普通索引只会包含它定义的字段内容
- 主键会包含trx id和roll ptr,普通索引不会包含
- 即便不定义主键也会包含一个根据rowid排列的聚集索引,很明显如果不定义普通索引则不会存在
- 普通索引叶子结点包含了主键或者rowid
下面我们验证一下,我们来观察第2行数据,即:
| 2| gaopeng | NULL | gaopeng |
在主键上这条记录存在于(offset:180)中,在普通索引这条记录存在于(offset:137)中,下面我们分别解析:
主键(block 3 offset 180 ):
[root@gp1 test]# ./bcview baguait1.ibd 1618050|grep 00000003
current block:00000003--Offset:00180--cnt bytes:50--data is:8000000200000005d96adc00000042011067616f70656e6767616f70656e6720202020202020202020202020070600002000
解析一下:
- 80000002:主键
- 00000005d96a:trx id 6字节
- dc000000420110:undo ptr 7字节
- 67616f70656e67:第二个字段的‘gaopeng’的ASCII编码
- 67616f70656e6720202020202020202020202020:第四个字段的‘gaopeng’的ASCII编码,并且因为是char(20)类型因此出现了0X20补足的情况,这实际上也解决了第5个问题,我们可以实实在在的看到这种补足操作,占用了更多的空间。
这里我们发现这条记录没有第三个字段,因为其为NULL,其包含在NULL位图中,后面我们会说明。
普通索引(block 4 offset 137 ):
[root@gp1 test]# ./bcview baguait1.ibd 16 137 20 |grep 00000004 current block: 00000004 -- Offset : 00137 --cnt bytes: 20 --data is : 67616f70656e67800000020700000020ffd56761
解析如下:
- 67616f70656e67:‘gaopeng’的ASCII编码
- 80000002:主键值2
后面的内容是下一行的行头了,这一点如果不确定可以看看最后一行,最后一行的位置是(offset:173)查看如下:
[root@gp1 test]# ./bcview baguait1.ibd 16 173 20 |grep 00000004 current block: 00000004 -- Offset : 00173 --cnt bytes: 20 --data is : 6180000004000000000000000000000000000000
解析为:
- 61:‘a’的ASCII编码
- 80000004:主键值4
后面是0了,我们这里可以看到没有trx id和roll ptr,除了键值以外普通索引还包含了主键。
五、char和varchar在存储上的区别?
这一点我在上面已经说了,下面我们还是以第二行数据为例:
| 2| gaopeng | NULL | gaopeng |
其中第1个‘gaopeng’是varchar(20)第2个‘gaopeng’是char(20)下面是他们的存储方式:
- 67616f70656e67:第二个字段的‘gaopeng’的ASCII编码
- 67616f70656e6720202020202020202020202020:第四个字段的‘gaopeng’的ASCII编码,并且因为是char(20)类型因此出现了0X20补足20字节的情况,我们可以实实在在的看到这种补足操作,占用了更多的空间。
不再过多熬述
六、数据中的NULL值如何存储的?
这一点还记得‘行头’的NULL位图吗?实际上这个位图会为每一个可以为NULL的字段预留1位的空间,用于标记是否字段的值为NULL,当然至少1字节(8位)。
+----+---------+---------+---------+ | id | c1 | c2 | c3 | +----+---------+---------+---------+ | 1 | NULL | gaopeng | gaopeng | | 2 | gaopeng | NULL | gaopeng | | 3 | gaopeng | NULL | NULL | | 4 | a | NULL | NULL | +----+---------+---------+---------+
c1\c2\c3均可以为空,因此我们分别访问4条记录聚集索引(block 3)上的NULL位图信息,计算方式如下:
- 第1行:记录(offset:128)那么128-5(5字节固定)-1(1字节NULL位图)= 122
- 第2行:记录(offset:180)那么180-5(5字节固定)-1(1字节NULL位图)= 174
- 第3行:记录(offset:231)那么231-5(5字节固定)-1(1字节NULL位图)= 225
- 第4行:记录(offset:262)那么262-5(5字节固定)-1(1字节NULL位图)= 256
好了有了偏移量我们可以使用bcview访问这1字节的NULL位图信息了如下:
第1行 [root@gp1 test]# ./bcview baguait1.ibd 16 122 1 |grep 00000003 current block:00000003--Offset:00122--cnt bytes:01--data is:01 转换为二进制为:0000 0001
第2行 [root@gp1 test]# ./bcview baguait1.ibd 16 174 1 |grep 00000003 current block:00000003--Offset:00174--cnt bytes:01--data is:02 转换为二进制为:0000 0010
第3行 [root@gp1 test]# ./bcview baguait1.ibd 16 225 1 |grep 00000003 current block:00000003--Offset:00225--cnt bytes:01--data is:06 转换为二进制为:0000 0110
第4行 [root@gp1 test]# ./bcview baguait1.ibd 16 256 1 |grep 00000003 current block:00000003--Offset:00256--cnt bytes:01--data is:06 转换为二进制为:0000 0110
下面就是NULL位图的表示方法,1为NULL,我们发现和我们记录中的NULL记录一模一样。
c3 | c2 | c1 | |
第1行 | 0 | 0 | 1 |
第2行 | 0 | 1 | 0 |
第3行 | 1 | 1 | 0 |
第4行 | 1 | 1 | 0 |
我们DDL修改字段的NULL属性的时候并不能通过修改数据字典来快速完成,我觉得修改更改ibd文件的实际内容是其中很大的一部分原因。下面是我修改NULL属性的记录,具体参考官方文档。
设置NULL和NOT NULL属性 都是inplace方式,因为需要修改NULL位图 因此都需要重组,代价较高 ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE; ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;