先日に続き、mysql-casualから。 しかも今度は自分からの質問。 yoku0825さんが答えて下さったので、自分のほうで検証して確認してみようと。 その結果をここにアップしました。
１．ユニークキーって？
UNIQUE 制約を使用して、主キーに関係しない特定の列に重複した値が入力されないようにできます。UNIQUE 制約も PRIMARY KEY 制約も一意性を設定しますが、主キーではない列または列セットに一意性を設定する場合は、PRIMARY KEY 制約ではなく UNIQUE 制約を使用します。 UNIQUE 制約は 1 つのテーブルに複数定義できますが、PRIMARY KEY 制約は 1 つのテーブルに 1 つしか定義できません。 また、PRIMARY KEY 制約とは異なり、UNIQUE 制約は NULL 値を許容できます。ただし、UNIQUE 制約が適用される他の値と同様に、NULL 値も 1 列に 1 つしか使用できません。 UNIQUE 制約は FOREIGN KEY 制約から参照することもできます。
by TechNet - Microsoft.aspx)
ユニーク制約は上でも書いている通り、特定のカラム上のデータが一意になるようにする制約です。 この制約についてはMySQLでは外部参照制約同様、インデックスと対になっています。 但し外部キーと違い、既にインデックスが作られていたとしてもユニーク制約を追加すると自動的にユニークキーができてしまいます。
２．パフォーマンスはどうなのか？
ユニークキーもインデックスが作られるので、その性能が気になりました。 データベース上にユニークであるべきカラムに通常のインデックスが張られている場合とちゃんとユニークキーを張った場合とで差はあるのか？
実行計画を見ると、UKだと
通常のインデックスだと
色も違うし、どうなんだろう？と。
まあ、↑これは何も調べない状態で思ったことで、MySQLの大きなテーブルでのパフォーマンスを改善する10の方法 に、
- 不要なインデックスを消す。チェンジバッファ機能を無効にしてしまうUNIQUEキーに特に注意しよう。制約を使う必要がない場合はUNIQUEキーを使わず、通常のINDEXを使うこと。
という1行があったので、逆に「ユニークキーはできるだけ避けるべきか…」という気に検証前からなってました。
３．検証
3-1．検証環境
- MySQL8.0.13
- ディスクはHDD
- パラメータはほぼデフォルト値のままですが、メモリ周りだけ増えています。
- (メモリが1GBの環境のため、自動でinnodb_buffer_pool_size＝805306368が割り当たってる(※))
※どうでもいいけど、MySQL8って優秀だよなぁ。
3-2．テーブル
以下の３つのテーブルを用意した。(※)
| テーブル名 | 主キー用カラム | 検証用カラム |
|---|---|---|
| t1_no_key | 自動採番 | キー無し |
| t2_unique_key | 自動採番 | キーあり(ユニークキー) |
| t3_sec_key | 自動採番 | キーあり(Not ユニークキー) |
(※DDLは後述参照)
3-3．検証方法
任意のデータ(ランダムな文字列)を任意の数作成し、各テーブルに対し追加した場合の時間と、各テーブルを検証用カラムで参照した場合のMySQL内部APIの呼び出し回数を確認してみる。
3-4．検証結果
①データ登録時間
| テーブル名 | 10万件(sec) | 100万件(sec) | 300万件(sec) |
|---|---|---|---|
| t1_no_key | 4.134 | 24.469 | 123.262 |
| t2_unique_key | 7.850 | 118.747 | 607.921 |
| t3_sec_key | 7.933 | 114.129 | 557.985 |
10万くらいならその差は小さかったのに100万件を超えると、インデックスが張られている状態でのインサートの時間が約5倍になった。 これがよく言われる『データコピーするならインデックスは後で張ろう』というやつですね。 試しに1000万件も行ったが、5倍どころじゃなかったのでおとなしく(？)Ctrl + Cを押した。 ちなみに、チェンジバッファ機能が無効になるということで、INSERT時に結構影響を受けるのかな？と思ったが、そこまで神経質になる必要もないかな、というのが個人的な感想。
②データ参照時のAPI呼び出し回数
参照時のSQLは以下のような感じでvalに対し一意な値を入れている。
select * from t1_no_key where val = 'b7390a6b-ef09-4cdc-aec0-9b1abf5f0810'; select * from t2_unique_key where val = 'b7390a6b-ef09-4cdc-aec0-9b1abf5f0810'; select * from t3_sec_key where val = 'b7390a6b-ef09-4cdc-aec0-9b1abf5f0810';
そして、それぞれのクエリ実行後に
SHOW SESSION STATUS LIKE 'handler%';
を叩くことで、各APIの呼び出し回数を確認することができる。
(各SELECT文の前にflush status;でリセットすることを忘れずに)
100分の1秒までしか測れない(丸められてる)のでアレだが、やはりフルテーブルスキャンはダメということが良くわかるｗ
②-1．10万件の場合
| Variable_name | t1_no_key | t2_unique_key | t3_sec_key |
|---|---|---|---|
| Handler_commit | 1 | 1 | 1 |
| Handler_delete | 0 | 0 | 0 |
| Handler_discover | 0 | 0 | 0 |
| Handler_external_lock | 2 | 2 | 2 |
| Handler_mrr_init | 0 | 0 | 0 |
| Handler_prepare | 0 | 0 | 0 |
| Handler_read_first | 1 | 0 | 0 |
| Handler_read_key | 1 | 1 | 1 |
| Handler_read_last | 0 | 0 | 0 |
| Handler_read_next | 0 | 0 | 1 |
| Handler_read_prev | 0 | 0 | 0 |
| Handler_read_rnd | 0 | 0 | 0 |
| Handler_read_rnd_next | 10001 | 0 | 0 |
| Handler_rollback | 0 | 0 | 0 |
| Handler_savepoint | 0 | 0 | 0 |
| Handler_savepoint_rollback | 0 | 0 | 0 |
| Handler_update | 0 | 0 | 0 |
| Handler_write | 0 | 0 | 0 |
②-2．100万件の場合
| Variable_name | t1_no_key | t2_unique_key | t3_sec_key |
|---|---|---|---|
| Handler_commit | 1 | 1 | 1 |
| Handler_delete | 0 | 0 | 0 |
| Handler_discover | 0 | 0 | 0 |
| Handler_external_lock | 2 | 2 | 2 |
| Handler_mrr_init | 0 | 0 | 0 |
| Handler_prepare | 0 | 0 | 0 |
| Handler_read_first | 1 | 0 | 0 |
| Handler_read_key | 1 | 1 | 1 |
| Handler_read_last | 0 | 0 | 0 |
| Handler_read_next | 0 | 0 | 1 |
| Handler_read_prev | 0 | 0 | 0 |
| Handler_read_rnd | 0 | 0 | 0 |
| Handler_read_rnd_next | 1000001 | 0 | 0 |
| Handler_rollback | 0 | 0 | 0 |
| Handler_savepoint | 0 | 0 | 0 |
| Handler_savepoint_rollback | 0 | 0 | 0 |
| Handler_update | 0 | 0 | 0 |
| Handler_write | 0 | 0 | 0 |
②-3．300万件の場合
| Variable_name | t1_no_key | t2_unique_key | t3_sec_key |
|---|---|---|---|
| Handler_commit | 1 | 1 | 1 |
| Handler_delete | 0 | 0 | 0 |
| Handler_discover | 0 | 0 | 0 |
| Handler_external_lock | 2 | 2 | 2 |
| Handler_mrr_init | 0 | 0 | 0 |
| Handler_prepare | 0 | 0 | 0 |
| Handler_read_first | 1 | 0 | 0 |
| Handler_read_key | 1 | 1 | 1 |
| Handler_read_last | 0 | 0 | 0 |
| Handler_read_next | 0 | 0 | 1 |
| Handler_read_prev | 0 | 0 | 0 |
| Handler_read_rnd | 0 | 0 | 0 |
| Handler_read_rnd_next | 3000001 | 0 | 0 |
| Handler_rollback | 0 | 0 | 0 |
| Handler_savepoint | 0 | 0 | 0 |
| Handler_savepoint_rollback | 0 | 0 | 0 |
| Handler_update | 0 | 0 | 0 |
| Handler_write | 0 | 0 | 0 |
Handler_read_rnd_nextについて
全ての値については公式(MySQL5.6日本語)または公式(MySQL8.0英語) で見てもらうとして、ここでは数値が高く変化のあったHandler_read_rnd_nextについてのみ書いておくと、
データファイル内で次の行の読み取りリクエスト数。多くのテーブルスキャンを実行すると、この値は高くなります。一般的に、これはテーブルが正しくインデックス付けされていないか、作成したインデックスを利用するようにクエリーが記述されていないことを示します。
by 公式(MySQL5.6日本語)より
要するに、 テーブルに入ったデータ全部読んでしまってる ということ。 EXPLAINを見て、かつHandlerチェックまでやればVery Very Goodなんでしょうね。(よし、今後はやろう！)
ただ、今回の確認事項であるUKと通常のインデックスではHandler_read_nextが一回発生しているくらいの差しか無いので、速度面の効果はぶっちゃけ変わらないと言っても過言じゃないだろう。
これから比べればINSERTの300万件での10秒のほうが大きいとも言えなくはない。
③データ参照時間
| テーブル名 | 10万件(sec) | 100万件(sec) | 300万件(sec) |
|---|---|---|---|
| t1_no_key | 0.02 | 0.22 | 0.62 |
| t2_unique_key | 0.00 | 0.00 | 0.00 |
| t3_sec_key | 0.00 | 0.00 | 0.00 |
Handler_read_nextの呼び出し回数から推して知るべし！ フルテーブルスキャン、ダメ！
まとめ
今後の方針としては
- ユニーク制約を使いたい のであれば、ユニークキーを張る
- ユニークになると分かっていても、特段その要件が無いのであれば、ユニーク制約は張らない
といったところか。
参考
テーブルはnumをPKにしました。 valについてはvarchar(36)です。
SQL(DDL)
drop table if exists t1_no_key; CREATE TABLE `t1_no_key` ( `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(36) DEFAULT NULL, PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; drop table if exists t2_unique_key; CREATE TABLE `t2_unique_key` ( `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(36) DEFAULT NULL, PRIMARY KEY (`num`), UNIQUE KEY `val` (`val`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; drop table if exists t3_sec_key; CREATE TABLE `t3_sec_key` ( `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `val` varchar(36) DEFAULT NULL, PRIMARY KEY (`num`), KEY `val` (`val`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
実行ツール
せっかくyoku0825さんに30万件くらいダミーを作るワンライナー
$ perl -ML -e 'for (my $n= 1; $n <= 300000; $n++) { printf("%d\t%s\n", $n, Digest::MD5::md5_hex($n)) }' > /tmp/md5
とそれを詰め込むワンライナー
$ perl -MDBI -M"Time::HiRes qw{gettimeofday}" -E 'my $conn= DBI->connect("DBI:mysql:database=xxxx;host=hostname;port=xxxx", "user", "password"); for (my $n= 1; $n <= 10; $n++) { foreach (qw{t1_no_key t2_unique_key t3_sec_key}) { $conn->do("TRUNCATE $"); my $start= gettimeofday(); $conn->do(qq{LOAD DATA INFILE "/tmp/md5" INTO TABLE $}); my $end= gettimeofday(); printf("%s%d\t%4.2f\n", $, $n, $end - $start) } }'
をもらったのに、自分の環境のperlがしょぼくて、Songmuさんのp5-Lを使える環境になかなかならなかったので、pythonとshellで代替しました。 (上のコマンドで出来る人はそれでやったほうが楽なはず)
make_uuid.py
# coding: utf-8
import uuid
for var in range(0, 100000): # ここを適当に数字いじって入れる件数変える
u4 = str(uuid.uuid4())
print u4.encode('utf-8')
ファイル作成
python make_uuid.py>${WORK_DIR}/uuid.lst
データ投入
# t1_no_key用 mysql -u (user_name) -p (password) -h (hostname) -P (port_number) (dbname) -e "LOAD DATA LOCAL INFILE 'uuid.lst' INTO TABLE t1_no_key (@val) SET val=@val" # t2_unique_key用 mysql -u (user_name) -p (password) -h (hostname) -P (port_number) (dbname) -e "LOAD DATA LOCAL INFILE 'uuid.lst' INTO TABLE t1_no_key (@val) SET val=@val" # t3_sec_key用 mysql -u (user_name) -p (password) -h (hostname) -P (port_number) (dbname) -e "LOAD DATA LOCAL INFILE 'uuid.lst' INTO TABLE t1_no_key (@val) SET val=@val"
謝辞
yoku0825さん、いつもありがとうございます。 今回もモヤモヤが晴れて、いい週末を過ごせます。
