MySQL RAND() – jak pobrać losowe wiersze

logo-mysql

Pisząc różnego rodzaju aplikacje zdarza się, że trzeba wprowadzić pewną losowość. Jeśli aplikacja jest oparta na bazie danych, taką losowość najłatwiej wprowadzić pobierając losowe dane. Przerabiając ostatnio stary system, oparty jeszcze o pliki tekstowe postanowiłem przenieść go na bazę danych MySQL. Ponieważ duża część systemu z założenia ma być losowa, zatem koniecznym było napisanie dobrej funkcji wybierającej losowe dane z bazy danych. Wydaje się to banalnie proste, jednak nie tak bardzo.

Najczęstsze rozwiązanie

Posłużę się prawdziwym przykładem, nad którym pracowałem. Mamy tabelę keys, która, dla uproszczenia zawiera 2 kolumny: id | key. Chcemy pobrać jeden losowy rekord. Pierwsza myśl przy próbie napisania takiego zapytania to:

SELECT `id`,`key` FROM `keys`ORDER BY RAND() LIMIT 1;

Szukając rozwiązania w necie najczęściej natkniemy się właśnie na takie rozwiązanie. Nawet przy niewielkiej ilości rekordów nie nazwałbym tego rozwiązania akceptowalnym. Oto co się dzieje przy niewielkiej ilości rekordów:

mysql> SELECT COUNT(`id`) as ILOSC FROM `keys`;
+--------+
| ILOSC  |
+--------+
| 113528 |
+--------+
1 row in set (0.09 sec)
 
mysql> SELECT `id`,`key` FROM `keys` ORDER BY RAND() LIMIT 1;
+--------+-------+
| id     | key   |
+--------+-------+
| 143941 | yahoo
+--------+-------+
1 row in set (0.61 sec)
 
mysql> DESCRIBE SELECT `id`,`key` FROM `keys` ORDER BY RAND() LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | keys  | ALL  | NULL          | NULL | NULL    | NULL | 113739 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.04 sec)

Więcej niż pół sekundy! Ledwo ponad 100 000 rekordów, a zapytanie trwa 0,6 sekundy. Takie rozwiązanie w żadnym wypadku nie może zostać.

Inne rozwiązania

Aby nie wymyślać koła na nowo przeszukałem czeluście Wujka G. Okazało się, że powyższe rozwiązanie jest najczęściej występującym, niemalże jedynym. Kolejne rozwiązania opierają się już na wykorzystaniu PHP i pobieraniu rekordów poprzez zastosowanie kilku zapytań. Przykładowe rozwiązanie 1:

$range_result = mysql_query(" SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `table` ");
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query(" SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 ");

Pobranie tylko jednego losowego rekordu i aż 2 zapytania? Wydaje się, że powinno dać się zrobić to sprawniej. Okazuje się, że autor powyższego kodu pokusił się o jego optymalizację, a nawet użycie pojedynczego zapytania sql.

SELECT `id`,`key` FROM `keys` WHERE id >= (SELECT FLOOR( MAX(`id`) * RAND()) FROM `keys` ) ORDER BY `id` LIMIT 1;

Zapytanie ma za zadanie pobrać rekord o id większym niż zaokrąglony w dół do liczby całkowitej wynik mnożenia maksymalnego id z wylosowaną liczbą z przedziału <0,1>. Niby ma sens, ale co z „optymalnością” zapytania? Gorzej niż z pierwszym. Pomimo, że autor chwali zapytanie, że zajmuje 16% czasu pierwszego, mnie nie satysfakcjonuje. Wystarczy spojrzeć na wyniki działania:

mysql> SELECT `id`,`key` FROM `keys` WHERE id >= (SELECT FLOOR( MAX(`id`) * RAND()) FROM `keys` ) ORDER BY `id` LIMIT 1;
+-----+-------------+
| id  | key         |
+-----+-------------+
| 401 | alfa romeo
+-----+-------------+
1 row in set (12.64 sec)
 
mysql> DESCRIBE SELECT `id`,`key` FROM `keys` WHERE id >= (SELECT FLOOR( MAX(`id`) * RAND()) FROM `keys` ) ORDER BY `id` LIMIT 1;
+----+----------------------+-------+-------+---------------+------------------+---------+------+--------+-------------+
| id | select_type          | table | type  | possible_keys | key              | key_len | ref  | rows   | Extra       |
+----+----------------------+-------+-------+---------------+------------------+---------+------+--------+-------------+
|  1 | PRIMARY              | keys  | index | NULL          | PRIMARY          | 4       | NULL |      1 | Using where |
|  2 | UNCACHEABLE SUBQUERY | keys  | index | NULL          | UniqueSubdomains | 765     | NULL | 113715 | Using index |
+----+----------------------+-------+-------+---------------+------------------+---------+------+--------+-------------+
2 rows in set (0.04 sec)

