MySQL Wybierz 10 losowych wierszy z 600k wierszy szybko

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

Nie wydajne rozwiązanie, ale działa

To bardzo proste i jednoliniowe zapytanie.

SELECT * FROM Table_Name ORDER BY RAND() LIMIT 0,10;

Otrzymuję szybkie zapytania (około 0,5 sekundy) z powolnym procesorem , wybierając 10 losowych wierszy w 400k rejestruje bazę danych MySQL nie buforowaną o rozmiarze 2Gb. Zobacz tutaj mój kod: Szybki wybór losowych wierszy w MySQL

<?php
$time= microtime_float();

$sql='SELECT COUNT(*) FROM pages';
$rquery= BD_Ejecutar($sql);
list($num_records)=mysql_fetch_row($rquery);
mysql_free_result($rquery);

$sql="SELECT id FROM pages WHERE RAND()*$num_records<20
   ORDER BY RAND() LIMIT 0,10";
$rquery= BD_Ejecutar($sql);
while(list($id)=mysql_fetch_row($rquery)){
    if($id_in) $id_in.=",$id";
    else $id_in="$id";
}
mysql_free_result($rquery);

$sql="SELECT id,url FROM pages WHERE id IN($id_in)";
$rquery= BD_Ejecutar($sql);
while(list($id,$url)=mysql_fetch_row($rquery)){
    logger("$id, $url",1);
}
mysql_free_result($rquery);

$time= microtime_float()-$time;

logger("num_records=$num_records",1);
logger("$id_in",1);
logger("Time elapsed: <b>$time segundos</b>",1);
?>

Z książki:

Wybierz losowy wiersz za pomocą offsetu

Jeszcze jedna technika, która pozwala uniknąć problemów znalezionych w poprzednim alternatywą jest policzenie wierszy w zbiorze danych i zwrócenie losowego Liczba od 0 do liczby. Następnie użyj tej liczby jako przesunięcia podczas odpytywania zbioru danych

<?php
$rand = "SELECT ROUND(RAND() * (SELECT COUNT(*) FROM Bugs))";
$offset = $pdo->query($rand)->fetch(PDO::FETCH_ASSOC);
$sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset";
$stmt = $pdo->prepare($sql);
$stmt->execute( $offset );
$rand_bug = $stmt->fetch();

Użyj tego rozwiązania, gdy nie możesz przyjąć sąsiednich wartości kluczy i musisz upewnić się, że każdy wiersz ma równe szanse na wybranie.

Proste zapytanie, które ma doskonałą wydajność (działa z lukami):

SELECT * FROM tbl WHERE id IN 
    (SELECT id FROM (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) t)

używane są dwa zagnieżdżone zapytania podrzędne, ponieważ MySQL nie obsługuje jeszcze limitu w pierwszym.

Jest to szybkie, ponieważ faza sortowania używa tylko zindeksowanej kolumny ID.

Dla wersji ważonej: https://stackoverflow.com/a/41577458/893432

Jak wybrać losowe wiersze z tabeli:

Stąd: Wybierz losowe wiersze w MySQL

Szybką poprawą w stosunku do "skanowania tabeli" jest użycie indeksu do pobierania losowych identyfikatorów.

SELECT *
FROM random, (
        SELECT id AS sid
        FROM random
        ORDER BY RAND( )
        LIMIT 10
    ) tmp
WHERE random.id = tmp.sid;

Cóż, jeśli nie masz luk w kluczach i wszystkie są numeryczne, możesz obliczyć liczby losowe i wybrać te linie. ale prawdopodobnie tak nie będzie.

Więc jedno rozwiązanie byłoby następujące:

SELECT * FROM table WHERE key >= FLOOR(RAND()*MAX(id)) LIMIT 1

Który w zasadzie zapewni, że otrzymasz losową liczbę w zakresie kluczy, a następnie wybierzesz następny najlepszy, który jest większy. musisz to zrobić 10 razy.

Jednak nie jest to tak naprawdę losowe, ponieważ twoje klucze najprawdopodobniej nie będą rozłożone równomiernie.

To naprawdę duży problem i nie jest łatwy do rozwiązania spełniając wszystkie wymagania, MySQL rand() jest najlepszym, jaki możesz uzyskać, jeśli naprawdę chcesz 10 losowych wierszy.

