MySQL 管理しやすくなりました！5.5 のパーティショニング
mysql 5.5になり、パーティションの機能もバージョンアップしました。MySQL 5.1では以前、当ブログにて書きましたが、今回は、MySQL 5.5で書きたいと思います。
後ろの行で、MySQL 5.1 と 5.5の比較（のようなもの）を書きましたのでご確認ください。
では、さっそく検証開始！
・パーティショニングが対応になっているか確認する [sql] mysql> show variables like '%partition%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.05 sec) [/sql]
・テスト用スキーマに移動
[sql]
mysql> use develop
Database changed
[/sql]
・テスト用のテーブルを作成する
[sql]
drop table tpat01;
CREATE TABLE tpat01 (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
wdate date NOT NULL COMMENT '日時',
age int(11) NOT NULL DEFAULT '0' COMMENT '年齢',
created datetime DEFAULT NULL COMMENT '作成時間',
PRIMARY KEY (id,wdate)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
[/sql]
・パーティションテーブルに変更
wdateをパーティショニングします
[sql]
alter table tpat01
PARTITION BY RANGE columns(wdate) (
PARTITION p20110101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p20110201 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
PARTITION p20110301 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB,
PARTITION p20110401 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
PARTITION p20110501 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
PARTITION p20110601 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB,
PARTITION p20110701 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
PARTITION p20110801 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION p20110901 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION p20111001 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p20111101 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p20111201 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN MAXVALUE
);
[/sql]
・生成された結果を確認する
mysql> show create table tpat01;
[sql]
CREATE TABLE tpat01 (
id bigint(20) unsigned NOT NULL,
wdate date NOT NULL COMMENT '日時',
age int(11) NOT NULL DEFAULT '0' COMMENT '年齢',
created datetime DEFAULT NULL COMMENT '作成時間',
PRIMARY KEY (id,wdate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/!50500 PARTITION BY RANGE COLUMNS(wdate)
(PARTITION p20110101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p20110201 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
PARTITION p20110301 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB,
PARTITION p20110401 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
PARTITION p20110501 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
PARTITION p20110601 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB,
PARTITION p20110701 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
PARTITION p20110801 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION p20110901 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION p20111001 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p20111101 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p20111201 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) /
[/sql]
・テストデータの作成
以下のSQLを実行し、ランダム年齢とランダムな日付を格納したデータを10000件ほど実行します。
作成したシェルはこんな感じです。まあはじめからsqlファイルを作ってそれを10000万回回せばいいんだけどね。。
[sql] insert into develop.tpat01 ( wdate, age, created ) select FLOOR( REVERSE( RAND() ) ) %(100 - 1 + 1) + 1 as age , STR_TO_DATE( concat( '2011-',FLOOR( REVERSE( RAND() ) ) %(12 - 1 + 1) + 1, '-', FLOOR( REVERSE( RAND() ) ) %(30 - 1 + 1) + 1 ), '%Y-%m-%d' ) as wdate, current_timestamp ; [/sql] ・shellを作成 上記のsqlをshellにしたのがこんな感じです [shell]
!/bin/bash
echo "#" echo "# MYSQL テストデータの作成 かなりべたべたなやり方 " echo "#"
共通変数
dbinfo
host=192.168.101.41 userid=mysqluser passwd=mypassword schema=develop
日付ファイル
_wdate=date +"%Y%m%d%H%M%S"
各種コンフィグファイルのバックアップ用のディレクトリを指定
_backupdir=/backups/${HOSTNAME}
ワーク用のディレクトリを指定（このディレクトリ配下に作成されたファイルは、削除されます
_workdir=/tmp
********************************************
_sqlfile=create_data.sql
初期処理
if [ ! -d $backupdir ]; then echo "create directory $backupdir" mkdir -p $_backupdir fi
if [ ! -d $workdir ]; then echo "create directory $workdir" mkdir -p $_workdir fi
テストデータ格納用のSQLを生成する
cat << EOF > ${workdir}/${sqlfile} insert into develop.tpat01 ( age, wdate, created ) select FLOOR( REVERSE( RAND() ) ) %(100 - 1 + 1) + 1 as age , STR_TO_DATE( concat( '2011-',FLOOR( REVERSE( RAND() ) ) %(12 - 1 + 1) + 1, '-', FLOOR( REVERSE( RAND() ) ) %(30 - 1 + 1) + 1 ), '%Y-%m-%d' ) as wdate, current_timestamp ; EOF
loop 処理
cnt=0 while [ $cnt -ne 10000 ] do echo "$cnt " mysql -u ${userid} -p${passwd} ${schema} < ${workdir}/${sqlfile} cnt=$*1 done
exit 0 [/shell]
・データが登録されましたらデータがどのように分布されたか以下のSQLで確認してみます。 下記出力結果の、TABLE_ROWSの部分がおおよその分布です。おおよそといったのが、ここの件数、うようよと動くから。。です。当てにならないっす。
[sql] mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='tpat01'; +--------------+------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+------------+----------------+----------------------------+------------+ | develop | tpat01 | p20110101 | 1 | 47 | | develop | tpat01 | p20110201 | 2 | 1072 | | develop | tpat01 | p20110301 | 3 | 685 | | develop | tpat01 | p20110401 | 4 | 687 | | develop | tpat01 | p20110501 | 5 | 484 | | develop | tpat01 | p20110601 | 6 | 1591 | | develop | tpat01 | p20110701 | 7 | 574 | | develop | tpat01 | p20110801 | 8 | 679 | | develop | tpat01 | p20110901 | 9 | 579 | | develop | tpat01 | p20111001 | 10 | 1328 | | develop | tpat01 | p20111101 | 11 | 544 | | develop | tpat01 | p20111201 | 12 | 675 | | develop | tpat01 | pover | 13 | 723 | +--------------+------------+----------------+----------------------------+------------+ [/sql]
以上が、レンジパーティショニングの作成方法です・・が本題からそずれてしまいました。ここで説明したかったのは、MySQL 5.1と5.5の違いです。 5.1と5.5で代わったことを、以下で書いていきたいと思います。
【変わった部分】 変わったというか改善したといったほうが適切な表現ですね。MySQL 5.5になって色々と便利になりました。ちょっと間違い探しみたいになってしまいますが、以下に記載していきます。
▼ alter table ・mysql 5.1 [sql] alter table tpat01 PARTITION BY RANGE (TO_DAYS(wdate)) ( PARTITION p20110101 VALUES LESS THAN (TO_DAYS('2011-01-01')) ENGINE = InnoDB, PARTITION p20110201 VALUES LESS THAN (TO_DAYS('2011-02-01')) ENGINE = InnoDB, PARTITION p20110301 VALUES LESS THAN (TO_DAYS('2011-03-01')) ENGINE = InnoDB, PARTITION p20110401 VALUES LESS THAN (TO_DAYS('2011-04-01')) ENGINE = InnoDB, PARTITION p20110501 VALUES LESS THAN (TO_DAYS('2011-05-01')) ENGINE = InnoDB, PARTITION p20110601 VALUES LESS THAN (TO_DAYS('2011-06-01')) ENGINE = InnoDB, PARTITION p20110701 VALUES LESS THAN (TO_DAYS('2011-07-01')) ENGINE = InnoDB, PARTITION p20110801 VALUES LESS THAN (TO_DAYS('2011-08-01')) ENGINE = InnoDB, PARTITION p20110901 VALUES LESS THAN (TO_DAYS('2011-09-01')) ENGINE = InnoDB, PARTITION p20111001 VALUES LESS THAN (TO_DAYS('2011-10-01')) ENGINE = InnoDB, PARTITION p20111101 VALUES LESS THAN (TO_DAYS('2011-11-01')) ENGINE = InnoDB, PARTITION p20111201 VALUES LESS THAN (TO_DAYS('2011-12-01')) ENGINE = InnoDB, PARTITION pover VALUES LESS THAN MAXVALUE ); [/sql]
・mysql 5.5 [sql] alter table tpat01 PARTITION BY RANGE columns(wdate) ( PARTITION p20110101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB, PARTITION p20110201 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB, PARTITION p20110301 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB, PARTITION p20110401 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB, PARTITION p20110501 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB, PARTITION p20110601 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB, PARTITION p20110701 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB, PARTITION p20110801 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB, PARTITION p20110901 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB, PARTITION p20111001 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB, PARTITION p20111101 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB, PARTITION p20111201 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB, PARTITION pover VALUES LESS THAN MAXVALUE ); [/sql]
TO_DAYS という指定は必要なくなりましたね。TO_DAYS自体は、dateに指定された日付を日数（年 0 からの通し日数）に変換して返す関数なのです。 そもそもなんでTO_DAYSを指定する必要があったのか？・・ですが単純にMySQL 5.1のレンジパーティショニングって数値しか扱えなかったから・・・なんです。 MySQL 5.5では、日付型も扱えるようになりました。「別に、TO_DAYS関数でできるのであればそのままでいいんじゃないの？」と思うかもしれません。じゃあ、次はshow create table した結果を比較してみます。
▼ show create table
・mysql 5.1
[sql]
CREATE TABLE tpat01 (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
wdate date NOT NULL COMMENT '日時',
age int(11) NOT NULL DEFAULT '0' COMMENT '年齢',
created datetime DEFAULT NULL COMMENT '作成時間',
PRIMARY KEY (id,wdate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/!50100 PARTITION BY RANGE (TO_DAYS(wdate))
(PARTITION p20110101 VALUES LESS THAN (734503) ENGINE = InnoDB,
PARTITION p20110201 VALUES LESS THAN (734534) ENGINE = InnoDB,
PARTITION p20110301 VALUES LESS THAN (734562) ENGINE = InnoDB,
PARTITION p20110401 VALUES LESS THAN (734593) ENGINE = InnoDB,
PARTITION p20110501 VALUES LESS THAN (734623) ENGINE = InnoDB,
PARTITION p20110601 VALUES LESS THAN (734654) ENGINE = InnoDB,
PARTITION p20110701 VALUES LESS THAN (734684) ENGINE = InnoDB,
PARTITION p20110801 VALUES LESS THAN (734715) ENGINE = InnoDB,
PARTITION p20110901 VALUES LESS THAN (734746) ENGINE = InnoDB,
PARTITION p20111001 VALUES LESS THAN (734776) ENGINE = InnoDB,
PARTITION p20111101 VALUES LESS THAN (734807) ENGINE = InnoDB,
PARTITION p20111201 VALUES LESS THAN (734837) ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN MAXVALUE ENGINE = InnoDB) /
[/sql]
・mysql 5.5
[sql]
CREATE TABLE tpat01 (
id bigint(20) unsigned NOT NULL,
wdate date NOT NULL COMMENT '日時',
age int(11) NOT NULL DEFAULT '0' COMMENT '年齢',
created datetime DEFAULT NULL COMMENT '作成時間',
PRIMARY KEY (id,wdate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/!50500 PARTITION BY RANGE COLUMNS(wdate)
(PARTITION p20110101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p20110201 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
PARTITION p20110301 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB,
PARTITION p20110401 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
PARTITION p20110501 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
PARTITION p20110601 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB,
PARTITION p20110701 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
PARTITION p20110801 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION p20110901 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION p20111001 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p20111101 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p20111201 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) /
[/sql]
mysql 5.1のほうは、もはや以前なにを設定したのかわからん。。。今回の検証では、MAXVALUEを設定したので、パーティションを追加するっていう作業が発生しないのですが、MAXVALUEを設定しない場合は、データ分布を見つつ追加するような運用が発生しますね。その際に、5.1の場合は、もはや以前何が設定されているのかがわからん状態になっております。逆に、mysql 5.5では、ちゃんと日付が設定されているのでわかり易いですよね？よね！？
*1: $cnt + 1
