MySQLのパーティションについて勉強

MySQLのパーティショニングとは？

1つのテーブルを分割する機能。
テーブルを分割するので、格納できる根本的な容量の拡張ややり方によっては高速処理を実現できる。

項目	意味
RANGE	パーティションごとに範囲を指定して振り分ける
LIST	パーティションごとに格納する値で振り分ける
HASH	1つのカラムの値を式の結果で振り分ける
KEY	1つ以上のカラムの値をMD5関数等で評価して分割する

• メリット１ 高速化が見込める
  インデックスを貼りたいカラム等のデータの種類が少ない場合
  データ参照時にオプティマイザが、対象のパーティションのみを参照するらしいので、対象範囲が限定されるため高速化が見込める

• メリット２ データ管理が楽になる
  限定的な期間だけ必要なデータを蓄えて置くような場合 ログデータなど
  パーティションを効率敵に使用していると不要データの削除が楽になる。
  → パーティションの削除は内部的にはテーブルの削除（DROP）と同じ様な処理らしいから。

実際に操作してみる

CREATE TABLE IF NOT EXISTS test_log_10000000 (
  id int(11) NOT NULL AUTO_INCREMENT,
  type tinyint(1) NOT NULL,
  created datetime NOT NULL,
  PRIMARY KEY (id, type_code),
);

パーティションの作成

ALTER TABLE test_log_10000000
PATITION BY RANGE columns(type) (
 PARTITION p_type_code0 VALUES LESS THAN (1) ENGINE = InnoDB,
  PARTITION p_type_code1 VALUES LESS THAN (2) ENGINE = InnoDB,
  PARTITION p_type_code2 VALUES LESS THAN (3) ENGINE = InnoDB,
  PARTITION p_type_code3 VALUES LESS THAN (4) ENGINE = InnoDB,
  PARTITION p_type_code4 VALUES LESS THAN (5) ENGINE = InnoDB,
  PARTITION p_type_code5 VALUES LESS THAN (6) ENGINE = InnoDB,
  PARTITION pmax VALUES LESS THAN MAXVALUE
)

パーティションの確認

select
    TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
from INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='test_log_1000000’;

対象パーティションの削除

ALTER TABLE test_log_1000000 DROP PARTITION p_type_code5;

※ パーティションの削除したデータは戻すことができないので注意を払う必要がある

対象パーティションの追加

ALTER TABLE test_log_1000000 REORGANIZE PARTITION pmax INTO (
PARTITION p_type_code5 VALUES LESS THAN (6),
PARTITION pmax VALUES LESS THAN MAXVALUE);

実験

DB作成

CREATE DATABASE partition_test;
USE partition_test;

テーブルの作成

• パーティションテーブル

RANGEパーティショニングで区分キー（パーティションキー）としてcreated_atを指定し、年代ごとに分割しました。

CREATE TABLE partition_record (
  id INT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  test_text VARCHAR(500) NOT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION pl VALUES LESS THAN (2015),
  PARTITION p2015 VALUES LESS THAN (2016),
  PARTITION p2016 VALUES LESS THAN (2017),
  PARTITION p2017 VALUES LESS THAN (2018),
  PARTITION p2018 VALUES LESS THAN (2019),
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION pu VALUES LESS THAN MAXVALUE
);

このテーブルの動きとしては、こんな感じになりそうです。
データ挿入の時は自動で対象パーティションに振り分けされ、参照時には格納されているパーティションのみにアクセスされます。

• 非パーティションテーブル

CREATE TABLE not_partition_record (
  id INT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  test_text VARCHAR(500) NOT NULL,
  PRIMARY KEY (id),
  KEY (created_at)
);

大量データ準備

