LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@hmatsu47

MySQL のパーティショニングで速くなる？ならない？問題、実験の続き

Last updated at Posted at 2020-10-04

この記事は、

の続きのエントリとなります。

先のエントリのコメント欄に、

先ほどの「パーティショニングあり／なしの速度差の原因」ですが、端的に言うと**「アクセス経路の違い」と「スキャンするデータの局在性」**でしょうね。

と書きましたが、**「それなら非パーティショニングテーブルでも、スキャン／抽出するデータ行がまとまった場所に集まっていれば速い（遅くならない）のでは？」**という疑問が湧くと思います。

先のエントリで使った実験データは（詳細の説明をしていませんでしたが）以下のとおり主キーの順番≠日時列の順番で入っていました。

元データ（部分）
mysql> USE no_partition_test;
Database changed
mysql> SELECT * FROM log_record ORDER BY log_id LIMIT 3;
+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| log_id | log_datetime        | log_text                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|      1 | 2018-08-28 00:00:00 | cea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfacea62607ca12aa2ff2ff850cf4c4abfa |
|      2 | 2018-11-01 00:00:00 | 8819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb68819b8e829bbd453888020d84dc0ccb6 |
|      3 | 2014-12-10 00:00:00 | 8428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae938428a3f252267bc76200d6e9e52aae93 |
+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

テーブル名がlog_recordなのでいささかミスリーディングな気がしますが、これはネタの発端となった**第 17 回 PostgreSQL アンカンファレンス@オンライン**での発表内容（さらにいうとそのネタ元のインフラ勉強会での発表内容）を受けて作ったものでした。

通常、何らかのシステムが吐き出すログは、ほぼ時系列で記録されるので、主キーがAUTO_INCREMENTであればほぼ主キーの順番≒日時列の順番となるはずです（※）。MySQL（InnoDB）は主キー順にデータ行が入るテーブル構造（クラスタインデックス）になっているので、日時順にデータを入れ直してあげれば、先のエントリ（コメント含む）で実行したクエリでスキャン／抽出対象となる行のデータが近接した場所に連続して入るはずです。

（※）通常、ログは処理の実行完了時に記録されるので、（処理が並行または並列で実行されて）完了順が変わるケースでは完全な日時順にならないことがあります。ただし、Web サーバなど多くのケースでは、完了が遅れて順番が入れ替わる範囲は数秒か長くて数分程度でしょう。

非パーティショニングテーブルでデータの並び順を変更する

まずは新しい非パーティショニングテーブルを作ってデータの並び順を変更します。

新しい非パーティショニングテーブルを作成してデータを日時順に入れ替える
mysql> SHOW CREATE TABLE log_record\G
*************************** 1. row ***************************
       Table: log_record
