Hoe kan ik de ORDER BY RAND()-functie van MySQL optimaliseren?

Ik wil mijn zoekopdrachten optimaliseren, dus ik kijk naar mysql-slow.log.

De meeste van mijn langzame zoekopdrachten bevatten ORDER BY RAND(). Ik kan geen echte oplossing vinden om dit probleem op te lossen. Er is een mogelijke oplossing op MySQLPerformanceBlogmaar ik denk niet dat dit genoeg is. Op slecht geoptimaliseerde (of vaak bijgewerkte, door de gebruiker beheerde) tabellen werkt het niet of ik moet twee of meer zoekopdrachten uitvoeren voordat ik mijn door PHPgegenereerde willekeurige rij kan selecteren.

Is er een oplossing voor dit probleem?

Een dummy voorbeeld:

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

Antwoord 1, autoriteit 100%

Probeer dit:

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

Dit is vooral efficiënt op MyISAM(aangezien de COUNT(*)direct is), maar zelfs in InnoDBis het 10keer efficiënter dan ORDER BY RAND().

Het belangrijkste idee hier is dat we niet sorteren, maar in plaats daarvan twee variabelen behouden en de running probabilityberekenen van een rij die moet worden geselecteerd op de huidige stap.

Zie dit artikel in mijn blog voor meer details:

• Willekeurige rijen selecteren

Bijwerken:

Als je maar één willekeurig record hoeft te selecteren, probeer dan dit:

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

Dit gaat ervan uit dat uw ac_id‘s min of meer gelijkmatig zijn verdeeld.

Antwoord 2, autoriteit 18%

Het hangt ervan af hoe willekeurig je moet zijn. De oplossing die je hebt gelinkt, werkt redelijk goed IMO. Tenzij je grote gaten in het ID-veld hebt, is het nog steeds vrij willekeurig.

U zou het echter in één query moeten kunnen doen door dit te gebruiken (voor het selecteren van een enkele waarde):

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

Andere oplossingen:

• Voeg een permanent zwevend veld met de naam randomtoe aan de tabel en vul deze met willekeurige getallen. Je kunt dan een willekeurig getal genereren in PHP en "SELECT ... WHERE rnd > $random"

doen

• Pak de volledige lijst met ID’s en cache ze in een tekstbestand. Lees het bestand en kies er een willekeurige ID uit.
• Cache de resultaten van de zoekopdracht als HTML en bewaar deze een paar uur.

Antwoord 3

Zo zou ik het doen:

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;

Antwoord 4

(Ja, ik zal worden berispt omdat ik hier niet genoeg vlees heb, maar kun je niet een dag veganist zijn?)

Case: opeenvolgende AUTO_INCREMENT zonder gaten, 1 rij geretourneerd
Case: opeenvolgende AUTO_INCREMENT zonder gaten, 10 rijen
Case: AUTO_INCREMENT met hiaten, 1 rij geretourneerd
Case: Extra FLOAT-kolom voor randomisatie
Case: UUID- of MD5-kolom

Die 5 koffers kunnen zeer efficiënt worden gemaakt voor grote tafels. Zie mijn blogvoor de details.

Antwoord 5

Dit geeft je een enkele subquery die de index gebruikt om een ​​willekeurige id te krijgen, en de andere query wordt geactiveerd om je samengevoegde tabel te krijgen.

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
)

Antwoord 6

De oplossing voor uw dummy-voorbeeld zou zijn:

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

Als je meer wilt lezen over alternatieven voor ORDER BY RAND(), lees dan dit artikel.

Antwoord 7

Ik optimaliseer veel bestaande query’s in mijn project. De oplossing van Quassnoi heeft me enorm geholpen de vragen te versnellen! Ik vind het echter moeilijk om de genoemde oplossing in alle zoekopdrachten op te nemen, vooral voor gecompliceerde zoekopdrachten met veel subquery’s op meerdere grote tabellen.

Dus ik gebruik een minder geoptimaliseerde oplossing. In wezen werkt het op dezelfde manier als de oplossing van 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]berekent de waarschijnlijkheid van het kiezen van een willekeurige rij. De rand() genereert een willekeurig getal. De rij wordt geselecteerd als rand() kleiner is dan of gelijk is aan de kans. Dit voert effectief een willekeurige selectie uit om de tafelgrootte te beperken. Aangezien er een kans is dat het minder teruggeeft dan het gedefinieerde limietaantal, moeten we de kans vergroten om ervoor te zorgen dat we voldoende rijen selecteren. Daarom vermenigvuldigen we $size met een $factor (ik stel meestal $factor = 2 in, werkt in de meeste gevallen). Eindelijk doen we de limit $size

Het probleem is nu het berekenen van de accomodation_table_row_count.
Als we de tafelgrootte weten, KUNNEN we de tafelgrootte hard coderen. Dit zou het snelst lopen, maar dit is natuurlijk niet ideaal. Als u Myisam gebruikt, is het zeer efficiënt om tafeltellingen te krijgen. Omdat ik innodb gebruik, doe ik gewoon een eenvoudige telling + selectie. In jouw geval ziet het er als volgt uit:

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

Het lastige is om de juiste kans te berekenen. Zoals je kunt zien, berekent de volgende code eigenlijk alleen de ruwe grootte van de tijdelijke tabel (eigenlijk te ruw!): (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category))Maar u kunt deze logica verfijnen om een ​​nauwkeurigere benadering van de tabelgrootte te geven. Merk op dat het beter is om te OVER-selecteren dan om rijen te weinig te selecteren. d.w.z. als de kans te laag is ingesteld, loopt u het risico niet genoeg rijen te selecteren.

Deze oplossing werkt langzamer dan de oplossing van Quassnoi, omdat we de tabelgrootte opnieuw moeten berekenen. Ik vind deze codering echter een stuk beter beheersbaar. Dit is een afweging tussen nauwkeurigheid + prestatiesversus coderingscomplexiteit. Dat gezegd hebbende, is dit aan grote tafels nog steeds veel sneller dan Order by Rand().

Opmerking: als de querylogica het toelaat, voer de willekeurige selectie dan zo vroeg mogelijk uit voordat u verbinding maakt.

Antwoord 8

Mijn aanbeveling is om een ​​kolom toe te voegen met een UUID (versie 4) of een andere willekeurige waarde, met een unieke index (of alleen de primaire sleutel).

Vervolgens kunt u eenvoudig een willekeurige waarde genereren tijdens de zoekopdracht en rijen selecteren die groter zijn dan de gegenereerde waarde, gerangschikt op willekeurige kolom.

Zorg ervoor dat als u minder dan het verwachte aantal rijen ontvangt, u de zoekopdracht herhaalt zonder de groter dan-clausule (om rijen aan het “begin” van de resultatenset te selecteren).

uuid = generateUUIDV4()
select * from foo
where uuid > :uuid
order by uuid
limit 42
if count(results) < 42 {
  select * from foo
  order by uuid
  limit :remainingResultsRequired
}

Antwoord 9

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