MySQL パーティションによる性能の変化を計測する

このエントリーは MySQL - Qiita Advent Calendar 2025 - Qiita の 13日目のエントリーです。昨日は、@discus_hamburg さんの帰ってきた！標準はあるにはあるが癖の多いSQL #20 - Table Value Constructer （TVC）でした。

以下のエントリーで、MySQL でパーティション化によって性能が向上するケース、劣化するケースを考察しました。今回は、ベンチマークを行い、想定どおりの動きになっているか確かめてみます。

mita2db.hateblo.jp

テストデータ

パーティションキーは order_date の年です。 パーティション化なし と パーティション数 2, 4, 8, 16 の合計 5パターンのテーブルで計測しました。

-- パーティション数2
mysql> SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `customer_id` bigint NOT NULL,
  `order_date` date NOT NULL,
  PRIMARY KEY (`id`,`order_date`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_order_date` (`order_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
/*!50100 PARTITION BY RANGE (year(`order_date`))
(PARTITION p2008 VALUES LESS THAN (2008) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB) */
1 row in set (0.00 sec)

-- パーティション数16
mysql> SHOW CREATE TABLE t16 \G
*************************** 1. row ***************************
       Table: t16
Create Table: CREATE TABLE `t16` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `customer_id` bigint NOT NULL,
  `order_date` date NOT NULL,
  PRIMARY KEY (`id`,`order_date`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_order_date` (`order_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
/*!50100 PARTITION BY RANGE (year(`order_date`))
(PARTITION p2001 VALUES LESS THAN (2001) ENGINE = InnoDB,
 PARTITION p2002 VALUES LESS THAN (2002) ENGINE = InnoDB,
 PARTITION p2003 VALUES LESS THAN (2003) ENGINE = InnoDB,
 PARTITION p2004 VALUES LESS THAN (2004) ENGINE = InnoDB,
 PARTITION p2005 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p2006 VALUES LESS THAN (2006) ENGINE = InnoDB,
 PARTITION p2007 VALUES LESS THAN (2007) ENGINE = InnoDB,
 PARTITION p2008 VALUES LESS THAN (2008) ENGINE = InnoDB,
 PARTITION p2009 VALUES LESS THAN (2009) ENGINE = InnoDB,
 PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2011 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION p2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB) */
1 row in set (0.00 sec)

レコード数は 1年あたり、62500 行、合計 100万行。各パーティションに含まれるレコード数のばらつきによる影響が出ないよう、各パーティションのレコード数は均等になるようにパーティションの区切りを調整しています。パーティション数が 2 であれば、１パーティションあたりは 50万行、パーティション数が 16 であれば、1パーティションあたり 62500行です。

mysql> select count(*), year(order_date) from t0 group by year(order_date);
+----------+------------------+
| count(*) | year(order_date) |
+----------+------------------+
|    62500 |             2000 |
|    62500 |             2001 |
|    62500 |             2002 |
|    62500 |             2003 |
|    62500 |             2004 |
|    62500 |             2005 |
|    62500 |             2006 |
|    62500 |             2007 |
|    62500 |             2008 |
|    62500 |             2009 |
|    62500 |             2010 |
|    62500 |             2011 |
|    62500 |             2012 |
|    62500 |             2013 |
|    62500 |             2014 |
|    62500 |             2015 |
+----------+------------------+
16 rows in set (0.61 sec)

MySQL はバージョン 8.0.44 を利用しました。

パーティションキーを含まない検索：性能が低下

パーティションキー order_date を含まないクエリの性能を見てみましょう。クエリは customer_id = 1000 のレコードをSELECTするシンプルなものです。

mysql> SELECT * FROM t2 WHERE customer_id = 1000;
+------+-------------+------------+
| id   | customer_id | order_date |
+------+-------------+------------+
| 1000 |        1000 | 2000-09-06 |
+------+-------------+------------+
1 row in set (0.00 sec)

パーティションしていない場合がもっとも速く、またパーティション数を増やすにつれて、性能が劣化していきます。前回の記事に書いたとおり、パーティション化により複数のインデックスをスキャンする必要があるため、パーティションすればするほど遅くなったと考えられます。

実行計画からも partitions: p2001,p2002,p2003,... とすべてのパーティションをスキャンしていることが読み取れます。

mysql> EXPLAIN SELECT * FROM t16 WHERE customer_id = 1000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t16
   partitions: p2001,p2002,p2003,p2004,p2005,p2006,p2007,p2008,p2009,p2010,p2011,p2012,p2013,p2014,p2015,p2016
         type: ref
possible_keys: idx_customer_id
          key: idx_customer_id
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

インデックスを使うパーティションキーを含んだ検索：変化なし

次は検索条件にパーティションキーを含む場合です。このクエリはパーティションをまたがず、単一のパーティションのデータを取り出します。 order_date にはインデックスが追加してあり、このクエリはインデックスにより最適化されます。

SELECT COUNT(*) FROM t16 WHERE order_date BETWEEN "2011-01-01" AND "2011-12-31"

インデックスが効くようなクエリでは、パーティション有無による大きな差はありませんでした (パーティションしないほうが若干速い...）。パーティション化によってインデックスも分割されているはずですが、インデックス(B-Tree) の検索コストは O(log n) なので、16分割程度では差がでないのでしょう。

実行計画ではパーティションプルーニングされていることが確認できます。

mysql> EXPLAIN SELECT COUNT(*) FROM t16 WHERE order_date BETWEEN "2011-01-01" AND "2011-12-31" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t16
   partitions: p2012
         type: range
possible_keys: PRIMARY,idx_customer_id,idx_order_date
          key: idx_order_date
      key_len: 3
          ref: NULL
         rows: 31387
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ちなみに、より狭いレンジでのクエリでも同様の傾向でした。

SELECT COUNT(*) FROM t16 WHERE order_date = "2011-01-01"

テーブルフルスキャン：効果あり

パーティションキーを含んだ検索で、フルスキャンを利用する場合です。先ほどのクエリにヒント句を追加して、フルスキャンを選択させました。

mysql> SELECT COUNT(*) FROM t16 IGNORE INDEX (idx_order_date, idx_customer_id) WHERE order_date BETWEEN "2011-01-01" AND "2011-12-31";
+----------+
| COUNT(*) |
+----------+
|    62500 |
+----------+
1 row in set (0.06 sec)

パーティション数を増やすと、レスポンスタイムが小さくなり、性能が向上しました。パーティションプルーニングによる効果が確認できました。

実行計画でも、パーティション化したテーブルでは rows の見積もりが小さくなっています。

-- パーティションなし
mysql> EXPLAIN SELECT COUNT(*) FROM t0 IGNORE INDEX (idx_order_date, idx_customer_id) WHERE order_date BETWEEN "2011-01-01" AND "2011-12-31" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t0
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 997878
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

-- パーティションあり
mysql> EXPLAIN SELECT COUNT(*) FROM t16 IGNORE INDEX (idx_order_date, idx_customer_id) WHERE order_date BETWEEN "2011-01-01" AND "2011-12-31 " \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t16
   partitions: p2012
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 11
          ref: NULL
         rows: 62775
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

まとめ

検索条件にパーティションキーを含むか	インデックスの利用	パーティション化の効果
含まない	インデックスを利用	性能向上は見込めず、オーバーヘッドが増える。
含む	インデックスを利用	インデックスが効率的なため、パーティションの恩恵はほとんどない。
含む	フルスキャン	パーティションプルーニングによる読み込みデータ量削減で性能が向上する。

明日は、zoosm3 さんです！

mita2 database life

主にMySQLに関するメモです

MySQL パーティションによる性能の変化を計測する

テストデータ

パーティションキーを含まない検索：性能が低下

インデックスを使うパーティションキーを含んだ検索：変化なし

テーブルフルスキャン：効果あり

まとめ