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

Wenn Sie einen zufälligen Datensatz möchten (unabhängig davon, ob Lücken zwischen den IDs vorhanden sind):

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

EXECUTE stmt USING @count;

Quelle: https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/#comment-1266

Answer 2

Dies ist super schnell und zu 100% zufällig, auch wenn Sie Lücken haben.

1. Zählen Sie die Anzahl x der verfügbaren Zeilen mit SELECT COUNT(*) as rows FROM TABLE
2. Wählen Sie 10 verschiedene Zufallszahlen a_1,a_2,...,a_10 zwischen 0 und x
3. Abfragen Sie Ihre Zeilen wie folgt: SELECT * FROM TABLE LIMIT 1 offset a_i für i=1,...,10

Ich habe diesen Hack im Buch SQL Antipatterns von Bill Karwin gefunden.

Answer 3

Das Folgende sollte schnell, unvoreingenommen und unabhängig von der ID-Spalte sein. Es garantiert jedoch nicht, dass die Anzahl der zurückgegebenen Zeilen mit der angeforderten Anzahl übereinstimmt.

SELECT *
FROM t
WHERE RAND() < (SELECT 10 / COUNT(*) FROM t)

Erklärung: Angenommen, Sie möchten 10 Zeilen aus 100 erhalten, dann hat jede Zeile eine 1/10-ige Wahrscheinlichkeit, ausgewählt zu werden, was durch WHERE RAND() < 0.1 erreicht werden kann. Dieser Ansatz garantiert nicht 10 Zeilen; aber wenn die Abfrage oft genug ausgeführt wird, wird die durchschnittliche Anzahl von Zeilen pro Ausführung ungefähr 10 betragen und jede Zeile in der Tabelle wird gleichmäßig ausgewählt.

Answer 4

Wenn Sie nur eine Leseanfrage haben

Kombinieren Sie die Antwort von @redsio mit einer temp-Tabelle (600K ist nicht so viel):

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;

Und dann nehmen Sie eine Version von @redsios Antwort:

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;

Wenn die Tabelle groß ist, können Sie im ersten Teil sieben:

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

Wenn Sie viele Leseanfragen haben

1. Version: Sie könnten die Tabelle tmp_randorder persistent halten, nennen Sie sie datatable_idlist. Erstellen Sie diese Tabelle in bestimmten Intervallen neu (Tag, Stunde), da sie auch Löcher bekommen wird. Wenn Ihre Tabelle wirklich groß wird, könnten Sie auch Löcher nachfüllen

   select l.data_id as whole from datatable_idlist l left join datatable dt on dt.id = l.data_id where dt.id is null;

2. Version: Geben Sie Ihrem Datensatz eine random_sortorder-Spalte entweder direkt in datatable oder in einer persistenten zusätzlichen Tabelle datatable_sortorder. Indexieren Sie diese Spalte. Generieren Sie einen Zufallswert in Ihrer Anwendung (Ich nenne ihn $rand).

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

Diese Lösung diskriminiert die 'Randreihen' mit der höchsten und der niedrigsten random_sortorder, daher sollten Sie sie in Intervallen neu anordnen (einmal am Tag).

Answer 5

Eine weitere einfache Lösung wäre das Ranking der Zeilen und das zufällige Abrufen einer davon. Mit dieser Lösung benötigen Sie keine Spalte mit 'Id' in der Tabelle.

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;

Sie können den Limit-Wert entsprechend Ihren Anforderungen ändern, um auf so viele Zeilen wie gewünscht zuzugreifen, aber das wären meist aufeinander folgende Werte.

Wenn Sie jedoch keine aufeinander folgenden zufälligen Werte möchten, können Sie eine größere Stichprobe abrufen und zufällig auswählen. So etwas wie ...

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;