日報 #93 - MySQLでRAND()
畳とサーバは新しいほうがいいと昔から申しますが、
コピペとジャンクは使いようとも申しまして、
6,000円のジャンクPCを購入してオフィスに設置いたしました。
俺はロストテクノロジーだと思ってるAドライブがあったり、
UbuntuがマウントできないHDDでも、Vineなら出来るというN氏の予想が的中したり、
色々珍事がありましたが、今は安定して動いております。
ジャンクとはいえやっぱりPC買ってくるとベタベタと触りたくなるものですね。
でかいと逆に可愛くも見えてきますしね。
さて、本日はひたすら勉強と考えることばかりだったので実際手を動かしておりません。
gitのワークフローと、バージョン管理の手順など、管理方面の知識というものは難しいものですね。
かっちりしていなければならないところと、そうでないところを分けるための判断基準は組織ごとに違いますしね。
ということで、
今日は一瞬で終わらします。
MySQLでのRAND()の扱いは要注意です。
EXPLAINしてみれば分りますが、RAND()を使うと大体Extra項目に、
Using where; Using index; Using temporary; Using filesort
が出力されます。
あっかんですね。
先にサブクエリでセレクトしてからRANDしてみてもあんま変わりません。
だから事前にidなどの範囲を取得して、その範囲で目的の数だけ乱数を発生させてから、
where in句で引っ張るのがよいそうです。
http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/
ここが参考になります。
安全なことを言えば、大きなテーブルに対してRANDは使わない方がよいよ、ってことですね。
あとは各々うまいやり方でランダムにデータを取得するのがよいということでした。
はい、以上です。
あまりにも哀しいので、今回出たEXPLAINでのExtra項目について軽くおさらいしておきましょう。
Using where
インデックスによるフィルタリングができてなく、ポストフィルタリングされましたよ、という意味。
具体的に言えば、MySQLのフィルタリングには大きく2段階あります。
一つ目がストレージ領域でのフィルタリング。
これは単純にindexが上手に効いて、引っ張ってくる時点でもうwhereが出来てしまっている場合です。
最速ですね。
二つ目がMySQLサーバ側でのフィルタリング。
ストレージ領域から全件を取得して、そこから該当部をフィルタリングします。
場合によっては遅いです。
なので、Using whereがでてしまったら、インデックスを使えないか検討する必要があります。
Using index
カバリングインデックスを使ったぞ、ということ。
例えば
SELECT id FROM hoge ORDER BY RAND();
これならUsing indexが出ますが、
SELECT * FROM hoge ORDER BY RAND();
これだとUsing indexが出ない可能性があります。
この違いは、セレクト対象となるもの（前者で言えばid）が「完全に含まれているインデックス」が存在しているのか、
そうでないのか、ということです。
SELECT * をすると、インデックスに含まれないカラムがある可能性があるので、
大抵の場合、カバリングインデックスではなくなります。
カバリングインデックスだと、行にアクセスする必要がない（インデックスだけでクエリが完結する）ため、
ものっっそ速くなる可能性があります。
Using indexは歓迎すべきExtraです。
Using temporary
クエリ結果を「ソート」する時に、「メモリではなく」一時テーブルを使うぞ、ということ。
もちろんこれは最悪です。
回避方法は、とにかくソート対象がきちんとインデックスが張ってあれば、インデックスの順番にだせばいいだけなので早くなります。
地味な回避方法は、メモリに乗るようにソート対象を極限まで減らすということや、結合状態でのソートを回避すること。
とにかく、こいつは巨悪です。
Using filesort
インデックス順序を使わずに、外部ソートをつかったぞ、ということ。
よく誤解されがちだが、オンメモリだろうが、一時テーブルを使おうが、
ソートアルゴリズムが走ったらUsing filesortです。
Using filesort と Using temporaryのコンボはかなり悪い状態だが、
Using filesort だけならまだマシの可能性はある。
とはいえ、MySQLのソートにおいてカラムごとに「固定長」を使うので、
intとかであってもむちゃくちゃ容量食います。
だからオンメモリのことはあまりなく、大体Using temporaryが出力されます。
現場からは以上でした。
