运维工作中你遇到过哪些神奇事件？

GreatSQL社区

搜索

[已解决] 字段个数与字段长度对idb大小的影响

183 5 2024-4-9 19:01
数据库版本: MySQL 8.0.25
字符集：utf8mb4
问题描述
表数据为新insert数据，无delete、无update
mysql 一个数据量为1万的表，有100个字段，每个字段存10个字母，idb大小为21M
mysql 一个数据量为1万的表，有100个字段，每个字段存100个字母，idb大小为4.7G
为什么磁盘使用大小为什么不是10倍，而是200多倍？

  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 8.0.25    |
  6. +-----------+
  7. 1 row in set (0.00 sec)

  9. mysql> select count() from t_user_100_10000_100 ;
  10. +----------+
  11. | count() |
  12. +----------+
  13. | 10000 |
  14. +----------+
  15. 1 row in set (0.06 sec)
  16. mysql> select count() from t_user_100_1000_100 ;
  17. +----------+
  18. | count() |
  19. +----------+
  20. | 10000 |
  21. +----------+
  22. 1 row in set (0.18 sec)

  24. #os ibd 文件大小
  25. ll
  26. total 4313096
  27. -rw-r----- 1 mysql mysql 4395630592 Apr 9 18:52 t_user_100_10000_100.ibd
  28. -rw-r----- 1 mysql mysql 20971520 Apr 9 18:40 t_user_100_1000_100.ibd

  30. mysql>
  31. mysql> select 4395630592/20971520 from dual;
  32. +---------------------+
  33. | 4395630592/20971520 |
  34. +---------------------+
  35. | 209.6000 |
  36. +---------------------+
  37. 1 row in set (0.00 sec)

  39. mysql> select * from tables where TABLE_SCHEMA='aa'\G;
  40. *************************** 1. row ***************************
  41.   TABLE_CATALOG: def
  42.    TABLE_SCHEMA: aa
  43.      TABLE_NAME: t_user_100_10000_100
  44.      TABLE_TYPE: BASE TABLE
  45.          ENGINE: InnoDB
  46.         VERSION: 10
  47.      ROW_FORMAT: Dynamic
  48.      TABLE_ROWS: 5000
  49. AVG_ROW_LENGTH: 992496
  50.     DATA_LENGTH: 4962484224
  51. MAX_DATA_LENGTH: 0
  52.    INDEX_LENGTH: 0
  53.       DATA_FREE: 1048576
  54. AUTO_INCREMENT: 10001
  55.     CREATE_TIME: 2024-04-10 09:41:23
  56.     UPDATE_TIME: NULL
  57.      CHECK_TIME: NULL
  58. TABLE_COLLATION: utf8mb4_0900_ai_ci
  59.        CHECKSUM: NULL
  60. CREATE_OPTIONS:
  61.   TABLE_COMMENT:
  62. *************************** 2. row ***************************
  63.   TABLE_CATALOG: def
  64.    TABLE_SCHEMA: aa
  65.      TABLE_NAME: t_user_100_1000_100
  66.      TABLE_TYPE: BASE TABLE
  67.          ENGINE: InnoDB
  68.         VERSION: 10
  69.      ROW_FORMAT: Dynamic
  70.      TABLE_ROWS: 10000
  71. AVG_ROW_LENGTH: 1522
  72.     DATA_LENGTH: 15220736
  73. MAX_DATA_LENGTH: 0
  74.    INDEX_LENGTH: 0
  75.       DATA_FREE: 2097152
  76. AUTO_INCREMENT: 10001
  77.     CREATE_TIME: 2024-04-10 09:42:57
  78.     UPDATE_TIME: NULL
  79.      CHECK_TIME: NULL
  80. TABLE_COLLATION: utf8mb4_0900_ai_ci
  81.        CHECKSUM: NULL
  82. CREATE_OPTIONS:
  83.   TABLE_COMMENT:
  84. 2 rows in set (0.01 sec)

  86. ERROR:
  87. No query specified

  89. mysql> show create table t_user_100_1000_100  \G;
  90. *************************** 1. row ***************************
  91.        Table: t_user_100_1000_100
  92. Create Table: CREATE TABLE `t_user_100_1000_100` (
  93.   `id` int NOT NULL AUTO_INCREMENT,
  94.   `c_name1` varchar(10) NOT NULL DEFAULT '',
  95.   `c_name2` varchar(10) NOT NULL DEFAULT '',
  96.   `c_name3` varchar(10) NOT NULL DEFAULT '',
  97.   `c_name4` varchar(10) NOT NULL DEFAULT '',
  98.   `c_name5` varchar(10) NOT NULL DEFAULT '',
  99.   `c_name6` varchar(10) NOT NULL DEFAULT '',
  100.   `c_name7` varchar(10) NOT NULL DEFAULT '',
  101.   `c_name8` varchar(10) NOT NULL DEFAULT '',
  102.   `c_name9` varchar(10) NOT NULL DEFAULT '',
  103.   `c_name10` varchar(10) NOT NULL DEFAULT '',
  104.   `c_name11` varchar(10) NOT NULL DEFAULT '',
  105.   `c_name12` varchar(10) NOT NULL DEFAULT '',
  106.   `c_name13` varchar(10) NOT NULL DEFAULT '',
  107.   `c_name14` varchar(10) NOT NULL DEFAULT '',
  108.   `c_name15` varchar(10) NOT NULL DEFAULT '',
  109.   `c_name16` varchar(10) NOT NULL DEFAULT '',
  110.   `c_name17` varchar(10) NOT NULL DEFAULT '',
  111.   `c_name18` varchar(10) NOT NULL DEFAULT '',
  112.   `c_name19` varchar(10) NOT NULL DEFAULT '',
  113.   `c_name20` varchar(10) NOT NULL DEFAULT '',
  114.   `c_name21` varchar(10) NOT NULL DEFAULT '',
  115.   `c_name22` varchar(10) NOT NULL DEFAULT '',
  116.   `c_name23` varchar(10) NOT NULL DEFAULT '',
  117.   `c_name24` varchar(10) NOT NULL DEFAULT '',
  118.   `c_name25` varchar(10) NOT NULL DEFAULT '',
  119.   `c_name26` varchar(10) NOT NULL DEFAULT '',
  120.   `c_name27` varchar(10) NOT NULL DEFAULT '',
  121.   `c_name28` varchar(10) NOT NULL DEFAULT '',
  122.   `c_name29` varchar(10) NOT NULL DEFAULT '',
  123.   `c_name30` varchar(10) NOT NULL DEFAULT '',
  124.   `c_name31` varchar(10) NOT NULL DEFAULT '',
  125.   `c_name32` varchar(10) NOT NULL DEFAULT '',
  126.   `c_name33` varchar(10) NOT NULL DEFAULT '',
  127.   `c_name34` varchar(10) NOT NULL DEFAULT '',
  128.   `c_name35` varchar(10) NOT NULL DEFAULT '',
  129.   `c_name36` varchar(10) NOT NULL DEFAULT '',
  130.   `c_name37` varchar(10) NOT NULL DEFAULT '',
  131.   `c_name38` varchar(10) NOT NULL DEFAULT '',
  132.   `c_name39` varchar(10) NOT NULL DEFAULT '',
  133.   `c_name40` varchar(10) NOT NULL DEFAULT '',
  134.   `c_name41` varchar(10) NOT NULL DEFAULT '',
  135.   `c_name42` varchar(10) NOT NULL DEFAULT '',
  136.   `c_name43` varchar(10) NOT NULL DEFAULT '',
  137.   `c_name44` varchar(10) NOT NULL DEFAULT '',
  138.   `c_name45` varchar(10) NOT NULL DEFAULT '',
  139.   `c_name46` varchar(10) NOT NULL DEFAULT '',
  140.   `c_name47` varchar(10) NOT NULL DEFAULT '',
  141.   `c_name48` varchar(10) NOT NULL DEFAULT '',
  142.   `c_name49` varchar(10) NOT NULL DEFAULT '',
  143.   `c_name50` varchar(10) NOT NULL DEFAULT '',
  144.   `c_name51` varchar(10) NOT NULL DEFAULT '',
  145.   `c_name52` varchar(10) NOT NULL DEFAULT '',
  146.   `c_name53` varchar(10) NOT NULL DEFAULT '',
  147.   `c_name54` varchar(10) NOT NULL DEFAULT '',
  148.   `c_name55` varchar(10) NOT NULL DEFAULT '',
  149.   `c_name56` varchar(10) NOT NULL DEFAULT '',
  150.   `c_name57` varchar(10) NOT NULL DEFAULT '',
  151.   `c_name58` varchar(10) NOT NULL DEFAULT '',
  152.   `c_name59` varchar(10) NOT NULL DEFAULT '',
  153.   `c_name60` varchar(10) NOT NULL DEFAULT '',
  154.   `c_name61` varchar(10) NOT NULL DEFAULT '',
  155.   `c_name62` varchar(10) NOT NULL DEFAULT '',
  156.   `c_name63` varchar(10) NOT NULL DEFAULT '',
  157.   `c_name64` varchar(10) NOT NULL DEFAULT '',
  158.   `c_name65` varchar(10) NOT NULL DEFAULT '',
  159.   `c_name66` varchar(10) NOT NULL DEFAULT '',
  160.   `c_name67` varchar(10) NOT NULL DEFAULT '',
  161.   `c_name68` varchar(10) NOT NULL DEFAULT '',
  162.   `c_name69` varchar(10) NOT NULL DEFAULT '',
  163.   `c_name70` varchar(10) NOT NULL DEFAULT '',
  164.   `c_name71` varchar(10) NOT NULL DEFAULT '',
  165.   `c_name72` varchar(10) NOT NULL DEFAULT '',
  166.   `c_name73` varchar(10) NOT NULL DEFAULT '',
  167.   `c_name74` varchar(10) NOT NULL DEFAULT '',
  168.   `c_name75` varchar(10) NOT NULL DEFAULT '',
  169.   `c_name76` varchar(10) NOT NULL DEFAULT '',
  170.   `c_name77` varchar(10) NOT NULL DEFAULT '',
  171.   `c_name78` varchar(10) NOT NULL DEFAULT '',
  172.   `c_name79` varchar(10) NOT NULL DEFAULT '',
  173.   `c_name80` varchar(10) NOT NULL DEFAULT '',
  174.   `c_name81` varchar(10) NOT NULL DEFAULT '',
  175.   `c_name82` varchar(10) NOT NULL DEFAULT '',
  176.   `c_name83` varchar(10) NOT NULL DEFAULT '',
  177.   `c_name84` varchar(10) NOT NULL DEFAULT '',
  178.   `c_name85` varchar(10) NOT NULL DEFAULT '',
  179.   `c_name86` varchar(10) NOT NULL DEFAULT '',
  180.   `c_name87` varchar(10) NOT NULL DEFAULT '',
  181.   `c_name88` varchar(10) NOT NULL DEFAULT '',
  182.   `c_name89` varchar(10) NOT NULL DEFAULT '',
  183.   `c_name90` varchar(10) NOT NULL DEFAULT '',
  184.   `c_name91` varchar(10) NOT NULL DEFAULT '',
  185.   `c_name92` varchar(10) NOT NULL DEFAULT '',
  186.   `c_name93` varchar(10) NOT NULL DEFAULT '',
  187.   `c_name94` varchar(10) NOT NULL DEFAULT '',
  188.   `c_name95` varchar(10) NOT NULL DEFAULT '',
  189.   `c_name96` varchar(10) NOT NULL DEFAULT '',
  190.   `c_name97` varchar(10) NOT NULL DEFAULT '',
  191.   `c_name98` varchar(10) NOT NULL DEFAULT '',
  192.   `c_name99` varchar(10) NOT NULL DEFAULT '',
  193.   `c_name100` varchar(10) NOT NULL DEFAULT '',
  194.   PRIMARY KEY (`id`)
  195. ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  196. 1 row in set (0.00 sec)



  200. mysql> show create table t_user_100_10000_100  \G;
  201. *************************** 1. row ***************************
  202.        Table: t_user_100_10000_100
  203. Create Table: CREATE TABLE `t_user_100_10000_100` (
  204.   `id` int NOT NULL AUTO_INCREMENT,
  205.   `c_name1` varchar(100) NOT NULL DEFAULT '',
  206.   `c_name2` varchar(100) NOT NULL DEFAULT '',
  207.   `c_name3` varchar(100) NOT NULL DEFAULT '',
  208.   `c_name4` varchar(100) NOT NULL DEFAULT '',
  209.   `c_name5` varchar(100) NOT NULL DEFAULT '',
  210.   `c_name6` varchar(100) NOT NULL DEFAULT '',
  211.   `c_name7` varchar(100) NOT NULL DEFAULT '',
  212.   `c_name8` varchar(100) NOT NULL DEFAULT '',
  213.   `c_name9` varchar(100) NOT NULL DEFAULT '',
  214.   `c_name10` varchar(100) NOT NULL DEFAULT '',
  215.   `c_name11` varchar(100) NOT NULL DEFAULT '',
  216.   `c_name12` varchar(100) NOT NULL DEFAULT '',
  217.   `c_name13` varchar(100) NOT NULL DEFAULT '',
  218.   `c_name14` varchar(100) NOT NULL DEFAULT '',
  219.   `c_name15` varchar(100) NOT NULL DEFAULT '',
  220.   `c_name16` varchar(100) NOT NULL DEFAULT '',
  221.   `c_name17` varchar(100) NOT NULL DEFAULT '',
  222.   `c_name18` varchar(100) NOT NULL DEFAULT '',
  223.   `c_name19` varchar(100) NOT NULL DEFAULT '',
  224.   `c_name20` varchar(100) NOT NULL DEFAULT '',
  225.   `c_name21` varchar(100) NOT NULL DEFAULT '',
  226.   `c_name22` varchar(100) NOT NULL DEFAULT '',
  227.   `c_name23` varchar(100) NOT NULL DEFAULT '',
  228.   `c_name24` varchar(100) NOT NULL DEFAULT '',
  229.   `c_name25` varchar(100) NOT NULL DEFAULT '',
  230.   `c_name26` varchar(100) NOT NULL DEFAULT '',
  231.   `c_name27` varchar(100) NOT NULL DEFAULT '',
  232.   `c_name28` varchar(100) NOT NULL DEFAULT '',
  233.   `c_name29` varchar(100) NOT NULL DEFAULT '',
  234.   `c_name30` varchar(100) NOT NULL DEFAULT '',
  235.   `c_name31` varchar(100) NOT NULL DEFAULT '',
  236.   `c_name32` varchar(100) NOT NULL DEFAULT '',
  237.   `c_name33` varchar(100) NOT NULL DEFAULT '',
  238.   `c_name34` varchar(100) NOT NULL DEFAULT '',
  239.   `c_name35` varchar(100) NOT NULL DEFAULT '',
  240.   `c_name36` varchar(100) NOT NULL DEFAULT '',
  241.   `c_name37` varchar(100) NOT NULL DEFAULT '',
  242.   `c_name38` varchar(100) NOT NULL DEFAULT '',
  243.   `c_name39` varchar(100) NOT NULL DEFAULT '',
  244.   `c_name40` varchar(100) NOT NULL DEFAULT '',
  245.   `c_name41` varchar(100) NOT NULL DEFAULT '',
  246.   `c_name42` varchar(100) NOT NULL DEFAULT '',
  247.   `c_name43` varchar(100) NOT NULL DEFAULT '',
  248.   `c_name44` varchar(100) NOT NULL DEFAULT '',
  249.   `c_name45` varchar(100) NOT NULL DEFAULT '',
  250.   `c_name46` varchar(100) NOT NULL DEFAULT '',
  251.   `c_name47` varchar(100) NOT NULL DEFAULT '',
  252.   `c_name48` varchar(100) NOT NULL DEFAULT '',
  253.   `c_name49` varchar(100) NOT NULL DEFAULT '',
  254.   `c_name50` varchar(100) NOT NULL DEFAULT '',
  255.   `c_name51` varchar(100) NOT NULL DEFAULT '',
  256.   `c_name52` varchar(100) NOT NULL DEFAULT '',
  257.   `c_name53` varchar(100) NOT NULL DEFAULT '',
  258.   `c_name54` varchar(100) NOT NULL DEFAULT '',
  259.   `c_name55` varchar(100) NOT NULL DEFAULT '',
  260.   `c_name56` varchar(100) NOT NULL DEFAULT '',
  261.   `c_name57` varchar(100) NOT NULL DEFAULT '',
  262.   `c_name58` varchar(100) NOT NULL DEFAULT '',
  263.   `c_name59` varchar(100) NOT NULL DEFAULT '',
  264.   `c_name60` varchar(100) NOT NULL DEFAULT '',
  265.   `c_name61` varchar(100) NOT NULL DEFAULT '',
  266.   `c_name62` varchar(100) NOT NULL DEFAULT '',
  267.   `c_name63` varchar(100) NOT NULL DEFAULT '',
  268.   `c_name64` varchar(100) NOT NULL DEFAULT '',
  269.   `c_name65` varchar(100) NOT NULL DEFAULT '',
  270.   `c_name66` varchar(100) NOT NULL DEFAULT '',
  271.   `c_name67` varchar(100) NOT NULL DEFAULT '',
  272.   `c_name68` varchar(100) NOT NULL DEFAULT '',
  273.   `c_name69` varchar(100) NOT NULL DEFAULT '',
  274.   `c_name70` varchar(100) NOT NULL DEFAULT '',
  275.   `c_name71` varchar(100) NOT NULL DEFAULT '',
  276.   `c_name72` varchar(100) NOT NULL DEFAULT '',
  277.   `c_name73` varchar(100) NOT NULL DEFAULT '',
  278.   `c_name74` varchar(100) NOT NULL DEFAULT '',
  279.   `c_name75` varchar(100) NOT NULL DEFAULT '',
  280.   `c_name76` varchar(100) NOT NULL DEFAULT '',
  281.   `c_name77` varchar(100) NOT NULL DEFAULT '',
  282.   `c_name78` varchar(100) NOT NULL DEFAULT '',
  283.   `c_name79` varchar(100) NOT NULL DEFAULT '',
  284.   `c_name80` varchar(100) NOT NULL DEFAULT '',
  285.   `c_name81` varchar(100) NOT NULL DEFAULT '',
  286.   `c_name82` varchar(100) NOT NULL DEFAULT '',
  287.   `c_name83` varchar(100) NOT NULL DEFAULT '',
  288.   `c_name84` varchar(100) NOT NULL DEFAULT '',
  289.   `c_name85` varchar(100) NOT NULL DEFAULT '',
  290.   `c_name86` varchar(100) NOT NULL DEFAULT '',
  291.   `c_name87` varchar(100) NOT NULL DEFAULT '',
  292.   `c_name88` varchar(100) NOT NULL DEFAULT '',
  293.   `c_name89` varchar(100) NOT NULL DEFAULT '',
  294.   `c_name90` varchar(100) NOT NULL DEFAULT '',
  295.   `c_name91` varchar(100) NOT NULL DEFAULT '',
  296.   `c_name92` varchar(100) NOT NULL DEFAULT '',
  297.   `c_name93` varchar(100) NOT NULL DEFAULT '',
  298.   `c_name94` varchar(100) NOT NULL DEFAULT '',
  299.   `c_name95` varchar(100) NOT NULL DEFAULT '',
  300.   `c_name96` varchar(100) NOT NULL DEFAULT '',
  301.   `c_name97` varchar(100) NOT NULL DEFAULT '',
  302.   `c_name98` varchar(100) NOT NULL DEFAULT '',
  303.   `c_name99` varchar(100) NOT NULL DEFAULT '',
  304.   `c_name100` varchar(100) NOT NULL DEFAULT '',
  305.   PRIMARY KEY (`id`)
  306. ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  307. 1 row in set (0.00 sec)
复制代码


复现造数脚本
脚本1：100个字段1000字节1万数据量造数脚本
  1. CREATE TABLE `t_user_100_1000_100` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `c_name1` varchar(10) NOT NULL DEFAULT '',
  4.   `c_name2` varchar(10) NOT NULL DEFAULT '',
  5.   `c_name3` varchar(10) NOT NULL DEFAULT '',
  6.   `c_name4` varchar(10) NOT NULL DEFAULT '',
  7.   `c_name5` varchar(10) NOT NULL DEFAULT '',
  8.   `c_name6` varchar(10) NOT NULL DEFAULT '',
  9.   `c_name7` varchar(10) NOT NULL DEFAULT '',
  10.   `c_name8` varchar(10) NOT NULL DEFAULT '',
  11.   `c_name9` varchar(10) NOT NULL DEFAULT '',
  12.   `c_name10` varchar(10) NOT NULL DEFAULT '',
  13.   `c_name11` varchar(10) NOT NULL DEFAULT '',
  14.   `c_name12` varchar(10) NOT NULL DEFAULT '',
  15.   `c_name13` varchar(10) NOT NULL DEFAULT '',
  16.   `c_name14` varchar(10) NOT NULL DEFAULT '',
  17.   `c_name15` varchar(10) NOT NULL DEFAULT '',
  18.   `c_name16` varchar(10) NOT NULL DEFAULT '',
  19.   `c_name17` varchar(10) NOT NULL DEFAULT '',
  20.   `c_name18` varchar(10) NOT NULL DEFAULT '',
  21.   `c_name19` varchar(10) NOT NULL DEFAULT '',
  22.   `c_name20` varchar(10) NOT NULL DEFAULT '',
  23.   `c_name21` varchar(10) NOT NULL DEFAULT '',
  24.   `c_name22` varchar(10) NOT NULL DEFAULT '',
  25.   `c_name23` varchar(10) NOT NULL DEFAULT '',
  26.   `c_name24` varchar(10) NOT NULL DEFAULT '',
  27.   `c_name25` varchar(10) NOT NULL DEFAULT '',
  28.   `c_name26` varchar(10) NOT NULL DEFAULT '',
  29.   `c_name27` varchar(10) NOT NULL DEFAULT '',
  30.   `c_name28` varchar(10) NOT NULL DEFAULT '',
  31.   `c_name29` varchar(10) NOT NULL DEFAULT '',
  32.   `c_name30` varchar(10) NOT NULL DEFAULT '',
  33.   `c_name31` varchar(10) NOT NULL DEFAULT '',
  34.   `c_name32` varchar(10) NOT NULL DEFAULT '',
  35.   `c_name33` varchar(10) NOT NULL DEFAULT '',
  36.   `c_name34` varchar(10) NOT NULL DEFAULT '',
  37.   `c_name35` varchar(10) NOT NULL DEFAULT '',
  38.   `c_name36` varchar(10) NOT NULL DEFAULT '',
  39.   `c_name37` varchar(10) NOT NULL DEFAULT '',
  40.   `c_name38` varchar(10) NOT NULL DEFAULT '',
  41.   `c_name39` varchar(10) NOT NULL DEFAULT '',
  42.   `c_name40` varchar(10) NOT NULL DEFAULT '',
  43.   `c_name41` varchar(10) NOT NULL DEFAULT '',
  44.   `c_name42` varchar(10) NOT NULL DEFAULT '',
  45.   `c_name43` varchar(10) NOT NULL DEFAULT '',
  46.   `c_name44` varchar(10) NOT NULL DEFAULT '',
  47.   `c_name45` varchar(10) NOT NULL DEFAULT '',
  48.   `c_name46` varchar(10) NOT NULL DEFAULT '',
  49.   `c_name47` varchar(10) NOT NULL DEFAULT '',
  50.   `c_name48` varchar(10) NOT NULL DEFAULT '',
  51.   `c_name49` varchar(10) NOT NULL DEFAULT '',
  52.   `c_name50` varchar(10) NOT NULL DEFAULT '',
  53.   `c_name51` varchar(10) NOT NULL DEFAULT '',
  54.   `c_name52` varchar(10) NOT NULL DEFAULT '',
  55.   `c_name53` varchar(10) NOT NULL DEFAULT '',
  56.   `c_name54` varchar(10) NOT NULL DEFAULT '',
  57.   `c_name55` varchar(10) NOT NULL DEFAULT '',
  58.   `c_name56` varchar(10) NOT NULL DEFAULT '',
  59.   `c_name57` varchar(10) NOT NULL DEFAULT '',
  60.   `c_name58` varchar(10) NOT NULL DEFAULT '',
  61.   `c_name59` varchar(10) NOT NULL DEFAULT '',
  62.   `c_name60` varchar(10) NOT NULL DEFAULT '',
  63.   `c_name61` varchar(10) NOT NULL DEFAULT '',
  64.   `c_name62` varchar(10) NOT NULL DEFAULT '',
  65.   `c_name63` varchar(10) NOT NULL DEFAULT '',
  66.   `c_name64` varchar(10) NOT NULL DEFAULT '',
  67.   `c_name65` varchar(10) NOT NULL DEFAULT '',
  68.   `c_name66` varchar(10) NOT NULL DEFAULT '',
  69.   `c_name67` varchar(10) NOT NULL DEFAULT '',
  70.   `c_name68` varchar(10) NOT NULL DEFAULT '',
  71.   `c_name69` varchar(10) NOT NULL DEFAULT '',
  72.   `c_name70` varchar(10) NOT NULL DEFAULT '',
  73.   `c_name71` varchar(10) NOT NULL DEFAULT '',
  74.   `c_name72` varchar(10) NOT NULL DEFAULT '',
  75.   `c_name73` varchar(10) NOT NULL DEFAULT '',
  76.   `c_name74` varchar(10) NOT NULL DEFAULT '',
  77.   `c_name75` varchar(10) NOT NULL DEFAULT '',
  78.   `c_name76` varchar(10) NOT NULL DEFAULT '',
  79.   `c_name77` varchar(10) NOT NULL DEFAULT '',
  80.   `c_name78` varchar(10) NOT NULL DEFAULT '',
  81.   `c_name79` varchar(10) NOT NULL DEFAULT '',
  82.   `c_name80` varchar(10) NOT NULL DEFAULT '',
  83.   `c_name81` varchar(10) NOT NULL DEFAULT '',
  84.   `c_name82` varchar(10) NOT NULL DEFAULT '',
  85.   `c_name83` varchar(10) NOT NULL DEFAULT '',
  86.   `c_name84` varchar(10) NOT NULL DEFAULT '',
  87.   `c_name85` varchar(10) NOT NULL DEFAULT '',
  88.   `c_name86` varchar(10) NOT NULL DEFAULT '',
  89.   `c_name87` varchar(10) NOT NULL DEFAULT '',
  90.   `c_name88` varchar(10) NOT NULL DEFAULT '',
  91.   `c_name89` varchar(10) NOT NULL DEFAULT '',
  92.   `c_name90` varchar(10) NOT NULL DEFAULT '',
  93.   `c_name91` varchar(10) NOT NULL DEFAULT '',
  94.   `c_name92` varchar(10) NOT NULL DEFAULT '',
  95.   `c_name93` varchar(10) NOT NULL DEFAULT '',
  96.   `c_name94` varchar(10) NOT NULL DEFAULT '',
  97.   `c_name95` varchar(10) NOT NULL DEFAULT '',
  98.   `c_name96` varchar(10) NOT NULL DEFAULT '',
  99.   `c_name97` varchar(10) NOT NULL DEFAULT '',
  100.   `c_name98` varchar(10) NOT NULL DEFAULT '',
  101.   `c_name99` varchar(10) NOT NULL DEFAULT '',
  102.   `c_name100` varchar(10) NOT NULL DEFAULT '',  
  103.   PRIMARY KEY (`id`)
  104. ) CHARSET=utf8mb4;



  108. # 创建随机字符串
  109. delimiter $$
  110. DROP FUNCTION IF EXISTS `randStr` $$
  111. CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(1000) CHARSET utf8mb4
  112.     DETERMINISTIC
  113. BEGIN
  114.     DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  115.     DECLARE return_str varchar(1000) DEFAULT '' ;
  116.     DECLARE i INT DEFAULT 0;
  117.     WHILE i < n DO
  118.         SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
  119.         SET i = i + 1;
  120.     END WHILE;
  121.     RETURN return_str;
  122. END$$



  126. # 创建插入数据存储过程
  127. delimiter $$
  128. CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_100_1000_100`(IN n int)
  129. BEGIN
  130.     DECLARE i INT DEFAULT 1;
  131.     WHILE (i <= n) DO
  132.         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));
  133.         SET i = i + 1;
  134.     END WHILE;
  135. END $$
  136. delimiter ;



  140. call  add_t_user_100_1000_100(10000);
复制代码

脚本2：100个字段10000字节1万数据量造数脚本
  1. CREATE TABLE `t_user_100_10000_100` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `c_name1` varchar(100) NOT NULL DEFAULT '',
  4.   `c_name2` varchar(100) NOT NULL DEFAULT '',
  5.   `c_name3` varchar(100) NOT NULL DEFAULT '',
  6.   `c_name4` varchar(100) NOT NULL DEFAULT '',
  7.   `c_name5` varchar(100) NOT NULL DEFAULT '',
  8.   `c_name6` varchar(100) NOT NULL DEFAULT '',
  9.   `c_name7` varchar(100) NOT NULL DEFAULT '',
  10.   `c_name8` varchar(100) NOT NULL DEFAULT '',
  11.   `c_name9` varchar(100) NOT NULL DEFAULT '',
  12.   `c_name10` varchar(100) NOT NULL DEFAULT '',
  13.   `c_name11` varchar(100) NOT NULL DEFAULT '',
  14.   `c_name12` varchar(100) NOT NULL DEFAULT '',
  15.   `c_name13` varchar(100) NOT NULL DEFAULT '',
  16.   `c_name14` varchar(100) NOT NULL DEFAULT '',
  17.   `c_name15` varchar(100) NOT NULL DEFAULT '',
  18.   `c_name16` varchar(100) NOT NULL DEFAULT '',
  19.   `c_name17` varchar(100) NOT NULL DEFAULT '',
  20.   `c_name18` varchar(100) NOT NULL DEFAULT '',
  21.   `c_name19` varchar(100) NOT NULL DEFAULT '',
  22.   `c_name20` varchar(100) NOT NULL DEFAULT '',
  23.   `c_name21` varchar(100) NOT NULL DEFAULT '',
  24.   `c_name22` varchar(100) NOT NULL DEFAULT '',
  25.   `c_name23` varchar(100) NOT NULL DEFAULT '',
  26.   `c_name24` varchar(100) NOT NULL DEFAULT '',
  27.   `c_name25` varchar(100) NOT NULL DEFAULT '',
  28.   `c_name26` varchar(100) NOT NULL DEFAULT '',
  29.   `c_name27` varchar(100) NOT NULL DEFAULT '',
  30.   `c_name28` varchar(100) NOT NULL DEFAULT '',
  31.   `c_name29` varchar(100) NOT NULL DEFAULT '',
  32.   `c_name30` varchar(100) NOT NULL DEFAULT '',
  33.   `c_name31` varchar(100) NOT NULL DEFAULT '',
  34.   `c_name32` varchar(100) NOT NULL DEFAULT '',
  35.   `c_name33` varchar(100) NOT NULL DEFAULT '',
  36.   `c_name34` varchar(100) NOT NULL DEFAULT '',
  37.   `c_name35` varchar(100) NOT NULL DEFAULT '',
  38.   `c_name36` varchar(100) NOT NULL DEFAULT '',
  39.   `c_name37` varchar(100) NOT NULL DEFAULT '',
  40.   `c_name38` varchar(100) NOT NULL DEFAULT '',
  41.   `c_name39` varchar(100) NOT NULL DEFAULT '',
  42.   `c_name40` varchar(100) NOT NULL DEFAULT '',
  43.   `c_name41` varchar(100) NOT NULL DEFAULT '',
  44.   `c_name42` varchar(100) NOT NULL DEFAULT '',
  45.   `c_name43` varchar(100) NOT NULL DEFAULT '',
  46.   `c_name44` varchar(100) NOT NULL DEFAULT '',
  47.   `c_name45` varchar(100) NOT NULL DEFAULT '',
  48.   `c_name46` varchar(100) NOT NULL DEFAULT '',
  49.   `c_name47` varchar(100) NOT NULL DEFAULT '',
  50.   `c_name48` varchar(100) NOT NULL DEFAULT '',
  51.   `c_name49` varchar(100) NOT NULL DEFAULT '',
  52.   `c_name50` varchar(100) NOT NULL DEFAULT '',
  53.   `c_name51` varchar(100) NOT NULL DEFAULT '',
  54.   `c_name52` varchar(100) NOT NULL DEFAULT '',
  55.   `c_name53` varchar(100) NOT NULL DEFAULT '',
  56.   `c_name54` varchar(100) NOT NULL DEFAULT '',
  57.   `c_name55` varchar(100) NOT NULL DEFAULT '',
  58.   `c_name56` varchar(100) NOT NULL DEFAULT '',
  59.   `c_name57` varchar(100) NOT NULL DEFAULT '',
  60.   `c_name58` varchar(100) NOT NULL DEFAULT '',
  61.   `c_name59` varchar(100) NOT NULL DEFAULT '',
  62.   `c_name60` varchar(100) NOT NULL DEFAULT '',
  63.   `c_name61` varchar(100) NOT NULL DEFAULT '',
  64.   `c_name62` varchar(100) NOT NULL DEFAULT '',
  65.   `c_name63` varchar(100) NOT NULL DEFAULT '',
  66.   `c_name64` varchar(100) NOT NULL DEFAULT '',
  67.   `c_name65` varchar(100) NOT NULL DEFAULT '',
  68.   `c_name66` varchar(100) NOT NULL DEFAULT '',
  69.   `c_name67` varchar(100) NOT NULL DEFAULT '',
  70.   `c_name68` varchar(100) NOT NULL DEFAULT '',
  71.   `c_name69` varchar(100) NOT NULL DEFAULT '',
  72.   `c_name70` varchar(100) NOT NULL DEFAULT '',
  73.   `c_name71` varchar(100) NOT NULL DEFAULT '',
  74.   `c_name72` varchar(100) NOT NULL DEFAULT '',
  75.   `c_name73` varchar(100) NOT NULL DEFAULT '',
  76.   `c_name74` varchar(100) NOT NULL DEFAULT '',
  77.   `c_name75` varchar(100) NOT NULL DEFAULT '',
  78.   `c_name76` varchar(100) NOT NULL DEFAULT '',
  79.   `c_name77` varchar(100) NOT NULL DEFAULT '',
  80.   `c_name78` varchar(100) NOT NULL DEFAULT '',
  81.   `c_name79` varchar(100) NOT NULL DEFAULT '',
  82.   `c_name80` varchar(100) NOT NULL DEFAULT '',
  83.   `c_name81` varchar(100) NOT NULL DEFAULT '',
  84.   `c_name82` varchar(100) NOT NULL DEFAULT '',
  85.   `c_name83` varchar(100) NOT NULL DEFAULT '',
  86.   `c_name84` varchar(100) NOT NULL DEFAULT '',
  87.   `c_name85` varchar(100) NOT NULL DEFAULT '',
  88.   `c_name86` varchar(100) NOT NULL DEFAULT '',
  89.   `c_name87` varchar(100) NOT NULL DEFAULT '',
  90.   `c_name88` varchar(100) NOT NULL DEFAULT '',
  91.   `c_name89` varchar(100) NOT NULL DEFAULT '',
  92.   `c_name90` varchar(100) NOT NULL DEFAULT '',
  93.   `c_name91` varchar(100) NOT NULL DEFAULT '',
  94.   `c_name92` varchar(100) NOT NULL DEFAULT '',
  95.   `c_name93` varchar(100) NOT NULL DEFAULT '',
  96.   `c_name94` varchar(100) NOT NULL DEFAULT '',
  97.   `c_name95` varchar(100) NOT NULL DEFAULT '',
  98.   `c_name96` varchar(100) NOT NULL DEFAULT '',
  99.   `c_name97` varchar(100) NOT NULL DEFAULT '',
  100.   `c_name98` varchar(100) NOT NULL DEFAULT '',
  101.   `c_name99` varchar(100) NOT NULL DEFAULT '',
  102.   `c_name100` varchar(100) NOT NULL DEFAULT '',  
  103.   PRIMARY KEY (`id`)
  104. ) CHARSET=utf8mb4;



  108. # 创建随机字符串
  109. delimiter $$
  110. DROP FUNCTION IF EXISTS `randStr` $$
  111. CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(10000) CHARSET utf8mb4
  112.     DETERMINISTIC
  113. BEGIN
  114.     DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  115.     DECLARE return_str varchar(10000) DEFAULT '' ;
  116.     DECLARE i INT DEFAULT 0;
  117.     WHILE i < n DO
  118.         SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
  119.         SET i = i + 1;
  120.     END WHILE;
  121.     RETURN return_str;
  122. END$$



  126. # 创建插入数据存储过程
  127. delimiter $$
  128. CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_100_10000_100`(IN n int)
  129. BEGIN
  130.     DECLARE i INT DEFAULT 1;
  131.     WHILE (i <= n) DO
  132.         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));
  133.         SET i = i + 1;
  134.     END WHILE;
  135. END $$
  136. delimiter ;



  140. call  add_t_user_100_10000_100(10000);
