mysqlのソートの違い

MySQL のソート モードについて、浅いものから深いものまで詳しく説明します。それが MySQL のさまざまなソート モードの選択にどのような影響を与えるか、そしてソートを最適化する方法について説明します。

推奨コース: MySQL チュートリアル 。

# ソートはデータベースの基本機能であり、MySQL も例外ではありません。

ユーザーは、Order by ステートメントを使用して指定された結果セットを並べ替えることができます。実際、Order by ステートメントだけでなく、Group by ステートメントと Distinct ステートメントでも暗黙的に並べ替えが使用されます。この記事では、まず SQL がソートのコストを回避するためにどのようにインデックスを使用するかを簡単に紹介し、次にソートを実装するための MySQL の内部原理を紹介します。

みんなの次の質問に対する解決策:

MySQL はどこでソートを使用しますか?MySQL がソートを使用していることをどのように判断するか;

MySQL にはいくつかのソート モードがあり、その方法は何ですか? MySQL はさまざまなソート モードを選択します;

MySQL のソートと read_rnd_buffer_size の関係は何ですか、どのような状況で read_rnd_buffer_size を増やすとソートが最適化されますか;

MySQL がソートにディスクを使用するかどうかを判断する方法とその方法回避または最適化するには ディスクのソート;

ソート中に可変長フィールド (varchar) データがメモリに格納される方法と、5.7 での改善点;

この場合、どのような改善点があるかソート モードにはありますか?

sort_merge_pass とは正確には何ですか?ステータス値が大きすぎる場合、どのような問題が示されますか?どうすれば解決できますか?

MySQL がソートを使用する場合、どのような方法があるでしょうか?を使用して分析および最適化し、並べ替えを高速化できますか?

2. ソート

Explain を通じて MySQL 実行プランを表示すると、[Extra] 列に [Using filesort] が表示されることがよくあります。

ソートを回避するためにインデックスを使用できない SQL の場合、ユーザーのニーズに合わせてデータベース自体がソート機能を実装する必要があります。このとき、SQL 実行プランには「ファイルソートを使用する」と表示されます。ここでの filesort は、これがファイルのソートであることを意味するものではありません。実際、メモリのソートである可能性もあります。これは主に、sort_buffer_size パラメータと結果セットのサイズによって決まります。

実際、この状況は MySQL がソートを使用していることを示しています。 filesort の使用は、多くの場合、order by、group by、distinct、join などで表示されます。

MySQL の内部でソートを実装するには、通常のソート、最適化されたソート、および優先キューのソートという 3 つの主な方法があります。

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

これら 3 つの並べ替えの違いを見てください:

a. 従来の並べ替え

(1 ) .テーブル t1

(2) から WHERE 条件を満たすレコードを取得します。レコードごとに、レコードの主キー ソート キー (id、col2) を取り出し、ソート バッファ##に入れます。

#(3 ). ソートバッファーに条件を満たすすべての (id,col2) ペアを格納できる場合はソートし、そうでない場合はソートバッファーがいっぱいになったときにソートして一時ファイルに固めます。 (ソート アルゴリズムはクイック ソート アルゴリズムを使用します)

(4). ソート中に一時ファイルが生成される場合は、マージ ソート アルゴリズムを使用して、一時ファイル内のレコードが正しい順序であることを確認する必要があります

( 5). 条件を満たすすべてのレコードがソートに関与するまで上記のプロセスをループで実行します

(6). ソートされた (id,col2) ペアをスキャンし、id を使用しますSELECT で返す必要がある列を取得します (col1,col2,col3)

(7)。取得した結果セットをユーザーに返します。

上記のプロセスから、ファイルの並べ替えを使用するかどうかは、主に、並べ替えが必要な (id,col2) ペアを並べ替えバッファーが収容できるかどうかによって決まります。このバッファーのサイズは、sort_buffer_size パラメーターによって制御されます。さらに、並べ替えには 2 つの IO が必要です。1 つは (id,col2) を取得することであり、2 つ目は (col1、col2、col3) を取得することです。返された結果セットは、col2 で並べ替えられているため、ID の順序が狂っています。 ID で (col1、col2、col3) をフィッシングすると、大量のランダム IO が生成されます。 2 番目の MySQL 自体の最適化は、ID を取得する前に ID を並べ替えてバッファに入れることであり、このバッファのサイズはパラメータ read_rnd_buffer_size によって制御され、その後、順序立ててレコードを取得し、ランダム IO をシーケンシャル IO に変換します。

b. 最適化された並べ替え

従来の並べ替え方法では、並べ替え自体に加えて 2 つの追加 IO が必要です。従来のソートと比較して、最適化されたソート方法により 2 回目の IO が削減されます。主な違いは、ソートバッファーが (id,col2) ではなく (col1,col2,col3) であることです。並べ替えバッファにはクエリに必要なフィールドがすべて含まれているため、並べ替えの完了後にデータを再度取得することなく直接返すことができます。この方法のコストは、同じサイズのソート バッファーに格納できる (col1、col2、col3) の数が (id、col2) よりも小さいことです。ソート バッファーが十分に大きくない場合、一時ファイルが作成される可能性があります。書き込む必要があるため、追加の IO が発生します。もちろん、MySQL には max_length_for_sort_data というパラメータが用意されており、ソートされたタプルが max_length_for_sort_data より小さい場合にのみ最適化されたソート方法が使用でき、それ以外の場合は従来のソート方法のみが使用できます。

c. 優先キューのソート

最終的な並べ替え結果を取得するには、条件を満たすすべてのレコードを並べ替えてから返す必要があります。では、並べ替え方法の最適化と比較して、まだ最適化の余地はあるのでしょうか?バージョン 5.6 では、Order by limit M, N ステートメントが空間レベルで最適化され、ヒープ ソートを使用して実装される新しいソート方法である優先キューが追加されました。ヒープ ソート アルゴリズムの特性により、制限 M, N のソート問題を解決できます。ソートに参加するにはすべての要素が必要ですが、ソート バッファー スペースの M N タプルのみが必要です。M と N が小さいシナリオの場合、基本的には必要ありませんが、ソートバッファ不足によりマージソートに一時ファイルが必要になる場合があります。昇順の場合、大きな上部ヒープが使用され、最終ヒープの要素が最小 N 要素を形成します。降順の場合、小さな上部ヒープが使用され、最終ヒープの要素が最大 N 要素を形成します。

以上がmysqlのソートの違いの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。