INSERT INTO partition_record (id, created_at, test_text) 
VALUE 
    (NULL, '2014-07-26 00:00:00', 'dummy1'),
    (NULL, '2015-07-26 00:00:00', 'dummy2'),
    (NULL, '2016-07-26 00:00:00', 'dummy3'),
    (NULL, '2017-07-26 00:00:00', 'dummy4'),
    (NULL, '2018-07-26 00:00:00', 'dummy5'),
    (NULL, '2019-07-26 00:00:00', 'dummy6'),
    (NULL, '2020-07-26 00:00:00', 'dummy7');

INSERT INTO partition_record (
  SELECT 
    partition_record.id = NULL,
    partition_record.created_at,
    partition_record.test_text
    FROM partition_record, 
    partition_record partition_record2, 
    partition_record partition_record3, 
    partition_record partition_record4, 
    partition_record partition_record5,
    partition_record partition_record6
);

SELECT COUNT(*) FROM partition_record;
+----------+
| COUNT(*) |
+----------+
|   117656 |
+----------+

INSERT INTO not_partition_record (id, created_at, test_text) 
VALUE 
    (NULL, '2014-07-26 00:00:00', 'dummy1'),
    (NULL, '2015-07-26 00:00:00', 'dummy2'),
    (NULL, '2016-07-26 00:00:00', 'dummy3'),
    (NULL, '2017-07-26 00:00:00', 'dummy4'),
    (NULL, '2018-07-26 00:00:00', 'dummy5'),
    (NULL, '2019-07-26 00:00:00', 'dummy6'),
    (NULL, '2020-07-26 00:00:00', 'dummy7');

INSERT INTO not_partition_record (
  SELECT 
    not_partition_record.id = NULL,
    not_partition_record.created_at,
    not_partition_record.test_text
    FROM not_partition_record, 
    not_partition_record not_partition_record2, 
    not_partition_record not_partition_record3, 
    not_partition_record not_partition_record4, 
    not_partition_record not_partition_record5,
    not_partition_record not_partition_record6
);

SELECT COUNT(*) FROM not_partition_record;
+----------+
| COUNT(*) |
+----------+
|   117656 |
+----------+

117656件のデータを作成

非パーティションテーブルでの絞り込み

mysql> SELECT COUNT(*) FROM not_partition_record WHERE created_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-31 23:59:59';
+----------+
| COUNT(*) |
+----------+
|    16808 |
+----------+
1 row in set (0.02 sec)

実行計画
mysql> EXPLAIN SELECT COUNT(*) FROM not_partition_record WHERE created_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-31 23:59:59';
+----+-------------+----------------------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
| id | select_type | table                | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+----------------------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | not_partition_record | NULL       | range | created_at    | created_at | 5       | NULL | 28716 |   100.00 | Using where; Using index |
+----+-------------+----------------------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+

mysql> SELECT COUNT(*) FROM not_partition_record WHERE created_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-31 23:59:59' AND test_text > 'a';
+----------+
| COUNT(*) |
+----------+
|    16808 |
+----------+
1 row in set (0.05 sec)

パーティションテーブルでの絞り込み

SELECT COUNT(*) FROM partition_record WHERE created_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-31 23:59:5;5D9';
+----------+
| COUNT(*) |
+----------+
|    16808 |
+----------+
1 row in set, 2 warnings (0.02 sec)

実行計画
mysql> EXPLAIN SELECT COUNT(*) FROM partition_record WHERE created_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-31 23:59:59';
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | partition_record | p2019      | index | NULL          | PRIMARY | 9       | NULL | 16808 |    11.11 | Using where; Using index |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT COUNT(*) FROM partition_record WHERE created_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-31 23:59:59' AND test_text > 'a';
+----------+
| COUNT(*) |
+----------+
|    16808 |
+----------+
1 row in set (0.01 sec)

比較すると速くなっていることがわかります。

テーブル	実行計画	参照の実行時間
非パーティション	type: range, partitions: NULL, rows: 28716	0.05sec
パーティション	type: index, partitions: p2019, rows: 16808	0.01sec

type：結合型
partitions：一致するパーティション
rows：クエリが実行するために調査する行数

※ データの種類によりますが、今回は速くなっていることに気をつけてください。