Jeśli przeczytałeś instrukcję MySQL, mogłeś zobaczyć ORDER BY RAND() do randomizacji wierszy i używając LIMIT 1, aby tylko wziąć jeden z wierszy.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Ten przykład działa dobrze i jest szybki, jeśli masz tylko powiedzmy 1000 wierszy. Jak tylko będziesz miał 10000 wierszy, narzut na sortowanie wierszy stanie się istotny. Nie zapominaj: my tylko sortujemy, aby wyrzucić prawie wszystkie wiersze.
Nigdy mi się to nie podobało. I są lepsze sposoby, aby to zrobić. Bez sortowania.Tak długo, jak mamy numeryczny klucz główny.
Dla pierwszych przykładów zakładamy, że be ID zaczyna się od 1 i nie mamy dziur pomiędzy 1 a maksymalną wartością ID.
Pierwszy pomysł: Możemy uprościć całą pracę, jeśli obliczymy ID wcześniej w aplikacji.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Jako MAX(id) == COUNT(id) po prostu generujemy losową liczbę pomiędzy1 a MAX(id) i przekazujemy ją do bazy danych, aby pobrać losowy wiersz.
Pierwszy SELECT jest NO-OP i jest zoptymalizowany z dala. Drugi jest eq_refprzeciwko stałej wartości i również bardzo szybki.
przeniesienie zadania do bazy danych
Ale czy naprawdę trzeba to robić w aplikacji ? Czy nie możemy tego zrobić w bazie danych ?
Ok, teraz wiemy jak wygenerować losowe ID, ale jak uzyskać wiersz ?
NIE, NIE, NIE, NIE. Nie idź tą drogą. Jest to najbardziej oczywisty, ale również najbardziej błędny sposób, aby to zrobić.Powód: SELECT w klauzuli WHERE jest wykonywany dla każdego wiersza, który zewnętrzny SELECT pobiera.To prowadzi do 0 do 4091 wierszy, w zależności od szczęścia.
Potrzebujemy sposobu, aby upewnić się, że random-id jest generowany tylko raz:
Wewnętrzny SELECT generuje stałą tabelę TEMPORARY, a JOIN wybiera tylko singlerow. Perfect.
No Sorting, No Application, Most parts of the query optimized away.
adding holes to the numbers
Aby uogólnić ostatnie rozwiązanie dodajemy możliwość wystąpienia dziur, jak wtedy gdy DELETE rows.
The JOIN now adds all the IDs which are greater or equal than our random value and selects only the direct neighboorif a direct match is not possible. ALE jak tylko jeden wiersz zostanie znaleziony, zatrzymujemy się (LIMIT 1). I czytamy wiersze zgodnie z indeksem (ORDER BY id ASC). Ponieważ używamy >= zamiast =, możemy pozbyć się CEIL i uzyskać ten sam wynik przy nieco mniejszym nakładzie pracy.
Rozkład równomierny
Jak tylko rozkład identyfikatorów nie jest równomierny, nasz wybór wierszy również nie jest losowy.
Funkcja RAND generuje identyfikatory takie jak od 9 do 15, które prowadzą do identyfikatora 16, który jest wybierany jako kolejny wyższy numer.
Nie ma prawdziwego rozwiązania tego problemu, ale twoje dane są w większości stałe, możesz dodać tabelę mapującą, która mapuje numer wiersza na id:
Funkcja row_id jest teraz wolna od dziur i możemy ponownie uruchomić nasze losowe zapytanie:
Po 1000 pobraniach znów widzimy równą dystrybucję:
Utrzymanie dziur
Przyjmijmy tabele jak poprzednio:
Kiedy kiedykolwiek zmienimy coś w r2 chcemy, aby r2_equi_dist również zostało zaktualizowane.
INSERT jest całkiem prosty, przy DELETE musimy zaktualizować equi-dist-id, aby utrzymać konfigurację bez dziur:
UPDATE jest znowu prosty. Musimy tylko utrzymać ograniczenie klucza obcego:
Wiele wierszy na raz
Jeśli chcesz uzyskać więcej niż jeden wiersz, możesz to zrobić:
- wykonać zapytanie kilka razy
- napisać procedurę składowaną, która wykonuje zapytanie i przechowuje wynik w tabeli tymczasowej
- zrobić UNION
procedurę składowaną
Procedury składowane zapewniają struktury, które znasz ze swojego ulubionego języka programowania:
- pętle
- struktury kontrolne
- procedury
- …
Do tego zadania wystarczy nam LOOP:
Poprawianie błędów pozostawiam czytelnikowi:
- wykorzystać dynamiczny SQL i przekazać nazwę tabeli tymczasowej
- wykorzystać indeks UNIQUE na tabeli i złapać naruszenie klucza UNIQUE, aby usunąć ewentualne duplikaty w zbiorze wyników
Wydajność
Teraz zobaczmy, co się dzieje z naszą wydajnością. Mamy 3 różne zapytania do rozwiązania naszych problemów.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 ma kosztować N * log2(N), Q2 i Q3 są prawie stałe.
Aby uzyskać rzeczywiste wartości wypełniliśmy tabelę N wierszami (od tysiąca do miliona) i wykonaliśmy każde zapytanie 1000 razy.