検証：パーティショニングテーブルの挙動

レンジパーティショニングの設定をしたテーブルをSQLで条件を指定して実行した場合、パーティションはどのような挙動をするのか？を検証してみました。explain partitionsなんていう便利なコマンドがあり、検証が楽勝でした。まだまとめきれていないので、すこし乱暴に書いておりますが、ざっと見ていただき、「あ～パーティショニングってこういうものなのか」と理解いただくとうれしいです。

パーティショニングテーブルにしたのは良いけど、パフォーマンスが向上していない・・という方は、一度、パーティションの動きを確認しただき、SQLをチューニングしていただければ良いかと・・。

## テストテーブルの作成

以下のようなパーティショングテーブルを作成してみました（レンジパーティション）

```sql

create table pt_test
(
id int not null,
detail varchar(20),
PRIMARY KEY (id)
);
```
## パーティション化

```sql

alter table pt_test
PARTITION BY RANGE (id) (
PARTITION p01 VALUES LESS THAN (5000),
PARTITION p02 VALUES LESS THAN (10000),
PARTITION p03 VALUES LESS THAN (15000),
PARTITION p04 VALUES LESS THAN (20000),
PARTITION p05 VALUES LESS THAN (25000),
PARTITION p06 VALUES LESS THAN (30000),
PARTITION p07 VALUES LESS THAN (35000),
PARTITION p08 VALUES LESS THAN (40000),
PARTITION p09 VALUES LESS THAN (45000),
PARTITION p10 VALUES LESS THAN (50000),
PARTITION pover VALUES LESS THAN MAXVALUE
);
```

## 初回データ投入

id=1～65535 件のデータをインサート

## 結果確認

```sql
mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='pt_test';

+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| test_db      | pt_test    | p01            |                          1 |       4999 |
| test_db      | pt_test    | p02            |                          2 |       5000 |
| test_db      | pt_test    | p03            |                          3 |       5000 |
| test_db      | pt_test    | p04            |                          4 |       5000 |
| test_db      | pt_test    | p05            |                          5 |       5000 |
| test_db      | pt_test    | p06            |                          6 |       5000 |
| test_db      | pt_test    | p07            |                          7 |       5000 |
| test_db      | pt_test    | p08            |                          8 |       5000 |
| test_db      | pt_test    | p09            |                          9 |       5000 |
| test_db      | pt_test    | p10            |                         10 |       5000 |
| test_db      | pt_test    | pover          |                         11 |      15537 |
+--------------+------------+----------------+----------------------------+------------+
11 rows in set (0.00 sec)
```

## パーティションの利用状況を確認する

### 片側指定ですと、limitを指定していようがいまいが、パーティションをまたいで検索する

```sql
mysql> explain partitions select * from pt_test where id < 10000;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | pt_test | p01,p02    | ALL  | PRIMARY       | NULL | NULL    | NULL | 9999 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from pt_test where id < 10000 limit 100;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | pt_test | p01,p02    | range | PRIMARY       | PRIMARY | 4       | NULL | 9957 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
```

### From ～ To 指定ですと、パーティションは限定される

```sql
mysql> explain partitions select * from pt_test where id > 5000 and id < 10000 limit 100;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | pt_test | p02        | range | PRIMARY       | PRIMARY | 4       | NULL | 4981 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from pt_test where id < 40000 limit 10000;

+----+-------------+---------+---------------------------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table   | partitions                      | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+---------+---------------------------------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | pt_test | p01,p02,p03,p04,p05,p06,p07,p08 | range | PRIMARY       | PRIMARY | 4       | NULL | 39855 | Using where |
+----+-------------+---------+---------------------------------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
```