mysql5.1 検証：パーティションをまたぐ検索の動き

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

▼テストテーブルの作成 create table pt_test ( id int not null, detail varchar(20), PRIMARY KEY (id) );

▼パーティション化 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 件のデータをインサート

▼結果確認 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を指定していようがいまいが、パーティションをまたいで検索する

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 指定ですと、パーティションは限定される

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)