MySQLでHASHのパーティションを試してみる

MySQLでHASHのパーティションを試してみる。
MySQLのバージョンは5.6

【参考】
高速処理化!MySQLのパーティショニング機能を使ってみよう | 株式会社LIG
http://liginc.co.jp/programmer/archives/3832

とりあえず確認用にtestのデータベースを作成する。

CREATE DATABASE test CHARACTER SET utf8;

パーティションを設定したテーブルを作成する。

CREATE TABLE sample (
  id    INT NOT NULL AUTO_INCREMENT,
  type  INT NOT NULL,
  PRIMARY KEY (id, type)
)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY HASH(type)
PARTITIONS 10;

下記のSQLで、パーティションが作成されているのが確認できる。

SELECT
  TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='sample';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| test         | sample     | p0             |                          1 |          0 |
| test         | sample     | p1             |                          2 |          0 |
| test         | sample     | p2             |                          3 |          0 |
| test         | sample     | p3             |                          4 |          0 |
| test         | sample     | p4             |                          5 |          0 |
| test         | sample     | p5             |                          6 |          0 |
| test         | sample     | p6             |                          7 |          0 |
| test         | sample     | p7             |                          8 |          0 |
| test         | sample     | p8             |                          9 |          0 |
| test         | sample     | p9             |                         10 |          0 |
+--------------+------------+----------------+----------------------------+------------+

大量のデータをINSERTするための準備をして

CREATE TABLE tmp (id int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO tmp VALUES (), (), (), (), (), (), (), (), (), ();
SELECT * FROM tmp;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

大量にデータをINSERTしてみる。

INSERT INTO sample (type) SELECT t1.id from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;

データが入った。

SELECT COUNT(*) FROM sample;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
SELECT * FROM sample ORDER BY id LIMIT 15;
+----+------+
| 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 |
+----+------+
SELECT * FROM sample ORDER BY id DESC LIMIT 15;
+----------+------+
| id       | type |
+----------+------+
| 10000000 |   10 |
|  9999999 |    9 |
|  9999998 |    8 |
|  9999997 |    7 |
|  9999996 |    6 |
|  9999995 |    5 |
|  9999994 |    4 |
|  9999993 |    3 |
|  9999992 |    2 |
|  9999991 |    1 |
|  9999990 |   10 |
|  9999989 |    9 |
|  9999988 |    8 |
|  9999987 |    7 |
|  9999986 |    6 |
+----------+------+

パーティションに分散されて格納されているのが確認できる。

SELECT
  TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='sample';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| test         | sample     | p0             |                          1 |     998739 |
| test         | sample     | p1             |                          2 |     998739 |
| test         | sample     | p2             |                          3 |     998739 |
| test         | sample     | p3             |                          4 |     973726 |
| test         | sample     | p4             |                          5 |     998739 |
| test         | sample     | p5             |                          6 |     998739 |
| test         | sample     | p6             |                          7 |     998739 |
| test         | sample     | p7             |                          8 |     973726 |
| test         | sample     | p8             |                          9 |     998739 |
| test         | sample     | p9             |                         10 |     998728 |
+--------------+------------+----------------+----------------------------+------------+