MySQL 8.0 のインデックススキップスキャン

序文

MySQL 8.0.13 では、インデックススキップスキャン (インデックスジャンプスキャンとも呼ばれる) のサポートが開始されました。この最適化方法は、SQL が複合インデックスの左端のプレフィックスの原則に準拠していない状況をサポートし、オプティマイザーは複合インデックスを引き続き使用できます。

口先だけは安い、コードを見せろ

練習する

公式ドキュメントの例を使用してデータを構築する

mysql> テーブル t1 を作成します (f1 INT NOT NULL、f2 INT NOT NULL、PRIMARY KEY(f1、f2));
クエリは正常、影響を受けた行は 0 行 (0.21 秒)
mysql> t1 に値 (1,1)、(1,2)、(1,3)、(1,4)、(1,5)、(2,1)、(2,2)、(2,3)、(2,4)、(2,5);
クエリは正常、10 行が影響を受けました (0.07 秒)
記録: 10 重複: 0 警告: 0
マイSQL>
mysql> t1 に挿入し、 t1 から f1、f2 + 5 を選択します。
クエリは正常、10 行が影響を受けました (0.06 秒)
記録: 10 重複: 0 警告: 0

mysql> t1 に挿入し、 t1 から f1、f2 + 10 を選択します。
クエリは正常、20 行が影響を受けました (0.03 秒)
記録: 20 重複: 0 警告: 0

mysql> t1 に挿入し、 t1 から f1、f2 + 20 を選択します。
クエリは正常、40 行が影響を受けました (0.03 秒)
レコード: 40 重複: 0 警告: 0

mysql> t1 に挿入し、 t1 から f1、f2 + 40 を選択します。
クエリは正常、80 行が影響を受けました (0.05 秒)
レコード: 80 重複: 0 警告: 0

テーブル t1 の主キーは複合インデックス (f1、f2) であることに注意してください。SQL の where 条件に左端のプレフィックス f1 が含まれていない場合、以前のバージョンでは FULL TABLE SCAN が実行されます。MySQL 8.0.20 ではどうなるのでしょうか?実行計画を見てみましょう

mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 = 40\G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t1
  パーティション: NULL
     タイプ: 範囲
可能なキー: プライマリ
     キー: PRIMARY
   キーの長さ: 8
     参照: NULL
     行数: 16
   フィルター: 100.00
    追加: where の使用; スキップスキャンにインデックスを使用する
セットに 1 行、警告 1 回 (0.01 秒)

mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40\G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t1
  パーティション: NULL
     タイプ: 範囲
可能なキー: プライマリ
     キー: PRIMARY
   キーの長さ: 8
     参照: NULL
     行数: 53
   フィルター: 100.00
    追加: where の使用; スキップスキャンにインデックスを使用する
セットに 1 行、警告 1 件 (0.00 秒)

条件 f2>40 と f2=40 の両方の 2 つの SQL ステートメントの実行プランには、スキップスキャンにインデックスの使用が含まれており、タイプは範囲です。

実行計画全体は次のようになります。

最初のスキャンはインデックスの左側から始まります
2回目は、key(1,40)を使用して、最初の範囲が終了するまでインデックスをスキャンします。key(1)、find_flag =HA_READ_AFTER_KEYを使用し、次のキー値2を検索します。
範囲が終了するまでインデックスをスキャンするには、key(2,40) を使用します。2 より大きいキー値を見つけるには、Key(2) を使用します。上記の例では、そのような値は存在しないため、スキャンは終了します。

上記の説明から、スキップスキャンを使用すると完全なインデックススキャンが回避され、パフォーマンスが向上することがわかります。

skip_scan機能がオフになっている場合、実行プランは、where フルテーブルスキャンを使用する type=all, extre になります。

mysql> セッション optimizer_switch='skip_scan=off' を設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> EXPLAIN SELECT * FROM t1 WHERE f2 = 40\G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t1
  パーティション: NULL
     タイプ: すべて
可能なキー: NULL
     キー: NULL
   キー長さ: NULL
     参照: NULL
     行数: 160
   フィルター: 10.00
    追加: where の使用
セットに 1 行、警告 1 件 (0.00 秒)

制限

1. 選択したフィールドにはインデックス以外のフィールドを含めることはできません

たとえば、c1 フィールドが複合インデックス内にある場合、select * sql はスキャンをスキップできません。

mysql> EXPLAIN SELECT * FROM t1 WHERE f2 = 40\G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t1
  パーティション: NULL
     タイプ: すべて
可能なキー: NULL
     キー: NULL
   キー長さ: NULL
     参照: NULL
     行数: 160
   フィルター: 10.00
    追加: where の使用
セットに 1 行、警告 1 件 (0.00 秒)

2. SQLにはgroup by構文やdistinct構文を含めることはできません

mysql> EXPLAIN SELECT distinct f1 FROM t1 WHERE f2 = 40\G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t1
  パーティション: NULL
     タイプ: 範囲
可能なキー: プライマリ
     キー: PRIMARY
   キーの長さ: 8
     参照: NULL
     行数: 3
   フィルター: 100.00
    追加: where の使用; group-by にインデックスを使用する
セットに 1 行、警告 1 回 (0.01 秒)

3. スキップスキャンは単一テーブルのクエリのみをサポートし、複数テーブルの関連付けには使用できません。

4. 複合インデックス ([A_1、…、A_k、] B_1、…、B_m、C [、D_1、…、D_n]) の場合、A と D は空にすることができますが、B フィールドと C フィールドは空にできません。

データベースの最適化には万能薬は存在しないことを強調することが重要です。 MySQL オプティマイザは、コストに基づいて適切な実行プランを選択します。左端のプレフィックスを無視するすべての条件付きクエリがインデックススキップスキャンを利用できるわけではありません。

例えば：

mysql> テーブル `t3` を作成します 
( id int not null auto_increment 主キー、  
`f1` int NULLではない、  
`f2` int NULLではない、 
`c1` int デフォルト '0', 
キー idx_f12(`f1`,`f2`,c1) ) 
エンジン=InnoDB デフォルト文字セット=utf8mb4;
クエリは正常、影響を受けた行は 0 行 (0.24 秒)

mysql> t3(f1,f2,c1) に挿入し、t1 から f1、f2、c1 を選択します。
クエリは正常、320 行が影響を受けました (0.07 秒)
レコード: 320 重複: 0 警告: 0

データ量は2倍の320行になります。このとき、クエリf2=40はインデックススキップスキャンを使用しません。

mysql> explain select f2 from t3 where f2=40 \G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t3
  パーティション: NULL
     タイプ: インデックス
可能なキー: idx_f12
     キー: idx_f12
   キーの長さ: 13
     参照: NULL
     行数: 320
   フィルター: 10.00
    追加: where の使用; index の使用
セットに 1 行、警告 1 件 (0.00 秒)

-終わり-

以上がMySQL 8.0のインデックススキップスキャンの詳細です。MySQL 8.0のインデックススキップスキャンの詳細については、123WORDPRESS.COMの他の関連記事をご覧ください。

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