MySQLでパーティショニングを試してみる
CentOSにMySQLとMemcacheのインストールしてみる
の続き
MySQLでパーティショニングを試してみる。
大量データ追加用のテーブルを用意しておく
今回はtestデータベースで試してみる。
mysql> use test;
まず、大量のデータをINSERTするためのテーブルを用意しておく。
（大量データの追加に関しては、MySQLで大量のデータを追加してみるを参照）
mysql> create table tmp (id int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; mysql> insert into tmp values (), (), (), (), (), (), (), (), (), (); mysql> select * from tmp; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+
パーティションを設定しない場合
パーティションを設定しない場合を試してみる。
テーブルを作成して、
mysql> create table sample ( id int NOT NULL AUTO_INCREMENT, type int NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;
大量のデータをINSERTする。
mysql> insert into sample (type) select t1.id from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
1千万のデータが入る。
mysql> select count(id) from sample; +-----------+ | count(id) | +-----------+ | 10000000 | +-----------+
mysql> select * from sample limit 20; +----+------+ | id | type | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | | 10 | 10 | | 11 | 1 | | 12 | 2 | | 13 | 3 | | 14 | 4 | | 15 | 5 | | 16 | 6 | | 17 | 7 | | 18 | 8 | | 19 | 9 | | 20 | 10 | +----+------+
typeが1のデータだけ削除してみる。
約16秒かかった。
mysql> delete from sample where type = 1; Query OK, 1000000 rows affected (16.81 sec)
削除できているのを確認。
mysql> select count(id) from sample; +-----------+ | count(id) | +-----------+ | 9000000 | +-----------+
パーティションを設定した場合
次に、パーティションを設定した場合を試してみる。
まず、テーブルを作成する。
プライマリキーは、idとtypeにしている。
パーティションに使うカラムは、プライマリキーに含まないといけない制約があるため。
パーティションをプライマリキーにtypeを含めないと、下記のようなエラーが出る。 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> create table sample_p ( id int NOT NULL AUTO_INCREMENT, type int NOT NULL, PRIMARY KEY (id, type) ) ENGINE=InnoDB;
パーティションを設定する。
mysql> ALTER TABLE sample_p PARTITION BY RANGE (type) ( PARTITION p_type_1 VALUES LESS THAN (2) ENGINE = InnoDB, PARTITION p_type_2 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p_type_3 VALUES LESS THAN (4) ENGINE = InnoDB, PARTITION p_type_4 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p_type_5 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p_type_6 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p_type_7 VALUES LESS THAN (8) ENGINE = InnoDB, PARTITION p_type_8 VALUES LESS THAN (9) ENGINE = InnoDB, PARTITION p_type_9 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p_type_10 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN MAXVALUE );
大量のデータをINSERTする。
mysql> insert into sample_p (type) select t1.id from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
1千万のデータが入る。
mysql> select count(*) from sample_p; +----------+ | count(*) | +----------+ | 10000000 | +----------+
mysql> select * from sample_p limit 20; +----+------+ | id | type | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | | 10 | 10 | | 11 | 1 | | 12 | 2 | | 13 | 3 | | 14 | 4 | | 15 | 5 | | 16 | 6 | | 17 | 7 | | 18 | 8 | | 19 | 9 | | 20 | 10 | +----+------+
typeが1のデータだけ削除してみる。
一瞬で終わった。
mysql> ALTER TABLE sample_p DROP PARTITION p_type_1; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
削除できているのが確認できる。
mysql> select count(*) from sample_p; +----------+ | count(*) | +----------+ | 9000000 | +----------+
パーティションの確認
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, PARTITION_DESCRIPTION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sample_p'; +--------------+------------+----------------+----------------------------+-----------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | TABLE_ROWS | +--------------+------------+----------------+----------------------------+-----------------------+------------+ | test | sample_p | p_type_2 | 1 | 3 | 1000491 | | test | sample_p | p_type_3 | 2 | 4 | 1000491 | | test | sample_p | p_type_4 | 3 | 5 | 1000491 | | test | sample_p | p_type_5 | 4 | 6 | 1000491 | | test | sample_p | p_type_6 | 5 | 7 | 1000491 | | test | sample_p | p_type_7 | 6 | 8 | 1000491 | | test | sample_p | p_type_8 | 7 | 9 | 1000491 | | test | sample_p | p_type_9 | 8 | 10 | 1000491 | | test | sample_p | p_type_10 | 9 | 11 | 1000491 | | test | sample_p | pmax | 10 | MAXVALUE | 0 | +--------------+------------+----------------+----------------------------+-----------------------+------------+
下記のSQLでも確認できる。
mysql> show create table sample_p; +----------+--------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------+ | sample_p | CREATE TABLE `sample_p` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` int(11) NOT NULL, PRIMARY KEY (`id`,`type`) ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (type) (PARTITION p_type_2 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p_type_3 VALUES LESS THAN (4) ENGINE = InnoDB, PARTITION p_type_4 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p_type_5 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p_type_6 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p_type_7 VALUES LESS THAN (8) ENGINE = InnoDB, PARTITION p_type_8 VALUES LESS THAN (9) ENGINE = InnoDB, PARTITION p_type_9 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p_type_10 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ | +----------+--------------------------------------------------+
パーティションの追加
新たにパーティションを追加したい場合は、下記のようなSQLを発行する。
mysql> ALTER TABLE sample_p REORGANIZE PARTITION pmax INTO ( PARTITION p_type_11 VALUES LESS THAN (12), PARTITION pmax VALUES LESS THAN MAXVALUE);
追加されているのが確認できる。
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, PARTITION_DESCRIPTION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sample_p'; +--------------+------------+----------------+----------------------------+-----------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | TABLE_ROWS | +--------------+------------+----------------+----------------------------+-----------------------+------------+ | test | sample_p | p_type_2 | 1 | 3 | 1000491 | | test | sample_p | p_type_3 | 2 | 4 | 1000491 | | test | sample_p | p_type_4 | 3 | 5 | 1000491 | | test | sample_p | p_type_5 | 4 | 6 | 1000491 | | test | sample_p | p_type_6 | 5 | 7 | 1000491 | | test | sample_p | p_type_7 | 6 | 8 | 1000491 | | test | sample_p | p_type_8 | 7 | 9 | 1000491 | | test | sample_p | p_type_9 | 8 | 10 | 1000491 | | test | sample_p | p_type_10 | 9 | 11 | 1000491 | | test | sample_p | p_type_11 | 10 | 12 | 0 | | test | sample_p | pmax | 11 | MAXVALUE | 0 | +--------------+------------+----------------+----------------------------+-----------------------+------------+
