Jak mogę zoptymalizować funkcję ORDER BY RAND () MySQL?

90

Chciałbym zoptymalizować moje zapytania, więc przyjrzę się mysql-slow.log.

Większość moich powolnych zapytań zawiera ORDER BY RAND(). Nie mogę znaleźć prawdziwego rozwiązania tego problemu. Jest możliwe rozwiązanie na MySQLPerformanceBlog, ale myślę, że to nie wystarczy. W przypadku źle zoptymalizowanych (lub często aktualizowanych, zarządzanych przez użytkowników) tabel nie działa lub muszę uruchomić co najmniej dwa zapytania, zanim będę mógł wybrać PHPwygenerowany przeze mnie losowy wiersz.

Czy jest jakieś rozwiązanie tego problemu?

Fikcyjny przykład:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
ORDER BY
        RAND()
LIMIT 1
mysql  random  performance 
— fabrik
źródło
— Ciro Santilli 郝海东 冠状 病 六四 事件 事件

Odpowiedzi:

67

Spróbuj tego:

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND(20090301) < @lim / @cnt
        ) i

Jest to szczególnie wydajne na MyISAM(ponieważ COUNT(*)jest natychmiastowe), ale nawet InnoDBwtedy jest 10bardziej wydajne niż ORDER BY RAND().

Główną ideą jest to, że nie sortujemy, ale zamiast tego przechowujemy dwie zmienne i obliczamy running probabilitywiersz, który ma zostać wybrany w bieżącym kroku.

Zobacz ten artykuł na moim blogu, aby uzyskać więcej szczegółów:

Aktualizacja:

Jeśli chcesz wybrać tylko jeden losowy rekord, spróbuj tego:

SELECT  aco.*
FROM    (
        SELECT  minid + FLOOR((maxid - minid) * RAND()) AS randid
        FROM    (
                SELECT  MAX(ac_id) AS maxid, MIN(ac_id) AS minid
                FROM    accomodation
                ) q
        ) q2
JOIN    accomodation aco
ON      aco.ac_id =
        COALESCE
        (
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_id > randid
                AND ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        ),
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        )
        )

Zakłada się, że Twoje ac_idsą rozmieszczone mniej więcej równomiernie.

— Quassnoi
źródło
Cześć, Quassnoi! Przede wszystkim dziękuję za szybką odpowiedź! Może to moja wina, ale nadal nie jest jasne, jakie jest Twoje rozwiązanie. Zaktualizuję mój oryginalny post konkretnym przykładem i będę szczęśliwy, jeśli wyjaśnisz swoje rozwiązanie na tym przykładzie.
— fabrik
wystąpiła literówka w „JOIN accomodation aco ON aco.id =”, gdzie aco.id tak naprawdę to aco.ac_id. z drugiej strony poprawione zapytanie nie zadziałało dla mnie, ponieważ zgłasza błąd # 1241 - operand powinien zawierać 1 kolumnę (y) przy piątym SELECT (czwarty sub-select). Próbowałem znaleźć problem w nawiasach (jeśli się nie mylę), ale nie mogę jeszcze znaleźć problemu.
— fabrik
@fabrik: Spróbuj teraz. Byłoby naprawdę pomocne, gdybyś opublikował skrypty tabeli, abym mógł je sprawdzić przed wysłaniem.
— Quassnoi
Dzięki, działa! :) Czy możesz edytować część JOIN ... ON aco.id, aby JOIN ... ON aco.ac_id, abym mógł zaakceptować twoje rozwiązanie. Dzięki jeszcze raz! Pytanie: zastanawiam się, czy to możliwe, że jest to gorszy losowy, taki jak ORDER BY RAND ()? Tylko dlatego, że to zapytanie wielokrotnie powtarza niektóre wyniki.
— fabrik
1
@Adam: nie, to zamierzone, abyś mógł odtworzyć wyniki.
— Quassnoi,
12

To zależy od tego, jak losowy musisz być. Połączone rozwiązanie działa całkiem dobrze w IMO. O ile nie masz dużych luk w polu ID, nadal jest to dość przypadkowe.

