ORDER BY RAND()を10000%速くする方法と考え方を紹介 #MariaDB

ORDER BY RAND()

ランダムに表示させる時に便利なソートですが、
とっっっても重たいので使うには工夫が必要です。

速くする方法と考え方を事例と共に紹介したい。

＜環境＞
・MariaDB：10.6.11
・PHP：8.2.1
・Ubuntu 22.04.1 LTS

実現したいこと

下記のランダム表示をしています。

・過去180日間の記事をランダム取得
・データ件数は約10万件、約40MB。

これを0.01秒くらいで処理したい。
そして、取得期間をもっと伸ばしたい。

対策前（標準的な書き方）

SELECT * FROM `table1` WHERE `hiduke` BETWEEN (NOW() - INTERVAL 180 DAY) AND NOW() ORDER BY RAND() limit 20

約0.474秒

さすがに遅い。
「SELECT * 」にしていますが、本当に全部必要なのでこうしています。
「NOW()」にしているのは、未来の日付も多いためです。

この秒数では使えないので改善が必要です。

対策１（サブクエリでURL別途取得）

SELECT * FROM `table1` AS tbl,(SELECT url FROM `table1` WHERE `hiduke` BETWEEN (NOW() - INTERVAL 180 DAY) AND NOW() ORDER BY RAND() LIMIT 20) AS tb2 WHERE tbl.url = tb2 .url LIMIT 20;

約0.084秒

サブクエリはカラム「url」のみ取得してのランダムソートなので処理は速い。
ここで得たURLを照合しています。
urlカラムはインデックスを貼っているので照合も速いです。

しかしまだ遅い。約0.1秒とかダメです。

対策２（日付をランダム取得）

ランダムな1時間を取得して、そこでORDER BY RAND()。
それを20回繰り返す。

PHPが絡むのでコードを乗せます。

<?php
$kueri2 = "";

for($i=0;$i<20;$i++){

//〇日前をランダムで
$niti = mt_rand(-180, -1);
$niti2 = $niti." day";
$niti3 = date('Y-m-d', strtotime($niti2, time()));

//0時～23時をランダムで
$jikan = mt_rand(0, 23);

//1時～5時は投稿が少なくランダム性が悪くなるのでまとめる
if($jikan > 0 && $jikan < 6){
$jikan = 1;
$jikan2 = 5;
}else{
$jikan2 = $jikan;
}

//1行ずつのUNIONを組み立て
$kueri2 .= " UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '".$niti3." ".$jikan.":00:00' AND '".$niti3." ".$jikan2.":59:59' ORDER BY RAND() LIMIT 1)";

}

//元のクエリ
$kueri = "SELECT * FROM (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '".$niti3." ".$jikan.":00:00' AND '".$niti3." ".$jikan2.":59:59' ORDER BY RAND() LIMIT 1) as a1";

//クエリ合体
$kuerimatome = $kueri.$kueri2;

?>

出来上がったクエリが下記です。

//こんなクエリになる
SELECT * FROM (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-03-03 0:00:00' AND '2023-03-03 0:59:59' ORDER BY RAND() LIMIT 1) as a1
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-10-07 1:00:00' AND '2022-10-07 5:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-01-27 17:00:00' AND '2023-01-27 17:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-12 7:00:00' AND '2022-09-12 7:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-02-28 9:00:00' AND '2023-02-28 9:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-10-11 14:00:00' AND '2022-10-11 14:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-01-23 9:00:00' AND '2023-01-23 9:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-24 8:00:00' AND '2022-09-24 8:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-09 10:00:00' AND '2022-09-09 10:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-22 11:00:00' AND '2022-09-22 11:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-13 10:00:00' AND '2022-09-13 10:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-12-10 19:00:00' AND '2022-12-10 19:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-11 1:00:00' AND '2022-09-11 5:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-10-08 22:00:00' AND '2022-10-08 22:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-11-14 23:00:00' AND '2022-11-14 23:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-09-27 12:00:00' AND '2022-09-27 12:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-11-29 23:00:00' AND '2022-11-29 23:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2022-10-07 13:00:00' AND '2022-10-07 13:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-01-28 0:00:00' AND '2023-01-28 0:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-02-20 23:00:00' AND '2023-02-20 23:59:59' ORDER BY RAND() LIMIT 1)
 UNION (SELECT * FROM `table1` WHERE `hiduke` BETWEEN '2023-03-03 0:00:00' AND '2023-03-03 0:59:59' ORDER BY RAND() LIMIT 1)

約0.004秒！

求めていたのはこの速さです。
完全ランダムとは言えないですが、目的は十分に達成しています。
日付（hiduke）はインデックスを貼っているので、1時間だけに絞ると爆速になる。
ORDER BY RAND()もデータが数十件なら速いです。

日付を活用できる場合はこれが一番良いと思います。
CPUもメモリもほとんど使わない。

まとめ

ORDER BY RAND()は1個1個取得するような考え方でやりたい。

まとめて20個となると、どうしても範囲が大きくなるので重たくなります。
爆速で取得できる1個を20回繰り返す、もしくは20個指定すると速い。

基本的なORDER BY RAND()を速くする策として、
「ID一覧表からランダムに抜き取ってそのIDを指定」というのがあるのですが、
これはその一覧表を読み取るのにメモリを多量に消費するのが難点。
今回の対策のようなSQLだけでやるとメモリの消費が少なくて良い。

日付じゃなくてもIDの数字の範囲でも実現可能です。
是非いろいろとORDER BY RAND()と戦ってみてください。