大規模なデータセットに対する MySQL の ORDER BY RAND() のパフォーマンスを向上させる方法は?

MySQL の ORDER BY RAND() パフォーマンスを向上させる方法

MySQL の ORDER BY RAND() 関数は、大規模なデータセットに対して非効率であることが判明する可能性があります。クエリの実行が遅くなる原因となります。この問題に対処するには、MySQL の低速クエリ ログを調べて洞察を得ることが重要です。

ORDER BY RAND() の非効率

ORDER BY RAND() を含むクエリ多くの場合、遅いクエリのログが大半を占めます。 MySQLPerformanceBlog が提供する提案されたソリューションは、特定の条件下では十分な場合があります。ただし、最適化が不十分なテーブルやユーザー管理のテーブルには、より効果的な対策が必要な課題が存在します。

解決策: 並べ替えの回避

最も最適な解決策は、並べ替えを完全に回避することです。これは、行の選択確率を計算する手法を採用することで実現できます。このアプローチを利用するクエリは次のとおりです。

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 データベースで非常に効率的であり、InnoDB でもパフォーマンスが大幅に向上します。

単一のランダム レコードの選択

単一のランダム レコードの選択を伴うシナリオの場合は、次のことを考慮してください。 query:

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 値が均等に分布していることを前提としています。

以上が大規模なデータセットに対する MySQL の ORDER BY RAND() のパフォーマンスを向上させる方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。