MySQLのパーティショニングを活かすwhere句の書き方 #MySQL

MySQL がプルーニングを行うことができるのは、パーティショニング関数の列との比較に限られる。

という一節があったため確認してみた、という話です。

すべてのパーティションにアクセスしてしまうクエリ

例えば、create_onという列で年ごとにパーティションを切っているようなテーブルがあるとします。

create文

CREATE TABLE `test_table` (
 `pkey_col` int unsigned NOT NULL AUTO_INCREMENT,
 `some_col` int,
 `create_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`pkey_col`,`create_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(create_on)
(PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);

このテーブルにパーティションで指定していない列で検索するクエリーを作ります。

最初のバージョンのSQL

SELECT
  some_col
FROM
  test_table
WHERE
  pkey_col = 123

実行計画はこのようになります。

+----+-------------+------------+-------------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions        | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+-------------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_table | p2018,p2019,p2020 | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+-------------------+------+---------------+---------+---------+-------+------+----------+-------+

partitionsのところにすべてのパーティションが並んでしまっていますね。

改善版

ここで、where句にパーティションで使用している列を指定してみます。

改善版のSQL

SELECT
  some_col
FROM
  test_table
WHERE
  pkey_col = 123 and
  create_on = '2020-03-21 0:19:00'

すると実行計画は次のようになります。

+----+-------------+------------+------------+-------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test_table | p2020      | const | PRIMARY       | PRIMARY | 9       | const,const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------+------+----------+-------+

アクセスするパーティションが１つになりました。

アクセスするパーティションを限定することの効果

パーティションの限定はプルーニングと呼ばれており、クエリを早くする効果があります。

検索を制限することにより、テーブル内のすべてのパーティションをスキャンするよりも、一致する行を見つけるための時間と労力をはるかに少なくすることができます。この不要なパーティションの「切り取り」は、プルーニングとして知られています。オプティマイザーがこのクエリの実行でパーティションプルーニングを利用できる場合、クエリの実行は、同じ列定義とデータを含むパーティション化されていないテーブルに対する同じクエリよりも桁違いに速くなります。

引用元：　MySQL 8.0 Reference Manualを翻訳

このプルーニングを使うためには、WHERE句にパーティションで指定した列を指定する必要があります。

オプティマイザーは、 WHERE条件を次の2つのケースのいずれかに減らすことができる場合はいつでもプルーニングを実行できます。
partition_column = constant
partition_column IN ( constant1 , constant2 , ..., constantN )

引用元：同上

まとめ

ということで、テーブルの設計や、where句の条件に気をつけないとパーティショニングの効果が薄れてしまうことがわかりました。

なお、MySQLはクエリでパーティションを明示的に指定することもできますので、下に記しておきます。

パーティションを指定する方法

SELECT
  some_col
FROM
  test_table　PARTITION (p2020)
WHERE
  pkey_col = 123