MySQLで高パフォーマンスに特定のテーブルからランダムなレコードを取得する

本章では、MySQLで高パフォーマンスにランダムなデータを取得するためのtipsについて紹介します。

背景

今回のクエリを作る背景に、業務で特定条件のデータをランダムかつ高速に取得したいという要望がありました。
データベースは、MySQLでVerが5.7とwindow関数が使えない状況です。また一部のテーブルのデータ件数は、億を超えていて効率よいクエリを記述する必要がありました。

今回のクエリの重要な観点

ランダムなデータを取得する

まずは、ランダムなデータを取得する方法です。
実現するための簡単な方法の一つは、 RAND 関数を用いてソートする方法です。しかし対象のテーブルのデータ件数が多い場合、並び替えに膨大な時間がかかるため、この手法は使えません。
そこで一般的なテーブル設計にある、 primary key の数値型の id をランダムに範囲取得する方法を採用します。任意のレコードを対象のテーブルの id の最小値を RAND 、 MIN と MAX  関数で計算し取得します。もし対象のテーブルに複数のインデックスが貼られていた場合、取得したいカラムと検索条件のカラムに限定したインデックスを使用するように強制することで、パフォーマンスよくデータを抽出できます。
以下サンプルクエリです。

SELECT a.aa, a.bb, a.cc, a.dd FROM hoge AS a
INNER JOIN (SELECT CEIL(RAND() * (MAX(id) - MIN(id))) + MIN(id) AS minId  -- 抽出対象のidの最小値をランダム関数を使って取る
                       FROM hoge USE INDEX (IDX__hoge__aa, IDX__hoge__bb)
                       -- hogeテーブルに数多くのインデックスが貼られていた場合、検索条件に指定されているカラムと抽出したいカラムだけを対象にインデックスを強制させることで、パフォーマンスが向上する場合がある
                       WHERE aa = ? AND bb = ?) AS b
                      ON  a.id >= b.minId AND a.aa = ? AND a.bb = ?

テーブルを結合して、結合先のテーブル情報を利用する

次に、テーブルを結合して、結合先のカラムを参照する際のtipです。
外部のテーブルを参照する際に、参照先のテーブルで他のテーブルと結合して条件を指定する場合があります。その際に、テーブルを単純に結合し続けると、参照されるカラムの数が増え続けるため、パフォーマンスに影響があります。そこで、テーブルを結合し続けるのではなく、カラムとして参照されないテーブルは、EXISTS句を利用して条件を指定します。
以下サンプルクエリです。

LEFT OUTER JOIN
foo AS c
ON a.id = c.id
AND EXISTS(
              SELECT *
              FROM fuga AS d
              WHERE d.hogeId = c.id AND d.zzz = ?
         )

筆休め

SQLは、大量のデータを同時に、抽出や編集加工ができます。ただ効率よく速度が早い実現方法については、通常のプログラムと考え方が違う工夫が求められます。対象SQL言語の特性を踏まえ、解決法を考えるのはパズルのようで楽しいです。

以上、「MySQLで高パフォーマンスに特定のテーブルからランダムなレコードを取得する」でした。