【MySQL】phpMyAdminでインデックスが効いているか確認する方法

MySQLでインデックス

インデックス、というとMySQLの検索速度を上げるために設定するやつ、ですよね。

わたしの中のイメージでは普通にテーブルを作るとID順にしかならんでないけど、例えば「名前」というカラムをあいうえお順で並べておけば名前の検索が早くなるよね！というのがインデックスです。

最近laravelでいろいろ作っていて、インデックスの設定自体もすぐにできたんですが、laravelではあまりにも簡単にインデックスの設定ができてしまい、ホントにこんなんで大丈夫なの？と実際に作ったインデックスがちゃんと有効になっているのか気になったので、その確認方法を紹介します。

1. MySQLでインデックス
2. 今回の環境
3. ①まずデータをたくさん入力する
4. ②EXPLAINでインデックス確認！
5. 「type」と「key」を見よう！
6. おまけ
7. まとめ

今回の環境

• phpMyAdmin

今回はphpMyAdminを使ったインデックス有効確認の方法紹介となります。わたしはテーブルやインデックスはlaravelで作成してますが、別に他の方法でも大丈夫です。

①まずデータをたくさん入力する

まずはデータベースにたくさんのデータを保存していきます。

というのもMySQLはデータ数が少なかったりするとインデックスを使ってくれない事があるので、ある程度データがたまったデータベースにしておく必要があるのです。

ダミーデータをどんどん追加していくのですが、一つ一つ作っていては大変です。1つ2つデータを追加したなら後はphpMyAdminで簡単にデータを増やせます。

上記のクエリをphpMyAdminの「SQL」から実行するとテーブルにすでに入っているデータをそのまま全部コピーして追加してくれます。たった一行ですが実行するたびに倍々になるので、最初はテーブルに一つだけしかデータがなかったとしても、10回すれば1024個に、20回すれば1048576個（約100万）になります。

②EXPLAINでインデックス確認！

下準備が終わりました。さっそく作ったインデックスが有効になっているか確認していきましょう。

この確認もphpMyAdminから行っていきます。先ほどと同じ「SQL」の画面を開き、

こんな風に入力します。先頭の「EXPLAIN」が重要で、その後ろはインデックスが使われているか確認したいクエリをそのまま入力するだけ！「EXPLAIN」というのはMySQLのコマンドで、実行するクエリの情報が見れるコマンドです。

クエリにエラーがなくてちゃんと動作すると「id」「select_type」「table」「type」「possible_keys」「key」…などが一行にまとまった画面が表示されます。

「type」と「key」を見よう！

「EXPLAIN」して出てきた項目の中でインデックスが使われているか確認するには「type」と「key」に注目。

1. typeが…「ref」「const」ならOK！反対に「index」「ALL」ならNG！
2. keyが…作ったインデックス名になっていたらOK！

こんな風にまずは「type」の値をみて、次に「key」の値をみてみてくださいね。

特に注意しないといけないのが、「type」が「ALL」になっている場合。これはインデックスを使わずにテーブルの全データを一個ずつ検索している一番遅い検索方法になっています。

いつも検索が遅いなら「なんでALLなんだろ」原因を考える必要があるかもしれません。

ただ、冒頭でも書いたようにデータの数が少ないときとかはインデックスに頼るより全部見た方が早い！とMySQLが判断するのか（どうやって判断しているかはわかりませんが）、インデックスを作っていてもALLになってたりもしていたので、検索が遅くないのなら気にしなくてもいいかもです。

おまけ

特定カラムの値をランダムに設定する

例：「users」テーブルの「pref」カラムをランダムに設定する（数字バージョン）

UPDATE users SET pref = CEIL(RAND() * 47)

この一行だけで「usres」テーブル内の全レコードの「pref」カラムがランダムに設定されます。「47」という数字は都道府県の数が47だからです。1~47の数字が得られます。

例：「users」テーブルの「name」カラムをランダムに設定する（文字バージョン）

UPDATE users SET name = SUBSTRING(MD5(RAND()), 1, 10)

最後の数字の1は固定値で、最後の10が文字数になります。最大32文字なはず。（補足：MD5という32文字のランダムな文字列が生成できる関数にRANDで生成されるランダム数を与えています。そしてSUBSTRING関数で切り取り位置を指定しています。第二引数が切り取り開始位置、第二引数が切り取り終了位置。）

例：両方同時にする

UPDATE users SET 
pref = CEIL(RAND() * 47),
name = SUBSTRING(MD5(RAND()), 1, 10)

まとめ

今回はmyPhpAdmin、そしてEXPLAINを使ったインデックスが有効かどうか確認する方法を紹介しました。

データベースの事って目には見えない部分が多くて、設定してみたものの本当に有効になっているかわからない事が多いですよね。

そんな時こそ実際に試してみると少しずつデータベースが身近なものに感じられて開発がより楽しくなりますよ！