Istnieje jednak inne rozwiązanie, które jest szybkie, ale ma również kompromis, jeśli chodzi o losowość, ale może Ci lepiej odpowiadać. Przeczytaj o tym tutaj: Jak mogę zoptymalizować kolejność MySQL za pomocą funkcji RAND ()?

Pytanie brzmi, jak losowy ma być.

Czy możesz wyjaśnić trochę bardziej żebym mógł dać ci dobre rozwiązanie.

Na przykład firma, z którą pracowałem, miała rozwiązanie, w którym bardzo szybko potrzebowali absolutnej losowości. Skończyło się na wstępnym wypełnieniu bazy losowymi wartościami, które zostały wybrane malejąco i ustawione na różne losowe wartości później ponownie.

Jeśli prawie nigdy nie zaktualizujesz, możesz również wypełnić rosnący identyfikator, aby nie mieć luk i po prostu obliczyć losowe klucze przed wyborem... To zależy od przypadku użycia!

Użyłem tego http://jan.kneschke.de/projects/mysql/order-by-rand / wysłany przez Riedsio (użyłem przypadku procedury składowanej, która zwraca jedną lub więcej losowych wartości):

   DROP TEMPORARY TABLE IF EXISTS rands;
      CREATE TEMPORARY TABLE rands ( rand_id INT );

    loop_me: LOOP
        IF cnt < 1 THEN
          LEAVE loop_me;
        END IF;

        INSERT INTO rands
           SELECT r1.id
             FROM random AS r1 JOIN
                  (SELECT (RAND() *
                                (SELECT MAX(id)
                                   FROM random)) AS id)
                   AS r2
            WHERE r1.id >= r2.id
            ORDER BY r1.id ASC
            LIMIT 1;

        SET cnt = cnt - 1;
      END LOOP loop_me;

W artykule rozwiązuje problem luk w identyfikatorach powodujących niezbyt przypadkowe wyniki utrzymując tabelę (używając wyzwalaczy itp...zobacz artykuł); Rozwiązuję problem dodając kolejną kolumnę do tabeli, wypełnioną ciągłymi liczbami, zaczynając od 1 (edytuj: Ta kolumna jest dodawana do tymczasowej tabeli utworzonej przez zapytanie podrzędne podczas wykonywania, nie ma wpływu na stałą tabelę):

   DROP TEMPORARY TABLE IF EXISTS rands;
      CREATE TEMPORARY TABLE rands ( rand_id INT );

    loop_me: LOOP
        IF cnt < 1 THEN
          LEAVE loop_me;
        END IF;

        SET @no_gaps_id := 0;

        INSERT INTO rands
           SELECT r1.id
             FROM (SELECT id, @no_gaps_id := @no_gaps_id + 1 AS no_gaps_id FROM random) AS r1 JOIN
                  (SELECT (RAND() *
                                (SELECT COUNT(*)
                                   FROM random)) AS id)
                   AS r2
            WHERE r1.no_gaps_id >= r2.id
            ORDER BY r1.no_gaps_id ASC
            LIMIT 1;

        SET cnt = cnt - 1;
      END LOOP loop_me;

W artykule widzę, że zadał sobie wiele trudu, aby zoptymalizować kod; nie mam pomysłu, czy / jak bardzo moje zmiany wpływają na wydajność, ale działa bardzo dobrze dla mnie.

Potrzebowałem zapytania, aby zwrócić dużą liczbę losowych wierszy z dość dużej tabeli. To właśnie wymyśliłem. Najpierw uzyskaj maksymalny identyfikator rekordu:

SELECT MAX(id) FROM table_name;

Następnie zamień tę wartość na:

SELECT * FROM table_name WHERE id > FLOOR(RAND() * max) LIMIT n;

Gdzie max jest maksymalnym ID rekordu w tabeli, a n jest liczbą wierszy, które chcesz w zestawie wyników. Założenie jest takie, że nie ma luk w id rekordu, chociaż wątpię, że wpłynie to na wynik, gdyby tak było(nie próbowałem tego jednak). Stworzyłem również ten zapisany procedura ma być bardziej ogólna; podaj nazwę tabeli i liczbę wierszy, które mają zostać zwrócone. Uruchamiam MySQL 5.5.38 na Windows 2008, 32GB, dual 3GHz E5450, a na stole z 17,361,264 wierszami jest dość spójny w ~.03 sek / ~11 sek, aby zwrócić 1 000 000 wierszy. (czasy pochodzą z MySQL Workbench 6.1; możesz również użyć CEIL zamiast FLOOR W 2nd SELECT w zależności od preferencji)

