如何使用 ORDER BY RAND() 最佳化 MySQL 查詢？

MySQL RAND() 排序最佳化

ORDER BY RAND() 的緩慢查詢帶來了效能挑戰。

問題

ORDER 查詢BY RAND() 通常會導致效能不佳，特別是在大型或頻繁更新的表上。 MySQL 的解決方案（MySQLPerformanceBlog）可能不夠。

解

要最佳化RAND() 排序，請考慮以下方法：

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) <p>這種方法透過在迭代資料時保持運行機率來避免排序，使其比ORDER BY更有效率RAND().</p><p><strong>對於單行選擇</strong></p><p>要選擇單一隨機行，請嘗試以下查詢：</p><pre class="brush:php;toolbar:false">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
        )
        )

此最佳化假設ac_ids 分佈較均勻。

以上是如何使用 ORDER BY RAND() 最佳化 MySQL 查詢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！