MySQLのrand()ランダム関数の負荷問題を考えてみる


MySQLで結構使う場面が多いランダム関数rand()ですが、間違った指定をするとデータベースに思わぬ負荷をかけることになります。
今回はMySQLの指定方法からどの程度の負荷が掛かるのかを簡単にまとめ、DB設計の見直しや理想のプログラムも参考程度に掲載してみます。

SPONSORED LINK

テーブルからランダムに取り出す時の負荷

もちろん少ないレコードならば大きな問題にはなりませんが、数十万行といった大規模データになってくると以下の負荷がかかって一瞬でサーバーダウンも夢ではなくなりますので注意が必要ですね。

無指定ランダム

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はダメ絶対。

今日も知識欲は止まらない。

関連トピック

PHPエラー対処方法「 Warning: Missing argument 1 for」

PHPにおけるPHP Warning: Missing argument 1 for... というエラーの原因と対処方法メモです。

文字コードが違う!SJISのページをUTF-8に変更したらクエリ付URLが死んだ話

ガラケーサイトからスマホやPC向けサイトへとサイトを拡大する場合、 SJISだった文字コードを「この際だからUTF-8に一括で変更して

MySQL intレコードにランダム整数を一括挿入してみた

MySQLのテーブル hoge の intカラム test に10までの整数をランダムに挿入してみました。 範囲指定や上限の変更も簡単

JavaScriptでUA判別してスマホとタブレットを振り分ける方法

JavaScriptを使用してUAでiPhoneやAndroidのスマホを判定したり、増加傾向にあるタブレットを判別する方法メモ。 U

PHPでスマホのUAからキャリア判別する唯一の方法

PHPを使ってスマートフォンのUserAgentからキャリアを判別するたった一つの方法メモです。 今まではキャリアごとの制約やサービス

PHPエラー対処方法「simplexml_load_file」&「simplexml_load_string」

PHP simplexml_load_fileを使用して「I/O warning : failed to load external e

Yahoo!デベロッパーネットワークAPI 画像検索

検索API提供終了 有料版を含む検索APIが終了してしまいました・・・。 APIを利用したウェブアプリケーションのリスクですね。

PHPのmb_send_mail関数やmail関数で文字化け問題を解消する

PHPのmb_send_mail関数やmail関数で文字化けに悩んでいませんか? 正直、色々な原因があるので特効薬のような対処法はあり

公開日:2010/05/26