Create Table: CREATE TABLE `log_record` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `log_datetime` datetime NOT NULL,
  `log_text` varchar(500) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `log_datetime` (`log_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=655361 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> CREATE TABLE log_record2 (
    ->   log_id int NOT NULL AUTO_INCREMENT,
    ->   log_datetime datetime NOT NULL,
    ->   log_text varchar(500) NOT NULL,
    ->   PRIMARY KEY (log_id),
    ->   KEY `log_datetime` (log_datetime)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO log_record2 (log_datetime, log_text) SELECT log_datetime, log_text FROM log_record ORDER BY log_datetime;
Query OK, 655360 rows affected (57.62 sec)
Records: 655360  Duplicates: 0  Warnings: 0

変更完了です。

並べ替え後のデータ（部分）
mysql> SELECT * FROM log_record2 ORDER BY log_id LIMIT 3;
+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| log_id | log_datetime        | log_text                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|      1 | 2014-01-01 00:00:00 | 15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa15e7ecc786fd8d70df40ce03bb0ac1fa |
|      2 | 2014-01-01 00:00:00 | b4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4cb4ec96b6a62f1c1c48912bf1d29d4d4c |
|      3 | 2014-01-01 00:00:00 | c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433c64641b28c17d0eccb19bb0addb22433 |
+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

同じ日時が並んでいるのでちょっとわかりづらいですが、最も日時が古い行が先頭に来ています。

並び順を変えた非パーティショニングテーブルでクエリを実行してみる

  • DATETIME列＋非インデックス列（長いVARCHAR文字列）を条件に絞り込み
  • 射影（取得）列に非インデックス列（長いVARCHAR文字列）を含める（コメント内で実行したもの）

を試します。

並び順を変えた非パーティショニングテーブルでクエリ実行
mysql> SELECT COUNT(*) FROM log_record2 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a';
+----------+
| COUNT(*) |
+----------+
|     4756 |
+----------+
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record2 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record2
   partitions: NULL
         type: range
possible_keys: log_datetime
          key: log_datetime
      key_len: 5
          ref: NULL
         rows: 29182
     filtered: 33.33
        Extra: Using index condition; Using where; Using MRR
1 row in set, 1 warning (0.01 sec)

mysql> SELECT COUNT(log_text) FROM log_record2 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999';
+-----------------+
| COUNT(log_text) |
+-----------------+
|           15706 |
+-----------------+
1 row in set (0.07 sec)

いずれも 3 秒以上掛かっていた元のデータよりも大幅に高速化し、さらに年別のパーティショニングテーブルと比較して 2 倍以上速くなりました。

…って、なんだか一見高速化テクニックを語っているように見えますが、むしろ「入るデータの特性によってはパーティショニングが全く生きない（どころか足かせになる）」という意味ですので…。

パーティションを細かくしたらどうなる？

一方、パーティションを細分化したパーティショニングテーブルではどうなるでしょうか？

試しに、年別ではなく年月別のパーティションに変更し、データは日時順にせずに元の順番で入れてみます。

ただし、DATETIME列を単純に年月別のパーティションに分割することができないので、ここではTIMESTAMP列に変更して試してみます。

パーティショニングテーブルでパーティションを年月単位に細分化
mysql> USE partition_test;
Database changed
mysql> CREATE TABLE log_record3 (
    ->   log_id int NOT NULL AUTO_INCREMENT,
    ->   log_datetime timestamp NOT NULL,
    ->   log_text varchar(500) NOT NULL,
    ->   PRIMARY KEY (log_id, log_datetime),
    ->   KEY log_datetime (log_datetime)
    -> )
    -> PARTITION BY RANGE ( UNIX_TIMESTAMP(log_datetime) )
    -> (
    ->   PARTITION pl VALUES LESS THAN ( UNIX_TIMESTAMP('2015-01-01 00:00:00') ),
    ->   PARTITION p201501 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-02-01 00:00:00') ),
    ->   PARTITION p201502 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-03-01 00:00:00') ),
    ->   PARTITION p201503 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-04-01 00:00:00') ),
    ->   PARTITION p201504 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-05-01 00:00:00') ),
    ->   PARTITION p201505 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-06-01 00:00:00') ),
    ->   PARTITION p201506 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-07-01 00:00:00') ),
    ->   PARTITION p201507 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-08-01 00:00:00') ),
    ->   PARTITION p201508 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-09-01 00:00:00') ),
    ->   PARTITION p201509 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-10-01 00:00:00') ),
    ->   PARTITION p201510 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-11-01 00:00:00') ),
    ->   PARTITION p201511 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-01 00:00:00') ),
    ->   PARTITION p201512 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-01-01 00:00:00') ),
    ->   PARTITION p201601 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-02-01 00:00:00') ),
    ->   PARTITION p201602 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-03-01 00:00:00') ),
    ->   PARTITION p201603 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-04-01 00:00:00') ),
    ->   PARTITION p201604 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ),
    ->   PARTITION p201605 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ),
    ->   PARTITION p201606 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-07-01 00:00:00') ),
    ->   PARTITION p201607 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-08-01 00:00:00') ),
    ->   PARTITION p201608 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-09-01 00:00:00') ),
    ->   PARTITION p201609 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00') ),
    ->   PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00') ),
    ->   PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00') ),
    ->   PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),
    ->   PARTITION p201701 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00') ),
    ->   PARTITION p201702 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00') ),
    ->   PARTITION p201703 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00') ),
    ->   PARTITION p201704 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-05-01 00:00:00') ),
    ->   PARTITION p201705 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-06-01 00:00:00') ),
    ->   PARTITION p201706 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00') ),
    ->   PARTITION p201707 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-08-01 00:00:00') ),
    ->   PARTITION p201708 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-09-01 00:00:00') ),
    ->   PARTITION p201709 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-10-01 00:00:00') ),
    ->   PARTITION p201710 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-11-01 00:00:00') ),
    ->   PARTITION p201711 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-01 00:00:00') ),
    ->   PARTITION p201712 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-01-01 00:00:00') ),
    ->   PARTITION p201801 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-02-01 00:00:00') ),
    ->   PARTITION p201802 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-03-01 00:00:00') ),
    ->   PARTITION p201803 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-04-01 00:00:00') ),
    ->   PARTITION p201804 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-05-01 00:00:00') ),
    ->   PARTITION p201805 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-06-01 00:00:00') ),
    ->   PARTITION p201806 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-07-01 00:00:00') ),
    ->   PARTITION p201807 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-08-01 00:00:00') ),
    ->   PARTITION p201808 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-09-01 00:00:00') ),
    ->   PARTITION p201809 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-10-01 00:00:00') ),
    ->   PARTITION p201810 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-11-01 00:00:00') ),
    ->   PARTITION p201811 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-12-01 00:00:00') ),
    ->   PARTITION p201812 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-01 00:00:00') ),
    ->   PARTITION p201901 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-02-01 00:00:00') ),
    ->   PARTITION p201902 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-03-01 00:00:00') ),
    ->   PARTITION p201903 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-04-01 00:00:00') ),
    ->   PARTITION p201904 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-05-01 00:00:00') ),
    ->   PARTITION p201905 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-06-01 00:00:00') ),
    ->   PARTITION p201906 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-07-01 00:00:00') ),
    ->   PARTITION p201907 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-08-01 00:00:00') ),
    ->   PARTITION p201908 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-09-01 00:00:00') ),
    ->   PARTITION p201909 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-10-01 00:00:00') ),
    ->   PARTITION p201910 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-11-01 00:00:00') ),
    ->   PARTITION p201911 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-12-01 00:00:00') ),
    ->   PARTITION p201912 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-01-01 00:00:00') ),
    ->   PARTITION pu VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (5.52 sec)

mysql> INSERT INTO log_record3 SELECT * FROM log_record;
Query OK, 655360 rows affected (2 min 14.79 sec)
Records: 655360  Duplicates: 0  Warnings: 0

データが入ったので試してみます。

細分化したパーティショニングテーブルで確認
mysql> SELECT COUNT(*) FROM log_record3 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a';
+----------+
| COUNT(*) |
+----------+
|     4756 |
+----------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record3 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record3
   partitions: p201904,p201905
         type: ALL
possible_keys: log_datetime
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15706
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql> SELECT COUNT(log_text) FROM log_record3 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999';
+-----------------+
| COUNT(log_text) |
+-----------------+
|           15706 |
+-----------------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT COUNT(log_text) FROM log_record3 WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record3
   partitions: p201904,p201905
         type: ALL
possible_keys: log_datetime
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15706
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

いずれも 2 つの年月のパーティションに対してフルスキャンしていますが、性能面では非パーティショニングテーブルでインデックスを使うケースとほぼ同等の結果となりました。

（もうちょっと速くなるかな？と思ったんですけどね。）

というわけで、

使いどころが（実験前より）さらに難しく感じるようになった MySQL のパーティショニング…。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?