單條記錄大小增長倍數和ibd文件大小的增長倍數不成正比 環境信息 資料庫版本: GreatSQL 8.0.25 字元集：utf8mb4 innodb_default_row_format: dynamic innodb_page_size: 16384 問題描述 表數據為新insert數據，無del ...
單條記錄大小增長倍數和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多倍？
greatsql> show create table t_user_100_1000_100 \G;
*************************** 1. row ***************************
Table: t_user_100_1000_100
Create Table: CREATE TABLE `t_user_100_1000_100` (
`id` int NOT NULL AUTO_INCREMENT,
`c_name1` varchar(10) NOT NULL DEFAULT '',
。。。
`c_name100` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
greatsql> show create table t_user_100_10000_100 \G;
*************************** 1. row ***************************
Table: t_user_100_10000_100
Create Table: CREATE TABLE `t_user_100_10000_100` (
`id` int NOT NULL AUTO_INCREMENT,
`c_name1` varchar(100) NOT NULL DEFAULT '',
。。。
`c_name100` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
greatsql> select count() from t_user_100_10000_100 ;
+----------+
| count() |
+----------+
| 10000 |
+----------+
1 row in set (0.06 sec)
greatsql> select count() from t_user_100_1000_100 ;
+----------+
| count() |
+----------+
| 10000 |
+----------+
1 row in set (0.18 sec)
#os ibd 文件大小
ll
total 4313096
-rw-r----- 1 mysql mysql 5016387584 Apr 9 18:52 t_user_100_10000_100.ibd
-rw-r----- 1 mysql mysql 20971520 Apr 9 18:40 t_user_100_1000_100.ibd
greatsql> select 5016387584/20971520 from dual;
+---------------------+
| 5016387584/20971520 |
+---------------------+
| 239.2000 |
+---------------------+
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。
dberr_t insert(){
......
//分配一個新的16KB的PAGE
first_page_t first(mtr, index);
buf_block_t *first_block = first.alloc(mtr, ctx->is_bulk());
......
//將100位元組寫入寫入
ulint to_write = first.write(trxid, ptr, len);
......
}
以下是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
