Villámgyors MySQL ORDER BY RAND() lekérdezés
Tags: mysql, performance
Published: 2023-10-03 16:17:24

Jelenleg dolgozom egy projekten ahol az adatbázis rendberakása is a feladatom része. Ez több lépéses, több dolgot is érint itt most a véletlenszerű rendezéssel kapcsolatban szeretnék mutatni egy alternatív megoldást.

Bekapcsoltam az adatbázis központi lassú SQL loggolását, így bukott ki, hogy van néhány `ORDER BY RAND()` SQL amik nem teljesítenek túl jól (azaz szarul teljesítenek 🤣).

## Kiinduló állapot

**4000 rekordból 9 véletlenszerű elem kiválasztása: ~16 másodperc**

Valami hasonló a kiinduló lekérdezés, lecsupaszítva (az `EXPLAIN ANALYZE` 4-6 másodpercig fut):
```sql
SELECT * FROM my_table ORDER BY RAND() LIMIT 9;
```

## Megoldás, részletek

Keresgéltem de nem találtam számomra megfelelő megoldást (túl bonyolultak - ideiglenes tábla - vagy "nem teljesen" random megoldásokkal találkoztam). Így elkezdtem a saját kútfőből összerakni:

1. Fogjuk a táblát amiből le akarunk kérdezni és kérdezzük le plusz értékként a `RAND()` értékét is: ```sql
SELECT id, RAND() AS random_order FROM my_table;
```
2. Ezt a lekérdezést csomagoljuk egy subselectbe, csináljuk meg rajta a rendezést és a szükséges LIMIT-et kérjük le: ```sql
SELECT rnd.id FROM (
        SELECT id, RAND() AS random_order FROM my_table	
) AS rnd ORDER BY rnd.random_order LIMIT 9;
```
3. Most a fentit tegyük egy újabb subselectbe és kapcsoljuk az eredeti táblához: ```sql
SELECT * FROM my_table AS myt
JOIN (
        SELECT rnd.id FROM (
            SELECT id, RAND() AS random_order FROM my_table	
        ) AS rnd ORDER BY rnd.random_order LIMIT 9
) AS random_list ON random_list.id = myt.id;
```

## Végeredmény

Egy picit hosszabb nem annyira triviális mint az `ORDER BY` után írni, hogy `RAND()` de a fő kérdés megéri? Szerintem egyértelműen igen:

Végeredmény ~400x sebesség növekedés:
**4000 rekordból 9 véletlenszerű elem kiválasztása: ~0.04 másodperc**

`EXPLAIN ANALYZE` (a sallangokat kitöröltem):
```sql
mysql> SELECT COUNT(id) FROM my_table;
+-----------+
| COUNT(id) |
+-----------+
|      4193 |
+-----------+
1 row in set (0.08 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM my_table ORDER BY RAND() LIMIT 9;
------------------------------------------------------------------
  -> Limit: 9 row(s)  (actual time=5233..5235 rows=9 loops=1)
    -> Sort row IDs: rand(), limit input to 9 row(s) per chunk  (actual time=5233..5235 rows=9 loops=1)
        -> Table scan on <temporary>  (cost=18633..18670 rows=2736) (actual time=3694..5232 rows=4193 loops=1)
            -> Temporary table  (cost=18633..18633 rows=2736) (actual time=3694..3694 rows=4193 loops=1)
                -> Table scan on my_table  (cost=18359 rows=2736) (actual time=0.0227..2653 rows=4193 loops=1)
------------------------------------------------------------------
1 row in set (5.24 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM my_table AS myt JOIN ( SELECT rnd.id FROM ( SELECT id, RAND() AS random_order FROM my_table ) AS rnd ORDER BY rnd.random_order LIMIT 9 ) AS random_list ON random_list.id = myt.id;
------------------------------------------------------------------
 -> Nested loop inner join  (cost=11.5 rows=0) (actual time=2.53..4.01 rows=9 loops=1)
    -> Table scan on random_list  (cost=2.5..2.5 rows=0) (actual time=2.2..2.21 rows=9 loops=1)
        -> Materialize  (cost=0..0 rows=0) (actual time=2.2..2.2 rows=9 loops=1)
            -> Limit: 9 row(s)  (actual time=2.19..2.19 rows=9 loops=1)
                -> Sort: random_order, limit input to 9 row(s) per chunk  (actual time=2.19..2.19 rows=9 loops=1)
                    -> Stream results  (cost=18931 rows=2736) (actual time=0.454..1.89 rows=4193 loops=1)
                        -> Covering index scan on my_table using disabled  (cost=18931 rows=2736) (actual time=0.448..1.36 rows=4193 loops=1)
    -> Single-row index lookup on myt using PRIMARY (id=random_list.id)  (cost=1.01 rows=1) (actual time=0.2..0.2 rows=1 loops=9)
------------------------------------------------------------------
1 row in set (0.00 sec)
```

Éles környezetben is teszteltem a fenti megoldást, valós táblákon:
```
Test table 1:    321 149 records, time:  0.2512 seconds
Test table 2:    865 706 records, time:  0.3070 seconds
Test table 3: 10 630 486 records, time: 52.1207 seconds
```
Ami ebből látható: a fenti megoldás 1 millió rekordig működőképes lehet (1 másodpercen belül fut). De azt is tegyük hozzá, hogy nem túl életszerű, hogy több százezer vagy milliós rekordokból kell random adatokat kiszednünk, hanem lesznek más szűrések előtte: aktív adatokra, felhasználóra, stb.

Tetszik, használnád, tudsz jobb megoldást? Írd meg! Köszönöm!

UPDATE: sikerült tovább egyszerűsítenem:
```sql
SELECT * FROM my_table AS myt
JOIN (
    SELECT id FROM my_table ORDER BY RAND() LIMIT 9
) AS random_list ON random_list.id = myt.id;
```