DELIMITER $$

USE [schema name] $$

DROP PROCEDURE IF EXISTS `random_rows` $$

CREATE PROCEDURE `random_rows`(IN tab_name VARCHAR(64), IN num_rows INT)
BEGIN

SET @t = CONCAT('SET @max=(SELECT MAX(id) FROM ',tab_name,')');
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @t = CONCAT(
    'SELECT * FROM ',
    tab_name,
    ' WHERE id>FLOOR(RAND()*@max) LIMIT ',
    num_rows);

PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
$$

Then

CALL [schema name].random_rows([table name], n);

Oto Zmiana gry, która może być pomocna dla wielu;]}

Mam tabelę z 200k wierszy, z id sekwencyjnym , musiałem wybrać N losowe wiersze, więc wybieram losowe wartości na podstawie największego ID w tabeli, stworzyłem ten skrypt, aby dowiedzieć się, która jest najszybsza operacja:

logTime();
query("SELECT COUNT(id) FROM tbl");
logTime();
query("SELECT MAX(id) FROM tbl");
logTime();
query("SELECT id FROM tbl ORDER BY id DESC LIMIT 1");
logTime();

Wyniki są następujące:

• Count: 36.8418693542479 ms
• Max: 0.241041183472 ms
• Order: 0.216960906982 ms

Na podstawie tych wyników, kolejność desc jest najszybsza operacja, aby uzyskać max id,
Oto moja odpowiedź na pytanie:

SELECT GROUP_CONCAT(n SEPARATOR ',') g FROM (
    SELECT FLOOR(RAND() * (
        SELECT id FROM tbl ORDER BY id DESC LIMIT 1
    )) n FROM tbl LIMIT 10) a

...
SELECT * FROM tbl WHERE id IN ($result);

FYI: aby uzyskać 10 losowych wierszy z tabeli 200k, Zajęło mi 1.78 ms (w tym wszystkie operacje po stronie php)

Wszystkie najlepsze odpowiedzi zostały już zamieszczone (głównie te odsyłające do linku http://jan.kneschke.de/projects/mysql/order-by-rand/).

Chcę wskazać kolejną możliwość przyspieszenia - buforowanie . Pomyśl, dlaczego musisz uzyskać losowe wiersze. Prawdopodobnie chcesz wyświetlić losowy post lub losową reklamę na stronie internetowej. Jeśli otrzymujesz 100 req/ s, Czy naprawdę konieczne jest, aby każdy odwiedzający otrzymywał losowe wiersze? Zwykle jest całkowicie dobrze buforować te x losowe wiersze dla 1 sekund (a nawet 10 sekund). Nie ma znaczenia, czy 100 unikalnych użytkowników w tej samej 1 sekundzie otrzyma te same losowe posty, ponieważ w następnej sekundzie kolejne 100 odwiedzających otrzyma inny zestaw postów.

Podczas korzystania z tego buforowania możesz również użyć wolniejszego rozwiązania do pobierania losowych danych, ponieważ będą one pobierane z MySQL tylko raz na sekundę, niezależnie od twoich req / s.

Jeśli masz tylko jeden Read-Request

Połącz odpowiedź @redsio z tabelą temp (600K to nie tyle):

DROP TEMPORARY TABLE IF EXISTS tmp_randorder;
CREATE TABLE tmp_randorder (id int(11) not null auto_increment primary key, data_id int(11));
INSERT INTO tmp_randorder (data_id) select id from datatable;

A potem weź wersję @redsios Odpowiedz:

SELECT dt.*
FROM
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM tmp_randorder)) AS id)
        AS rnd
 INNER JOIN tmp_randorder rndo on rndo.id between rnd.id - 10 and rnd.id + 10
 INNER JOIN datatable AS dt on dt.id = rndo.data_id
 ORDER BY abs(rndo.id - rnd.id)
 LIMIT 1;

Jeśli stół jest duży, możesz przesiać na pierwszą część:

INSERT INTO tmp_randorder (data_id) select id from datatable where rand() < 0.01;

Jeśli masz wiele read-requests

1. Version: możesz zachować tabelę tmp_randorder persistent, nazwij ją datatable_idlist. Odtwórz tę tabelę w określonych odstępach czasu( dzień, godzina), ponieważ również dostanie dziury. Jeśli twój stół robi się naprawdę duży, możesz również uzupełnić otwory

   Wybierz L. data_id jako całość from datatable_idlist l left join datatable dt on dt.id = l. data_id gdzie dt.id is null;

