MySQLの複合主キーテーブル検索時の速度比較

MySQL の複合主キーテーブルのレコードを何件かピックアップして主キー検索したい機会があったので、いくつかのパターンの SQL で検索してパフォーマンス比較をしてみました。

実験の内容

  • それぞれ適当にピックアップした15レコードを1クエリで検索する
  • 実行時間は同じクエリを5回実行したときの最小と最大の時間を計測
  • レコードは適当に100,000件投入

実験環境

  • Ubuntu 13.10 (on Vagrant)
  • MySQL 5.5.37
  • InnoDB

使用したテーブル

CREATE TABLE `myTable` (
  `player_id` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `body` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`id`)
) ENGINE=InnoDB;

各検索方法の実行時間とインデックス使用状況

1. OR検索の場合

SQL例

SELECT * FROM `myTable`
WHERE  (`player_id` = 50 AND `id` = 4856)
  OR (`player_id` = 61 AND `id` = 4274);

※実験時は15個のORを繋げて検索しましたが長くなるのでここでは省略しています。

実行時間

1.2ms ~ 1.5ms

EXPLAIN結果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myTable range PRIMARY PRIMARY 8 NULL 15 Using where

2. UNION ALL検索の場合

SQL例

SELECT * FROM `myTable` WHERE (`player_id` = 50 AND `id` = 4856)
UNION ALL
SELECT * FROM `myTable` WHERE (`player_id` = 61 AND `id` = 4274);

実行時間

1.5ms ~ 2.0ms

EXPLAIN結果

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY myTable const PRIMARY PRIMARY 8 const,const 1
2 UNION myTable const PRIMARY PRIMARY 8 const,const 1
15 UNION myTable const PRIMARY PRIMARY 8 const,const 1
NULL UNION RESULT ALL NULL NULL NULL NULL NULL

3. WHERE IN の複合カラム指定による検索の場合

SQL例

SELECT * FROM `myTable`
WHERE (player_id, id) IN ((50, 4856),(61, 4274));

実行時間

40.9ms ~ 49.7ms

EXPLAIN結果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myTable ALL NULL NULL NULL NULL 100714 Using where

コメント

最も高速なのは普通に OR で繋げる検索でした。range スキャンになるんですね。次点で UNION ALL 検索。ちなみに ALL 無しの UNION に変えてもほぼ同じ速度でした。

そして最も遅かったのは WHERE IN 検索でした。IN 句に指定する件数が多いとオプティマイザはフルスキャンを選択するため大幅に遅い結果になりました。

Apacheのモジュールでコンテンツの帯域制限をかける

Apache 2.xでコンテンツの転送速度制限をかける方法のメモです。共用レンタルサーバや従量課金の AWS などを使っていてサイトの転送量を抑えたい場合など使えると思います。

mod_bw モジュール導入

リポジトリを指定し yum でインストールします。

yum --enablerepo=epel install mod_bw

設定ファイルのバックアップを取り、設定ファイルの設定を行います。

mv /etc/httpd/conf.d/mod_bw.conf /etc/httpd/conf.d/mod_bw.conf.orig
vi /etc/httpd/conf.d/mod_bw.conf

同じクライアントからのアクセスを2Mbps(256kbytes/s)に制限する場合の mod_bw.conf の内容です。BandWidth は転送可能な秒間バイト数です。

<IfDefine HAVE_BW>
  <IfModule !mod_bw.c>
    LoadModule bw_module    extramodules/mod_bw.so
  </IfModule>
</IfDefine>

<IfModule mod_bw.c>
  BandWidthModule On
  ForceBandWidthModule On
  BandWidth all 256000
</IfModule>

128bytes 以上の .flv と .avi ファイルに対して2Mbps(256kbytes/s)に制限する場合の例です。LoadModule は省略。

<IfModule mod_bw.c>
  BandWidthModule On
  ForceBandWidthModule On
  BandWidth all 256000
  LargeFileLimit .flv 128 256000
  LargeFileLimit .avi 128 256000
</IfModule>

設定が終了したらサーバを再起動します。

/etc/init.d/httpd restart

帯域制限がかかっていることを確認

ab コマンドなどでコンテンツの転送速度が制限なされていること、Apache のエラーログにエラーが出ないことを確認します。

ab -n 1 -c 1 http://example.com/hoge.flv

短縮URLなどのランダム文字列を作りたい

Youtube だったり bit.ly を見ていると URL の後ろに英数字の文字列がついていますよね。

色々方法はあると思いますが、そのままランダムな文字列をキーとしてデータベースに登録しても、テーブルに登録される情報が膨大になってくるとどうしても速度的な問題が発生してきます。

文字列をキーとして扱うのはデータベースにとって都合が悪いことが多いのです。

やはり高速なのは数値型の整数フィールドを主キーとして、その主キーを WHERE 句で指定して絞り込む方法です。

そこで、調べていていいなと思ったのは基数変換を使った方法。

例えば32進数や63進数、64進数などを使って、URL にはその N 進数の文字列を使ってアクセスします。

例: http://example.com/index.php?key=aY8rszM

データベースにはこのキー文字列を10進数に変換した値を主キーとして保存します。この主キーはデータベースのオートインクリメント機能を使って連番で割り振っていきます。

つまり、見た目はランダムな英数字の羅列ですが内部的には10進数の連番というわけです。Youtube の動画とかもランダムに英数字を割り当ててるように見えて実は auto_increment の整数らしいです。

PHP などでも N 進数と10進数の相互基数変換さえ出来てしまえば実現できるはず。

ちなみにこの方法で本当にランダムな順番で割り振るには、例えば主キーの管理テーブルを作る方法があります。

レコード ID と使用フラグを管理する使用表のテーブルを作って、あらかじめ大量のレコード数を格納しておきます。

key_master
record_id(int) used_flag(bool)
1 0
2 0
3 0
4 0
5 0

そして新たなレコードを挿入する度にこのテーブルから未使用の番号を一件ランダムで取り出し、それをキーとしてデータを挿入します。

以下はランダムに取り出すときの MySQL のサンプルクエリです。

SELECT `record_id` FROM `key_master`
WHERE `used_flag` = 0
ORDER BY rand() LIMIT 1;

この取り出した ID のレコードを挿入して、それを基数変換した文字列のページを生成します。

使用後は忘れずに used_flag にフラグを立てて更新します。同じトランザクション内で処理するのが安全だと思います。

他にもっといい方法ありそうですが。何かあったらまた更新します。