Xis’ blog

Mój wirtualny schowek

Szybkie losowanie rekordów w SQL – jeszcze jeden sposób

Posted on | 10 marca, 2011 | 2 komentarze

Jeśli masz tabelę, w której znajduje się wiele rekordów i stajesz przed potrzebą wyświetlenie kilku losowych rekordów – na ogół masz problem związany z wydajnym sposobem realizacji zapytania.
Są różne sposoby na rozwiązanie tego problemu. Najprostszy jest oczywiście ORDER BY RAND(), jednak słynie on z tego, że jest straszliwie powolny, zwłaszcza na dużych tabelach.
Inne rozwiązanie to załadowanie listy kluczy podstawowych tabeli do tablicy w pamięci i losowanie po stronie programu. Wykonujemy wprawdzie dwa zapytania zamiast jednego, jednak jest to metoda dużo bardziej wydajna w niektórych przypadkach.
Wymyśliłem dziś inny sposób, dzięki któremu nie musimy skanować całej tabeli w celu wyświetlenia losowych rekordów. Rozwiązanie to jest bardzo proste w realizacji.

Wystarczy, że do tabeli, z której chcemy pobierać rekordy dodamy kolumnę np. random_num, najlepiej wesprzeć ją indeksem.  W kolumnie tej zapisywać będziemy losową liczbę z jakiegoś zakresu (np. 1 do 1000) podczas każdego zapisania obiektu.

Gdy mamy już tabelę wypełnioną  rekordami z losowymi wartościami  w kolumnie random_num (wartość ta oczywiście będzie się powtarzać w przypadku, gdy tabela ma > 1000 rekordów, ale to nie szkodzi), wystarczy wykonać dwa zapytania:

SELECT COUNT(*) AS cnt FROM tabela

… by zliczyć ilość rekordów.  Wynik zapytania załadujmy do zmiennej cnt. W zmiennej limit natomiast zapamiętajmy ilość rekordów, jakie chcemy pobrać. Następnie:

SELECT * FROM tabela ORDER BY random_num OFFSET :offset LIMIT :LIMIT

Pod parametr :offset podstawiamy losową wartość (losowaną po stronie programu) z zakresu 1 do cnt – ilość rekordów, jakie chcemy pobrać, a pod :limit – wartość zmiennej limit.

Wadą tego rozwiązania jest jego „statyczność”, tzn dla każdego wylosowanego offsetu wynik zapytania będzie się powtarzał jeśli offest będzie się powtarzał. Jeśli jednak zawartość Twojej tabeli zmienia się dość często nie jest to problemem. Innym sposobem obejścia tego stanu rzeczy jest wykonywanie co jakiś czas „reindeksacji” kolumny random_num.

Comments

2 komentarze to “Szybkie losowanie rekordów w SQL – jeszcze jeden sposób”

  1. kef
    19 marca, 2011 @ 11:09

    Fajnie, ale:
    – mieszasz przeznaczenie bazy danych (przechowywanie informacji) z logiką aplikacji.
    – wydajność poprawia się przez usuwanie zbędnych zapytań, nie ich mikro-optymalizację.

    Tak więc w tym przypadku poszedłbym w wyciąganie losowych danych backendowym procesem, który odkłada je do warstwy cache’ującej (choćby najzwyklejszy plik). Userowi serwujesz dane z cache’u. Cache przegenerowujesz z ustalonym interwałem. W takim podejściu nawet zwykły „order by rand()” nie stanowi problemu.

    Baza dostaje mniej zapytań, a Ty masz więcej czasu na rzeczy naprawdę istotne, czyli funkcjonalność aplikacji.

  2. xis
    19 marca, 2011 @ 11:54

    Dzięki za komentarz.

    Fajnie, ale:
    – mieszasz przeznaczenie bazy danych (przechowywanie informacji) z logiką aplikacji.
    – wydajność poprawia się przez usuwanie zbędnych zapytań, nie ich mikro-optymalizację.

    Owszem, zgoda. Jednak nie należy tego traktować tak ostro. Np. dane drzewka left-right, albo „przeliczone” wcześniej „slugi” nazw. Takie dane są nadmiarowe (można je wyliczyć podczas zapytania), jednak często są przechowywane w bazie. Generalnie jeśli jakieś rekordy są często czytane to warto pomęczyć się podczas ich zapisywania, nawet lekko naciągając dobre bazodanowe obyczaje, żeby zyskać na wydajności.

    Tak więc w tym przypadku poszedłbym w wyciąganie losowych danych backendowym procesem, który odkłada je do warstwy cache’ującej (choćby najzwyklejszy plik). Userowi serwujesz dane z cache’u. Cache przegenerowujesz z ustalonym interwałem. W takim podejściu nawet zwykły „order by rand()” nie stanowi problemu.

    To rozwiązanie wykorzystuję najczęściej właśnie ze względów wydajności. Cache to dobre rozwiązanie, jednak jego „statyczność” jest jeszcze większa. Owszem, można wygenerować (wylosować) kilka wariantów danego zbioru i serwować któryś (również losowo) z nich, jednak mamy tu dużą powtarzalność wyników. Bo przecież zasadą cache jest żeby czytać często raz zapisane dane. Ale to zasada kompletnie nie pasująca do losowania.

    Mój wniosek jest taki, że żadne rozwiązanie nie jest idealne i tylko w zależności od specyfiki problemu można zastosować któreś z nich.
    Pozdrawiam

Leave a Reply