发布于 2024-04-11 14:30:52

字段个数与字段长度对idb大小的影响

数据库版本: MySQL 8.0.25
字符集：utf8mb4

问题描述
表数据为新insert数据，无delete、无update
mysql 一个数据量为1万的表，有100个字段，每个字段存10个字母，idb大小为21M
mysql 一个数据量为1万的表，有100个字段，每个字段存100个字母，idb大小为4.7G
为什么磁盘使用大小为什么不是10倍，而是200多倍？

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql> select count() from t_user_100_10000_100 ;
+----------+
| count() |
+----------+
| 10000 |
+----------+
1 row in set (0.06 sec)
mysql> 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 4395630592 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

mysql>
mysql> select 4395630592/20971520 from dual;
+---------------------+
| 4395630592/20971520 |
+---------------------+
| 209.6000 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tables where TABLE_SCHEMA='aa'\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: aa
     TABLE_NAME: t_user_100_10000_100
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 5000
 AVG_ROW_LENGTH: 992496
    DATA_LENGTH: 4962484224
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 1048576
 AUTO_INCREMENT: 10001
    CREATE_TIME: 2024-04-10 09:41:23
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 2. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: aa
     TABLE_NAME: t_user_100_1000_100
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 10000
 AVG_ROW_LENGTH: 1522
    DATA_LENGTH: 15220736
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 2097152
 AUTO_INCREMENT: 10001
    CREATE_TIME: 2024-04-10 09:42:57
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
2 rows in set (0.01 sec)

ERROR: 
No query specified

