我如何优化MySQL的ORDER BY RAND()函数? [英] How can i optimize MySQL's ORDER BY RAND() function?

问题描述

我想优化查询，所以我研究了mysql-slow.log.

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

我的大多数慢查询都包含ORDER BY RAND().我找不到解决此问题的真正解决方案.在 MySQLPerformanceBlog 但我认为这还不够.在优化欠佳(或频繁更新，用户管理)的表上，该表不起作用，或者我需要运行两个或多个查询，然后才能选择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中，它的效率也比ORDER BY RAND()快10倍.

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

这里的主要思想是我们不进行排序，而是保留两个变量并计算要在当前步骤中选择的行的running probability.

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()函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！