Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

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

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

テストテーブルの作成

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

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)
hit
なんとなくデータベースエンジニア。なんとなくメモしてます
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした