MySQL でランダム抽出を実装する方法

1. はじめに

今度は、単語リストから一度に 3 つの単語をランダムに選択するという要件があります。

このテーブルのテーブル作成ステートメントは次のとおりです:

mysql> Create table 'words'(
    'id' int(11) not null auto_increment;
    'word' varchar(64) default null;
    primary key ('id')
) ENGINE=InnoDB;

次に、そこに 10,000 行のデータを挿入します。次に、その中からランダムに 3 つの単語を選択する方法を見てみましょう。

2. メモリ一時テーブル

まず、通常、このロジックを実装するには、order by rand() を使用することを考えます:

mysql> select word from words order by rand() limit 3;

この文は非常に単純ですが、しかし、実行プロセスはより複雑です。 Explain を使用してステートメントの実行を確認します。

「Extra」フィールドで「temporary」を使用すると、一時テーブルを使用する必要があることを示し、「filesort を使用」は並べ替えが必要であることを示します。 。つまり、ソート操作が必要となる。

InnoDB テーブル の場合、フィールド全体の並べ替えを実行するとディスク アクセスが軽減されるため、これが推奨されます。

#メモリ テーブルの場合、テーブルを返すプロセスは単にメモリに直接アクセスして、データ行の位置に基づいてデータを取得するだけであり、結果は発生しません。複数のディスクへのアクセスはまったくありません。したがって、現時点では MySQL は ROWID ソートを優先します。

このステートメントの実行プロセスを整理しましょう:

• 一時テーブルを作成します。このテーブルはメモリを使用します。 Engine では、テーブルに 2 つのフィールドがあります。最初のフィールドは double 型で、R とマークされ、2 番目のフィールドは varchar(64) 型で、W とマークされています。そして、このテーブルにはインデックスがありません。

• 単語テーブルから主キー順にすべての単語を取り出します。単語ごとに、 rand() 関数を呼び出して、0 より大きく 1 未満のランダムな 10 進数をランダムに生成し、そのランダムな 10 進数と単語を一時テーブルの R フィールドと W フィールドにそれぞれ保存します。

• 次のステップは、フィールド R

• sort_buffer に従って並べ替えることです。 sort_buffer には double 型と整数フィールドが含まれます。

• 一時メモリテーブルから R 値と位置情報を 1 行ずつ取り出し、sort_buffer の 2 つのフィールドにそれぞれ格納します。

• sort_buffer は R 値に従ってソートされます

• ソートが完了すると、最初の 3 つの結果の位置情報が取り出され、そして、対応する情報がメモリ一時テーブルから取り出され、単語がクライアントに返されます。

プロセス図は次のとおりです。

上記の位置情報は、実際には行の位置です。つまり、これは前に説明した ROWID です。

InnoDB エンジンの場合、主キーの有無にかかわらず、テーブルに対して 2 つの処理方法があります。

• 主キーのある

  InnoDB テーブルの場合たとえば、この ROWID は主キー IDです。

• 主キーのない

  InnoDB テーブルの場合、この ROWID はシステムによって生成され、さまざまな行を識別するために使用されます。 。

したがって、

order by randn() はメモリ一時テーブルを使用し、メモリ一時テーブルのソート方法には rowid ソート方法

を使用します。 3. ディスク一時テーブル

すべての一時テーブルがメモリ一時テーブルであるわけではありません

。 tmp_table_size 設定により、メモリ一時テーブルのサイズが制限されます。このサイズを超えると、ディスク一時テーブルが使用されます。 InnoDB エンジンはデフォルトでディスク一時テーブルを使用します。 4. プライオリティ キュー ソート アルゴリズム

MySQL5.6 以降、

プライオリティ キュー ソート アルゴリズムが導入され、一時ファイルを使用する必要はありません。元のマージ ソート アルゴリズムでは、一時ファイルの使用が必要です。

マージ アルゴリズムを使用する場合、実際には上位 3 つを取得するだけで済みますが、マージ ソートが不足すると、全体がすでに整っていて、リソースの無駄が発生するためです。

プライオリティ キュー ソート アルゴリズムでは、上位 3 つだけを取得できます。実行プロセスは次のとおりです:

これらの 10,000 (R、rowid) をソートするには、上位 3 つが最初に取得されます。3 つの行がヒープに構築され、最大値がヒープの先頭に配置されます。

• 次の行 (R’, rowid’) を取得します。現在のヒープ内の最大の R と比較します。R' が R より小さい場合は、(R, rowid) をヒープから削除し、(R', rowid') に置き換えます。

• 上記のプロセスを繰り返します。

• プロセスは次の図に示されています。

ただし、制限数が比較的大きい場合、ヒープを維持するため、マージ ソート アルゴリズムが使用されます。

以上がMySQL でランダム抽出を実装する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。