mysql> 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_name2` varchar(10) NOT NULL DEFAULT '',
  `c_name3` varchar(10) NOT NULL DEFAULT '',
  `c_name4` varchar(10) NOT NULL DEFAULT '',
  `c_name5` varchar(10) NOT NULL DEFAULT '',
  `c_name6` varchar(10) NOT NULL DEFAULT '',
  `c_name7` varchar(10) NOT NULL DEFAULT '',
  `c_name8` varchar(10) NOT NULL DEFAULT '',
  `c_name9` varchar(10) NOT NULL DEFAULT '',
  `c_name10` varchar(10) NOT NULL DEFAULT '',
  `c_name11` varchar(10) NOT NULL DEFAULT '',
  `c_name12` varchar(10) NOT NULL DEFAULT '',
  `c_name13` varchar(10) NOT NULL DEFAULT '',
  `c_name14` varchar(10) NOT NULL DEFAULT '',
  `c_name15` varchar(10) NOT NULL DEFAULT '',
  `c_name16` varchar(10) NOT NULL DEFAULT '',
  `c_name17` varchar(10) NOT NULL DEFAULT '',
  `c_name18` varchar(10) NOT NULL DEFAULT '',
  `c_name19` varchar(10) NOT NULL DEFAULT '',
  `c_name20` varchar(10) NOT NULL DEFAULT '',
  `c_name21` varchar(10) NOT NULL DEFAULT '',
  `c_name22` varchar(10) NOT NULL DEFAULT '',
  `c_name23` varchar(10) NOT NULL DEFAULT '',
  `c_name24` varchar(10) NOT NULL DEFAULT '',
  `c_name25` varchar(10) NOT NULL DEFAULT '',
  `c_name26` varchar(10) NOT NULL DEFAULT '',
  `c_name27` varchar(10) NOT NULL DEFAULT '',
  `c_name28` varchar(10) NOT NULL DEFAULT '',
  `c_name29` varchar(10) NOT NULL DEFAULT '',
  `c_name30` varchar(10) NOT NULL DEFAULT '',
  `c_name31` varchar(10) NOT NULL DEFAULT '',
  `c_name32` varchar(10) NOT NULL DEFAULT '',
  `c_name33` varchar(10) NOT NULL DEFAULT '',
  `c_name34` varchar(10) NOT NULL DEFAULT '',
  `c_name35` varchar(10) NOT NULL DEFAULT '',
  `c_name36` varchar(10) NOT NULL DEFAULT '',
  `c_name37` varchar(10) NOT NULL DEFAULT '',
  `c_name38` varchar(10) NOT NULL DEFAULT '',
  `c_name39` varchar(10) NOT NULL DEFAULT '',
  `c_name40` varchar(10) NOT NULL DEFAULT '',
  `c_name41` varchar(10) NOT NULL DEFAULT '',
  `c_name42` varchar(10) NOT NULL DEFAULT '',
  `c_name43` varchar(10) NOT NULL DEFAULT '',
  `c_name44` varchar(10) NOT NULL DEFAULT '',
  `c_name45` varchar(10) NOT NULL DEFAULT '',
  `c_name46` varchar(10) NOT NULL DEFAULT '',
  `c_name47` varchar(10) NOT NULL DEFAULT '',
  `c_name48` varchar(10) NOT NULL DEFAULT '',
  `c_name49` varchar(10) NOT NULL DEFAULT '',
  `c_name50` varchar(10) NOT NULL DEFAULT '',
  `c_name51` varchar(10) NOT NULL DEFAULT '',
  `c_name52` varchar(10) NOT NULL DEFAULT '',
  `c_name53` varchar(10) NOT NULL DEFAULT '',
  `c_name54` varchar(10) NOT NULL DEFAULT '',
  `c_name55` varchar(10) NOT NULL DEFAULT '',
  `c_name56` varchar(10) NOT NULL DEFAULT '',
  `c_name57` varchar(10) NOT NULL DEFAULT '',
  `c_name58` varchar(10) NOT NULL DEFAULT '',
  `c_name59` varchar(10) NOT NULL DEFAULT '',
  `c_name60` varchar(10) NOT NULL DEFAULT '',
  `c_name61` varchar(10) NOT NULL DEFAULT '',
  `c_name62` varchar(10) NOT NULL DEFAULT '',
  `c_name63` varchar(10) NOT NULL DEFAULT '',
  `c_name64` varchar(10) NOT NULL DEFAULT '',
  `c_name65` varchar(10) NOT NULL DEFAULT '',
  `c_name66` varchar(10) NOT NULL DEFAULT '',
  `c_name67` varchar(10) NOT NULL DEFAULT '',
  `c_name68` varchar(10) NOT NULL DEFAULT '',
  `c_name69` varchar(10) NOT NULL DEFAULT '',
  `c_name70` varchar(10) NOT NULL DEFAULT '',
  `c_name71` varchar(10) NOT NULL DEFAULT '',
  `c_name72` varchar(10) NOT NULL DEFAULT '',
  `c_name73` varchar(10) NOT NULL DEFAULT '',
  `c_name74` varchar(10) NOT NULL DEFAULT '',
  `c_name75` varchar(10) NOT NULL DEFAULT '',
  `c_name76` varchar(10) NOT NULL DEFAULT '',
  `c_name77` varchar(10) NOT NULL DEFAULT '',
  `c_name78` varchar(10) NOT NULL DEFAULT '',
  `c_name79` varchar(10) NOT NULL DEFAULT '',
  `c_name80` varchar(10) NOT NULL DEFAULT '',
  `c_name81` varchar(10) NOT NULL DEFAULT '',
  `c_name82` varchar(10) NOT NULL DEFAULT '',
  `c_name83` varchar(10) NOT NULL DEFAULT '',
  `c_name84` varchar(10) NOT NULL DEFAULT '',
  `c_name85` varchar(10) NOT NULL DEFAULT '',
  `c_name86` varchar(10) NOT NULL DEFAULT '',
  `c_name87` varchar(10) NOT NULL DEFAULT '',
  `c_name88` varchar(10) NOT NULL DEFAULT '',
  `c_name89` varchar(10) NOT NULL DEFAULT '',
  `c_name90` varchar(10) NOT NULL DEFAULT '',
  `c_name91` varchar(10) NOT NULL DEFAULT '',
  `c_name92` varchar(10) NOT NULL DEFAULT '',
  `c_name93` varchar(10) NOT NULL DEFAULT '',
  `c_name94` varchar(10) NOT NULL DEFAULT '',
  `c_name95` varchar(10) NOT NULL DEFAULT '',
  `c_name96` varchar(10) NOT NULL DEFAULT '',
  `c_name97` varchar(10) NOT NULL DEFAULT '',
  `c_name98` varchar(10) NOT NULL DEFAULT '',
  `c_name99` 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)



mysql> 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_name2` varchar(100) NOT NULL DEFAULT '',
  `c_name3` varchar(100) NOT NULL DEFAULT '',
  `c_name4` varchar(100) NOT NULL DEFAULT '',
  `c_name5` varchar(100) NOT NULL DEFAULT '',
  `c_name6` varchar(100) NOT NULL DEFAULT '',
  `c_name7` varchar(100) NOT NULL DEFAULT '',
  `c_name8` varchar(100) NOT NULL DEFAULT '',
  `c_name9` varchar(100) NOT NULL DEFAULT '',
  `c_name10` varchar(100) NOT NULL DEFAULT '',
  `c_name11` varchar(100) NOT NULL DEFAULT '',
  `c_name12` varchar(100) NOT NULL DEFAULT '',
  `c_name13` varchar(100) NOT NULL DEFAULT '',
  `c_name14` varchar(100) NOT NULL DEFAULT '',
  `c_name15` varchar(100) NOT NULL DEFAULT '',
  `c_name16` varchar(100) NOT NULL DEFAULT '',
  `c_name17` varchar(100) NOT NULL DEFAULT '',
  `c_name18` varchar(100) NOT NULL DEFAULT '',
  `c_name19` varchar(100) NOT NULL DEFAULT '',
  `c_name20` varchar(100) NOT NULL DEFAULT '',
  `c_name21` varchar(100) NOT NULL DEFAULT '',
  `c_name22` varchar(100) NOT NULL DEFAULT '',
  `c_name23` varchar(100) NOT NULL DEFAULT '',
  `c_name24` varchar(100) NOT NULL DEFAULT '',
  `c_name25` varchar(100) NOT NULL DEFAULT '',
  `c_name26` varchar(100) NOT NULL DEFAULT '',
  `c_name27` varchar(100) NOT NULL DEFAULT '',
  `c_name28` varchar(100) NOT NULL DEFAULT '',
  `c_name29` varchar(100) NOT NULL DEFAULT '',
  `c_name30` varchar(100) NOT NULL DEFAULT '',
  `c_name31` varchar(100) NOT NULL DEFAULT '',
  `c_name32` varchar(100) NOT NULL DEFAULT '',
  `c_name33` varchar(100) NOT NULL DEFAULT '',
  `c_name34` varchar(100) NOT NULL DEFAULT '',
  `c_name35` varchar(100) NOT NULL DEFAULT '',
  `c_name36` varchar(100) NOT NULL DEFAULT '',
  `c_name37` varchar(100) NOT NULL DEFAULT '',
  `c_name38` varchar(100) NOT NULL DEFAULT '',
  `c_name39` varchar(100) NOT NULL DEFAULT '',
  `c_name40` varchar(100) NOT NULL DEFAULT '',
  `c_name41` varchar(100) NOT NULL DEFAULT '',
  `c_name42` varchar(100) NOT NULL DEFAULT '',
  `c_name43` varchar(100) NOT NULL DEFAULT '',
  `c_name44` varchar(100) NOT NULL DEFAULT '',
  `c_name45` varchar(100) NOT NULL DEFAULT '',
  `c_name46` varchar(100) NOT NULL DEFAULT '',
  `c_name47` varchar(100) NOT NULL DEFAULT '',
  `c_name48` varchar(100) NOT NULL DEFAULT '',
  `c_name49` varchar(100) NOT NULL DEFAULT '',
  `c_name50` varchar(100) NOT NULL DEFAULT '',
  `c_name51` varchar(100) NOT NULL DEFAULT '',
  `c_name52` varchar(100) NOT NULL DEFAULT '',
  `c_name53` varchar(100) NOT NULL DEFAULT '',
  `c_name54` varchar(100) NOT NULL DEFAULT '',
  `c_name55` varchar(100) NOT NULL DEFAULT '',
  `c_name56` varchar(100) NOT NULL DEFAULT '',
  `c_name57` varchar(100) NOT NULL DEFAULT '',
  `c_name58` varchar(100) NOT NULL DEFAULT '',
  `c_name59` varchar(100) NOT NULL DEFAULT '',
  `c_name60` varchar(100) NOT NULL DEFAULT '',
  `c_name61` varchar(100) NOT NULL DEFAULT '',
  `c_name62` varchar(100) NOT NULL DEFAULT '',
  `c_name63` varchar(100) NOT NULL DEFAULT '',
  `c_name64` varchar(100) NOT NULL DEFAULT '',
  `c_name65` varchar(100) NOT NULL DEFAULT '',
  `c_name66` varchar(100) NOT NULL DEFAULT '',
  `c_name67` varchar(100) NOT NULL DEFAULT '',
  `c_name68` varchar(100) NOT NULL DEFAULT '',
  `c_name69` varchar(100) NOT NULL DEFAULT '',
  `c_name70` varchar(100) NOT NULL DEFAULT '',
  `c_name71` varchar(100) NOT NULL DEFAULT '',
  `c_name72` varchar(100) NOT NULL DEFAULT '',
  `c_name73` varchar(100) NOT NULL DEFAULT '',
  `c_name74` varchar(100) NOT NULL DEFAULT '',
  `c_name75` varchar(100) NOT NULL DEFAULT '',
  `c_name76` varchar(100) NOT NULL DEFAULT '',
  `c_name77` varchar(100) NOT NULL DEFAULT '',
  `c_name78` varchar(100) NOT NULL DEFAULT '',
  `c_name79` varchar(100) NOT NULL DEFAULT '',
  `c_name80` varchar(100) NOT NULL DEFAULT '',
  `c_name81` varchar(100) NOT NULL DEFAULT '',
  `c_name82` varchar(100) NOT NULL DEFAULT '',
  `c_name83` varchar(100) NOT NULL DEFAULT '',
  `c_name84` varchar(100) NOT NULL DEFAULT '',
  `c_name85` varchar(100) NOT NULL DEFAULT '',
  `c_name86` varchar(100) NOT NULL DEFAULT '',
  `c_name87` varchar(100) NOT NULL DEFAULT '',
  `c_name88` varchar(100) NOT NULL DEFAULT '',
  `c_name89` varchar(100) NOT NULL DEFAULT '',
  `c_name90` varchar(100) NOT NULL DEFAULT '',
  `c_name91` varchar(100) NOT NULL DEFAULT '',
  `c_name92` varchar(100) NOT NULL DEFAULT '',
  `c_name93` varchar(100) NOT NULL DEFAULT '',
  `c_name94` varchar(100) NOT NULL DEFAULT '',
  `c_name95` varchar(100) NOT NULL DEFAULT '',
  `c_name96` varchar(100) NOT NULL DEFAULT '',
  `c_name97` varchar(100) NOT NULL DEFAULT '',
  `c_name98` varchar(100) NOT NULL DEFAULT '',
  `c_name99` 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)

