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 句に指定する件数が多いとオプティマイザはフルスキャンを選択するため大幅に遅い結果になりました。

パーマリンクの設定を変更したった

当サイトはこれまで日付と投稿名で個別ページURLを構成していました。
/%year%/%monthnum%/%day%/%postname%/

英語圏で使うなら非常にいい組み合わせでしょう。
URLに投稿日が含まれ、記事名もURLに付与されることによってSEO的な効果も多少なりともあります。

ただ、ここは日本です。
たいてい記事タイトルは日本語で、そのまま使っていると記事URLに日本語が含まれます。
広まりつつある日本語URLとはいえ、まだまだ弊害が多いのの事実。

というのもつい先日あるブックマークサイトから当サイトの個別ページがリンクされていたのですが、
そのリンクを辿って当サイトを開いてみると、エラーが表示されるではありませんか。
日本語URLが原因でした。

これをきっかけにパーマリンクpostidベースにすることを決意しました。
今はこうしてます。
/php-system/%post_id%

検索エンジンインデックスに悪影響を承知での変更です。
Permalink Redirectを導入して、旧URLにアクセスがあった場合301リダイレクトするようにはしてます。

ただこのPermalink Redirect、調べてみると相性的な問題や不具合も多い様子。
とりあえず入れておきますが。

このまま検索エンジンインデックスの再構築を待ちます。
最初からデフォルトのままにしておけばよかったなあ。

短縮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 にフラグを立てて更新します。同じトランザクション内で処理するのが安全だと思います。

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