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

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

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

以下に 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 の抽出プロセスは次のとおりです。

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

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

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

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

たとえば、key1(key_part1、key_part2、key_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 アクセスメソッドで使用できません。

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

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

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

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

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

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

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

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

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

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

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

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

インデックス分割がスキップされるクエリーの実行後、INFORMATION_SCHEMA.OPTIMIZER_TRACE テーブルの対応する行に skipped_due_to_force_index の index_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 の最適化」 を参照):

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

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

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

スキップスキャンの使用は、オプティマイザトレース出力で次の形式の"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_scan を off に設定します。

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' );

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

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

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

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

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