2. Version: Daj swojemu zestawowi danych kolumnę random_sortorder bezpośrednio w datatable lub w stałej dodatkowej tabeli datatable_sortorder. Indeks tej kolumny. Wygeneruj losową wartość w aplikacji (nazwę ją $rand).

   select l.*
   from datatable l 
   order by abs(random_sortorder - $rand) desc 
   limit 1;
   

To rozwiązanie rozróżnia "rzędy krawędzi" z najwyższy i najniższy random_sortorder, więc przestawiaj je w odstępach (raz dziennie).

Innym prostym rozwiązaniem będzie ranking wierszy i pobieranie jednego z nich losowo, a dzięki temu rozwiązaniu nie będziesz musiał mieć żadnej kolumny opartej na 'Id' w tabeli.

SELECT d.* FROM (
SELECT  t.*,  @rownum := @rownum + 1 AS rank
FROM mytable AS t,
    (SELECT @rownum := 0) AS r,
    (SELECT @cnt := (SELECT RAND() * (SELECT COUNT(*) FROM mytable))) AS n
) d WHERE rank >= @cnt LIMIT 10;

Możesz zmienić wartość graniczną zgodnie z potrzebą dostępu do dowolnej liczby wierszy, ale w większości będą to kolejne wartości.

Jednakże, jeśli nie chcesz kolejnych losowych wartości, możesz pobrać większą próbkę i wybrać losowo z niej. coś w tym stylu ...

SELECT * FROM (
SELECT d.* FROM (
    SELECT  c.*,  @rownum := @rownum + 1 AS rank
    FROM buildbrain.`commits` AS c,
        (SELECT @rownum := 0) AS r,
        (SELECT @cnt := (SELECT RAND() * (SELECT COUNT(*) FROM buildbrain.`commits`))) AS rnd
) d 
WHERE rank >= @cnt LIMIT 10000 
) t ORDER BY RAND() LIMIT 10;

Jednym ze sposobów, który uważam za całkiem dobry, jeśli istnieje autogenerowane id, jest użycie operatora modulo'%'. Na przykład, jeśli potrzebujesz 10 000 losowych rekordów z 70 000, możesz to uprościć, mówiąc, że potrzebujesz 1 z każdego wiersza 7. Można to uprościć w tym zapytaniu:

SELECT * FROM 
    table 
WHERE 
    id % 
    FLOOR(
        (SELECT count(1) FROM table) 
        / 10000
    ) = 0;

Jeśli wynik dzielenia wierszy docelowych przez liczbę całkowitą nie jest liczbą całkowitą, będziesz miał kilka dodatkowych wierszy niż to, o co prosiłeś, więc powinieneś dodać klauzulę limitu, która pomoże Ci przyciąć zestaw wyników to:

SELECT * FROM 
    table 
WHERE 
    id % 
    FLOOR(
        (SELECT count(1) FROM table) 
        / 10000
    ) = 0
LIMIT 10000;

Wymaga to pełnego skanowania, ale jest szybsze niż ORDER BY RAND i moim zdaniem prostsze do zrozumienia niż inne opcje wymienione w tym wątku. Również jeśli system, który zapisuje do DB tworzy zestawy wierszy w partiach, możesz nie uzyskać tak losowego wyniku, jak oczekiwałeś.

Poprawiłem odpowiedź @Riedsio miał. Jest to najbardziej wydajne zapytanie, jakie mogę znaleźć na dużej, równomiernie rozłożonej tabeli z lukami (przetestowano na pobraniu 1000 losowych wierszy z tabeli, która ma > 2.6 wierszy B).

(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max := (SELECT MAX(id) FROM table)) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1)

1. @max := (SELECT MAX(id) FROM table)
   • obliczam i oszczędzam max. W przypadku bardzo dużych tabel istnieje niewielki narzut na obliczenie MAX(id) za każdym razem, gdy potrzebujesz wiersza
2. SELECT FLOOR(rand() * @max) + 1 as rand)
   • dostaje random id
3. SELECT id FROM table INNER JOIN (...) on id > rand LIMIT 1
   To wypełnia luki. Zasadniczo, jeśli losowo wybierzesz numer w lukach, po prostu wybierzesz następny identyfikator. Zakładając, że luki są równomiernie rozłożone, nie powinno to stanowić problemu.

