MySQLからデータをサンプリングして取得する

何かしらのデータの傾向を調べたい時に、たとえばMySQLに保存されているデータをdumpして調べる、みたいな事を行います。
ただ、この方法は待機系サーバーであったり、データ件数が少ないうちはあまり気にせずに行なっても大丈夫ですが、本番系で大量のデータを持つテーブルからfull dumpなどを行うのは現実的では有りません。

ということで、対象テーブルから乱数を用いてサンプリングデータの抽出をしてみました。本当の意味ではメルセンヌ・ツイスターなどの精度が保証されている乱数器を用いるのがベターなのでしょうが、今回はその話は無しで。



MySQLには乱数を生成する”rand()”という関数があるので、こちらをwhere句に記述し、テーブルの各レコードに対して乱数を付与し、かつその値が一定の範囲に含まれるもののみ抽出対象とする、という方法で、手軽にサンプリングを行う事ができます。

たとえば約90,000件のレコードが存在する以下のテーブルに対して

mysql> select count(*) from hoge;
+----------+
| count(*) |
+----------+
|    88975 |
+----------+

以下のようなクエリーを発行すると、おおよそ全レコードの10%のデータがランダムに抽出されます。

mysql> select count(*) from hoge where rand() <= .1;
+----------+
| count(*) |
+----------+
|     8997 |
+----------+

内部的には、すべての対象レコードに対して"rand()"関数を実行し、当該乱数が0.1以下のデータを抽出、という動きになっていると思います。
全レコードを走査しながら、かつ内部関数を実行する、という、クエリー的には鬼のような挙動なので、どう考えても高トラフィックのサイトのサービス側で使用するクエリーとしては、使えません。

アドホックにデータ抽出する際でも、件数の多いテーブルに対して当該処理を行うと処理に膨大に時間がかかります。

そのため、パフォーマンス・チューニングとしては以下のような感じになると思います。

1. '*'指定ではなく、primary key もしくは index上のカラムを抽出対象にする
2. 1.で取得したデータと、元のテーブルのJOINでデータを抽出する
3. 1.で取得する件数を全件ではなく、一定の範囲に収める

すべてのデータを走査対象にするよりは、indexのみで完結するほうが走査コストが低くすむので、"where rand()" を使用する際の抽出条件はprimary keyや、index上のカラムのみにします。
たとえば、"id"というカラムがprimary keyなのであれば、以下のようにします。

mysql> select id from hoge where rand() <= .1;

explainしてみると、id指定の場合は"type=index"となり、'*'指定の場合は"type=ALL"となります。
（count句を使用した場合は例外で、primary keyが指定されているテーブルであればデータ件数はprimary keyの数を数えるだけで済むので、count(*)でも"type=index"になることがあります）


そのうえで、以下のようにJOINしてデータを取得してあげると、ナイーブにrand()を使う時よりも速くなります。

mysql> select * from hoge as a, (select id from hoge where rand() <= .1) as b where a.id = b.id;

また、subqueryの結果返されるidの数が少なければ少ないほどtemporary領域などのリソースを食いつぶす率が減りますし、JOINコストが減りますので、rand()をかけるデータの範囲を絞ってあげるのも１クエリーあたりのスループット向上と言う意味では効果があります。

mysql> select * from hoge as a, (select id from hoge where (id between 1 and 1000000) and rand() <= .1) as b where a.id = b.id; 
mysql> select * from hoge as a, (select id from hoge where (id between 1000001 and 2000000) and rand() <= .1) as b where a.id = b.id; 
・
・
・

のような形。
もう既に無作為抽出などは望むべくも無い状態になってますが…まあ、全データdumpでなく、一部のデータをMySQLにあまり負荷をかけずに取得する、という方法の紹介でした。