Last Updated on December 8, 2019

I believe that is absolutely random, but I could be wrong

Relational Database from a random rows in random order is very hard problem.REはランダム行を返す方法ですが、これは非常に難しい質問です。 この記事では、アンチパターンを見て、なぜこの問題が難しいのかを議論し、いくつかの不完全なソリューションを検証します。

UPDATE: いくつかの DBMS は TABLESAMPLE をサポートしています。 今日まで SQL Server の実装しか知らず、ランダムな行の抽出には向いていないと考えていたため、言及しませんでした (その理由は後ほど説明します)。 しかし、Daniël van EedenによるFacebookでの質問の後、私はいくつかの調査を行い、PostgreSQLの実装がより適している可能性があることを発見しました。 PostgreSQLの実装については、もう少し詳しく調べてから、改めて記事にする予定です。 とにかく、MySQLとMariaDBはTABLESAMPLE.

をサポートしていない。 ORDER BY RAND()

多くの開発者は、この方法でできると考えています:

SELECT some_columns FROM some_table ORDER BY RAND() LIMIT 10;

これは、期待通りの結果が得られる唯一のクエリです。 RAND() 関数が確実にランダムである場合、ランダムな行のセットを返し、各行が返される確率は同じになります。 ランダムな値を順序付けるには、最初にそれらを作成する必要があります。 テーブルの各行に対してランダムな値を作成するために、DBMSは各行を読み込む必要があります。

  1. Copy the rows plus a random value to a temporary table, hopefully in memory;
  2. Order the rows in the temporary table.