复制代码

全部回复(5)
只看楼主
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无其操作，无碎片。
喜欢就关注我公众号：DBA烂笔头
回复 采纳

举报

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倍。
  1. greatsql> show table status like 't_user%'\G
  2. *************************** 1. row ***************************
  3.            Name: t_user_100_10000_100
  4.          Engine: InnoDB
  5.         Version: 10
  6.      Row_format: Dynamic
  7.            Rows: 5001
  8. Avg_row_length: 868591
  9.     Data_length: 4343824384
  10. Max_data_length: 0
  11.    Index_length: 0
  12.       Data_free: 5242880
  13. Auto_increment: 20001
  14.     Create_time: 2024-04-10 17:38:42
  15.     Update_time: 2024-04-11 04:57:14
  16.      Check_time: NULL
  17.       Collation: utf8mb4_0900_ai_ci
  18.        Checksum: NULL
  19. Create_options:
  20.         Comment:
  21. *************************** 2. row ***************************
  22.            Name: t_user_100_1000_100
  23.          Engine: InnoDB
  24.         Version: 10
  25.      Row_format: Dynamic
  26.            Rows: 9240
  27. Avg_row_length: 1420
  28.     Data_length: 13123584
  29. Max_data_length: 0
  30.    Index_length: 0
  31.       Data_free: 4194304
  32. Auto_increment: 20001
  33.     Create_time: 2024-04-10 17:38:28
  34.     Update_time: 2024-04-10 17:41:39
  35.      Check_time: NULL
  36.       Collation: utf8mb4_0900_ai_ci
  37.        Checksum: NULL
  38. Create_options:
  39.         Comment:
  40. 2 rows in set (0.00 sec)
复制代码


再对比表空间文件大小，二者相差200倍
  1. -rw-r----- 1 mysql mysql 4.1G Apr 11 04:57 /data/GreatSQL/sbtest/t_user_100_10000_100.ibd
  2. -rw-r----- 1 mysql mysql  20M 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
喜欢就关注我公众号：DBA烂笔头
回复 采纳

举报

chongzh

3

主题

73

博客

916

贡献

金牌会员

Rank: 6Rank: 6

积分
1021

分享达人解惑专家（铜）助人为乐（铜）

发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-4-30 23:40 , Processed in 0.017513 second(s), 12 queries , Redis On.
快速回复 返回顶部 返回列表