裏MySQLクエリー入門 HANDLER構文を使ったレコードの超高速ランダム取得

mysql

　以下のORDER BY RAND()を使ったランダム取得は便利なのですが、インデックスが使用されず、フルスキャンになる為パフォーマンスが思わしくありません。

SELECT * FROM user ORDER BY RAND() LIMIT 1

　いくつかのこの問題を解決するアプローチがありましたが、どれもイマイチでした。前々回のエントリーにてHANDLER構文をエントリーを書いていて何かに使えないかなぁと考えていたところ以下のSQLが思い浮かびました。
　いくつかのこの問題を解決するアプローチがあった中で、主キーの最大値までのランダム値を生成するというアイディアまでは同じです。以下のSQLはID欠けが生じた際に次のIDを取得するといったことを実現させています。ポイントは、生成するランダム値を0〜(最大値-1)とし取得するデータをランダム値より大きい最初の1行をフェッチするようにしているところです。

HANDLER user OPEN;
set @max_user_id:=(SELECT MAX(user_id)-1 FROM user);
HANDLER user READ `PRIMARY` > (round(RAND()*@max_user_id));
HANDLER user CLOSE
  • 同様に3行取得したい場合は
SELECT * FROM user ORDER BY RAND() LIMIT 3

↓

HANDLER user OPEN;
set @max_user_id:=(SELECT MAX(user_id)-1 FROM user);
HANDLER user READ `PRIMARY` > (round(RAND()*@max_user_id));
HANDLER user READ `PRIMARY` > (round(RAND()*@max_user_id));
HANDLER user READ `PRIMARY` > (round(RAND()*@max_user_id));
HANDLER user CLOSE