Jednak powinieneś móc to zrobić w jednym zapytaniu, używając tego (do wyboru pojedynczej wartości):

SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 1

Inne rozwiązania:

  • Dodaj stałe pole zmiennoprzecinkowe wywołane randomdo tabeli i wypełnij je liczbami losowymi. Następnie możesz wygenerować liczbę losową w PHP i zrobić"SELECT ... WHERE rnd > $random"
  • Pobierz całą listę identyfikatorów i zapisz je w pliku tekstowym. Przeczytaj plik i wybierz z niego losowy identyfikator.
  • Buforuj wyniki zapytania w formacie HTML i przechowuj je przez kilka godzin.
— Niezadowolony Kozioł
źródło
8
Czy to tylko ja, czy to zapytanie nie działa? Wypróbowałem to z kilkoma wariacjami i wszystkie rzucają "Nieprawidłowe użycie funkcji grupy" ..
— Sophivorus
Możesz to zrobić za pomocą podzapytania, SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*(SELECT MAX(id) FROM [table])) LIMIT 1ale wydaje się, że nie działa to poprawnie, ponieważ nigdy nie zwraca ostatniego rekordu
— Mark
11
SELECT [fields] FROM [table] WHERE id >= FLOOR(1 + RAND()*(SELECT MAX(id) FROM [table])) LIMIT 1Wygląda na to, że mi się to udaje
— Mark
1

Oto jak bym to zrobił:

SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*)
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != 'draft'
        AND c.acat_slug != 'vendeglatohely'
        AND a.ac_images != 'b:0;';

