MySQLで結構使う場面が多いランダム関数rand()ですが、間違った指定をするとデータベースに思わぬ負荷をかけることになります。
今回はMySQLの指定方法からどの程度の負荷が掛かるのかを簡単にまとめ、DB設計の見直しや理想のプログラムも参考程度に掲載してみます。
目次
テーブルからランダムに取り出す時の負荷
もちろん少ないレコードならば大きな問題にはなりませんが、数十万行といった大規模データになってくると以下の負荷がかかって一瞬でサーバーダウンも夢ではなくなりますので注意が必要ですね。
無指定ランダム
SELECT * FROM table ORDER BY RAND();この指定は最悪かもしれません。全件取得と同等の負荷が掛かります。MySQLなどのDBは効率化を考えた記述ができる部分には積極的に利用していかないと、サーバーが悲鳴をあげます。
取得件数指定ランダム
SELECT * FROM table ORDER BY RAND() limit 0, 10;はい。一見すると10件のみをランダムに取得しそうな記述ですが、こちらも全件取得並の負荷がかかります。
そもそも全件ランダムをしてからのlimitなので意味はほとんどありません。
カラム指定ランダム
select id,name,tel,age table order by rand() limit 0,10;はい。ただカラム指定しただけです。例のように10件程度の取得であれば、一気にパフォーマンスは上がります。
なぜかというと、「*」指定だとインデックスを利用できない可能性が残りますのでインデックスを利用しないことがほとんどです。
しかしインデックスを利用できるカラムを含めれば「Using index」となります。
MySQLのrand()に代わる理想プログラム
個別ランダムの理想プログラム
1．SELECT COUNT(*) AS cnt FROM quotes で全件数取得する。
2．1の数値からランダムな値（$number）を生成する。
3．「SELECT quote FROM quotes LIMIT $number, 1」といった指定でランダムに個別取得可能。
複数ランダムの理想プログラム
1．「SELECT MAX(id) AS maxid FROM quotes」で最大のidを取得する。
2．1の値を最大値としてランダムなidを複数配列（$idlist）で生成する。
3．「SELECT quote FROM quotes WHERE id IN ($idlist)」といった指定でランダムに複数取得可能。
このケースでは前提としてauto_incrementされたプライマリキーである必要があります。
連番でない複数ランダムの理想プログラム
1．「SELECT MAX(id) AS maxid FROM quotes」で最大のidを取得する。
2．1の値を最大値としてランダムなidを必要以上に複数配列（$idlist_over）で生成する。
3．「SELECT quote FROM quotes WHERE id IN ($idlist_over) LIMIT $x」といった指定で完全ランダムに複数取得可能。
「$x」は必要個数が入りますね。
例としてIDを5つ生成して3つ取得する場合は以下のような指定になりますね。
SELECT quote FROM quotes WHERE id IN ('abc','def','ghi','jkl','mno') LIMIT 3まとめ
MySQLに重要なデータが入り始めてきたらEXPLAINでのExtra項目をチェックしながら重くないかを検討しないと軽く死ねますね。
とっても手軽に利用できるような見た目のrand()さん。まさにウサギの皮を被ったオオカミですね。
大きなテーブルにはrandはダメ絶対。
のプレミアム以上がおすすめです。今日も知識欲は止まらない。