Note that this detail: hopefully in memory.DBMS will do a two steps operation:

  1. 行とランダム値をメモリ上の一時テーブルにコピーする。 DBMSによっては、テーブルをディスク上に作成する必要がある理由があるかもしれません。 一般的に、これはテーブルが大きすぎる場合に起こります。 古い MySQL バージョンでは、TEXT または BLOB カラムを含む場合、ディスク上に一時テーブルを作成します。

    言うまでもなく、テーブルが大きい場合、これは多くのリソースを消費する遅い操作になります。 これは真偽の述語を扱うので論理学に基づいており、たとえ関係が集合よりも複雑であっても集合論に類似しており、テーブルとして見ることさえ数学的に正しくありません。 この記事は私が言いたいことの良い例ですが、もっと複雑な議論もあります。

    SQL は関係モデルの正確な実装ではないにもかかわらず、その代数は参照として使用されます。 数学のすべての分野と同様に、SQL には明確に定義された操作のセットがあります。 関係からランダムなタプルを取得することは、間違いなくそのうちの 1 つではありません。

    この回答は、少し理論的すぎると思いませんか? ORDER BY また、主キーのないテーブルも関係代数の規則に反していますが、SQLはそれらをサポートしています。

    しかし、データベースにとって、スピードはきわめて重要です。 リレーショナルDBMSはリレーショナル操作を非常に高速に実行できるように設計されている。 明らかに、ある操作に最適化されたデータ構造は、他の操作には最適化されない。 つまり、リレーショナルDBMSはランダム検索には最適化されていません。

    行をループしてそのうちのいくつかをランダムに選択するのが遅いのは明らかでしょう。 理論的には、インデックスを使用してランダムな行を読み取ることは、主キー値によって行を見つけるのと同じくらい高速になります。 しかし、その場合、異なる行が選択される可能性があります。

    上の画像は Jeremy Cole の GitHub リポジトリ、 innodb_diagrams からのものです。 遅かれ早かれ、InnoDB データ構造を分析する彼のプロジェクトについて書くことになるでしょう。

    ここで重要なのは、インデックスがメモリ ページをノードとするツリーであるということです。 リーフ ノードには実際のデータが含まれ、上位レベルには目的のリーフ ノードへの最短経路を選択するために必要な情報のみが含まれます。 詳細はこの文脈では重要ではありません。

    DBMS は理論的には、左か右のどちらに進むかといった一連のランダムな決定を実行することにより、ランダムなページを選択することができます。 しかし、その場合、リーフページには可変数の行が含まれます。 空である可能性さえあります。 そのため、異なる行が選択される可能性があります。

    この結果は明らかに DBMS が意図する使用例から外れており、インデックスが使用されるかどうかによって、遅くなったり、間違ったりする可能性があります。 DBMS がこの機能を実装しないことを選択したのは正しいことです。

    Some solutions

    完璧な解決策は存在しません。 受け入れられるが欠点がある解決策もある。

    Choosing randomly from a range

    このソリューションは、オートインクリメンタル主キーを持つテーブルに適用できます。 これは、最小 ID、最大 ID をチェックし、その範囲内で乱数を生成します。

    他の句を使用せずに、任意のインデックスで MIN() と MAX() を使用したクエリが非常に高速であるわけではありません。 複数の行を選択するためには、この問い合わせを複数回繰り返せばよい。

    • この操作が頻繁に実行される場合、このクエリを何度も実行することができます。
    • 同じ行が複数回選択されないという保証はありません。 このような場合、アプリケーションはクエリを再試行することがあります。 テーブルが大きい場合、このイベントはまれであり、RAND()関数が信頼できない場合を除き、無視することができます。

    また、主キーに穴があいている、つまり、いくつかの数値が欠落していることがあります。 これは削除やトランザクションの失敗によるものです。 最大値より小さい数値は再利用されない。 この問題を回避するには、2 つのテクニックを使用します。

    >=

    WHERE 節で >= 演算子を使用すると、クエリーは常に行を返します。 生成された番号が存在しない場合、次の番号が使用されます。 しかし、これは穴の直後の値が選択される可能性が高くなることを意味します。 穴が大きければ大きいほど、チャンスは高くなります。

    あなたはこのことを気にするべきではないと思うかもしれませんし、おそらくそのとおりでしょう。 しかし、決定が確定する前に、大きな穴が開く可能性を考慮してください。

    努力する

    クエリが行を返さなかった場合、再試行すればいいのです。 小さな穴がいくつかあるだけの大きなテーブルの場合、これはまったく問題ありません。 しかし、多くの穴がある大きな穴、または大きな穴の場合、この手法はあまりにも多くのリソースを消費する可能性があります。 ただ、少し計算してみてください。 あなたのテーブルの欠損値の比率は何ですか。 この手法で複数行を選択するには、次のようにします。

    • クエリを複数回繰り返す。

    Moving the random choice to the application

    最も単純な形式では、この手法は連続した ID のブロックをランダムに選択することから構成されます。 アプリケーションは、使用するものをランダムに選択し、一致する行を選択します。

    1000 個の値のブロックから選択したいとします。 アプリケーションには、ブロックからランダムに一意の既存の ID を選択する責任があるので、クエリを再試行する必要は決してないはずです。

    MOD variant

    この手法の問題は、行が連続した ID の同じブロックから返されることです。 これは、特にブロックが大きい場合、完全に受け入れられる可能性があります。

    • Define macro-blocks of mostly non-contiguous rows;
    • Randomly choose one macro-block;
    • Return a block from it.But if it is not disclosed, we could add some complexity to select a block of non-contiguous rows.

      The idea of this variant is to:

        【マクロブロック】は、ほとんど連続しない行から構成されます。

    これを行うには、id のモジュラスを計算し、それを使ってランダムに結果を選択することができます。 例えば、(id MOD 4)のような式でインデックス付きの仮想カラムを定義することができる。 各行は0から3の間の値を持つことになる。 穴があっても、これらのマクロブロックからの行はほとんど非連続で、ほぼ同じ数になるはずです。

    アプリケーションでは、0から3の間の乱数を生成する必要があります。 

    SET @block_size := 1000;SELECT id FROM some_table WHERE id_mod = FLOOR(RAND() * 3) ORDER BY id LIMIT @block_size;

    このアイデアを実装する同様の方法として、ID のモジュラスをパーティショニング関数とするパーティションを使用することができます。 テーブルが他の理由でパーティショニングの恩恵を受ける場合にのみ、これをお勧めします。

    手法の選択

    ブロック手法は、>= 演算子を使用した最初の手法と比較してより多くのクエリーを実行し、結果は良くなるかどうかわかりません。 一般的に、主キーに穴がある場合、ブロック技法はより優れています。

    Caching random series

    以前の方法は、パフォーマンスとより良いランダム性のトレードオフであり、より速いソリューションは実装がより複雑です。 ORDER BY RAND() のアンチパターンを使用しない限り、ランダムな行を選択してもパフォーマンスの問題は発生しないはずです。 しかし、もしそうだとしたらどうでしょう。

    以前のソリューションのいずれかで得られた、ランダムにソートされた ID をキャッシュすることができます。 次のようなクエリを使用できます (MySQL 構文):

    ランダムな昇順または降順でシリーズを使用できます。 または、アプリケーションでシャッフルすることもできます。

    • 項目が追加されたとき、ランダムな項目を置き換えて、シリーズに挿入する必要があるかどうかをランダムに選択する必要があります。
    • ユーザー エクスペリエンスにおけるランダム性を向上させるために、最も古いシリーズを定期的に削除し、新しいものに置き換えることもできます。

    結論

    いつものように、この記事で間違いを見つけた場合、何かに反対する場合、個人の経験を教えてくれたり、追加するアイデアがある場合、コメントして私に知らせてください。

By: adminPosted on

コメントを残す

メールアドレスが公開されることはありません。