MySQL データベースのインデックス順序の詳細な説明

MySQL でのソートに関して、最初に思い浮かぶことは何でしょうか?キーワードの順序は?順序フィールドにインデックスを設定するのが最適ですか?リーフノードはすでに整っていますか?それとも、MySQL 内でのソートはできるだけ避けるべきでしょうか?

事件の原因

ここで、ユーザーの友人テーブルがあるとします。

テーブル `user` を作成します (
  `id` int(10) AUTO_INCREMENT,
  `user_id` int(10)、
  `friend_addr` varchar(1000)、
  `friend_name` varchar(100)、  
  主キー (`id`)、
  キー `user_id` (`user_id`)
)ENGINE=InnoDB;

現在、表には注意が必要な点が 2 つあります。

ユーザーのuser_id、友人の名前friend_name、友人のアドレスfriend_addr
user_idがインデックス化されている

ある日、Xiaoyuan というジュニア開発エンジニアが、Xiaowang というジュニアプロダクトマネージャーから次のようなリクエストを受けました。
シャオ・ワン: シャオユアン同志、バックグラウンドで機能を追加する必要があります。この機能は、ユーザー ID に従ってすべての友人の名前とアドレスを照会し、友人の名前を辞書に従って並べ替えることをサポートする必要があります。
小元：わかりました。この機能は簡単です。すぐにオンラインになります。

そこで、Xiaoyuan は次の SQL を記述しました。

user_id=? の user から friend_name，friend_addr を選択します。名前で並べ替えます。

あっという間に、Xiaoyuan は大々的な宣伝とともにオンラインになりました。すべて順調に進んでいたのですが、ある日、オペレーションのクラスメートが次のような質問をしました。

user_id=10086 の user から friend_name，friend_addr を選択し、名前で並び替えます

しかし、このクエリは通常よりもはるかに遅く、データベースは遅いクエリを報告しました。Xiaoyuan はパニックになりました。何が起こっているのですか? user_id にはインデックスがあり、select * の代わりに select friend_name, friend_addr のみを巧みに使用しました。この時、シャオユアンは自分を慰め続け、落ち着くように自分に言い聞かせていましたが、突然、explain コマンドがあることを思い出しました。彼は、explain を使用してその SQL の実行プランを確認することにしました。シャオユアンが explain を使用した後、彼は追加フィールドに危険そうな単語「using filesort」を見つけました。

「このクエリは実際には伝説的なファイルソートを使用していますが、友達があまりいない人の場合は、ファイルソートを使用しても高速になるはずです」と、user_id=10086に多くの友達がいない限り。その後、Xiaoyuanが確認したところ、このユーザーには実際には10万人以上の友達がいることがわかりました〜。

小さな猿は考え込んでいました。どうやら、この責任は私にあるようです。100,000 のデータポイントは多すぎます。また、filesort を使用したソートの原理とは何でしょうか?

解剖学ファイルの並べ替え

上記の問題は、10w のデータが大きすぎて、ソートされていなくても遅くなるということだと言う人がいるかもしれません。これは実際には理にかなっています。10w のデータを一度にチェックすると、MySQL メモリバッファとネットワーク帯域幅の両方が非常に消費されます。1000 の制限を追加したらどうなるでしょうか。全体的なデータパケットサイズが小さくなったため、ネットワーク帯域幅の問題は確実に解決されましたが、filesort の使用に関する問題は解決されていません。これを見ると、filesort を使用するとファイルがソートされるのかという疑問が湧くかもしれません。ファイル内ではどのように分類されますか?あるいは、こう尋ねてみましょう。ソートを設計するように依頼された場合、どのように対処しますか?これらの質問と考察を踏まえて、filesort の使用に伴う技術的な困難とその解決方法を見てみましょう。

まず、user_id がインデックス化されているので、まずは user_id インデックスツリー上の対象データ、つまり user_id=10086 のデータを検索します。ただし、friend_name フィールドと friend_addr フィールドをクエリする必要があります。残念ながら、user_id インデックスだけでは、これら 2 つのフィールドの値を見つけることはできません。
したがって、テーブルに戻って、user_id に対応する主キーを通じて主キーインデックスツリーを検索する必要があります。最初の user_id=10086 の friend_name フィールドと friend_addr フィールドが見つかりました。
今何をすればいいでしょうか？ friend_name をソートする必要があるため、直接返すのは絶対に正しくありません。どのようにソートするのでしょうか?データはまだ見つかっていないので、まずは見つかったデータを sort_buffer という 1 か所に置く必要があります。名前から推測できると思います。そうです、sort_buffer はこの場合のソートに使用されるバッファです。ここで注意すべき点は、各スレッドに個別の sort_buffer があるということです。これを行う主な目的は、複数のスレッドが同じメモリブロックで動作することによって発生するロック競合を回避することです。
最初のデータの friend_name と friend_addr が sort_buffer に格納されても、もちろんまだ終了ではなく、user_id=10086 のすべての friend_name と friend_addr が sort_buffer に格納されるまで同期手順が繰り返されます。
sort_buffer のデータがデータに入力されたので、次はそれをソートします。ここで、MySQL は friend_name に対してクイックソートを実行します。クイックソートの後、sort_buffer の friend_name は順序どおりになります。
最後に、sort_buffer の最初の 1000 項目が返され、プロセスが終了します。

