MySQL の order by ステートメントの最適化方法の詳細な説明

この記事では、ORDER BY文の最適化について学びます。その前に、インデックスの基礎的な理解が必要です。理解していない場合は、まずは私が以前書いたインデックス関連の記事を読んでみてください。それでは始めましょう。

MySQL の 2 つのソート方法

1. 順序付けされたインデックスシーケンシャルスキャンを通じて順序付けられたデータを直接返す

インデックスの構造は B+ ツリーであるため、インデックス内のデータは特定の順序で配置され、インデックスをソートクエリで使用できる場合は追加のソート操作を回避できます。 EXPLAIN がクエリを分析すると、Extra が Using index として表示されます。

2. Filesortは返されたデータをソートする

インデックスを通じて直接ソートされた結果を返さないすべての操作は Filesort ソートであり、追加のソート操作が実行されることを意味します。 EXPLAIN がクエリを分析すると、Extra が Using filesort として表示されます。

ORDER BY最適化の基本原則

追加の並べ替えを最小限に抑え、インデックスを通じて順序付けられたデータを直接返します。

ORDER BY最適化の実践

実験に使用した顧客テーブルのインデックスは次のとおりです。

まず、以下の点にご注意ください。

MySQL では、1 つのクエリに対して 1 つのインデックスしか使用できません。複数のフィールドでインデックスを使用する場合は、複合インデックスを作成します。

ORDER BY最適化

1. クエリ対象フィールドには、このクエリで使用されるインデックス フィールドと主キーのみを含める必要があります。残りの非インデックス フィールドとインデックス フィールドでは、クエリ フィールドとしてインデックスは使用されません。

ソートに使用されるインデックス フィールドのみをクエリするには、インデックス ソートを使用できます。

explain select store_id,email from customer order by store_id,email;

ただし、ソート フィールドが複数のインデックスにある場合、インデックス ソートは使用できず、一度にクエリに使用できるインデックスは 1 つだけであることに注意してください。

explain select store_id,email,last_name from customer order by store_id,email,last_name;

ソートに使用するインデックス フィールドと主キーのみをクエリすると、インデックス ソートを使用できます。

ナレーション: MySQL のデフォルトの InnoDB エンジンは、プライマリ キーによる検索にクラスター化インデックスを物理的に使用するため、InnoDB エンジンではテーブルにプライマリ キーが必須となります。プライマリ キーが明示的に指定されていない場合でも、InnoDB エンジンは一意の暗黙的なプライマリ キーを生成します。つまり、インデックスにはプライマリ キーが必須となります。

explain select customer_id,store_id,email from customer order by store_id,email;

並べ替えに使用されるインデックス フィールドと主キー フィールド以外のフィールドをクエリすると、インデックス並べ替えは使用されません。

explain select store_id,email,last_name from customer order by store_id,email;

explain select * from customer order by store_id,email;

WHERE + ORDER BY 最適化

1. ソートフィールドが複数のインデックスに存在し、インデックスを使用してソートできない

ソート フィールドが複数のインデックス (同じインデックスではない) にあり、インデックス ソートは使用できません。

explain select * from customer where last_name='swj' order by last_name,store_id;

ナレーション: ソート フィールドが同じインデックスにない場合、B+ ツリーでソートを完了することはできず、追加のソートを実行する必要があります。

ソート フィールドがインデックス内にあり、WHERE 条件と ORDER BY が同じインデックスを使用する場合、インデックス ソートを使用できます。

explain select * from customer where last_name='swj' order by last_name;

もちろん、複合インデックスでもインデックスソートを使用できます。

store_id フィールドと email フィールドは複合インデックス内にあることに注意してください。

explain select * from customer where store_id = 5 order by store_id,email;

2. ソートフィールドの順序がインデックス列の順序と一致しておらず、インデックスソートが使用できない

