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:
$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).
Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...
Zwiń
Czytaj na blogu autora...
Kanał ATOM
