問題描述

我想優化我的查詢，所以我查看了 mysql-slow.log.

I'd like to optimize my queries so I look into mysql-slow.log.

我的大多數慢查詢都包含 ORDER BY RAND().我找不到解決此問題的真正解決方案.MySQLPerformanceBlog<有一個可能的解決方案/a> 但我認為這還不夠.在優化不佳(或經常更新、用戶管理)的表上，它不起作用，或者我需要運行兩個或多個查詢才能選擇我的 PHP 生成的隨機行.

Most of my slow queries contains ORDER BY RAND(). I cannot find a real solution to resolve this problem. Theres is a possible solution at MySQLPerformanceBlog but I don't think this is enough. On poorly optimized (or frequently updated, user managed) tables it doesn't work or I need to run two or more queries before I can select my PHP-generated random row.

這個問題有什么解決辦法嗎?

Is there any solution for this issue?

一個虛擬的例子:

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

推薦答案

試試這個:

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

這在 MyISAM 上特別有效(因為 COUNT(*) 是即時的)，但即使在 InnoDB 中它也是 10 比 ORDER BY RAND() 效率高幾倍.

This is especially efficient on MyISAM (since the COUNT(*) is instant), but even in InnoDB it's 10 times more efficient than ORDER BY RAND().

這里的主要思想是我們不排序，而是保留兩個變量并計算當前步驟要選擇的行的運行概率.

The main idea here is that we don't sort, but instead keep two variables and calculate the running probability of a row to be selected on the current step.

有關詳細信息，請參閱我博客中的這篇文章:

See this article in my blog for more detail:

• 選擇隨機行

更新:

如果你只需要選擇一個隨機記錄，試試這個:

If you need to select but a single random record, try this:

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_id 的分布或多或少是均勻的.

This assumes your ac_id's are distributed more or less evenly.

這篇關于如何優化 MySQL 的 ORDER BY RAND() 函數?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！