ナレーション: これは複合インデックス用です。複合インデックスを使用する場合は、左端の原則に従う必要があることは周知の事実です。WHERE 句には、インデックスの最初の列が必要です。ORDER BY 句にはこの要件はありませんが、並べ替えフィールドの順序が複合インデックスの列の順序と一致することも必要です。通常、複合インデックスを使用する場合は、複合インデックス列の順序で記述する習慣を身に付ける必要があります。

ソートフィールドの順序がインデックス列の順序と一致していないため、インデックス ソートは使用できません。

explain select * from customer where store_id > 5 order by email,store_id;

インデックス ソートを活用できるように、ソート フィールドの順序がインデックス列の順序と一致していることを確認する必要があります。

explain select * from customer where store_id > 5 order by store_id,email;

ORDER BY 句ではインデックスの最初の列は必要なく、インデックスがなくてもソートに使用できます。ただし、前提条件があり、範囲でクエリを実行する場合ではなく、等しい値でフィルタリングする場合にのみ可能です。

explain select * from customer where store_id = 5 order by email;

explain select * from customer where store_id > 5 order by email;

ナレーション:

理由は実は非常に単純です。範囲クエリを実行すると、最初の列 a は確実に並べ替えられます (デフォルトでは昇順) が、2 番目のフィールド b は実際には並べ替えられません。ただし、フィールド a の値が同じ場合は、フィールド b がソートされます。したがって、範囲クエリの場合は、b に対して 1 つの追加の並べ替えのみを実行できます。

3. 昇順と降順が一致せず、インデックスを使用してソートできない

ORDER BY ソート フィールドは昇順または降順でソートする必要があります。そうでない場合、インデックス ソートは使用できません。

explain select * from customer where store_id > 5 order by store_id,email;

explain select * from customer where store_id > 5 order by store_id desc,email desc;

explain select * from customer where store_id > 5 order by store_id desc,email asc;

要約:

上記の最適化は、実際には次のように要約できます。WHERE 条件と ORDER BY は同じインデックスを使用し、ORDER BY の順序はインデックスの順序と同じであり、ORDER BY フィールドは昇順または降順です。そうでない場合は、追加のソート操作が必ず必要になり、Filesort が表示されます。

ファイルソートの最適化

適切なインデックスを作成することで Filesort の発生を減らすことができますが、場合によっては Filesort を完全に排除できないことがあります。この場合、Filesort の動作を高速化する方法を見つけるしかありません。

Filesort の 2 つのソート アルゴリズム:

1. 2スキャンアルゴリズム

まず、条件に従ってソートフィールドと行ポインタ情報が取得され、ソートバッファ内でソートされます。このソート アルゴリズムでは、データに 2 回アクセスする必要があります。1 回目はソート フィールドと行ポインター情報を取得するため、2 回目は行ポインターに基づいてレコードを取得するためです。2 回目の読み取り操作では、多数のランダム I/O 操作が発生する可能性があります。利点は、ソート時のメモリオーバーヘッドが小さいことです。

2. ワンスキャンアルゴリズム

条件を満たす行のすべてのフィールドが一度に取得され、ソート バッファーでソートされた後、結果セットが直接出力されます。ソート時のメモリオーバーヘッドは比較的大きいですが、ソート効率は 2 スキャン アルゴリズムよりも高くなります。

2 つのソート アルゴリズムの特性に応じて、システム変数 max_length_for_sort_data の値を適切に増やすと、MySQL はより最適化された Filesort ソート アルゴリズムを選択できるようになります。また、SQL ステートメントを記述するときに、SELECT * all fields ではなく、必要なフィールドのみを使用します。これにより、ソート領域の使用が減り、SQL のパフォーマンスが向上します。

要約する

上記は、編集者が紹介したMysql order by文を最適化する方法についての詳細な説明です。皆様のお役に立てれば幸いです。ご質問がある場合は、メッセージを残してください。編集者がすぐに返信します。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。