MySQL wähle 10 zufällige Zeilen aus 600.000 Zeilen schnell

Question

Wie kann ich am besten eine Abfrage schreiben, die 10 Zeilen zufällig aus insgesamt 600.000 auswählt?

Answer 1

Ich weiß, dass es nicht das ist, was du möchtest, aber die Antwort, die ich dir geben werde, ist das, was ich in einer kleinen Website in der Produktion verwende.

Je nach Häufigkeit, mit der du auf den zufälligen Wert zugreifst, lohnt es sich nicht, MySQL zu verwenden, einfach weil du die Antwort nicht zwischenspeichern kannst. Dort haben wir einen Button, um auf eine zufällige Seite zuzugreifen, und ein Benutzer könnte dort mehrmals pro Minute klicken, wenn er möchte. Dies verursacht eine große Menge an MySQL-Anfragen und, zumindest für mich, ist MySQL das größte Problem bei der Optimierung.

Ich würde einen anderen Ansatz wählen, bei dem du die Antwort im Cache speichern kannst. Führe einen Aufruf an dein MySQL durch:

SELECT min(id) as min, max(id) as max FROM your_table

Mit deiner min und max Id kannst du auf deinem Server eine zufällige Zahl berechnen. In Python:

random.randint(min, max)

Dann kannst du mit deiner zufälligen Zahl eine zufällige Id in deiner Tabelle erhalten:

SELECT * 
FROM your_table 
WHERE id >= %s 
ORDER BY id ASC 
LIMIT 1

Bei dieser Methode führst du zwei Aufrufe an deine Datenbank durch, kannst sie aber zwischenspeichern und für einen längeren Zeitraum nicht auf die Datenbank zugreifen, was die Leistung verbessert. Beachte, dass dies nicht zufällig ist, wenn du Lücken in deiner Tabelle hast. Wenn du mehr als 1 Zeile hast, ist es einfach, da du die Id in Python erstellen und für jede Zeile eine Anfrage durchführen kannst, aber da sie zwischengespeichert sind, ist das in Ordnung.

Wenn du zu viele Lücken in deiner Tabelle hast, kannst du denselben Ansatz versuchen, jedoch jetzt für die Gesamtanzahl der Datensätze:

SELECT COUNT(*) as total FROM your_table

Dann gehst du in Python wie folgt vor:

random.randint(0, total)

Und um ein zufälliges Ergebnis abzurufen, verwendest du LIMIT wie unten gezeigt:

SELECT * 
FROM your_table 
ORDER BY id ASC 
LIMIT %s, 1

Bedenke, dass es 1 Wert nach X zufälligen Zeilen geben wird. Selbst wenn du Lücken in deiner Tabelle hast, wird es vollkommen zufällig sein, aber es wird mehr für deine Datenbank kosten.

Answer 2

Hier ist ein Game Changer, der für viele hilfreich sein könnte;

Ich habe eine Tabelle mit 200 Tausend Zeilen, mit fortlaufenden IDs, ich musste N zufällige Zeilen auswählen, also entschied ich mich, zufällige Werte basierend auf der größten ID in der Tabelle zu generieren. Ich habe dieses Skript erstellt, um herauszufinden, welche Operation am schnellsten ist:

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();

Die Ergebnisse sind:

• Anzahl: 36.8418693542479 ms
• Max: 0.241041183472 ms
• Bestellen: 0.216960906982 ms

Basierend auf diesen Ergebnissen ist die Anordnung in absteigender Reihenfolge die schnellste Operation, um die maximale ID zu erhalten,
Hier ist meine Antwort auf die Frage:

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);

Zur Info: Um 10 zufällige Zeilen aus einer 200k Tabelle zu erhalten, hat es mich 1.78 ms gekostet (einschließlich aller Operationen auf der PHP-Seite)

Answer 3

Ich benötigte eine Abfrage, um eine große Anzahl zufälliger Zeilen aus einer recht großen Tabelle zurückzugeben. Das ist, was ich entwickelt habe. Zuerst erhalte die maximale Datensatz-ID:

SELECT MAX(id) FROM table_name;

Ersetzen Sie dann diesen Wert durch:

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

Where max ist die maximale Datensatz-ID in der Tabelle und n ist die Anzahl der Zeilen, die Sie in Ihrem Ergebnisset möchten. Die Annahme ist, dass es keine Lücken in den Datensatz-IDs gibt, obwohl ich bezweifle, dass es das Ergebnis beeinträchtigen würde, wenn es welche gäbe (habe es aber nicht ausprobiert). Ich habe auch diese gespeicherte Prozedur erstellt, um allgemeiner zu sein; übergeben Sie den Tabellennamen und die Anzahl der zurückzugebenden Zeilen. Ich arbeite mit MySQL 5.5.38 auf Windows 2008, 32GB, dual 3GHz E5450, und auf einer Tabelle mit 17.361.264 Zeilen ist es ziemlich konstant bei ~.03 Sekunden / ~11 Sekunden, um 1.000.000 Zeilen zurückzugeben. (Zeiten stammen von MySQL Workbench 6.1; Sie könnten auch CEIL anstelle von FLOOR im 2. SELECT-Statement verwenden, abhängig von Ihrer Präferenz)

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
$$

dann

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

Answer 4

Sie können ganz einfach einen zufälligen Offset mit einem Limit verwenden

PREPARE stm from 'select * from table limit 10 offset ?';
SET @total = (select count(*) from table);
SET @_offset = FLOOR(RAND() * @total);
EXECUTE stm using @_offset;

Sie können auch eine where-Klausel wie folgt anwenden

PREPARE stm from 'select * from table where available=true limit 10 offset ?';
SET @total = (select count(*) from table where available=true);
SET @_offset = FLOOR(RAND() * @total);
EXECUTE stm using @_offset;

Auf 600.000 Zeilen (700 MB) getestet, dauerte die Abfrageausführung auf der Tabelle ca. 0,016 Sekunden auf einer HDD-Festplatte.

BEARBEITEN: Der Offset könnte einen Wert nahe dem Ende der Tabelle haben, was dazu führt, dass die select-Anweisung weniger Zeilen zurückgibt (oder vielleicht nur 1 Zeile), um dies zu vermeiden, können wir den offset erneut überprüfen, nachdem wir ihn deklariert haben, wie folgt

SET @rows_count = 10;
PREPARE stm from "select * from table where available=true limit ? offset ?";
SET @total = (select count(*) from table where available=true);
SET @_offset = FLOOR(RAND() * @total);
SET @_offset = (SELECT IF(@total-@_offset<@rows_count,@_offset-@rows_count,@_offset));
SET @_offset = (SELECT IF(@_offset<0,0,@_offset));
EXECUTE stm using @rows_count,@_offset;

Answer 5

Ich habe dieses http://jan.kneschke.de/projects/mysql/order-by-rand/ verwendet, veröffentlicht von Riedsio (ich habe den Fall einer gespeicherten Prozedur verwendet, die einen oder mehrere zufällige Werte zurückgibt):

   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;

In dem Artikel löst er das Problem von Lücken in IDs, die zu nicht so zufälligen Ergebnissen führen, indem er eine Tabelle pflegt (unter Verwendung von Triggern, etc...siehe den Artikel); Ich löse das Problem, indem ich eine weitere Spalte zur Tabelle hinzufüge, die mit aufeinanderfolgenden Zahlen gefüllt ist, beginnend bei 1 (Bearbeitung: diese Spalte wird zur temporären Tabelle hinzugefügt, die durch die Unterabfrage zur Laufzeit erstellt wird, und hat keinen Einfluss auf Ihre dauerhafte Tabelle):

   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;

In dem Artikel sehe ich, dass er sich große Mühe gegeben hat, den Code zu optimieren; ich habe keine Ahnung, ob/wie sehr meine Änderungen sich auf die Leistung auswirken, aber sie funktionieren sehr gut für mich.