复现造数脚本
脚本1：100个字段1000字节1万数据量造数脚本

CREATE TABLE `t_user_100_1000_100` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name1` varchar(10) NOT NULL DEFAULT '',
  `c_name2` varchar(10) NOT NULL DEFAULT '',
  `c_name3` varchar(10) NOT NULL DEFAULT '',
  `c_name4` varchar(10) NOT NULL DEFAULT '',
  `c_name5` varchar(10) NOT NULL DEFAULT '',
  `c_name6` varchar(10) NOT NULL DEFAULT '',
  `c_name7` varchar(10) NOT NULL DEFAULT '',
  `c_name8` varchar(10) NOT NULL DEFAULT '',
  `c_name9` varchar(10) NOT NULL DEFAULT '',
  `c_name10` varchar(10) NOT NULL DEFAULT '',
  `c_name11` varchar(10) NOT NULL DEFAULT '',
  `c_name12` varchar(10) NOT NULL DEFAULT '',
  `c_name13` varchar(10) NOT NULL DEFAULT '',
  `c_name14` varchar(10) NOT NULL DEFAULT '',
  `c_name15` varchar(10) NOT NULL DEFAULT '',
  `c_name16` varchar(10) NOT NULL DEFAULT '',
  `c_name17` varchar(10) NOT NULL DEFAULT '',
  `c_name18` varchar(10) NOT NULL DEFAULT '',
  `c_name19` varchar(10) NOT NULL DEFAULT '',
  `c_name20` varchar(10) NOT NULL DEFAULT '',
  `c_name21` varchar(10) NOT NULL DEFAULT '',
  `c_name22` varchar(10) NOT NULL DEFAULT '',
  `c_name23` varchar(10) NOT NULL DEFAULT '',
  `c_name24` varchar(10) NOT NULL DEFAULT '',
  `c_name25` varchar(10) NOT NULL DEFAULT '',
  `c_name26` varchar(10) NOT NULL DEFAULT '',
  `c_name27` varchar(10) NOT NULL DEFAULT '',
  `c_name28` varchar(10) NOT NULL DEFAULT '',
  `c_name29` varchar(10) NOT NULL DEFAULT '',
  `c_name30` varchar(10) NOT NULL DEFAULT '',
  `c_name31` varchar(10) NOT NULL DEFAULT '',
  `c_name32` varchar(10) NOT NULL DEFAULT '',
  `c_name33` varchar(10) NOT NULL DEFAULT '',
  `c_name34` varchar(10) NOT NULL DEFAULT '',
  `c_name35` varchar(10) NOT NULL DEFAULT '',
  `c_name36` varchar(10) NOT NULL DEFAULT '',
  `c_name37` varchar(10) NOT NULL DEFAULT '',
  `c_name38` varchar(10) NOT NULL DEFAULT '',
  `c_name39` varchar(10) NOT NULL DEFAULT '',
  `c_name40` varchar(10) NOT NULL DEFAULT '',
  `c_name41` varchar(10) NOT NULL DEFAULT '',
  `c_name42` varchar(10) NOT NULL DEFAULT '',
  `c_name43` varchar(10) NOT NULL DEFAULT '',
  `c_name44` varchar(10) NOT NULL DEFAULT '',
  `c_name45` varchar(10) NOT NULL DEFAULT '',
  `c_name46` varchar(10) NOT NULL DEFAULT '',
  `c_name47` varchar(10) NOT NULL DEFAULT '',
  `c_name48` varchar(10) NOT NULL DEFAULT '',
  `c_name49` varchar(10) NOT NULL DEFAULT '',
  `c_name50` varchar(10) NOT NULL DEFAULT '',
  `c_name51` varchar(10) NOT NULL DEFAULT '',
  `c_name52` varchar(10) NOT NULL DEFAULT '',
  `c_name53` varchar(10) NOT NULL DEFAULT '',
  `c_name54` varchar(10) NOT NULL DEFAULT '',
  `c_name55` varchar(10) NOT NULL DEFAULT '',
  `c_name56` varchar(10) NOT NULL DEFAULT '',
  `c_name57` varchar(10) NOT NULL DEFAULT '',
  `c_name58` varchar(10) NOT NULL DEFAULT '',
  `c_name59` varchar(10) NOT NULL DEFAULT '',
  `c_name60` varchar(10) NOT NULL DEFAULT '',
  `c_name61` varchar(10) NOT NULL DEFAULT '',
  `c_name62` varchar(10) NOT NULL DEFAULT '',
  `c_name63` varchar(10) NOT NULL DEFAULT '',
  `c_name64` varchar(10) NOT NULL DEFAULT '',
  `c_name65` varchar(10) NOT NULL DEFAULT '',
  `c_name66` varchar(10) NOT NULL DEFAULT '',
  `c_name67` varchar(10) NOT NULL DEFAULT '',
  `c_name68` varchar(10) NOT NULL DEFAULT '',
  `c_name69` varchar(10) NOT NULL DEFAULT '',
  `c_name70` varchar(10) NOT NULL DEFAULT '',
  `c_name71` varchar(10) NOT NULL DEFAULT '',
  `c_name72` varchar(10) NOT NULL DEFAULT '',
  `c_name73` varchar(10) NOT NULL DEFAULT '',
  `c_name74` varchar(10) NOT NULL DEFAULT '',
  `c_name75` varchar(10) NOT NULL DEFAULT '',
  `c_name76` varchar(10) NOT NULL DEFAULT '',
  `c_name77` varchar(10) NOT NULL DEFAULT '',
  `c_name78` varchar(10) NOT NULL DEFAULT '',
  `c_name79` varchar(10) NOT NULL DEFAULT '',
  `c_name80` varchar(10) NOT NULL DEFAULT '',
  `c_name81` varchar(10) NOT NULL DEFAULT '',
  `c_name82` varchar(10) NOT NULL DEFAULT '',
  `c_name83` varchar(10) NOT NULL DEFAULT '',
  `c_name84` varchar(10) NOT NULL DEFAULT '',
  `c_name85` varchar(10) NOT NULL DEFAULT '',
  `c_name86` varchar(10) NOT NULL DEFAULT '',
  `c_name87` varchar(10) NOT NULL DEFAULT '',
  `c_name88` varchar(10) NOT NULL DEFAULT '',
  `c_name89` varchar(10) NOT NULL DEFAULT '',
  `c_name90` varchar(10) NOT NULL DEFAULT '',
  `c_name91` varchar(10) NOT NULL DEFAULT '',
  `c_name92` varchar(10) NOT NULL DEFAULT '',
  `c_name93` varchar(10) NOT NULL DEFAULT '',
  `c_name94` varchar(10) NOT NULL DEFAULT '',
  `c_name95` varchar(10) NOT NULL DEFAULT '',
  `c_name96` varchar(10) NOT NULL DEFAULT '',
  `c_name97` varchar(10) NOT NULL DEFAULT '',
  `c_name98` varchar(10) NOT NULL DEFAULT '',
  `c_name99` varchar(10) NOT NULL DEFAULT '',
  `c_name100` varchar(10) NOT NULL DEFAULT '',  
  PRIMARY KEY (`id`)
) CHARSET=utf8mb4;



# 创建随机字符串
delimiter $$
DROP FUNCTION IF EXISTS `randStr` $$
CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(1000) DEFAULT '' ;
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END$$



# 创建插入数据存储过程
delimiter $$
CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_100_1000_100`(IN n int)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
        INSERT INTO t_user_100_1000_100 (c_name1, c_name2, c_name3, c_name4, c_name5, c_name6, c_name7, c_name8, c_name9, c_name10, c_name11, c_name12, c_name13, c_name14, c_name15, c_name16, c_name17, c_name18, c_name19, c_name20, c_name21, c_name22, c_name23, c_name24, c_name25, c_name26, c_name27, c_name28, c_name29, c_name30, c_name31, c_name32, c_name33, c_name34, c_name35, c_name36, c_name37, c_name38, c_name39, c_name40, c_name41, c_name42, c_name43, c_name44, c_name45, c_name46, c_name47, c_name48, c_name49, c_name50, c_name51, c_name52, c_name53, c_name54, c_name55, c_name56, c_name57, c_name58, c_name59, c_name60, c_name61, c_name62, c_name63, c_name64, c_name65, c_name66, c_name67, c_name68, c_name69, c_name70, c_name71, c_name72, c_name73, c_name74, c_name75, c_name76, c_name77, c_name78, c_name79, c_name80, c_name81, c_name82, c_name83, c_name84, c_name85, c_name86, c_name87, c_name88, c_name89, c_name90, c_name91, c_name92, c_name93, c_name94, c_name95, c_name96, c_name97, c_name98, c_name99, c_name100 ) VALUES ( randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10), randStr(10));
        SET i = i + 1;
    END WHILE;
