Villámgyors MySQL ORDER BY RAND() lekérdezés

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):

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:

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:
```
SELECT id, RAND() AS random_order FROM my_table;
```

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:

SELECT rnd.id FROM (
    SELECT id, RAND() AS random_order FROM my_table 
) AS rnd ORDER BY rnd.random_order LIMIT 9;

Most a fentit tegyük egy újabb subselectbe és kapcsoljuk az eredeti táblához:

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):

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:

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;

Blog

Villámgyors MySQL ORDER BY RAND() lekérdezés

Simon Benjámin

Kiinduló állapot

Megoldás, részletek

Végeredmény

Join Our Newsletter. No Spam, Only the good stuff.

Related