论坛问答 › 互助交流区 › 字段个数与字段长度对idb大小的影响

chongzh 发表于 2024-4-9 19:01:00

字段个数与字段长度对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 ;



calladd_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 ;



calladd_t_user_100_10000_100(10000);

yejr 发表于 2024-4-10 09:05:52

对两个表都执行 alter table x engine=innodb 重整表空间，再观察二者还有没差距，或多少差距

chongzh 发表于 2024-4-10 09:24:42

yejr 发表于 2024-4-10 09:05
对两个表都执行 alter table x engine=innodb 重整表空间，再观察二者还有没差距，或多少差距 ...

叶老师：alter table x engine=innodb 尝试过了，表数据是新insert无其操作，无碎片。

yejr 发表于 2024-4-10 17:53:40

chongzh 发表于 2024-4-10 09:24
叶老师：alter table x engine=innodb 尝试过了，表数据是新insert无其操作，无碎片。 ...

innodb表是索引组织表，不像myisam那样是堆组织表，因此其占用的磁盘空间并不是线性倍数关系

在写入数据时page会根据实际需求动态扩展，也会因为用户存储的数据行长度不同动态分配page，有时可能会存在某些page只存储少量几个字节的数据，而另外的某些page的填充率较高

可以利用innoblock工具及通过查看表的行平均长度等信息来分析佐证

更多参考内容详见
- https://mp.weixin.qq.com/s/QR_SAMsHDIgJ6yDMaAPk0A
- https://mp.weixin.qq.com/s/sFaQ2dFIvZk7ujUuC6x70A

yejr 发表于 2024-4-11 08:57:23

yejr 发表于 2024-4-10 17:53
innodb表是索引组织表，不像myisam那样是堆组织表，因此其占用的磁盘空间并不是线性倍数关系

在写入数据 ...

我用你的测例做测试，最后结果如下：

先查看表统计信息，结果很明显，两个表的Avg_row_length相差了611倍。
greatsql> show table status like 't_user%'\G
*************************** 1. row ***************************
         Name: t_user_100_10000_100
         Engine: InnoDB
      Version: 10
   Row_format: Dynamic
         Rows: 5001
Avg_row_length: 868591
    Data_length: 4343824384
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
Auto_increment: 20001
    Create_time: 2024-04-10 17:38:42
    Update_time: 2024-04-11 04:57:14
   Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
Create_options:
      Comment:
*************************** 2. row ***************************
         Name: t_user_100_1000_100
         Engine: InnoDB
      Version: 10
   Row_format: Dynamic
         Rows: 9240
Avg_row_length: 1420
    Data_length: 13123584
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
Auto_increment: 20001
    Create_time: 2024-04-10 17:38:28
    Update_time: 2024-04-10 17:41:39
   Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
Create_options:
      Comment:
2 rows in set (0.00 sec)

再对比表空间文件大小，二者相差200倍
-rw-r----- 1 mysql mysql 4.1G Apr 11 04:57 /data/GreatSQL/sbtest/t_user_100_10000_100.ibd
-rw-r----- 1 mysql mysql20M Apr 10 17:41 /data/GreatSQL/sbtest/t_user_100_1000_100.ibd

t_user_100_10000_100 表相对 t_user_100_1000_100 而言，每个字符串列长度扩大了10倍，共100个字符串列，也即每行扩大1000倍。

按照上述倍数关系算下来，二者表空间膨胀对比算是正常合理的。

最后，不知道你通过本案例测试想要得到什么结论？

chongzh 发表于 2024-4-11 14:35:30

yejr 发表于 2024-4-11 08:57
我用你的测例做测试，最后结果如下：

先查看表统计信息，结果很明显，两个表的Avg_row_length相差了611 ...

叶老师，一行存1000个字符，一行存10000个字符，记录数扩大是10倍。
如：10*（c1+c2+c3）=c1*10+c2*10+c3*10

查看完整版本: 字段个数与字段长度对idb大小的影响