MySQLは「order by」がどのように機能するかを簡単に理解します

並べ替えの場合、order by は非常に頻繁に使用するキーワードです。インデックスに関するこれまでの知識とこの記事を組み合わせることで、インデックスを使用してスキャンするテーブル数を減らす方法や、ソート時に外部ソートを使用する方法について深く理解できるようになります。

まず、後で理解しやすいようにテーブルを定義します。

テーブル `t` を作成します (
 `id` int(11) NULLではない、
 `city` varchar(16) NOT NULL,
 `name` varchar(16) NOT NULL,
 `age` int(11) NOT NULL,
 `addr` varchar(128) デフォルト NULL,
 主キー (`id`)、
 キー `city` (`city`)
)ENGINE=InnoDB;

クエリ文を書きます

select city,name,age from t where city= ' 杭州' order by name limit 1000 ;

上記のテーブル定義によれば、city=xxx は定義したインデックスを使用できます。しかし、名前で並べ替えるためのインデックスがないことだけは明らかです。そのため、最初にインデックスを使用して city=xxx をクエリし、次にテーブルをクエリして、最後に並べ替える必要があります。

フルフィールドソート

cityフィールドにインデックスを作成した後、実行プランを使用してこのステートメントを表示します。

インデックスがあっても、ソートが必要であることを示すために「Using filesort」を使用していることがわかります。MySQL は、ソート用に sort_buffer と呼ばれるメモリを各スレッドに割り当てます。

上記の選択文を実行すると、通常は次のようなプロセスが実行されます。

1. sort_buffer を初期化し、name、city、age の 3 つのフィールドが追加されていることを確認します。

2. インデックス city から、条件 city='Hangzhou' を満たす最初の主キー ID を見つけます。

3. テーブルを返して、name、city、age の 3 つのフィールドの値を取得し、sort_buffer に格納します。

4. インデックス city から主キー ID レコードを取得します。

5. 都市が条件を満たさなくなるまで手順 3 ～ 4 を繰り返します。

6. sort_buffer 内のデータをフィールド名で素早くソートします。

7. ソート結果の最初の 1000 行がクライアントに返されます。

これをフルフィールドソートと呼びます。

名前によるソートは、メモリ内または外部ファイルを使用して実行できます。これは sort_buffer_size に依存します。 sort_buffer_size のデフォルト値は 1048576 バイト、つまり 1M です。ソートするデータの量が 1M 未満の場合、ソートはメモリ内で実行されます。ソートするデータの量が多く、メモリに格納できない場合は、ソートを補助するために一時ディスク ファイルが使用されます。

Rowidソート

1 行が非常に大きい場合、必要なすべてのフィールドを sort_buffer に入れてもあまり効果的ではありません。

MySQL には、ソート用の行データの長さを制御するために特に使用されるパラメータ max_length_for_sort_data があります。デフォルト値は 1024 です。この値を超えると、rowid ソートが使用されます。上記の文を実行するプロセスは次のようになります。

1. sort_buffer を初期化し、name と id の 2 つのフィールドを必ず設定します。

2. インデックス city から、条件 city = 'Hangzhou' を満たす最初の主キー ID を見つけます。

3. name フィールドと id フィールドをテーブルに返し、sort_buffer に格納します。

4. 条件を満たす次のレコードを取得し、手順 2 と 3 を繰り返します。

5. sort_buffer内の名前をソートします。

6. 結果を走査し、最初の 1000 行を取得します。次に、結果フィールドが ID に従ってテーブルから再度取得され、クライアントに返されます。

実際、すべての order by ステートメントで上記の二次ソート操作が必要なわけではありません。上記で分析した実行プロセスから、次のことがわかります。 MySQL が一時テーブルを生成する必要がある理由は、以前に取得したデータが順序付けられていないため、一時テーブルを並べ替える必要があるためです。

前のインデックスを変更して結合インデックスにすると、2 番目のフィールドから取得する値が実際に順序付けられます。

結合インデックスは、最初のインデックス フィールドが等しい場合に 2 番目のフィールドが順序付けられるという条件を満たします。

これにより、(city, name) インデックスを作成すると、city='Hangzhou' を検索するときに、ターゲットの 2 番目のフィールド名が実際に正しい順序になることが保証されます。したがって、クエリ プロセスは次のように簡素化できます。

1. インデックス (city, name) から、条件 city = 'Hangzhou' を満たす最初の主キー ID を見つけます。

2. テーブルから名前、都市、年齢の 3 つの値を返します。

3. IDを取得します。

4. レコード数が 1,000 件になるか、条件「city = 'Hangzhou'」が満たされなくなるまで、手順 2 と 3 を繰り返します。

また、クエリ処理ではインデックスの順序性を利用できるため、ソートしたりソートバッファを使用したりする必要がありません。

さらなる最適化は、前述のインデックス カバレッジです。クエリする必要のあるフィールドもインデックスでカバーされ、テーブルに戻るステップが省略されるため、クエリ全体が高速化されます。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。