☰

為什麼分割區的 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_空間使用該工具我發現我的表大部分由空分配的頁面組成，這使我找到了這個 MySQL文件頁。它說：

頁面被分組為大小為 1MB 的擴充區，頁面大小最大為 16KB

和

當一個段在表空間內成長時，InnoDB 一次為其分配前 32 個頁面。之後，InnoDB開始將整個磁碟區分配給該段。 InnoDB可以新增最多 4 個範圍一次寫入一個大段，以確保資料的良好順序性。

這解釋了我的表格中的空白頁。因此，.ibd文件可能比實際數據大幾倍。

根本原因實際上與分區無關，但分區放大了效果，因為分區文件比單文件表小得多並且包含的行數更少（特別是如果有很多幾乎為空的分區，而這些分區的文件大小仍然相當大） ）

相關內容