END $$
delimiter ;



call  add_t_user_100_1000_100(10000);

脚本2：100个字段10000字节1万数据量造数脚本

CREATE TABLE `t_user_100_10000_100` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name1` varchar(100) NOT NULL DEFAULT '',
  `c_name2` varchar(100) NOT NULL DEFAULT '',
  `c_name3` varchar(100) NOT NULL DEFAULT '',
  `c_name4` varchar(100) NOT NULL DEFAULT '',
  `c_name5` varchar(100) NOT NULL DEFAULT '',
  `c_name6` varchar(100) NOT NULL DEFAULT '',
  `c_name7` varchar(100) NOT NULL DEFAULT '',
  `c_name8` varchar(100) NOT NULL DEFAULT '',
  `c_name9` varchar(100) NOT NULL DEFAULT '',
  `c_name10` varchar(100) NOT NULL DEFAULT '',
  `c_name11` varchar(100) NOT NULL DEFAULT '',
  `c_name12` varchar(100) NOT NULL DEFAULT '',
  `c_name13` varchar(100) NOT NULL DEFAULT '',
  `c_name14` varchar(100) NOT NULL DEFAULT '',
  `c_name15` varchar(100) NOT NULL DEFAULT '',
  `c_name16` varchar(100) NOT NULL DEFAULT '',
  `c_name17` varchar(100) NOT NULL DEFAULT '',
  `c_name18` varchar(100) NOT NULL DEFAULT '',
  `c_name19` varchar(100) NOT NULL DEFAULT '',
  `c_name20` varchar(100) NOT NULL DEFAULT '',
  `c_name21` varchar(100) NOT NULL DEFAULT '',
  `c_name22` varchar(100) NOT NULL DEFAULT '',
  `c_name23` varchar(100) NOT NULL DEFAULT '',
  `c_name24` varchar(100) NOT NULL DEFAULT '',
  `c_name25` varchar(100) NOT NULL DEFAULT '',
  `c_name26` varchar(100) NOT NULL DEFAULT '',
  `c_name27` varchar(100) NOT NULL DEFAULT '',
  `c_name28` varchar(100) NOT NULL DEFAULT '',
  `c_name29` varchar(100) NOT NULL DEFAULT '',
  `c_name30` varchar(100) NOT NULL DEFAULT '',
  `c_name31` varchar(100) NOT NULL DEFAULT '',
  `c_name32` varchar(100) NOT NULL DEFAULT '',
  `c_name33` varchar(100) NOT NULL DEFAULT '',
  `c_name34` varchar(100) NOT NULL DEFAULT '',
  `c_name35` varchar(100) NOT NULL DEFAULT '',
  `c_name36` varchar(100) NOT NULL DEFAULT '',
  `c_name37` varchar(100) NOT NULL DEFAULT '',
  `c_name38` varchar(100) NOT NULL DEFAULT '',
  `c_name39` varchar(100) NOT NULL DEFAULT '',
  `c_name40` varchar(100) NOT NULL DEFAULT '',
  `c_name41` varchar(100) NOT NULL DEFAULT '',
  `c_name42` varchar(100) NOT NULL DEFAULT '',
  `c_name43` varchar(100) NOT NULL DEFAULT '',
  `c_name44` varchar(100) NOT NULL DEFAULT '',
  `c_name45` varchar(100) NOT NULL DEFAULT '',
  `c_name46` varchar(100) NOT NULL DEFAULT '',
  `c_name47` varchar(100) NOT NULL DEFAULT '',
  `c_name48` varchar(100) NOT NULL DEFAULT '',
  `c_name49` varchar(100) NOT NULL DEFAULT '',
  `c_name50` varchar(100) NOT NULL DEFAULT '',
  `c_name51` varchar(100) NOT NULL DEFAULT '',
  `c_name52` varchar(100) NOT NULL DEFAULT '',
  `c_name53` varchar(100) NOT NULL DEFAULT '',
  `c_name54` varchar(100) NOT NULL DEFAULT '',
  `c_name55` varchar(100) NOT NULL DEFAULT '',
  `c_name56` varchar(100) NOT NULL DEFAULT '',
  `c_name57` varchar(100) NOT NULL DEFAULT '',
  `c_name58` varchar(100) NOT NULL DEFAULT '',
  `c_name59` varchar(100) NOT NULL DEFAULT '',
  `c_name60` varchar(100) NOT NULL DEFAULT '',
  `c_name61` varchar(100) NOT NULL DEFAULT '',
  `c_name62` varchar(100) NOT NULL DEFAULT '',
  `c_name63` varchar(100) NOT NULL DEFAULT '',
  `c_name64` varchar(100) NOT NULL DEFAULT '',
  `c_name65` varchar(100) NOT NULL DEFAULT '',
  `c_name66` varchar(100) NOT NULL DEFAULT '',
  `c_name67` varchar(100) NOT NULL DEFAULT '',
  `c_name68` varchar(100) NOT NULL DEFAULT '',
  `c_name69` varchar(100) NOT NULL DEFAULT '',
  `c_name70` varchar(100) NOT NULL DEFAULT '',
  `c_name71` varchar(100) NOT NULL DEFAULT '',
  `c_name72` varchar(100) NOT NULL DEFAULT '',
  `c_name73` varchar(100) NOT NULL DEFAULT '',
  `c_name74` varchar(100) NOT NULL DEFAULT '',
  `c_name75` varchar(100) NOT NULL DEFAULT '',
  `c_name76` varchar(100) NOT NULL DEFAULT '',
  `c_name77` varchar(100) NOT NULL DEFAULT '',
  `c_name78` varchar(100) NOT NULL DEFAULT '',
  `c_name79` varchar(100) NOT NULL DEFAULT '',
  `c_name80` varchar(100) NOT NULL DEFAULT '',
  `c_name81` varchar(100) NOT NULL DEFAULT '',
  `c_name82` varchar(100) NOT NULL DEFAULT '',
  `c_name83` varchar(100) NOT NULL DEFAULT '',
  `c_name84` varchar(100) NOT NULL DEFAULT '',
  `c_name85` varchar(100) NOT NULL DEFAULT '',
  `c_name86` varchar(100) NOT NULL DEFAULT '',
  `c_name87` varchar(100) NOT NULL DEFAULT '',
  `c_name88` varchar(100) NOT NULL DEFAULT '',
  `c_name89` varchar(100) NOT NULL DEFAULT '',
  `c_name90` varchar(100) NOT NULL DEFAULT '',
  `c_name91` varchar(100) NOT NULL DEFAULT '',
  `c_name92` varchar(100) NOT NULL DEFAULT '',
  `c_name93` varchar(100) NOT NULL DEFAULT '',
  `c_name94` varchar(100) NOT NULL DEFAULT '',
  `c_name95` varchar(100) NOT NULL DEFAULT '',
  `c_name96` varchar(100) NOT NULL DEFAULT '',
  `c_name97` varchar(100) NOT NULL DEFAULT '',
  `c_name98` varchar(100) NOT NULL DEFAULT '',
  `c_name99` varchar(100) NOT NULL DEFAULT '',
  `c_name100` varchar(100) NOT NULL DEFAULT '',  
  PRIMARY KEY (`id`)
) CHARSET=utf8mb4;



# 创建随机字符串
delimiter $$
DROP FUNCTION IF EXISTS `randStr` $$
CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(10000) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(10000) DEFAULT '' ;
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END$$



# 创建插入数据存储过程
delimiter $$
CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_100_10000_100`(IN n int)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
        INSERT INTO t_user_100_10000_100 (c_name1, c_name2, c_name3, c_name4, c_name5, c_name6, c_name7, c_name8, c_name9, c_name10, c_name11, c_name12, c_name13, c_name14, c_name15, c_name16, c_name17, c_name18, c_name19, c_name20, c_name21, c_name22, c_name23, c_name24, c_name25, c_name26, c_name27, c_name28, c_name29, c_name30, c_name31, c_name32, c_name33, c_name34, c_name35, c_name36, c_name37, c_name38, c_name39, c_name40, c_name41, c_name42, c_name43, c_name44, c_name45, c_name46, c_name47, c_name48, c_name49, c_name50, c_name51, c_name52, c_name53, c_name54, c_name55, c_name56, c_name57, c_name58, c_name59, c_name60, c_name61, c_name62, c_name63, c_name64, c_name65, c_name66, c_name67, c_name68, c_name69, c_name70, c_name71, c_name72, c_name73, c_name74, c_name75, c_name76, c_name77, c_name78, c_name79, c_name80, c_name81, c_name82, c_name83, c_name84, c_name85, c_name86, c_name87, c_name88, c_name89, c_name90, c_name91, c_name92, c_name93, c_name94, c_name95, c_name96, c_name97, c_name98, c_name99, c_name100 ) VALUES ( randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100), randStr(100));
        SET i = i + 1;
    END WHILE;
