パーティションの刈り込み
パーティショニングされたテーブルに対するクエリで重要なのが、パーティションの刈り込み (Partition Pruning) が行われるかどうかである。刈り込みとは、検索条件と照らし合わせた結果、アクセスする必要のないパーティションを検索対象から除外することであり、それにより、アクセスしなければならない行数が減り、クエリが効率化されるのがパーティショニングの利点となる。
例えば以下のようなテーブルを考える。
mysql> CREATE TABLE price_list_history (
-> item_id INT,
-> price DECIMAL(10, 0),
-> start_date DATE,
-> PRIMARY KEY (item_id, start_date))
-> PARTITION BY RANGE COLUMNS (start_date)
-> (PARTITION p1 VALUES LESS THAN ('2016-01-01'),
-> PARTITION p2 VALUES LESS THAN ('2016-02-01'),
-> PARTITION p3 VALUES LESS THAN ('2016-03-01'),
-> PARTITION p4 VALUES LESS THAN ('2016-04-01'),
-> PARTITION p5 VALUES LESS THAN ('2016-05-01'),
-> PARTITION p6 VALUES LESS THAN ('2016-06-01'),
-> PARTITION p7 VALUES LESS THAN ('2016-07-01'),
-> PARTITION p8 VALUES LESS THAN ('2016-08-01'),
-> PARTITION p9 VALUES LESS THAN ('2016-09-01'),
-> PARTITION p10 VALUES LESS THAN ('2016-10-01'),
-> PARTITION p11 VALUES LESS THAN ('2016-11-01'),
-> PARTITION p12 VALUES LESS THAN ('2016-12-01'),
-> PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO price_list_history VALUES (1, 1000, '2016-04-01'), (1, 1000, '2016-04-20'), (2, 1000, '2016-01-01'), (2, 1000, '2016-08-01');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM price_list_history;
+---------+-------+------------+
| item_id | price | start_date |
+---------+-------+------------+
| 2 | 1000 | 2016-01-01 |
| 1 | 1000 | 2016-04-01 |
| 1 | 1000 | 2016-04-20 |
| 2 | 1000 | 2016-08-01 |
+---------+-------+------------+
4 rows in set (0.00 sec) ここで、以下のような EXPLAIN を実行する。
mysql> EXPLAIN SELECT MAX(price) FROM price_list_history
-> WHERE item_id = 1 AND start_date >= '2016-04-01'
-> AND start_date < '2016-05-01' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: price_list_history
partitions: p5
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 7
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec) 上記の結果より p5 のみが検索対象になっていることがわかる。これは WHERE 句で指定された start_date の範囲から導き出された推論によるものである。
もし範囲を以下のように変えると p5 ~ p8 が検索対象になる。
mysql> EXPLAIN SELECT MAX(price) FROM price_list_history
-> WHERE item_id = 1 AND start_date >= '2016-04-01'
-> AND start_date < '2016-08-01' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: price_list_history
partitions: p5,p6,p7,p8
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 7
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec) パーティショニングによる検索の高速化は、刈り込みが聞いている場合しか効果はない。以下は、パーティションキーをクエリ内で指定していないが故に、刈り込みがないタイプのクエリである。この場合全てのパーティションにアクセスしなければならず、クエリのオーバーヘッドが増える。一般にパーティション数が多くなればなるほど、刈り込みが効かない場合のデメリットは大きい。
mysql> EXPLAIN SELECT MAX(price) FROM price_list_history WHERE item_id = 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: price_list_history
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,pmax
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 2
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) パーティションの物理レイアウト
デフォルトの設定では innodb_file_per_table が有効であるため、InnoDB テーブルは、パーティションごとに 1 つの .ibd ファイルを作成するようになっている。
$ sudo ls -l /var/lib/mysql/hoge/
total 382316
-rw-r----- 1 mysql mysql 65 8月 7 09:04 db.opt
-rw-r----- 1 mysql mysql 8714 8月 13 10:02 foo.frm
-rw-r----- 1 mysql mysql 390070272 8月 13 10:03 foo.ibd
-rw-r----- 1 mysql mysql 8640 8月 21 10:45 price_list_history.frm
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p10.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p11.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p12.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:46 price_list_history#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p3.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p4.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:46 price_list_history#P#p5.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p6.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p7.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#p8.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:46 price_list_history#P#p9.ibd
-rw-r----- 1 mysql mysql 98304 8月 21 10:45 price_list_history#P#pmax.ibd
-rw-r----- 1 mysql mysql 8554 8月 15 03:19 t.frm
-rw-r----- 1 mysql mysql 98304 8月 15 03:21 t.ibd パーティションごとに .ibd ファイルを別のディスクに配置することで、負荷分散やディスク容量の増加を狙うことも可能であり、そのようにするには、パーティションごとに DATA DIRECTORY を指定する。DATA DIRECTORY の指定は MySQL 5.6 から可能になっている。
mysql> CREATE TABLE price_list_history (
-> item_id INT,
-> price DECIMAL(10, 0) ,
-> start_date DATE,
-> PRIMARY KEY (item_id, start_date))
-> PARTITION BY RANGE COLUMNS (start_date)
-> (PARTITION p1 VALUES LESS THAN ('2016-01-01') DATA DIRECTORY = '/data1',
-> PARTITION p2 VALUES LESS THAN ('2016-02-01') DATA DIRECTORY = '/data2',
... 1 つのパーティションに 1 つのファイルの場合、パーティション数が多くなるとファイルのオープン、クローズによるオーバーヘッドが増える。そのような場合には一般テーブルスペースを利用すると良い。テーブルスペースは、テーブル全体で同じものを指定するだけではなく、パーティションごとに指定することも可能となる。DATA DIRECTORY では、パーティションごとに .ibd ファイルを作成するので、パーティション数に応じてファイル数も多くなる。一般テーブルスペースであれば、複数のパーティションをまとめて格納できるので、ファイル数は増えない。
mysql> CREATE TABLE price_list_history (
-> item_id INT,
-> price DECIMAL(10, 0) ,
-> start_date DATE,
-> PRIMARY KEY (item_id, start_date))
-> PARTITION BY RANGE COLUMNS (start_date)
-> (PARTITION p1 VALUES LESS THAN ('2016-01-01') TABLESPACE tsdisk_1,
-> PARTITION p2 VALUES LESS THAN ('2016-02-01') TABLESPACE tsdisk_2,
... テーブル設計時の留意点
MySQL 5.7 の時点では、まだグローバルインデックスがサポートされていない。ローカルインデックスでは他のパーティションにどのようなデータがあるかわからないため、インデックスを用いて一意性の確認を取ることは構造上できない。
パーティショニングされたテーブルであっても、主キーやユニークインデックスを作成することは可能であるが、MySQL 5.7 ではローカルインデックスしかないため、パーティションキーを算出するために使用するカラムは、すべての主キーまたはユニークインデックスに含まれていなければならないという制約がある。あるテーブルないのすべての主キーとユニークインデックスに共通で含まれるようなカラムがない場合には、パーティショニングはできない。
また、パーティショニングされたテーブルは、外部キー制約に含まれない。これは、子テーブルか親テーブルかに関係のない制限となる。外部キー制約が必要な場合はパーティショニングは諦める必要がある。