すべてがスムーズに見えますが、sort_buffer はメモリ領域を占有するため、扱いにくいです。メモリ自体は無限ではなく、上限が確実にあります。もちろん、sort_buffer が小さすぎることはできません。小さすぎると、あまり意味がありません。 InnoDB ストレージエンジンでは、この値はデフォルトで 256K になります。

mysql> 'sort_buffer_size' のような変数を表示します。
+------------------+--------+
| 変数名 | 値 |
+------------------+--------+
| ソートバッファサイズ | 262144 |
+------------------+--------+

つまり、sort_buffer に入れるデータが 256K より大きい場合、sort_buffer のクイックソートメソッドは確実に機能しません。この時点で、MySQL はデータサイズに応じて自動的に拡張できないのかと疑問に思うかもしれません。さて、MySQLはマルチスレッドモデルです。各スレッドが拡張されると、他の機能に割り当てられるバッファ（変更バッファなど）が小さくなり、他の機能の品質に影響を与えます。

このとき、ソート方法を変更する必要があります。はい、これは実際のファイルソート、つまりディスク上の一時ファイルです。MySQL はマージソートの概念を使用して、ソートするデータをいくつかの部分に分割します。各データはメモリ内でソートされた後、一時ファイルに格納されます。最後に、これらのソートされた一時ファイルのデータはマージされ、再度ソートされます。これは典型的な分割統治の原則です。具体的な手順は次のとおりです。

まず、ソートするデータを sort_buffer に入れることができる部分に分割します。
sort_buffer 内の各データをソートし、ソート後に一時ファイルに書き込みます。
すべてのデータが一時ファイルに書き込まれると、各一時ファイルは整列しますが、全体が整列しているわけではなく、全体が整列していないため、次にデータを結合する必要があります。
tmpX と tmpY という 2 つの一時ファイルがあるとします。このとき、データの一部が tmpX からメモリに読み込まれ、次にデータの一部が tmpY からメモリに読み込まれます。なぜ全体や 1 つのファイルではなく一部なのか、不思議に思うかもしれません。まず、ディスクは遅いので、毎回できるだけ多くのデータをメモリに読み込むようにしてください。ただし、バッファスペースの制限があるため、読み込みすぎないようにしてください。
tmpXについては、読み込まれるのはtmpX[0-5]であると仮定し、tmpYについては、読み込まれるのはtmpY[0-5]であると仮定します。次に、次のように比較するだけです。tmpX[0] < tmpY[0]の場合、tmpX[0]が最小である必要があります。次に、tmpX[1]とtmpY[0]を比較します。tmpX[1] > tmpY[0]の場合、tmpY[0]が2番目に小さい必要があります。これらを1つずつ比較することで、最終的にtmpXとtmpYを順序付けられたファイルtmpZにマージできます。このようなtmpZファイルを複数再度マージできます。最終的に、すべてのデータを順序付けられた大きなファイルにマージできます。

ファイルのソートが非常に遅いのですが、他に解決策はありますか?

上記のソート処理を通じて、ソートするデータが非常に大きく、sort_buffer のサイズを超える場合は、ファイルソートが必要であることがわかります。ファイルソートにはバッチソートとマージが含まれ、非常に時間がかかります。この問題の根本的な原因は、sort_buffer が十分ではないことです。friend_name をソートする必要があることに気付いたかどうかはわかりませんが、friend_addr も sort_buffer に詰め込まれています。このように、1 行のデータのサイズは friend_name の長さ + friend_addr の長さに等しくなります。sort_buffer に friend_name フィールドのみを保存できますか? この方法では、全体的な使用スペースが大きくなり、一時ファイルが不要になる可能性があります。そうです、これは次に説明するもう 1 つのソート最適化、rowid ソートです。