END $$
delimiter ;



call  add_t_user_100_10000_100(10000);

klustron

浏览 (420)

dazhao

2024-05-09 10:41:12

刚刚看到这个提问，这是个很好的提问，涉及到InnoDB 的内部工作机制，我来详细介绍一下。第一个表，每个列是varchar(10)，每个字段填充10个字节，于是每行大约1KB，1万行总共10MB数据。这些数据行存储到InnoDB数据页当中，有一些额外的空间成本，包括页头，行头，以及页内部的空间管理机制，这些元数据都需要一些存储空间。另外，ibd文件中还有B+树的内节点也会占据一些空间，本例中B+树的高度不会超过3，假定pagesize是4KB，那么就只有一个根节点和大约40多个内节点，其余全部是一共约5000个叶子结点。这样这个表的数据文件(ibd文件）占用21MB空间，是比较合理的。第二个表，每列是varchar(100)，每个字段填充100个字节，所以每行大约10KB，1万行总共100MB数据。相信本例的page size不是32KB，而是4/8/16KB。InnoDB要求每页至少存储两行否则只能溢出varchar, text, blob字段，还不行的话就INSERT失败。所以这个10KB的行，加上行头的元数据，就超过了页内允许的最大行长度，因此，InnoDB会把每个varchar(100）字段，overflow到单独的一个页中，也就是每个字段会占据一个page（4/8/16KB)，在主行中存储一个页号，这样主行长度就从10KB缩小为400字节，就可以存储成功了。这样一共占据1MB个页，每页4KB，所以一共4GB。这是各存储引擎处理大字段的常见做法，不过这样做并不是最优的，比如可以把多个字段溢出到同一个页，以便充分利用空间，直到一个页满了再overflow到下一页。不过这样的精细化管理，也会带来很多性能损失。比如字段更新导致增长的话怎么办等问题，因此通常做的都比较粗放，也就是每个溢出字段都使用1到N个page链起来存放。

2024-05-09 10:52:51

感谢老师对提问的详细解答!

Rex

2024-05-09 11:25:23

粗略看过相关代码，不知道有没有纰漏。我理解是，innodb是将varchar(100)逐一溢出，只要溢出后，主行长度少于page的一半，则不再继续溢出，所以是压缩到8k。相关逻辑在函数dtuple_convert_big_rec()。每个overflow单独页也是16K，但只写100字节的数据。写过相关分析：https://mp.weixin.qq.com/s/clKsKvWQaCrCHiz99nPHnw。

dazhao 回复 Rex

2024-05-09 11:36:49

OK，InnoDB 这样的做法更好。谢谢指正。我其实并没有看InnoDB的这块代码，而是根据其他存储引擎的做法写的。