SET @sql := CONCAT('
  SELECT  a.ac_id,
        a.ac_status,
        a.ac_name,
        a.ac_status,
        a.ac_images
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != ''draft''
        AND c.acat_slug != ''vendeglatohely''
        AND a.ac_images != ''b:0;''
  LIMIT ', @r, ', 1');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;
— Bill Karwin
źródło
— Bill Karwin,
moja tabela nie jest ciągła, ponieważ jest często edytowana. na przykład obecnie pierwszy identyfikator to 121.
— fabrik
3
Powyższa technika nie polega na tym, że wartości id są ciągłe. Wybiera losową liczbę od 1 do COUNT (*), a nie od 1 do MAX (id), jak niektóre inne rozwiązania.
— Bill Karwin,
1
Używanie OFFSET(do czego @rsłuży) nie zapobiega skanowaniu - aż do pełnego skanowania tabeli.
— Rick James
@RickJames, zgadza się. Gdybym miał dziś odpowiedzieć na to pytanie, wykonałbym zapytanie za pomocą klucza podstawowego. Użycie przesunięcia z LIMIT skanuje wiele wierszy. Zapytanie według klucza podstawowego, choć znacznie szybsze, nie gwarantuje równych szans na wybranie każdego wiersza - faworyzuje wiersze, które następują po lukach.
— Bill Karwin,
1

(Tak, zostanę obrzydzony za brak tu mięsa, ale czy nie możesz być weganinem przez jeden dzień?)

Przypadek: Konsekutywne AUTO_INCREMENT bez przerw, zwrócone 1 wiersz
Przypadek: Kolejne AUTO_INCREMENT bez przerw, 10 wierszy
Przypadek: AUTO_INCREMENT z przerwami, zwrócony 1 wiersz
Przypadek: Dodatkowa kolumna typu FLOAT do losowania
Przypadek: kolumna UUID lub MD5

Te 5 skrzynek może być bardzo wydajnych w przypadku dużych stołów. Zobacz mój blog po szczegóły.

— Rick James
źródło
0

To da ci jedno zapytanie podrzędne, które użyje indeksu do uzyskania losowego identyfikatora, a drugie zapytanie uruchomi twoją połączoną tabelę.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
AND accomodation.ac_id IS IN (
        SELECT accomodation.ac_id FROM accomodation ORDER BY RAND() LIMIT 1
)
— Karl Mikko
źródło
0

Rozwiązanie dla twojego przykładu fałszywego byłoby:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation,
        JOIN 
            accomodation_category 
            ON accomodation.ac_category = accomodation_category.acat_id
        JOIN 
            ( 
               SELECT CEIL(RAND()*(SELECT MAX(ac_id) FROM accomodation)) AS ac_id
            ) AS Choices 
            USING (ac_id)
WHERE   accomodation.ac_id >= Choices.ac_id 
        AND accomodation.ac_status != 'draft'
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
LIMIT 1

Aby dowiedzieć się więcej o alternatywach ORDER BY RAND(), przeczytaj ten artykuł .

— tereško
źródło
0

Optymalizuję wiele istniejących zapytań w moim projekcie. Rozwiązanie Quassnoi bardzo pomogło mi przyspieszyć zapytania! Jednak trudno jest włączyć to rozwiązanie do wszystkich zapytań, szczególnie w przypadku skomplikowanych zapytań obejmujących wiele podzapytań na wielu dużych tabelach.

Dlatego używam mniej zoptymalizowanego rozwiązania. Zasadniczo działa tak samo, jak rozwiązanie Quassnoi.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
        AND rand() <= $size * $factor / [accomodation_table_row_count]
LIMIT $size

$size * $factor / [accomodation_table_row_count]oblicza prawdopodobieństwo wybrania losowego wiersza. Rand () wygeneruje liczbę losową. Wiersz zostanie wybrany, jeśli rand () jest mniejszy lub równy prawdopodobieństwu. To skutecznie dokonuje losowego wyboru w celu ograniczenia rozmiaru tabeli. Ponieważ istnieje szansa, że ​​zwróci mniej niż zdefiniowany limit liczby, musimy zwiększyć prawdopodobieństwo, aby upewnić się, że wybieramy wystarczającą liczbę wierszy. Dlatego mnożymy rozmiar $ przez współczynnik $ (zwykle ustawiam współczynnik $ = 2, działa w większości przypadków). Wreszcie robimylimit $size

Teraz problemem jest ustalenie accomodation_table_row_count . Jeśli znamy rozmiar tabeli, MOŻEMY na stałe zakodować rozmiar tabeli. To działałoby najszybciej, ale oczywiście nie jest to idealne rozwiązanie. Jeśli używasz Myisam, uzyskiwanie liczby stołów jest bardzo wydajne. Ponieważ używam innodb, po prostu robię proste liczenie + wybór. W twoim przypadku wyglądałoby to tak:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
        AND rand() <= $size * $factor / (select (SELECT count(*) FROM `accomodation`) * (SELECT count(*) FROM `accomodation_category`))
LIMIT $size

Najtrudniejsze jest ustalenie odpowiedniego prawdopodobieństwa. Jak widać, poniższy kod w rzeczywistości oblicza tylko przybliżony rozmiar tabeli temp (w rzeczywistości jest zbyt przybliżony!): (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category))Ale możesz udoskonalić tę logikę, aby uzyskać bliższe przybliżenie rozmiaru tabeli. Zwróć uwagę, że lepiej jest zaznaczyć OVER niż niedostatecznie zaznaczyć wiersze. tzn. jeśli prawdopodobieństwo jest zbyt niskie, ryzykujesz, że nie wybierzesz wystarczającej liczby wierszy.

To rozwiązanie działa wolniej niż rozwiązanie Quassnoi, ponieważ musimy ponownie obliczyć rozmiar tabeli. Jednak uważam, że to kodowanie jest o wiele łatwiejsze w zarządzaniu. Jest to kompromis między dokładnością i wydajnością a złożonością kodowania . Powiedziawszy to, na dużych stołach jest to nadal znacznie szybsze niż Order by Rand ().

Uwaga: Jeśli pozwala na to logika zapytań, należy przeprowadzić losowy wybór możliwie jak najwcześniej przed wykonaniem jakichkolwiek operacji łączenia.

— lawrenceshen
źródło
-1 
function getRandomRow(){
    $id = rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT);
    $res = getRowById($id);
    if(!empty($res))
    return $res;
    return getRandomRow();
}

//rowid is a key on table
function getRowById($rowid=false){

   return db select from table where rowid = $rowid; 
}
— Rokhayakebe
źródło
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.