rowidソートの考え方は、不要なデータをsort_bufferから除外し、必要なデータのみをsort_bufferに保持することです。では、必要なデータとは何だと思いますか? friend_name を入力するだけですか?これは絶対にうまくいきません。ソートが完了したら、friend_addr はどうなるのでしょうか?したがって、主キー ID も入力する必要があります。ソート後、ID を介してセカンダリテーブルに戻り、friend_addr を取得できます。したがって、一般的なプロセスは次のようになります。

user_idインデックスに従って対象データを検索し、テーブルに戻ってidとfriend_nameのみをsort_bufferに格納します。
すべてのターゲットデータがsort_bufferに入るまでステップ1を繰り返します。
sort_buffer内のデータをfriend_nameフィールドでソートする
ソート後、id に従ってテーブルを再度検索して friend_addr を見つけ、1,000 件のレコードが返されるとプロセスが終了します。

実際にここで注意すべき点がいくつかあります。

この方法では、テーブルに 2 回戻る必要があります。
sort_buffer は小さくても、データの量が大きい場合は、一時ファイルをソートする必要があります。

そこで疑問になるのが、MySQL は 2 つの方法のどちらを選択すべきかということです。どちらの方法を使用するかは、特定の条件によって決まります。条件は、sort_buffer 内の単一行の長さです。長さが大きすぎる場合 (friend_name + friend_addr の長さ)、rowid が使用されます。それ以外の場合、最初の方法では、max_length_for_sort_data に基づく長さの標準が使用されます。これは、デフォルトで 1024 バイトです。

mysql> 'max_length_for_sort_data' のような変数を表示します。
+--------------------------+-------+
| 変数名 | 値 |
+--------------------------+-------+
| ソートデータの最大長 | 1024 |
+--------------------------+-------+

テーブルに戻って再度並べ替えたくない

実際、上記のどの方法を使用する場合でも、すべてテーブルに戻って並べ替える必要があります。テーブルに戻るのは、セカンダリインデックスにターゲットフィールドがないためであり、並べ替えるのは、データが順序付けられていないためです。セカンダリインデックスにターゲットフィールドがあり、すでに並べ替えられている場合は、両方の長所を兼ね備えた方法ではないでしょうか。

そうです、それはジョイントインデックスです。(user_id、friend_name、friend_addr)のジョイントインデックスを作成するだけです。このように、このインデックスを介してターゲットデータを取得でき、friend_nameフィールドはすでにソートされています。friend_addrフィールドもあります。テーブルに戻ったり、再度ソートしたりすることなく、1回で完了します。したがって、上記の SQL の場合、一般的なプロセスは次のようになります。

ジョイントインデックスを通じてuser_id=10086のデータを見つけ、対応するfriend_nameとfriend_addrフィールドを読み取って直接返します。friend_nameはすでにソートされており、追加の処理は不要です。
最初の手順を繰り返し、10086 以外の最初のデータが見つかるまで、リーフノードに沿って逆方向に検索を続けます。

共同インデックスはこの問題を解決できますが、実際のアプリケーションでは盲目的に構築すべきではありません。実際のビジネスロジックに基づいて構築する必要があるかどうかを判断する必要があります。類似のクエリが頻繁に発生しない場合は、共同インデックスによってストレージスペースとメンテナンスコストが増加するため、構築する必要はありません。

要約する

order by ステートメントでインデックスが使用されない場合は、explain ステートメントの Extra フィールドに「using filesort」という語句が表示されます。
「using filesort」が表示されても慌てないでください。データ量が多くない場合、たとえば数十個のデータだけの場合は、ソートバッファーでクイックソートを使用すると非常に高速になります。
データ量が多く、ソートバッファのサイズを超える場合は、一時ファイルソート (マージソート) が必要になります。これは、MySQL オプティマイザによって決定されます。
クエリに多数のフィールドがあり、ソートに一時ファイルを使用しないようにしたい場合は、max_length_for_sort_data フィールドのサイズを、すべてのクエリフィールドの長さの合計よりも小さく設定してみてください。これにより、問題を回避できる可能性がありますが、テーブルを返す操作が 1 つ多く発生します。
実際のビジネスでは、頻繁にクエリされるフィールドの組み合わせに対してジョイントインデックスを作成することもできます。これにより、テーブルに戻ったり、個別に並べ替えたりする必要がなくなりますが、ジョイントインデックスはより多くのストレージとオーバーヘッドを占有します。
大量のデータをクエリする場合は、バッチでクエリを実行し、事前に説明して SQL 実行プランを確認することをお勧めします。

上記はMySQLデータベースの並べ替えの詳細な内容です。MySQLデータベースの並べ替えの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません: