MySQL 8.0 リファレンスマニュアル


8.2.1.2 range の最適化

range アクセスメソッドは単一のインデックスを使用して、1 つまたは複数のインデックス値間隔の中に含まれるテーブル行のサブセットを取得します。 これは、シングルパートまたはマルチパートインデックスに使用できます。 次の各セクションでは、オプティマイザが範囲アクセスを使用する条件について説明します。

単一部品インデックスの範囲アクセス方法

単一パートインデックスの場合、インデックス値の間隔は、intervals ではなく範囲条件として示される WHERE 句内の対応する条件によって便利に表現できます。

シングルパートインデックスの範囲条件の定義は次のとおりです。

  • BTREE インデックスと HASH インデックスの両方で、キー部分と定数値の比較は、=, <=>, IN(), IS NULL または IS NOT NULL 演算子を使用する場合の範囲条件です。

  • さらに、BTREE インデックスでは、><>=<=BETWEEN!=、または <> 演算子、または LIKE への引数が、ワイルドカード文字で始まっていない定数文字列である場合の LIKE 比較を使用した場合に、キーパートと定数値の比較は範囲条件です。

  • すべてのインデックスタイプで、OR または AND と組み合せた複数の範囲条件によって範囲条件が形成されます。

先述の定数値とは次のいずれかを意味します。

  • クエリー文字列からの定数

  • 同じ結合からの const または system テーブルのカラム

  • 非相関サブクエリーの結果

  • 以前の型の部分式からのみ構成された式

以下に WHERE 句内で範囲条件を使用したクエリーのいくつかの例を示します。

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

一部の非定数値は、オプティマイザ定数伝播フェーズで定数に変換できます。

MySQL は可能なインデックスごとに、WHERE 句から範囲条件を抽出しようとします。 抽出プロセス時に、範囲条件の構築に使用できない条件はドロップされ、重複する範囲を生成する条件は組み合わされて、空の範囲を生成する条件は削除されます。

key1 がインデックス設定されたカラムで nonkey がインデックス設定されていない、次のステートメントを考慮します。

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