Satysfakcjonujące rozwiązanie

Powyższe rozwiązanie po małej modyfikacji znacznie przyśpieszyło, na testowanej maszynie dało nie taki zły wynik (select z podaniem id wykonywany jest w 0.05s).

mysql> SELECT `id`,`key` FROM `keys` WHERE `id`=(SELECT FLOOR(RAND()*(SELECT MAX(`id`) FROM `keys`)));
+-------+-----+
| id    | key |
+-------+-----+
| 11737 | php 
+-------+-----+
1 row in set (0.10 sec)
 
mysql> DESCRIBE SELECT `id`,`key` FROM `keys` WHERE `id`=(SELECT FLOOR(RAND()*(SELECT MAX(`id`) FROM `keys`)));
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
|  1 | PRIMARY     | keys  | ALL  | NULL          | NULL | NULL    | NULL | 113785 | Using where                  |
|  3 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL |   NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
2 rows in set, 1 warning (0.05 sec)

Niestety jak widzimy zapytanie przegląda nam wszystkie rekordy. Przy ilości ciut powyżej 100 000 nie jest to jeszcze straszne (wynik akceptowalny), co jednak jeśli ilość rekordów przekroczy 1 mln? Tak czy inaczej przeglądanie całej tabeli w celu pobrania jednego rekordu nie należy do sensownych rozwiązań.

Kolejne podejście zmienia filozofię. Zamiast wykonywać porównanie możemy użyć JOIN, który nie będzie sprawdzał każdego rekordu z tabeli celem porównania go z wylosowanym id (tak, jak to się dzieje w przypadku zastosowania WHERE). Po modyfikacji otrzymujemy:

SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`);
mysql> SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`);
+--------+-------+
| id     | key   |
+--------+-------+
| 116895 | mysql
+--------+-------+
1 row in set (0.05 sec)
 
mysql> DESCRIBE SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`);
+----+-------------+------------+--------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra                        |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 |                              |
|  1 | PRIMARY     | keys       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                              |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | No tables used               |
|  3 | SUBQUERY    | NULL       | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+------------------------------+
4 rows in set (0.04 sec)

Otrzymaliśmy zapytanie, w którym SELECT tworzy stałą tempową tablicę, z której JOIN wybiera już tylko pojedynczy wiersz. Takie rozwiązanie jest już całkiem sympatyczne;) Jednak nie jest tak do końca. Problemy pojawiają się w przypadku, gdy id nie są po kolei oraz gdy chcemy pobrać więcej niż jeden losowy rekord. Z pierwszym problemem możemy sobie poradzić relatywnie łatwo. Wystarczy utworzyć tabelę tymczasową i tam pod id generowane po kolei podstawić id z bazy. Następnie pobieranie załatwimy przez JOIN tabeli tymczasowej z główną. Co jednak, gdy zechcemy pobrać 5 losowych rekordów?

Pobieranie kilku losowych rekordów

System, który przerabiałem wymagał zastosowania losowego pobierania danych przynajmniej w trzech miejscach na jedno załadowanie strony. Do tego ilości losowych rekordów są różne: 2, 10, 20. Zatem nie mogłem skorzystać z powyższego rozwiązania, gdyż musiałbym wykonać 32 zapytania na jedno wyświetlenie strony. Serwer szybko odmówiłby posłuszeństwa.

Możliwości dużo nie mamy:

  1. Wykonać zapytanie tyle razy ile losowych rekordów potrzebujemy
  2. Skorzystać z UNION SELECT 2
  3. Wykorzystać procedury składowane

Pierwszą opcję odrzucam od razu. Wymagałoby to dodatkowej pętli po stronie skryptu, wygenerowałoby niewyobrażalną ilość zapytań do bazy.

Druga opcja chociaż lepsza, też nie zachwyca. Dobrze jeśli chcemy pobrać tylko 3 rekordy, wynik wygląda następująco:

mysql> SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`) UNION SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`) UNION SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`);
+--------+--------------+
| id     | key          |
+--------+--------------+
|  1303  | javascript
|   123  | jQuery
|  2412  | css
+--------+--------------+
3 rows in set (0.04 sec)
 
mysql> DESCRIBE SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`) UNION SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`) UNION SELECT `id`,`key` FROM `keys` JOIN (SELECT CEIL(RAND() * (SELECT MAX(`id`) FROM `keys`)) AS 'id') AS rows USING (`id`);
+----+--------------+--------------+--------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type  | table        | type   | possible_keys | key     | key_len | ref   | rows | Extra                                               |
+----+--------------+--------------+--------+---------------+---------+---------+-------+------+-----------------------------------------------------+
|  1 | PRIMARY      | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | Impossible WHERE noticed after reading const tables |
|  2 | DERIVED      | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | No tables used                                      |
|  3 | SUBQUERY     | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away                        |
|  4 | UNION        | <derived5>   | system | NULL          | NULL    | NULL    | NULL  |    1 |                                                     |
|  4 | UNION        | keys         | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                                                     |
|  5 | DERIVED      | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | No tables used                                      |
|  6 | SUBQUERY     | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away                        |
|  7 | UNION        | <derived8>   | system | NULL          | NULL    | NULL    | NULL  |    1 |                                                     |
|  7 | UNION        | keys         | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                                                     |
|  8 | DERIVED      | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | No tables used                                      |
|  9 | SUBQUERY     | NULL         | NULL   | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away                        |
| NULL | UNION RESULT | <union1,4,7> | ALL    | NULL          | NULL    | NULL    | NULL  | NULL |                                                     |
+----+--------------+--------------+--------+---------------+---------+---------+-------+------+-----------------------------------------------------+
12 rows in set (0.05 sec)

Pobieranie wielu rekordów – stored procedure

Kilka rekordów możemy pobrać za pomocą UNION SELECT, jednak wymaga to tworzenia zapytania w pętli, które będzie znacznie rosło przy większej ilości pobieranych rekordów. Lepszym rozwiązaniem byłoby przerzucenie większej liczby obowiązków ba bazie, która pracując ze swoimi danymi ma szansę wykonać je szybciej.

Procedura składowana będzie polegała na wcześniej dopracowanym zapytaniu. Jej zadanie jest proste. Tworzymy tabelę tymczasową, do niej w pętli pobieramy losowe wiersze, a na koniec zwracamy całą jej zawartość. W zasadzie jest to bardzo podobne do pierwszych dwóch punktów na liście możliwych rozwiązań. Będziemy wykonywać tyle zapytań na ile losowych rekordów powstanie zapotrzebowanie, z tą różnicą, że całość zadania odbędzie się w samym silniku bazy danych, dane nie będą przesyłane do skryptu, obrabiane, pobierane kolejne itd. Przetworzenie wszystkich danych po stronie RDBMS i zwrócenie jednego pakietu danych zaoszczędzi nam sporo czasu na działaniach I/O pomiędzy bazą a skryptem.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DELIMITER $$
DROP PROCEDURE IF EXISTS getRandomRecords$$
CREATE PROCEDURE getRandomRecords(IN cnt INT)
 
BEGIN
    DROP TEMPORARY TABLE IF EXISTS randomRecords;
    CREATE TEMPORARY TABLE randomRecords ( `rand_id` INT, `sub` CHAR(255) );
 
    loop_random: LOOP
        IF cnt < 1 THEN 
            LEAVE loop_random;
        END IF;
 
        INSERT INTO randomRecords
            SELECT r1.id, r1.sub FROM `keys` AS r1 JOIN
                (SELECT (RAND() *
                    (SELECT MAX(id) FROM `keys`)) AS id) AS r2
            WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1;
        SET cnt = cnt - 1;
    END LOOP loop_random;
 
SELECT * FROM randomRecords;
END$$
DELIMITER ;

Działanie powyższego kodu wygląda następująco:

  1. Usunięcie procedury jeśli istnieje, utworzenie nowej (parametr wejściowy – ilość losowych rekordów do pobrania)
  2. Utworzenie tymczasowej tabeli (usunięcie istniejącej jeśli występuje)
  3. W pętli powtarzanej tyle razy ile rekordów jest do pobrania do tabeli tymczasowej dodawane są pojedyncze rekordy
  4. Na koniec zwracana jest cała tabela tymczasowa, z wszystkimi wylosowanymi rekordami

To by było na tyle. Wykorzystanie procedury sprowadza się do wywołania zapytania:

CALL getRandomRecords(5);

Dzięki takiemu rozwiązaniu pobieranie większej ilości losowych rekordów odbywa się tak samo jak jednego. Poza tym tworzenie zapytania na podstawie innych zmiennych sprowadza się tylko do przekazania ilości wymaganych wierszy. Co ważniejsze, pobranie 15 rekordów trwa tyle samo czasu co pobranie 4-ech. Przygotowanie zapytania także nie zależy od liczby potrzebnych losowo pobranych wierszy.

mysql> call get_rands(15);
+---------+----------------------------------------+
| rand_id | sub                                    |
+---------+----------------------------------------+
|  11737  | PHP                                    |
 
+---------+----------------------------------------+
15 rows in set (0.05 sec)
 
Query OK, 0 rows affected (0.05 sec)

Źródła

  1. http://ranawd.wordpress.com/2009/03/25/select-random-value-from-mysql-database-table/ []
  2. http://dev.mysql.com/doc/refman/5.0/en/union.html []
 

Przeczytaj także

Komentarze: 12

Dodaj komentarz »

 
 
Marcin Kłeczek
 

Świetny tekst – o bardzo popularnym problemie – i jak widać, często źle rozwiązywanym. Dzięki.

 

Odpowiedz

 

Niestety kombinacja „ORDER BY RAND()” jest mordercza, ale często powtarzana i w wielu małych aplikacjach się zdarza…

Wiesz, nieraz lepiej zrobić 2 proste zapytania resztę zrzucając na język programowania, niż jedno przepakowane.

 

Odpowiedz

 

Ciekawy tekst i proponowane rozwiązania. Jest jednak jedno „ale”. Mianowicie tych metod, jako że korzystają z MAX(id), MIN(id) etc., nie można wykorzystać w przypadku, kiedy identyfikatory kolejnych rekordów nie utrzymują ciągłości. Tzn. jeśli np. jakiś rekord zostanie wykasowany fizycznie z bazy, to jest szansa, że losowanie z zakresu pomiędzy MIN a MAX może trafić w ten właśnie rekord.
Możliwe, że to m.in. dlatego funkcja: SELECT `id`,`key` FROM `keys`ORDER BY RAND() LIMIT 1; potrafi zająć tyle czasu – ale przynajmniej w jej przypadku można być pewnym zwracanego wyniku.

 

Odpowiedz

 

@Rumcajs
Tak, masz rację, właśnie dlatego pisałem na końcu części „Satysfakcjonujące rozwiązanie” o tabeli tymczasowej, która przechowywałaby klucze do istniejących rekordów: ID | KEY_ID , a całe losowanie id wykonywane byłoby na tej tabeli tymczasowej.

 

Odpowiedz

 

@Rumcjajs: wg mojego czytania ze zrozumieniem – mylisz się 😉
Dlatego „ktoś” napisał warunek: WHERE `id` >= $random aby uniknąć wpadnięcia pod MIN/MAX gdy rekord już usunięty…
Oczywiście brzydkie rozwiązanie, ale działa tak jak autor napisał

 

Odpowiedz

     

    @matipl: tu chodzi o sytuacje kiedy nie bedzie istnial rekord pomiędzy min id a max id.
    Jeśli sa id: 2,3,4,6,7 to min jest 2 max 7 a może się wylosować 5.
    O tym napisał @Rumcajs

     

    Odpowiedz

       

      @quentino: Dlatego w warunku WHERE jest `id`>=, w przypadku gdy nie ma wiersza o id 5 zostanie zwrócony następny w kolejności (istniejący)

       

      Odpowiedz

 

Warunek WHERE jest `id`>=, nie powoduje losowego wybrania elementu z bazy, gdyz faworyzuje pewne elementy
przyklad
mamy id: 1,2,3,7,8,9
najczesciej losowanym elementem bedzie 7 i to az 4 razy czeesciej niz inne.

 

Odpowiedz

 

Fajne rozwiązanie na które kiedyś się natknąłem i często stosuję to zliczenie wszystkich wierszy, wylosowanie numeru strony, a następnie pobranie losowej strony. Działa szybko i jest proste.

 

Odpowiedz

bezproblemu
 

SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM tabelka)));
SET @sql := CONCAT(‚SELECT * FROM tabelka LIMIT 1 OFFSET ‚, @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

powinno dzialac i nie ma problemu z ciagloscia rekordow

 

Odpowiedz

 

Skąd takie czasy? „1 row in set (0.61 sec)”

Kiedy u mnie przy 20tys rekordów i limit 10 wychodzi: „10 wszystkich, Wykonanie zapytania trwało 0.0222 sekund(y))”

Przy 100tys tak drastycznie wzrosło? Wątpię.

 

Odpowiedz

 

Szczerze, dopiero zaczynam swoją przygodę z Mysql ale wpadłem na pewien pomysł, który powinna zweryfikować osoba posiadająca większą wiedzę.

Czy wydajnym nie było by wykonanie zapytania num() o ilość rekordów, następnie za pomocą mt_randa określenie jakiegoś limitu w zapytaniu, a następnie wylosowanie wśród tych, dajmy na to 10 losowych rekordów?

 

Odpowiedz

 

Dodaj komentarz

 
(nie będzie publikowany)
 
 
Komentarz
 
 

Dozwolone tagi XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

 
© 2009 - 2016 Vokiel.com
WordPress Theme by Arcsin modified by Vokiel