Wykonywanie Unii pomaga dopasować wszystko do jednego zapytania, dzięki czemu można uniknąć wykonywania wielu zapytań. Pozwala również zaoszczędzić koszty obliczenia MAX(id). W zależności od zastosowania może to mieć duże znaczenie lub bardzo trochę.

Zauważ, że to pobiera tylko identyfikatory i dostaje je w losowej kolejności. Jeśli chcesz zrobić coś bardziej zaawansowanego polecam to zrobić:

SELECT t.id, t.name -- etc, etc
FROM table t
INNER JOIN (
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max := (SELECT MAX(id) FROM table)) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
    (SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1)
) x ON x.id = t.id
ORDER BY t.id

Jeśli chcesz jeden losowy rekord (bez względu na to, czy są gapy między identyfikatorami):

PREPARE stmt FROM 'SELECT * FROM `table_name` LIMIT 1 OFFSET ?';
SET @count = (SELECT
        FLOOR(RAND() * COUNT(*))
    FROM `table_name`);

EXECUTE stmt USING @count;

Źródło: https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/#comment-1266

Stare pytanie, ale to jest coś, na co wpadłem dzisiaj, chcąc wybrać losową stronę. Zdecydowałem się nie używać żadnej z odpowiedzi tutaj ze względu na obawy o wydajność i fakt, że wiele z nich ma silne uprzedzenia w "przypadkowe". Oto moje rozwiązanie (używając PHP):

Pages model:

public static function getIDs() {
    $sql  = "SELECT `id` FROM `pages`;";
    $db   = static::getDB();
    $stmt = $db->query($sql);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Pages controller:

public function randomAction() {
    $pages  = Pages::getIDs();
    $random = $pages[rand(0, count($pages))];

    $this->redirect('/' . $random['id'], 307);
}

Zasadniczo, wszystko co robi, to pobranie tablicy slugów stron z DB i użycie PHP do wybrania losowej z zwróconej tablicy.

Jeśli Chcesz 10 rekordy, po prostu iteruj przez tablicę i usuń wybrane, aby uniknąć duplikatów, a następnie dodaj je do oddzielnej tablicy wyników. Coś takiego:

public static function randomAction() {
    $pages   = Pages::getIDs();
    $count   = count($pages);
    $results = [];

    for($i = 0; $i < 10; $i++) {
        $random = rand(0, $count);
        $count -= 1;

        $results[] = $pages[$random];
        unset($pages[$random]);
    }

    return $results;
}

Przejrzałam wszystkie odpowiedzi i chyba nikt nie wspomina o tej możliwości i nie jestem pewna dlaczego.

Jeśli zależy ci na maksymalnej prostocie i szybkości, przy niewielkim koszcie, to wydaje mi się, że logiczne jest przechowywanie losowej liczby względem każdego wiersza w DB. Po prostu utwórz dodatkową kolumnę random_number i ustaw domyślną wartość RAND(). Utwórz indeks na tej kolumnie.

Następnie, gdy chcesz pobrać wiersz Wygeneruj losową liczbę w kodzie (PHP, Perl, cokolwiek) i porównaj to z kolumną.

SELECT FROM tbl WHERE random_number >= :random LIMIT 1

Myślę, że chociaż jest bardzo schludny dla pojedynczego rzędu, dla dziesięciu rzędów, jak prosił OP, będziesz musiał nazwać to dziesięć oddzielnych razy (lub wymyślić sprytne poprawki, które natychmiast mi umkną)

Używam tego zapytania:

select floor(RAND() * (SELECT MAX(key) FROM table)) from table limit 10

Query time: 0.016 s

Użyj poniższego prostego zapytania, aby uzyskać losowe DANE z tabeli.

SELECT user_firstname ,
COUNT(DISTINCT usr_fk_id) cnt
FROM userdetails 
GROUP BY usr_fk_id 
ORDER BY cnt ASC  
LIMIT 10

Tak to robię:

select * 
from table_with_600k_rows
where rand() < 10/600000
limit 10

Podoba mi się, ponieważ nie wymaga innych tabel, jest prosty w pisaniu i bardzo szybki w wykonaniu.

To chyba najlepszy możliwy sposób..

SELECT id, id * RAND( ) AS random_no, first_name, last_name
FROM user
ORDER BY random_no