☰

为什么分区的 MySQL InnoDB 表比未分区的表大两倍？

tag-icon • tag-icon mysql innodb table-partitioning
为什么分区的 MySQL InnoDB 表比未分区的表大两倍？

我有两个 InnoDB 表，除了一个被分区，另一个没有之外，其他各方面都完全相同：

DROP TABLE IF EXISTS `simple_table`;
CREATE TABLE `simple_table` (
  `date` date NOT NULL,
  `item_id` bigint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `partitioned_table`;
CREATE TABLE `partitioned_table` (
  `date` date NOT NULL,
  `item_id` bigint(8) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY RANGE ( TO_DAYS(`date`))
(PARTITION p20180207 VALUES LESS THAN (737098) ENGINE = InnoDB,
 PARTITION p20180208 VALUES LESS THAN (737099) ENGINE = InnoDB);

我将相同的数据插入到两者中：

INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
INSERT INTO `simple_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);
INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-07', 1), ('2018-02-07', 2), ('2018-02-07', 3);
INSERT INTO `partitioned_table` (`date`, `item_id`) VALUES ('2018-02-08', 1), ('2018-02-08', 2), ('2018-02-08', 3);

结果是分区数据是原来的两倍。结果如下SHOW TABLE STATUS：

*************************** 1. row ***************************
        Name: partitioned_table
        Engine: InnoDB
        Version: 10
    Row_format: Compact
        Rows: 6
Avg_row_length: 5461
    Data_length: 32768
Max_data_length: 0
Index_length: 0
    Data_free: 0
Auto_increment: NULL
    Create_time: 2018-02-19 14:36:29
    Update_time: NULL
    Check_time: NULL
    Collation: utf8_unicode_ci
    Checksum: NULL
Create_options: partitioned
        Comment: 
*************************** 2. row ***************************
        Name: simple_table
        Engine: InnoDB
        Version: 10
    Row_format: Compact
        Rows: 6
Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
Index_length: 0
    Data_free: 0
Auto_increment: NULL
    Create_time: 2018-02-19 14:36:29
    Update_time: NULL
    Check_time: NULL
    Collation: utf8_unicode_ci
    Checksum: NULL
Create_options: 
        Comment:

（查看Data_length值：16384 与 32768）

同样的事情也发生在稍微复杂一点的表上，每个日期包含数百万个条目：应用分区后，它们会变得两倍大。这可以在表状态以及实际文件大小中看到。

我找不到有关此类问题的任何信息。为什么会发生这种情况？

更新： 如果我将分区数更改为 10，并为每个日期插入 100 个条目（两个表总共 10000 个），则会得到以下结果：

           Name: partitioned_table
    Data_length: 655360
...
           Name: simple_table
    Data_length: 344064

（插入后，这些值会持续变化几分钟，但最终会稳定下来）如果我列出文件，我会得到以下信息：

-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180201.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180202.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180203.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180204.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180205.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180206.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180207.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180208.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180209.ibd
-rw-rw----  1 mysql mysql 131072 Feb 20 15:50 partitioned_table#P#p20180210.ibd
-rw-rw----  1 mysql mysql 409600 Feb 20 15:50 simple_table.ibd
-rw-rw----  1 mysql mysql    128 Feb 20 15:50 partitioned_table.par
-rw-rw----  1 mysql mysql   8596 Feb 20 15:50 partitioned_table.frm
-rw-rw----  1 mysql mysql   8596 Feb 20 15:50 simple_table.frm

答案1

找到了问题的原因——这是由 MySQL 如何为不断增长的表文件分配磁盘空间引起的。

我现在意识到我这里的例子对于我试图说明的情况来说并不那么好，但我最初的问题是大型数据库（有数百万个小行）

我找到了这个文章谈论的是innodb_space使用该工具，我发现我的表大部分由空的分配页面组成，这让我找到了这个 MySQL文档页面。 它说：

这些页面被分组为大小为 1MB 的区段，最大页面大小为 16KB

和

当表空间中的某个段增长时，InnoDB 会一次为其分配前 32 个页面。之后，InnoDB 开始为该段分配整个区。InnoDB 可以添加最多 4 个范围一次传输到一个大段中，以确保数据的良好顺序性。

这解释了我的表中的空白页。因此，.ibd文件可能比实际数据大几倍。

根本原因实际上与分区无关，但分区会放大影响，因为分区文件比单文件表小得多，并且包含的​​行数也少得多（特别是如果有很多几乎为空的分区，但这些分区仍然具有相当大的文件大小）

相关内容