MySQL

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

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

MySQLのrootユーザのパスワードを変更する

MySQLのrootのパスワードの変更方法をメモ。
ちなみにrootに限らず他のユーザのIDも同様に変更可能。

まずはコマンドラインなどでMySQLへログインします。
そのために、まずはＭｙＳＱＬがあるフォルダに移動します。パスが通っている場合は不要です。
私はxampp環境で制作していますのでC:\xampp\mysql\binのフォルダに移動してからログインしました。

cd /d C:\xampp\mysql\bin
mysql -u root -p

このコマンドを実行後、現在のパスワードを入力してログインします。

ログイン後は以下のコマンドを実行します。

mysql> SET PASSWORD FOR root@localhost=PASSWORD('sousaku');
Query OK, 0 rows affected

rootの部分は変更したいユーザを、localhostの部分はホスト名を、sousakuの部分は変更したいパスワードを書きます。
入力したら実行しＱｕｅｒｙ　OKとでれば変更完了。もう変わってます。

戻すときも同様の手順で可能です。
以外とさっくり出来てしまいます。

SQL_CALC_FOUND_ROWSを使うとインデックスが使われない？

MySQLテーブルのレコード数が多くなってきて検索が徐々に遅くなってきたのでチューニング。
EXPLAINでSELECT文を解析してみたところ、どうも一番肝心のレコードが多いテーブルでインデックスが使われていないみたい。

インデックスの作り方が悪いのか、テーブルの構造が悪いのか、はたまたSQL文が悪いのかいろいろ試しました。
結局どうしてもインデックスが使われずにusing filesortが表示されて泣きそうになりました。

途方に暮れていた時なにげなーくSQL_CALC_FOUND_ROWS文を外してクエリを実行したところ・・・

possible_keysとkeyにしっかりとインデックス名が表示されている・・・
そしてExtraにはUsing where表記のみに・・・！

まさか原因がそこだったとは自分の中では盲点でした。

ということでLIMITを解除した件数の取得は別でクエリを発行することを検討中。
調べてみたらレコード数が多い場合はSQL_CALC_FOUND_ROWSを使って一つのクエリで済まそうとせず、
件数取得のクエリを別で発行した方が早いこともあるみたいです。
ただ、件数取得用のSQLは出来る限りシンプルに、そして表示するフィールドは最小限(idのみなど)などしたほうがいいようです。

これがきっかけでいろいろとMySQLチューニングについては勉強できました。
収穫も多かったです。