单条记录大小增长倍数和ibd文件大小的增长倍数不成正比

手电筒

单条记录大小增长倍数和ibd文件大小的增长倍数不成正比

环境信息

数据库版本: GreatSQL 8.0.25
字符集：utf8mb4
innodb_default_row_format: dynamic
innodb_page_size: 16384
问题描述

表数据为新insert数据，无delete、无update
GreatSQL 一个数据量为1万的A表，有100个varchar字段，每个字段存10字节，ibd大小为21M
GreatSQL 一个数据量为1万的B表，有100个varchar字段，每个字段存100字节，ibd大小为4.7G
问题：相同数据量，相同数据量，B表的每行比A表大10倍，磁盘使用大小不是10倍，而是200多倍？

1. greatsql> show create table t_user_100_1000_100  \G;
2. *************************** 1. row ***************************
3.        Table: t_user_100_1000_100
4. Create Table: CREATE TABLE `t_user_100_1000_100` (
5.   `id` int NOT NULL AUTO_INCREMENT,
6.   `c_name1` varchar(10) NOT NULL DEFAULT '',
7. 。。。
8.   `c_name100` varchar(10) NOT NULL DEFAULT '',
9.   PRIMARY KEY (`id`)
10. ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
11. 1 row in set (0.00 sec)
13. greatsql> show create table t_user_100_10000_100  \G;
14. *************************** 1. row ***************************
15.        Table: t_user_100_10000_100
16. Create Table: CREATE TABLE `t_user_100_10000_100` (
17.   `id` int NOT NULL AUTO_INCREMENT,
18.   `c_name1` varchar(100) NOT NULL DEFAULT '',
19. 。。。
20.   `c_name100` varchar(100) NOT NULL DEFAULT '',
21.   PRIMARY KEY (`id`)
22. ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
23. 1 row in set (0.00 sec)
25. greatsql> select count() from t_user_100_10000_100 ;
26. +----------+
27. | count() |
28. +----------+
29. | 10000 |
30. +----------+
31. 1 row in set (0.06 sec)
33. greatsql> select count() from t_user_100_1000_100 ;
34. +----------+
35. | count() |
36. +----------+
37. | 10000 |
38. +----------+
39. 1 row in set (0.18 sec)
41. #os ibd 文件大小
42. ll
43. total 4313096
44. -rw-r----- 1 mysql mysql 5016387584 Apr 9 18:52 t_user_100_10000_100.ibd
45. -rw-r----- 1 mysql mysql 20971520 Apr 9 18:40 t_user_100_1000_100.ibd
47. greatsql> select 5016387584/20971520 from dual;
48. +---------------------+
49. | 5016387584/20971520 |
50. +---------------------+
51. | 239.2000 |
52. +---------------------+
53. 1 row in set (0.00 sec)

复制代码

问题分析

多出来的24倍难道是碎片导致的？
使用optimize table重整表后，几乎没有任何优化，查看系统视图，也没有多少空洞。

使用官方工具innochecksum查看表空间文件PAGE类型分布，可以看到，B表相对A表多了大量的Other type of page。看来主要的空间消耗是在这个“Other”上。

• INNODB的行溢出
  

INNODB默认下每个PAGE的大小为16K。B表每行10K，每个PAGE只能存放1行记录，余下的6K就浪费了。但即使按照这个算法，也只浪费了37%的空间。
实际上，INNODB在这里有个处理，当记录过大，会将最大的列使用一个指针替代，指针指向一个新的PAGE，在该PAGE上存放实际数据。
由函数page_zip_rec_needs_ext()判断是否需要溢出。判断方式是该记录长度是否大于空PAGE的可用空间。

GDB执行一下，可以看到一个16k的PAGE实际可用的空间为16252字节（页头等占用了小量字节）。一半粗略算作是8k。

如果行长度大于8K，会将最长的列存放到新的PAGE，原位置使用20字节的指针代替。如果处理后，行长度依然大于8K，则选择当前最长的列进一步处理，不断循环。如果列长度无法进一步缩少，仍然大于8K，则抛出DB_TOO_BIG_RECORD错误，即“row size too large”。dtuple_convert_big_rec()函数上可以看到更多的执行细节。

• 小量数据溢出的情况
  

以下堆栈展示把溢出数据写入"Off Page"。主要函数为lob::insert()。

log::insert()会申请一个新的16K大小的PAGE，并将数据写入新的page。

1. dberr_t insert(){
2.   ......
3.   //分配一个新的16KB的PAGE
4.   first_page_t first(mtr, index);
5.   buf_block_t *first_block = first.alloc(mtr, ctx->is_bulk());
6.   ......
7.   //将100字节写入写入
8.   ulint to_write = first.write(trxid, ptr, len);
9.   ......
10. }

复制代码

以下是B表插入数据，往innodb"Off page"写入数据时候的断点，可以看到只写了B表单列100字节数据。16KB的容量只写100字节的数据，剩余99%的空间用不上，实在太浪费了。

B表有100个varchar列，每个列100字节。如果需要满足列长少于8K，需要25个列以上进行溢出（一个列还有隐藏列和其它数据，实际需要溢出的列略多于25）。使用innochecksum查询到“OTHER” page 有29万，B表有一万行，平均每行29个“OTHER” page。看来这个“OTHER” page基本都是这种“Off page”了。
问题总结

GreatSQL 白白浪费了95%的磁盘空间，是因为大量的列溢出了小量的数据。INNODB存放每个溢出列的数据的最小分配单元大小是16KB。原本10KB的行长度，需要多占N倍的存储空间。
优化建议

表设计时，要注意控制行长度小于8k,避免小量列数据溢出，导致磁盘容量和性能问题。
延伸阅读

• https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html
  
• https://mp.weixin.qq.com/s/AjGrQONFPJgVAqy_qtoiYA
  
• https://mp.weixin.qq.com/s/QMZ7O0gfs81zXo69F1bHdQ
  

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18362349
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！