wake-up-neo.net

Wie kann ich die ORDER BY Rand () - Funktion von MySQL optimieren?

Ich möchte meine Abfragen optimieren, damit ich in mysql-slow.log nachschaue.

Die meisten meiner langsamen Abfragen enthalten ORDER BY Rand(). Ich kann keine echte Lösung finden, um dieses Problem zu lösen. Es gibt eine mögliche Lösung unter MySQLPerformanceBlog , aber ich denke nicht, dass dies ausreicht. Bei schlecht optimierten (oder häufig aktualisierten, vom Benutzer verwalteten) Tabellen funktioniert es nicht oder ich muss zwei oder mehr Abfragen ausführen, bevor ich meine PHP- generierte zufällige Zeile auswählen kann.

Gibt es eine Lösung für dieses Problem?

Ein Dummy-Beispiel:

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
mysqlrandomperformance
87
fabrik

Versuche dies:

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

Dies ist besonders effizient bei MyISAM (da die COUNT(*) instant ist), aber selbst bei InnoDB ist 10 mal effizienter als ORDER BY Rand().

Die Hauptidee hierbei ist, dass wir nicht sortieren, sondern zwei Variablen beibehalten und den running probability einer Zeile berechnen, die im aktuellen Schritt ausgewählt werden soll.

Weitere Informationen finden Sie in diesem Artikel in meinem Blog:

Update:

Wenn Sie nur einen zufälligen Datensatz auswählen müssen, versuchen Sie Folgendes:

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

Dies setzt voraus, dass Ihre ac_id-Werte mehr oder weniger gleichmäßig verteilt sind.

64
Quassnoi

Es hängt davon ab, wie zufällig Sie sein müssen. Die von Ihnen verlinkte Lösung funktioniert IMO ziemlich gut. Wenn Sie keine großen Lücken im ID-Feld haben, ist das immer noch ziemlich zufällig.

Sie sollten jedoch in der Lage sein, dies in einer Abfrage zu tun (um einen einzelnen Wert auszuwählen):

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

Andere Lösungen:

  • Fügen Sie der Tabelle ein permanentes Float-Feld mit dem Namen random hinzu und füllen Sie es mit Zufallszahlen. Sie können dann in PHP eine Zufallszahl generieren und "SELECT ... WHERE rnd > $random"
  • Holen Sie sich die gesamte Liste der IDs und speichern Sie sie in einer Textdatei. Lesen Sie die Datei und wählen Sie eine zufällige ID aus.
  • Zwischenspeichern Sie die Ergebnisse der Abfrage als HTML und bewahren Sie sie einige Stunden auf.
13
DisgruntledGoat

So würde ich es machen:

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;
1
Bill Karwin

Dadurch erhalten Sie eine einzelne Unterabfrage, die den Index verwendet, um eine zufällige ID abzurufen. Die andere Abfrage wird ausgelöst, um Ihre verbundene Tabelle abzurufen.

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
)
0
Karl Mikko

(Ja, ich bekomme das Ding, weil ich nicht genug Fleisch habe, aber kann man nicht einen Tag vegan sein?)

Fall: Aufeinanderfolgendes AUTO_INCREMENT ohne Lücken, 1 Zeile zurückgegeben
Fall: Aufeinanderfolgendes AUTO_INCREMENT ohne Lücken, 10 Zeilen
Fall: AUTO_INCREMENT mit Lücken, 1 Zeile zurückgegeben
Fall: Zusätzliche FLOAT-Spalte für das Randomisieren
Fall: UUID- oder MD5-Spalte

Diese 5 Fälle können für große Tische sehr effizient gestaltet werden. Siehe mein Blog für Details.

0
Rick James

Die Lösung für Ihr Dummy-Beispiel wäre:

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

Um mehr über Alternativen zu ORDER BY Rand() zu erfahren, sollten Sie diesen Artikel lesen.

0
tereško

Ich optimiere viele bestehende Abfragen in meinem Projekt. Die Lösung von Quassnoi hat mir geholfen, die Anfragen viel zu beschleunigen! Ich finde es jedoch schwierig, die genannte Lösung in alle Abfragen zu integrieren, insbesondere für komplizierte Abfragen, die viele Unterabfragen für mehrere große Tabellen umfassen.

Ich verwende also eine weniger optimierte Lösung. Grundsätzlich funktioniert es genauso wie die Lösung von 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]ermittelt die Wahrscheinlichkeit, eine zufällige Zeile auszuwählen. Der Rand () generiert eine Zufallszahl. Die Zeile wird ausgewählt, wenn Rand () kleiner oder gleich der Wahrscheinlichkeit ist. Dies führt effektiv eine zufällige Auswahl durch, um die Tabellengröße zu begrenzen. Da die Wahrscheinlichkeit geringer ist, dass weniger als die festgelegte Anzahl von Grenzwerten zurückgegeben wird, müssen wir die Wahrscheinlichkeit erhöhen, um sicherzustellen, dass wir genügend Zeilen auswählen. Daher multiplizieren wir $ size mit einem $ -Faktor (ich setze normalerweise $ factor = 2, funktioniert in den meisten Fällen). Zum Schluss machen wir den limit $size

Das Problem besteht nun darin, die Datei accommodation_table_row_count ..__ auszuarbeiten. Wenn wir die Tabellengröße kennen, KÖNNEN wir die Tabellengröße hart codieren. Das würde am schnellsten laufen, aber das ist offensichtlich nicht ideal. Wenn Sie Myisam verwenden, ist das Abrufen der Tabellenanzahl sehr effizient. Da ich innodb verwende, mache ich nur eine einfache Zählung + Auswahl. In Ihrem Fall würde es so aussehen:

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

Der knifflige Teil ermittelt die richtige Wahrscheinlichkeit. Wie Sie sehen, berechnet der folgende Code tatsächlich nur die grobe Temp-Tabellengröße (tatsächlich zu grob!): (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category)) Sie können diese Logik jedoch verfeinern, um eine Annäherung an die Tabellengröße zu erreichen. Beachten Sie, dass es besser ist, OVER-select zu wählen als Zeilen unter- zuwählen. Wenn die Wahrscheinlichkeit zu niedrig eingestellt ist, besteht die Gefahr, dass Sie nicht genügend Zeilen auswählen.

Diese Lösung läuft langsamer als die Lösung von Quassnoi, da die Tabellengröße neu berechnet werden muss. Ich finde diese Codierung jedoch viel einfacher zu handhaben. Dies ist ein Kompromiss zwischen Genauigkeit + Leistung vs Codierkomplexität . Bei großen Tischen ist dies jedoch noch weitaus schneller als Order by Rand ().

Hinweis: Wenn die Abfragelogik dies zulässt, führen Sie die Zufallsauswahl so früh wie möglich vor allen Join-Vorgängen aus.

0
lawrenceshen