8.2.1.3 range の最適化

range アクセスメソッドは単一のインデックスを使用して、1 つまたは複数のインデックス値間隔の中に含まれるテーブル行のサブセットを取得します。これは、シングルパートまたはマルチパートインデックスに使用できます。次のセクションでは、WHERE 句から間隔を抽出する方法について詳しく説明します。

8.2.1.3.1 シングルパートインデックスの range アクセスメソッド

シングルパートインデックスでは、インデックス値間隔は WHERE 句内の対応する条件によって便利に表すことができるため、間隔よりも範囲条件について説明します。

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

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

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

  • すべての種類のインデックスで、OR または AND で組み合わされた複数の範囲条件は、1 つの範囲条件を形成します。

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

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

  • 同じ結合からの 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 に入れます。

8.2.1.3.2 マルチパートインデックスの range アクセスメソッド

マルチパートインデックスの範囲条件は、シングルパートインデックスの範囲条件の拡張です。マルチパートインデックスの範囲条件は、インデックス行を 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'
    

    何を定数とみなすかの定義については、「セクション8.2.1.3.1「シングルパートインデックスの range アクセスメソッド」」を参照してください。

  • 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
    

セクション8.2.1.3.1「シングルパートインデックスの range アクセスメソッド」」では、単一パートインデックスで、範囲条件の間隔を組み合わせたり、削除したりするために、どのように最適化が実行されるかを説明しています。マルチパートインデックスでの範囲条件にも類似の手順が実行されます。

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

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

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

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

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

  • そうでない場合は、オプティマイザは、インデックスのダイブまたはインデックス統計を使用して、各範囲の行数を推定できます。

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

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

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

eq_range_index_dive_limit は MySQL 5.6.5 以降で使用できます。5.6.5 より前では、オプティマイザは eq_range_index_dive_limit=0 と同等のインデックスダイブを使用します。

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


User Comments
Sign Up Login You must be logged in to post a comment.