☰

パーティション化された MySQL InnoDB テーブルが、パーティション化されていないテーブルの 2 倍の大きさになるのはなぜですか?

tag-icon • tag-icon mysql innodb table-partitioning
パーティション化された MySQL InnoDB テーブルが、パーティション化されていないテーブルの 2 倍の大きさになるのはなぜですか?

2 つの InnoDB テーブルがありますが、1 つはパーティション化されており、もう 1 つはパーティション化されていない点を除いて、すべての点で同一です。

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

その結果、パーティション化されたデータは 2 倍の大きさになります。結果は次のとおりです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 vs. 32768)

同じことは、もう少し複雑で、日付ごとに数百万のエントリを含むテーブルでも起こります。パーティション分割を適用すると、テーブルのサイズは 2 倍になります。これは、テーブルの状態と実際のファイル サイズで確認できます。

このような問題に関する情報が見つかりません。なぜこのようなことが起こるのでしょうか?

追記: パーティションの数を 10 に変更し、日付ごとに 100 個のエントリを挿入すると、次のようになります (2 つのテーブルごとに合計 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 がディスク領域を割り当てる方法に起因しています。

ここでの例は、私が説明しようとしていたケースにはそれほど適していないことに今気づきましたが、私の元々の問題は大規模なデータベース（何百万もの小さな行がある）でした。

私はこれを見つけました記事それはスペースこのツールを使用すると、テーブルの大部分が空の割り当てページで構成されていることを発見し、このMySQLにたどり着きました。ドキュメントページそこにはこう書いてある。

ページは、最大16KBのサイズのページに対して1MBのサイズのエクステントにグループ化されます。

そして

テーブルスペース内のセグメントが大きくなると、InnoDBは最初の32ページを1つずつ割り当てます。その後、InnoDBはセグメントにエクステント全体を割り当て始めます。InnoDBは、最大4つの範囲データの適切な順序性を確保するために、一度に大きなセグメントに分割します。

これは、テーブル内の空のページを説明しています。その結果、.ibdファイルは実際のデータの数倍の大きさになる可能性があります。

根本的な原因は実際にはパーティション分割とは関係ありませんが、パーティション分割によって影響が増幅されます。パーティション ファイルは単一ファイルのテーブルよりもはるかに小さく、行数も少ないためです (特に、ほとんど空のパーティションが多数あり、ファイル サイズがかなり大きい場合)。

関連情報