ホーム » ブログ » [MySQL] 日別にパーティショニングする

[MySQL] 日別にパーティショニングする

MySQLで、日別にパーティショニングする例です。

パーティショニングの指定をつけてテーブルを作成します。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE logs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(1024) NOT NULL,
  log_date DATE NOT NULL,
  PRIMARY KEY (id, log_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE columns(log_date) (
  PARTITION p20120322 VALUES LESS THAN ('2012-03-23') ENGINE = InnoDB,
  PARTITION p20120323 VALUES LESS THAN ('2012-03-24') ENGINE = InnoDB,
  PARTITION p20120324 VALUES LESS THAN ('2012-03-25') ENGINE = InnoDB,
  PARTITION p20120325 VALUES LESS THAN ('2012-03-26') ENGINE = InnoDB,
  PARTITION p20120326 VALUES LESS THAN ('2012-03-27') ENGINE = InnoDB,
  PARTITION p20120327 VALUES LESS THAN ('2012-03-28') ENGINE = InnoDB,
  PARTITION p20120328 VALUES LESS THAN ('2012-03-29') ENGINE = InnoDB,
  PARTITION p20120329 VALUES LESS THAN ('2012-03-30') ENGINE = InnoDB,
  PARTITION p20120330 VALUES LESS THAN ('2012-03-31') ENGINE = InnoDB,
  PARTITION p20120331 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
  PARTITION p20120401 VALUES LESS THAN ('2012-04-02') ENGINE = InnoDB
);

※既存のテーブルをパーティショニングする場合は、ALTER TABLEでいけました。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
ALTER TABLE logs
PARTITION BY RANGE columns(log_date) (
PARTITION p20120322 VALUES LESS THAN ('2012-03-23') ENGINE = InnoDB,
PARTITION p20120323 VALUES LESS THAN ('2012-03-24') ENGINE = InnoDB,
PARTITION p20120324 VALUES LESS THAN ('2012-03-25') ENGINE = InnoDB,
PARTITION p20120325 VALUES LESS THAN ('2012-03-26') ENGINE = InnoDB,
PARTITION p20120326 VALUES LESS THAN ('2012-03-27') ENGINE = InnoDB,
PARTITION p20120327 VALUES LESS THAN ('2012-03-28') ENGINE = InnoDB,
PARTITION p20120328 VALUES LESS THAN ('2012-03-29') ENGINE = InnoDB,
PARTITION p20120329 VALUES LESS THAN ('2012-03-30') ENGINE = InnoDB,
PARTITION p20120330 VALUES LESS THAN ('2012-03-31') ENGINE = InnoDB,
PARTITION p20120331 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION p20120401 VALUES LESS THAN ('2012-04-02') ENGINE = InnoDB
);

以下のコマンドで、パーティショニングによってデータが振り分けられているか確認できます。
※テストデータを入れてあります。

mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=’logs';
+————–+——————-+—————-+—————————-+————+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+————–+——————-+—————-+—————————-+————+
| example | logs | p20120322 | 1 | 6537 |
| example | logs | p20120323 | 2 | 8951 |
| example | logs | p20120324 | 3 | 8951 |
| example | logs | p20120325 | 4 | 8391 |
| example | logs | p20120326 | 5 | 8951 |
| example | logs | p20120327 | 6 | 8951 |
| example | logs | p20120328 | 7 | 8951 |
| example | logs | p20120329 | 8 | 7832 |
| example | logs | p20120330 | 9 | 8951 |
| example | logs | p20120331 | 10 | 8951 |
| example | logs | p20120401 | 11 | 5371 |
+————–+——————-+—————-+—————————-+————+
11 rows in set (0.02 sec)

日別にパーティションを切った場合、先のパーティションがないので、cronなどで定期的にパーティションを拡張していけばよいと思います。

パーティションの拡張は以下のようにします。
mysql> ALTER TABLE logs ADD PARTITION (
PARTITION p20120402 VALUES LESS THAN(‘2012-04-03′) ENGINE = INNODB
);

最後に、パーティショニングされたテーブルとそうではないテーブルとで、ある日付のデータを削除した場合の速度を比べてみます。

パーティショニングされたテーブル

mysql> ALTER TABLE logs DROP PARTITION p20120322;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

パーティショニングされていないテーブル

mysql> DELETE FROM logs WHERE log_date=’2012-03-22′;
Query OK, 7144 rows affected (0.17 sec)

レコード件数が少ないのであまり有益なデータではないですが、大量にレコードをDELETEするよりもひとつのパーティションを削除する方がかなり速い結果となりました。


環境
MySQL 5.5.22
前の記事 «
次の記事 »