キー key1 の抽出プロセスは次のとおりです。

  1. 元の WHERE 句から始めます。

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
    (key1 < 'bar' AND nonkey = 4) OR
    (key1 < 'uux' AND key1 > 'z')
  2. nonkey = 4key1 LIKE '%b' は、範囲スキャンに使用できないため、削除します。 それらを削除する正しい方法は、範囲スキャンの実行時に一致する行を見落とさないように、それらを TRUE で置き換えることです。 これらを TRUE の歩留まりに置き換えます:

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
    (key1 < 'bar' AND TRUE) OR
    (key1 < 'uux' AND key1 > 'z')
  3. 常に true または false である条件を縮小します。

    • (key1 LIKE 'abcde%' OR TRUE) は常に true です

    • (key1 < 'uux' AND key1 > 'z') は常に false です

    これらの条件を定数に置き換えると、次のようになります:

    (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

    不要な TRUE 定数および FALSE 定数を削除すると、次のようになります:

    (key1 < 'abc') OR (key1 < 'bar')
  4. 重複する間隔を 1 つに組み合わせて、範囲スキャンに使用される最終的な条件が生成されます。

    (key1 < 'bar')

一般に (前の例で示したように)、範囲スキャンに使用される条件は、WHERE 句より制限がゆるくなります。 MySQL は、範囲条件を満たすが、完全な WHERE 句でない行をフィルタ処理する追加のチェックを実行します。

範囲条件抽出アルゴリズムは、任意の深さのネストの AND/OR 構造を処理でき、その出力は WHERE 句内の条件が存在する順番に依存しません。

MySQL では、空間インデックスの range アクセス方法に対する複数の範囲のマージはサポートされていません。 この制限を回避するには、同じ SELECT ステートメントで UNION を使用できますが、ただし、各空間述語は、別の SELECT に入れます。

マルチパートインデックスの範囲アクセス方法

マルチパートインデックスの範囲条件は、シングルパートインデックスの範囲条件の拡張です。 マルチパートインデックスの範囲条件は、インデックス行を 1 つまたは複数のキータプル間隔内に入るように制限します。 キータプル間隔は、インデックスからの順序付けを使用して、キータプルのセットに定義されます。

たとえば、key1(key_part1key_part2key_part3) として定義されたマルチパートインデックスと、キー順で示された次のキータプルのセットを考慮します。

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件 key_part1 = 1 は次の間隔を定義します。

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

間隔は前のデータセットの 4、5、6 番目のタプルをカバーし、range アクセスメソッドで使用できます。

対照的に、条件 key_part3 = 'abc' は単一の間隔を定義せず、range アクセスメソッドで使用できません。

次の説明では、マルチパートインデックスに対して、範囲条件がどのように作用するかを詳しく示します。

  • HASH インデックスでは、同一の値を含む各間隔を使用できます。 これは次の形式の条件に対してのみ、間隔を生成できることを意味します。

        key_part1 cmp const1
    AND key_part2 cmp const2
    AND ...
    AND key_partN cmp constN;

    ここで、const1const2、… は定数で、cmp は、=<=>、または IS NULL 比較演算子のいずれかで、条件はすべてのインデックスパートをカバーします。 (つまり、N パートインデックスの各パートに 1 つずつ N 条件があります。) たとえば、次は 3 パート HASH インデックスの範囲条件です。

    key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

    何を定数とみなすかの定義については、「単一部品インデックスの範囲アクセス方法」を参照してください。

  • BTREE インデックスでは、各条件で =<=>IS NULL><>=<=!=<>BETWEEN、または LIKE 'pattern' (ここで 'pattern' はワイルドカードで始まらない) を使用して、キーパートと定数値を比較する、AND で組み合わされた条件に、間隔を使用できます。 条件に一致するすべての行を含む単一のキータプルを判断できる場合にかぎり、1 つの間隔を使用できます (または <> または != を使用する場合は 2 つの間隔)。

    オプティマイザは、比較演算子が =<=>、または IS NULL である場合にかぎり、追加のキーパートを使用して、間隔を判断しようとします。 演算子が ><>=<=!=<>BETWEEN、または LIKE の場合、オプティマイザはそれを使用しますが、追加のキーパートは考慮しません。 次の式では、オプティマイザは最初の比較からの = を使用します。 さらに 2 番目の比較からの >= も使用しますが、それ以上のキーパートを考慮せず、間隔の構築に 3 番目の比較を使用しません。

    key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

    単一の間隔は次のとおりです。

    ('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)

    作成された間隔に初期条件よりも多い行が含まれる可能性があります。 たとえば、前の間隔は値 ('foo', 11, 0) を含みますが、これは元の条件を満たしません。

  • 間隔内に含まれる行セットをカバーする条件が OR で組み合わされている場合、それらは、それらの間隔の和集合内に含まれる行セットをカバーする条件を形成します。 条件が AND で組み合わされている場合、それらは間隔の共通集合内に含まれる行セットを対象とする条件を形成します。 たとえば、2 パートインデックスでのこの条件の場合:

    (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

    間隔は次のとおりです。

    (1,-inf) < (key_part1,key_part2) < (1,2)
    (5,-inf) < (key_part1,key_part2)

    この例で、1 行目の間隔は、左境界に 1 つのキーパートを使用し、右境界に 2 つのキーパートを使用しています。 2 行目の間隔は 1 つのキーパートのみを使用しています。 EXPLAIN 出力の key_len カラムは、使用されたキープリフィクスの最大長を示しています。

    場合によって、key_len はキーパートが使用されたことを示しますが、それが予期したものではないことがあります。 key_part1key_part2NULL になることがあるとします。 次に、key_len カラムに、次の条件の 2 つのキーパート長が表示されます。

    key_part1 >= 1 AND key_part2 < 2

    しかし、実際は条件が次に変換されます。

    key_part1 >= 1 AND key_part2 IS NOT NULL

単一パートインデックスの範囲条件の間隔を結合または排除するために最適化を実行する方法の詳細は、単一部品インデックスの範囲アクセス方法 を参照してください。 マルチパートインデックスでの範囲条件にも類似の手順が実行されます。

複数値比較の等価範囲の最適化

col_name がインデックス設定されたカラムである次の式を考慮します。

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

col_name が複数の値のいずれかと等しい場合に、各式は true になります。 これらの比較は等価範囲比較です (ここで範囲は単一の値です)。 オプティマイザは、次のように等価範囲比較の対象とする行の読み取りのコストを推定します。

  • col_name に一意のインデックスがある場合、指定した値を持つことができる行は多くても 1 つであるため、各範囲の行の見積もりは 1 です。

  • それ以外の場合、col_name のインデックスは一意ではなく、オプティマイザはインデックスまたはインデックス統計に分割して、各範囲の行数を見積もることができます。

インデックスダイブでは、オプティマイザは範囲の両端でダイブを作成し、範囲内の行数を見積もりとして使用します。 たとえば、式 col_name IN (10, 20, 30) には 3 つの等価範囲があり、オプティマイザは範囲あたり 2 つのダイブを作成して、行の見積もりを生成します。 ダイブのペアごとに、指定した値を持つ行数の見積もりを生成します。

インデックスダイブは、正確な行見積もりを提供しますが、式内の比較値の数が増えるほど、オプティマイザの行見積もりの生成に時間がかかるようになります。 インデックス統計の使用は、インデックスダイブより正確ではありませんが、大きな値リストの場合に、行見積もりが高速になります。

eq_range_index_dive_limit システム変数を使用して、オプティマイザが行の見積もり戦略を別の戦略に切り替える値の数を構成できます。 最大 N 個の等価範囲の比較にインデックスダイブの使用を許可するには、eq_range_index_dive_limitN + 1 に設定します。 統計の使用を無効にし、N に関係なく常にインデックスダイブを使用するには、eq_range_index_dive_limit を 0 に設定します。

最適な推定を行うためにテーブルインデックス統計を更新するには、ANALYZE TABLE を使用します。

MySQL 8.0 より前では、eq_range_index_dive_limit システム変数を使用する場合を除き、インデックス dives の使用をスキップしてインデックスの有用性を見積もる方法はありません。 MySQL 8.0 では、次のすべての条件を満たすクエリーでインデックス分割スキップが可能です:

  • クエリーは、複数のテーブルに対する結合ではなく、単一のテーブルに対するものです。

  • 単一インデックスの FORCE INDEX インデックスヒントが存在します。 インデックスの使用が強制される場合、インデックスへの分割を実行する追加のオーバーヘッドからは何も取得されません。

  • インデックスは一意ではなく、FULLTEXT インデックスではありません。

  • サブクエリーが存在しません。

  • DISTINCTGROUP BY または ORDER BY 句が存在しません。

EXPLAIN FOR CONNECTION の場合、index dives がスキップされると、出力は次のように変更されます:

  • 従来の出力では、rows および filtered の値は NULL です。

  • JSON 出力の場合、rows_examined_per_scan および rows_produced_per_join は表示されず、skip_index_dive_due_to_forcetrue で、コスト計算は正確ではありません。

FOR CONNECTION を使用しない場合、インデックス dives がスキップされても、EXPLAIN 出力は変更されません。

インデックス分割がスキップされるクエリーの実行後、INFORMATION_SCHEMA.OPTIMIZER_TRACE テーブルの対応する行に skipped_due_to_force_indexindex_dives_for_range_access 値が含まれます。

スキャン範囲アクセス方法のスキップ

次のシナリオを考えてみます:

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

このクエリーを実行するために、MySQL では、インデックススキャンを選択してすべての行をフェッチし (インデックスには選択するすべてのカラムが含まれます)、WHERE 句から f2 > 40 条件を適用して最終結果セットを生成できます。

レンジスキャンは全インデックススキャンよりも効率的ですが、最初のインデックスカラムである f1 に条件がないため、この場合は使用できません。 ただし、MySQL 8.0.13 の時点では、オプティマイザは、ループインデックススキャンと同様のスキップスキャンという方法を使用して、f1 の値ごとに複数のレンジスキャンを実行できます (セクション8.2.1.17「GROUP BY の最適化」 を参照):

  1. 最初のインデックス部分、f1 (インデックス接頭辞) の個別値間でスキップします。

  2. 残りのインデックス部分で、f2 > 40 条件の個別の接頭辞値ごとにサブレンジスキャンを実行します。

前述のデータセットの場合、アルゴリズムは次のように動作します:

  1. 最初のキー部分 (f1 = 1) の最初の個別値を取得します。

  2. 最初と 2 番目のキー部分 (f1 = 1 AND f2 > 40) に基づいて範囲を構築します。

  3. 範囲スキャンを実行します。

  4. 最初のキー部分の次の個別値を取得します (f1 = 2)。

  5. 最初と 2 番目のキー部分 (f1 = 2 AND f2 > 40) に基づいて範囲を構築します。

  6. 範囲スキャンを実行します。

この方法を使用すると、構成された範囲ごとに適格でない行が MySQL によってスキップされるため、アクセスされる行の数が減ります。 このスキップスキャンアクセス方法は、次の条件下で適用できます:

  • 表 T には、フォーム ([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n]) のキー部分を持つ複合インデックスが少なくとも 1 つあります。 キー部分 A および D は空でもかまいませんが、B および C は空でない必要があります。

  • クエリーは 1 つのテーブルだけを参照します。

  • クエリーでは、GROUP BY または DISTINCT は使用されません。

  • クエリーは、インデックス内のカラムのみを参照します。

  • A_1、...、A_ k の述語は等価述語であり、定数である必要があります。 これには、IN() 演算子が含まれます。

  • クエリーは結合クエリー (OR 条件の AND) である必要があります: (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

  • C には範囲条件が必要です。

  • D カラムに対する条件は許可されています。 D の条件は、C の範囲条件と組み合せる必要があります。

スキップスキャンの使用は、EXPLAIN 出力で次のように示されます:

  • Extra カラムの Using index for skip scan は、ルーズインデックススキップスキャンアクセス方法が使用されていることを示します。

  • インデックスをスキップスキャンに使用できる場合は、インデックスが possible_keys カラムに表示されます。

スキップスキャンの使用は、オプティマイザトレース出力で次の形式の"skip scan"要素によって示されます:

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

"best_skip_scan_summary"要素も表示される場合があります。 最適なレンジアクセスバリアントとしてスキャンのスキップを選択すると、"chosen_range_access_summary"が書き込まれます。 全体的な最適なアクセス方法としてスキャンのスキップが選択されている場合は、"best_access_path"要素が存在します。

スキップスキャンの使用は、optimizer_switch システム変数の skip_scan フラグの値の影響を受けます。 セクション8.9.2「切り替え可能な最適化」を参照してください。 デフォルトでは、このフラグは on です。 無効にするには、skip_scanoff に設定します。

optimizer_switch システム変数を使用してスキップスキャンセッション全体のオプティマイザの使用を制御することに加えて、MySQL ではオプティマイザヒントをサポートしてステートメントごとにオプティマイザに影響を与えます。 セクション8.9.3「オプティマイザヒント」を参照してください。

行コンストラクタ式の範囲最適化

オプティマイザは、レンジスキャンアクセス方法を次の形式のクエリーに適用できます:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前は、レンジスキャンを使用するには、次のようにクエリーを記述する必要がありました:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

オプティマイザでレンジスキャンを使用するには、クエリーが次の条件を満たす必要があります:

  • IN() 述語のみが使用され、NOT IN() は使用されません。

  • IN() 述語の左側では、行コンストラクタにはカラム参照のみが含まれます。

  • IN() 述語の右側にある行コンストラクタには、実行中に定数にバインドされるリテラルまたはローカルカラム参照であるランタイム定数のみが含まれます。

  • IN() 述語の右側には、複数の行コンストラクタがあります。

オプティマイザおよび行コンストラクタの詳細は、セクション8.2.1.22「行コンストラクタ式の最適化」 を参照してください

範囲最適化のためのメモリー使用の制限

範囲オプティマイザで使用可能なメモリーを制御するには、range_optimizer_max_mem_size システム変数を使用します:

  • 値 0 は 制限なしを表します。

  • 0 より大きい値を指定すると、オプティマイザは範囲アクセス方法を考慮する際に消費されるメモリーを追跡します。 指定した制限を超えると、レンジアクセス方法が破棄され、全テーブルスキャンなどの他の方法がかわりに考慮されます。 これは最適ではない可能性があります。 これが発生すると、次の警告が発生します (N は現在の range_optimizer_max_mem_size 値です):

    Warning    3170    Memory capacity of N bytes for
                       'range_optimizer_max_mem_size' exceeded. Range
                       optimization was not done for this query.
  • UPDATE および DELETE ステートメントでは、オプティマイザが全テーブルスキャンにフォールバックし、sql_safe_updates システム変数が有効になっている場合、変更する行の決定にキーが使用されないため、警告ではなくエラーが発生します。 詳細は、セーフ更新モードの使用 (--safe-updates)を参照してください。

使用可能な範囲最適化メモリーを超え、オプティマイザがより最適でない計画にフォールバックする個々のクエリーの場合、range_optimizer_max_mem_size 値を増やすとパフォーマンスが向上する可能性があります。

範囲式の処理に必要なメモリー量を見積もるには、次のガイドラインを使用します:

  • 範囲アクセス方法の候補キーが 1 つある次のような単純なクエリーの場合、OR と組み合された各述語では約 230 バイトが使用されます:

    SELECT COUNT(*) FROM t
    WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
  • 同様に、次のようなクエリーでは、AND と組み合された各述語で約 125 バイトが使用されます:

    SELECT COUNT(*) FROM t
    WHERE a=1 AND b=1 AND c=1 ... N;
  • IN() 述語を含むクエリーの場合:

    SELECT COUNT(*) FROM t
    WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

    IN() リストの各リテラル値は、OR と組み合された述語としてカウントされます。 2 つの IN() リストがある場合、OR と組み合せた述語の数は、各リストのリテラル値の数になります。 したがって、前述の例で OR と組み合されている述語の数は、M× N です。


関連キーワード:  key, インデックス, 範囲, 条件, OR, テーブル, オプティマイザ, range, クエリー, 間隔