MySQL 8.0 リファレンスマニュアル MySQL NDB Cluster 8.0 を含む
このページは機械翻訳したものです。
range
アクセスメソッドは単一のインデックスを使用して、1 つまたは複数のインデックス値間隔の中に含まれるテーブル行のサブセットを取得します。 これは、シングルパートまたはマルチパートインデックスに使用できます。 次の各セクションでは、オプティマイザが範囲アクセスを使用する条件について説明します。
単一パートインデックスの場合、インデックス値の間隔は、「intervals」 ではなく範囲条件として示される WHERE
句内の対応する条件によって便利に表現できます。
シングルパートインデックスの範囲条件の定義は次のとおりです。
BTREE
インデックスと HASH
インデックスの両方で、キー部分と定数値の比較は、=
, <=>
, IN()
, IS NULL
または IS NOT NULL
演算子を使用する場合の範囲条件です。
さらに、BTREE
インデックスでは、>
、<
、>=
、<=
、BETWEEN
、!=
、または <>
演算子、または LIKE
への引数が、ワイルドカード文字で始まっていない定数文字列である場合の LIKE
比較を使用した場合に、キーパートと定数値の比較は範囲条件です。
すべてのインデックスタイプで、OR
または AND
と組み合せた複数の範囲条件によって範囲条件が形成されます。
先述の「定数値」とは次のいずれかを意味します。
クエリー文字列からの定数
同じ結合からの const
または system
テーブルのカラム
非相関サブクエリーの結果
以前の型の部分式からのみ構成された式
以下に WHERE
句内で範囲条件を使用したクエリーのいくつかの例を示します。
SELECT * FROM t1 WHEREkey_col
> 1 ANDkey_col
< 10; SELECT * FROM t1 WHEREkey_col
= 1 ORkey_col
IN (15,18,20); SELECT * FROM t1 WHEREkey_col
LIKE 'ab%' ORkey_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
句から始めます。
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z')
nonkey = 4
と key1 LIKE '%b'
は、範囲スキャンに使用できないため、削除します。 それらを削除する正しい方法は、範囲スキャンの実行時に一致する行を見落とさないように、それらを TRUE
で置き換えることです。 これらを TRUE
の歩留まりに置き換えます:
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
常に 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')
重複する間隔を 1 つに組み合わせて、範囲スキャンに使用される最終的な条件が生成されます。
(key1 < 'bar')
一般に (前の例で示したように)、範囲スキャンに使用される条件は、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 アクセスメソッドで使用できます。
対照的に、条件
は単一の間隔を定義せず、range アクセスメソッドで使用できません。
key_part3
= 'abc'
次の説明では、マルチパートインデックスに対して、範囲条件がどのように作用するかを詳しく示します。
HASH
インデックスでは、同一の値を含む各間隔を使用できます。 これは次の形式の条件に対してのみ、間隔を生成できることを意味します。
key_part1
cmp
const1
ANDkey_part2
cmp
const2
AND ... ANDkey_partN
cmp
constN
;
ここで、const1
、const2
、… は定数で、cmp
は、=
、<=>
、または IS NULL
比較演算子のいずれかで、条件はすべてのインデックスパートをカバーします。 (つまり、N
パートインデックスの各パートに 1 つずつ N
条件があります。) たとえば、次は 3 パート HASH
インデックスの範囲条件です。
key_part1
= 1 ANDkey_part2
IS NULL ANDkey_part3
= 'foo'
何を定数とみなすかの定義については、「単一部品インデックスの範囲アクセス方法」を参照してください。
BTREE
インデックスでは、各条件で =
、<=>
、IS NULL
、>
、<
、>=
、<=
、!=
、<>
、BETWEEN
、または LIKE '
(ここで pattern
''
はワイルドカードで始まらない) を使用して、キーパートと定数値を比較する、pattern
'AND
で組み合わされた条件に、間隔を使用できます。 条件に一致するすべての行を含む単一のキータプルを判断できる場合にかぎり、1 つの間隔を使用できます (または <>
または !=
を使用する場合は 2 つの間隔)。
オプティマイザは、比較演算子が =
、<=>
、または IS NULL
である場合にかぎり、追加のキーパートを使用して、間隔を判断しようとします。 演算子が >
、<
、>=
、<=
、!=
、<>
、BETWEEN
、または LIKE
の場合、オプティマイザはそれを使用しますが、追加のキーパートは考慮しません。 次の式では、オプティマイザは最初の比較からの =
を使用します。 さらに 2 番目の比較からの >=
も使用しますが、それ以上のキーパートを考慮せず、間隔の構築に 3 番目の比較を使用しません。
key_part1
= 'foo' ANDkey_part2
>= 10 ANDkey_part3
> 10
単一の間隔は次のとおりです。
('foo',10,-inf) < (key_part1
,key_part2
,key_part3
) < ('foo',+inf,+inf)
作成された間隔に初期条件よりも多い行が含まれる可能性があります。 たとえば、前の間隔は値 ('foo', 11, 0)
を含みますが、これは元の条件を満たしません。
間隔内に含まれる行セットをカバーする条件が OR
で組み合わされている場合、それらは、それらの間隔の和集合内に含まれる行セットをカバーする条件を形成します。 条件が AND
で組み合わされている場合、それらは間隔の共通集合内に含まれる行セットを対象とする条件を形成します。 たとえば、2 パートインデックスでのこの条件の場合:
(key_part1
= 1 ANDkey_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_part1
と key_part2
が NULL
になることがあるとします。 次に、key_len
カラムに、次の条件の 2 つのキーパート長が表示されます。
key_part1
>= 1 ANDkey_part2
< 2
しかし、実際は条件が次に変換されます。
key_part1
>= 1 ANDkey_part2
IS NOT NULL
単一パートインデックスの範囲条件の間隔を結合または排除するために最適化を実行する方法の詳細は、単一部品インデックスの範囲アクセス方法 を参照してください。 マルチパートインデックスでの範囲条件にも類似の手順が実行されます。
col_name
がインデックス設定されたカラムである次の式を考慮します。
col_name
IN(val1
, ...,valN
)col_name
=val1
OR ... ORcol_name
=valN
col_name
が複数の値のいずれかと等しい場合に、各式は true になります。 これらの比較は等価範囲比較です (ここで「範囲」は単一の値です)。 オプティマイザは、次のように等価範囲比較の対象とする行の読み取りのコストを推定します。
col_name
に一意のインデックスがある場合、指定した値を持つことができる行は多くても 1 つであるため、各範囲の行の見積もりは 1 です。
それ以外の場合、col_name
のインデックスは一意ではなく、オプティマイザはインデックスまたはインデックス統計に分割して、各範囲の行数を見積もることができます。
インデックスダイブでは、オプティマイザは範囲の両端でダイブを作成し、範囲内の行数を見積もりとして使用します。 たとえば、式
には 3 つの等価範囲があり、オプティマイザは範囲あたり 2 つのダイブを作成して、行の見積もりを生成します。 ダイブのペアごとに、指定した値を持つ行数の見積もりを生成します。
col_name
IN (10, 20, 30)
インデックスダイブは、正確な行見積もりを提供しますが、式内の比較値の数が増えるほど、オプティマイザの行見積もりの生成に時間がかかるようになります。 インデックス統計の使用は、インデックスダイブより正確ではありませんが、大きな値リストの場合に、行見積もりが高速になります。
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 では、次のすべての条件を満たすクエリーでインデックス分割スキップが可能です:
クエリーは、複数のテーブルに対する結合ではなく、単一のテーブルに対するものです。
単一インデックスの FORCE INDEX
インデックスヒントが存在します。 インデックスの使用が強制される場合、インデックスへの分割を実行する追加のオーバーヘッドからは何も取得されません。
インデックスは一意ではなく、FULLTEXT
インデックスではありません。
サブクエリーが存在しません。
DISTINCT
、GROUP 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_force
は true
で、コスト計算は正確ではありません。
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 の最適化」 を参照):
最初のインデックス部分、f1
(インデックス接頭辞) の個別値間でスキップします。
残りのインデックス部分で、f2 > 40
条件の個別の接頭辞値ごとにサブレンジスキャンを実行します。
前述のデータセットの場合、アルゴリズムは次のように動作します:
最初のキー部分 (f1 = 1
) の最初の個別値を取得します。
最初と 2 番目のキー部分 (f1 = 1 AND f2 > 40
) に基づいて範囲を構築します。
範囲スキャンを実行します。
最初のキー部分の次の個別値を取得します (f1 = 2
)。
最初と 2 番目のキー部分 (f1 = 2 AND f2 > 40
) に基づいて範囲を構築します。
範囲スキャンを実行します。
この方法を使用すると、構成された範囲ごとに適格でない行が 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_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' );
オプティマイザでレンジスキャンを使用するには、クエリーが次の条件を満たす必要があります:
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
です。