Comment améliorer les performances ORDER BY RAND() de MySQL pour les grands ensembles de données ?

Comment améliorer les performances ORDER BY RAND() de MySQL

La fonction ORDER BY RAND() de MySQL peut s'avérer inefficace pour les grands ensembles de données, conduisant à une exécution lente des requêtes. Pour résoudre ce problème, il est crucial de plonger dans le journal des requêtes lentes de MySQL pour obtenir des informations.

Inefficacité avec ORDER BY RAND()

Requêtes contenant ORDER BY RAND() dominent souvent les journaux de requêtes lents. La solution proposée par MySQLPerformanceBlog peut suffire dans des conditions spécifiques. Cependant, les tableaux mal optimisés ou gérés par l'utilisateur présentent des défis qui nécessitent des mesures plus efficaces.

Solution : éviter le tri

La solution la plus optimale consiste à éviter complètement le tri. Nous pouvons y parvenir en employant une technique qui calcule la probabilité de sélection d’une ligne. Voici une requête qui exploite cette approche :

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

Cette technique est très efficace dans les bases de données MyISAM et fournit également des améliorations significatives des performances dans InnoDB.

Sélection d'un seul enregistrement aléatoire

Pour les scénarios impliquant la sélection d'un seul enregistrement aléatoire, considérez ce qui suit requête :

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

Cette requête suppose une distribution uniforme des valeurs ac_id.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!