首页 文章

为什么InnoDB表的大小远大于预期？

提问于
浏览 次
8

我正在试图找出不同存储引擎的存储要求 . 我有这张 table ：

CREATE TABLE  `mytest` (
  `num1` int(10) unsigned NOT NULL,
  KEY `key1` (`num1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

当我插入一些值然后运行 show table status; 时，我得到以下内容：

+----------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| mytest         | InnoDB |      10 | Compact    | 1932473 |             35 |    67715072 |                0 |     48840704 |   4194304 |           NULL | 2010-05-26 11:30:40 | NULL                | NULL       | latin1_swedish_ci |     NULL |                |         |

注意avg_row_length是35.我感到困惑的是，当我只存储一个不可为空的整数时，InnoDB不会更好地利用空间 .

我在myISAM上运行了同样的测试，默认情况下myISAM在这个表上每行使用7个字节 . 我跑的时候

ALTER TABLE mytest MAX_ROWS=50000000, AVG_ROW_LENGTH = 4;

导致myISAM最终正确使用5字节行 .

当我为InnoDB运行相同的ALTER TABLE语句时，avg_row_length不会改变 .

为什么只存储一个4字节的无符号整数时需要这么大的avg_row_length？

mysql innodb

3 回答

  • 2

    InnoDB 表是群集的，这意味着所有数据都包含在 B-Tree 中， PRIMARY KEY 作为键，所有其他列作为有效负载 .

    由于未定义显式 PRIMARY KEY ， InnoDB 使用隐藏的6字节列对记录进行排序 .

    B-Tree 组织的这个和开销（带有额外的非叶级块）需要比 sizeof(int) * num_rows 更多的空间 .

    回复于
  • 1

    以下是您可能会发现有用的更多信息 .

    InnoDB以16KB页面的形式分配数据，因此如果您只有几行并且表总计<16K，“SHOW TABLE STATUS”将为行大小提供膨胀的数字 . （例如，有4行，平均行大小返回为4096.）

    当空间是一个重要考虑因素时，“不可见”主键每行额外的6个字节是一个关键点 . 如果您的表只有一列，那么这是制作主键的理想列，假设其中的值是唯一的：

    CREATE TABLE `mytest2`
       (`num1` int(10) unsigned NOT NULL primary key)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

    通过使用这样的PRIMARY KEY：

    • 不需要INDEX或KEY子句，因为您没有辅助索引 . InnoDB表的索引组织格式使您可以基于主键值免费快速查找 .

    • 您最终没有使用NUM1列数据的另一个副本，这是明确索引该列时发生的情况 .

    • 你也不知道你可能想要一个组合了几个不同列的主键或者是一个长字符串列的原因 . ）

    总的来说，只使用主键意味着与表索引相关的数据更少 . 为了了解整体数据大小，我喜欢运行

    set innodb_file_per_table = 1;

    并检查data / database / * table * .ibd文件的大小 . 每个.ibd文件都包含InnoDB表及其所有相关索引的数据 .

    为了快速构建一个用于测试的大表，我通常会运行如下语句：

    insert into mytest
select * from mytest;

    每次数据量增加一倍 . 对于使用主键的单列表，由于值必须是唯一的，我使用变体来保持值不会相互冲突：

    insert into mytest2
select num1 + (select count(*) from mytest2) from mytest2;

    通过这种方式，我可以将平均行大小降低到25.空间开销基于您希望使用指针式机制快速查找单个行的基本假设，并且大多数表将具有其值的列除了具有实数数据的列之外，还可以作为指针（即主键）进行求和，平均和显示 .

    回复于
  • 10

    除了Quassnoi的非常好的答案，你应该尝试使用重要的数据集 .

    我要做的是，加载1M行模拟 生产环境 数据，然后测量表格大小并将其用作指南 .

    这就是I've done in the past anyway

    回复于

相关问题