インデックススキャンを使用したMySQLソート

sakilaをインストールする

MySQL サンプル データベース sakila を使用して、SQL のデモンストレーションと説明を行います (dev.mysql.com/doc/sakila/…)。

インデックススキャンソート

MySQL には、並べ替え操作による方法とインデックス順序でのスキャンによる方法の 2 つの方法で順序付けられた結果を生成します。EXPLAIN によって出力される type 列の値が「index」の場合、MySQL は並べ替えにインデックス スキャンを使用していることを意味します。
インデックス自体のスキャンは、1 つのインデックス レコードから次のレコードに移動するだけなので高速です。ただし、インデックスがクエリに必要なすべての列をカバーしていない場合は、インデックス レコードをスキャンするたびに、テーブルに戻って対応する行をクエリする必要があります。これは本質的にランダム I/O であるため、インデックス順序でデータを読み取ると、特に IO 集中型のワークロードでは、順次的なフル テーブル スキャンよりも遅くなるのが一般的です。この時点では、インデックス検索の代わりに完全なテーブルスキャンが使用される場合があります。
可能であれば、インデックスは行の並べ替えと検索の両方に対応するように設計する必要があります。
MySQL は、インデックス列の順序が ORDER BY 句の順序とまったく同じであり、すべての列のソート方向 (逆または順) が同じである場合にのみ、インデックスを使用して結果をソートできます。クエリで複数のテーブルを結合する必要がある場合、ORDER BY 句で参照されるすべてのフィールドが最初のテーブルのものである場合にのみ、インデックスを並べ替えに使用できます。 ORDER BY 句の制限は検索クエリの制限と同じです。インデックスの左端のプレフィックスを満たす必要があります。そうでない場合、MySQL はソート操作 (filesort) を実行する必要があり、インデックス ソートを使用できません。

テーブル構造

レンタルテーブルを使って説明します

テーブル「レンタル」を作成します（
  
  一意のキー `rental_date` (`rental_date`,`inventory_id`,`customer_id`)、
  キー `idx_fk_inventory_id` (`inventory_id`)、
  キー `idx_fk_customer_id` (`customer_id`)、
  キー `idx_fk_staff_id` (`staff_id`)、
  
) ENGINE=InnoDB AUTO_INCREMENT=16050 デフォルトCHARSET=utf8mb4;

Extra に Using filesort が表示されているかどうかを確認します (MySQL ではインデックスを使用して完了できないソート操作は「ファイル ソート」と呼ばれます)。インデックスなしでフィールドをソートしようとすると、filesort になります。中にファイルがありますが、ファイルとは何の関係もありません。実際は内部のクイックソートです。

インデックススキャンをソートに使用できる状況

先頭の列に記入してください

ORDER BY 句がインデックスの左端のプレフィックス要件を満たす必要がないケースが 1 つあります。つまり、先頭の列が定数である場合です。 WHERE 句または JOIN 句でこれらの列に定数を指定すると、インデックスの不足を「補う」ことができます。 Sakilaデータベースを使用してテストします

見ることができます

本の中のExtraではUsing whereと書いてあるのですが、実行してみたところUsing index conditionを使ってしまいました。理由は、高性能MySQLで使われているバージョンが5.5であり、バージョン5.6でのindex condition pushdownがまだ正式リリースされていない段階だからです。ここでファイルソートが行われない理由は、rental_date = '2005-05-25' という定数条件があるためです。これは、インデックスの最初の列を埋めることと同等であり、インデックスの左端のプレフィックス要件を満たします。

並べ替えには1つの並べ替えのみが含まれます

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id desc

見ることができます

この本で使用されている条件は rental_date>'2005-05-25' であることに注意してください。

WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id

現時点では、インデックス ソートを使用することはできませんが、テーブル全体を直接スキャンしてソートします。その理由は、返されるデータ項目が多すぎるため、現時点ではインデックス クエリを使用するのはコスト効率が良くないからです。

ここでの説明の行数は正確ではなく、あくまでも推定値であることに注意してください。実際には、この条件によると、クエリには16036のデータ項目があります。この問題を解決するには、制限を追加する必要があります。

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id limit 0,10

対応する実行計画

インデックスが使用されていることがわかります

インデックススキャンが使用できない状況

クエリ条件に異なる並べ替え方向が含まれています

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id desc, customer_id asc

インデックスの両方の列は昇順になっています。順序付けでは、1 つの列が昇順で、もう 1 つの列が降順になっているため、2 次ソートが必要になります。

クエリ条件はインデックスにない列を参照しています

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY inventory_id ,staff_id

左端の接頭辞を結合できない場合

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY customer_id

最初の列がクエリ範囲の場合

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-08-22' ORDER BY inventory_id,customer_id

複数の同等の条件がある場合

SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' and inventory_id in(1,2) ORDER BY customer_id

簡単に言えば、インデックスの左端のプレフィックスに一致しないものがソートされます。

要約する

今日は、MySQL のインデックス スキャン ソートについて説明しました。明日は、引き続き、高パフォーマンス インデックスを構築する他の方法について紹介します。次回の記事もお楽しみに!

上記はMySQLインデックススキャンの簡単な使用方法の詳細内容です。MySQLインデックススキャンソートの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。