【MySQL】ランダムにレコードを抽出する時の「偏り」はあるのか？

# はじめに

MySQLでテーブルからランダムにレコードを抽出する場合、以下のように`rand()`関数を使って実装することができます。

> SELECT * FROM {テーブル名} ORDER BY rand() LIMIT {抽出するレコード数};

ところが、[MySQLでランダムにレコードを取得する場合の手法](https://qiita.com/iri/items/b82795b6a3c0a8df62a5)を見ると、「LIMITで持ってくる関係上、複数取得する場合は組み合わせに偏りが生じる」と書かれていたので、どの程度偏るのかを調べてみました。

# 調査に使用したデータ

* [【MySQL】SQLで基本統計量を求める](https://qiita.com/nkojima/items/2c483d4ddbdb29439c87)に掲載している、`mountain`テーブルのデータを使用しました。
  * 八ヶ岳の標高データで、レコード数は23です。

# 調査方法

1. 上記の`mountain`テーブルの全23レコードから、ランダムに3件抽出します。
1. 抽出されたレコードの`id`列と等しい一時表のレコードに対して、`count`列をインクリメントします。
1. 1～2の操作を1000回繰り返した後、一時表に記録された各山の出現回数を表示します。

```sql:調査用SQL
-- 各山が出現した回数を格納する一時表を作る。
CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 (
  id INT,
  name varchar(50),
  count INT DEFAULT 0,
  PRIMARY KEY (id)
);

-- 一時表にイニシャルデータとして山リストを投入する。
INSERT INTO tmp1 (id, name) SELECT id, name FROM mountain;


-- ランダムに3件抽出するストアドプロシージャ。
DELIMITER //
CREATE PROCEDURE rand_test()
BEGIN
  DECLARE currentID INT DEFAULT 0;       -- 現在の山ID
  -- カーソルがデータセットの最後に達したかを判定する変数。
  DECLARE done INT DEFAULT FALSE;
  -- 山をランダムに3件取得するカーソルを定義する。
  DECLARE cur1 CURSOR FOR
    SELECT id FROM mountain ORDER BY rand() LIMIT 3;
  -- カーソルが最後に達した時の動作制御。
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  -- ランダムに3つの山を抽出して、各山の出現した回数を一時表に入れる。
  OPEN cur1;
  
  -- 一時表に出現した山の回数を記録する。
  for_loop: LOOP
    FETCH cur1 INTO currentID;
    -- カーソルの読み出しが最後に到達していたらループを抜ける。
    IF done THEN
      LEAVE for_loop;
    END IF;
    -- 一時表の該当する山IDの出現回数を＋1する。
    UPDATE tmp1 SET count = count + 1 WHERE id = currentID;
  END LOOP;
  
  CLOSE cur1;
END;
//

-- 「rand_test」を指定回数繰り返すためのストアドプロシージャ。
CREATE PROCEDURE rand_repeat(IN repeat_cnt INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  
  WHILE repeat_cnt > i DO
    CALL rand_test();
    SET i = i + 1;
  END WHILE;
END;
//

DELIMITER ;  -- 区切り文字をセミコロンに戻す。

CALL rand_repeat(1000);

-- 各山の抽出された回数を一時表から取り出す。
SELECT * FROM tmp1;
```

# 結果

* 23レコードから3レコードを選ぶ処理を1000回繰り返すので、各山が抽出される回数は理論上は130.43回となります。
* 実際に抽出された回数は102～159回なので、バラツキはあるものの大きく偏っているとは言えないと思います。
  * 抽出元のテーブルのレコード数が多くなった場合は、この結果が変わって来るかもしれませんが...

```text:実行結果
mysql> SELECT * FROM tmp1;
+----+--------------+-------+
| id | name         | count |
+----+--------------+-------+
|  1 | 編笠山       |   118 |
|  2 | 西岳         |   132 |
|  3 | 三ッ頭       |   128 |
|  4 | 権現岳       |   139 |
|  5 | 赤岳         |   140 |
|  6 | 中岳         |   138 |
|  7 | 阿弥陀岳     |   137 |
|  8 | 横岳         |   125 |
|  9 | 硫黄岳       |   124 |
| 10 | 赤岩の頭     |   159 |
| 11 | 峰の松目     |   135 |
| 12 | 箕冠山       |   148 |
| 13 | 根石岳       |   149 |
| 14 | 天狗岳       |   138 |
| 15 | 中山         |   102 |
| 16 | 丸山         |   120 |
| 17 | 茶臼山       |   125 |
| 18 | 縞枯山       |   115 |
| 19 | 北横岳       |   123 |
| 20 | 大岳         |   134 |
| 21 | 双子山       |   105 |
| 22 | 蓼科山       |   128 |
| 23 | 八子ヶ峰     |   138 |
+----+--------------+-------+
```


# 調査後の処理

* 以下のSQLで不要となった一時表とストアドプロシージャを削除しました。
  * `TEMPORARY`キーワードを付けると一時表のみが削除対象となるため、誤操作を防ぐためにも付けておいた方が安心ですね。

```sql:後処理用のSQL
-- 一時表を削除する。
DROP TEMPORARY TABLE tmp1;

-- ストアドプロシージャを削除する。
DROP PROCEDURE rand_test;
DROP PROCEDURE rand_repeat;
```


# 参考URL

* [MySQLでランダムにレコードを取得する場合の手法](https://qiita.com/iri/items/b82795b6a3c0a8df62a5)
* [【MySQL】SQLで基本統計量を求める](https://qiita.com/nkojima/items/2c483d4ddbdb29439c87)