MySQL 内部オプティマイザー

オプティマイザー

この記事では、MySQL クエリ オプティマイザーがどのように機能するかについて説明します。 MySQL クエリ オプティマイザーは主に、実行されたクエリの最も効率的なルート (ルーチン、方向) を決定します。

1つ。ソース コードと概念

このパートでは、オプティマイザーの主要な概念と用語、およびそれらが MySQL ソース コードにどのように対応するかについて説明します。

1. 定義

狭義の定義: オプティマイザーは、クエリ時にどの実行パスを使用するかを決定するために DBMS が使用する一連のルートです。

MySQL はクエリ ルートを調整することが多いため、この記事で説明されているロジックとソース コード内のロジックを比較する必要があります。比較を容易にするために、source code/sql/sql_select.cc、optimize_cond() 関数など、関連するファイルとパスの注釈がここに含まれます。

クエリが別のクエリに変換され、結果が同じ場合、ステートメント変換が発生します。次のクエリ

SELECT ... WHERE 5 = a
は

SELECT ... WHERE a = 5
に変換されます。最も明白なステートメント変換はより少なく、一部のステートメント変換はより高速な実行を目的としています。

2. オプティマイザーのソースコード

次の疑似コードは、/sql/sql_select.cc の handle_select() 関数の論理構造を示しています。 (ソースコード/sql/sql_select.cc は SQL クエリを処理します)

handle_select()
mysql_select()
JOIN::PRepare()
setup_fields()
JOIN::optimize() /* オプティマイザーはここから... * /
Optimize_cond ()
OPT_SUM_QUERY ()
Make_join_statistics ()
get_quick_record_count ()
chooose_plan ()
/ * P を受け入れる最適な方法を見つける / * ユーザーの指定に従って * /
Optimize_straight_join ()
BEST_ACC ESS_PATH ()
best_extension_by_limited_search () _ BEST_ACCESS_PATH ()
/ * 最適なプランの徹底的な検索を実行 * /
Find_best ()
Make_join_select どの関数を呼び出すか (handle_select() 関数は mysql_select() 関数を呼び出し、mysql_select() 関数はJOIN::prepare()、JOIN::optimize()、JOIN::exec() などを呼び出します。 mysql_select() 関数の最初の部分は、JOIN::prepare() を呼び出すことです。この関数は、コンテキスト分析、メタデータの作成、および一部のステートメント変換に使用されます。クエリ オプティマイザー関数 JOIN::optimize() と、最適化プロセスにおけるそのすべてのサブルート。 JOIN::optimize() 関数の実行後、JOIN::exec() が引き継ぎ、JOIN::optimize() 関数の最適化決定後に実行作業を完了します。

JOIN という単語が表示されますが、クエリ オプティマイザーは実際には JOIN 結合クエリだけでなく、すべての種類のクエリを処理します。



2つ。主要な最適化

このセクションでは、サーバーによって実行される最も重要な最適化について説明します。

1. 定数関係の最適化

定数等価変換

次の式はステートメント変換を受けます:

WHERE column1 = column2 AND column2 = 'x'
この式は、ご存知のとおり、A の場合=B && B=C => A=C (等しい値で渡すことができます)、前の文の式は次のようになります:

WHERE column1='x' AND column2='x'

if and演算子が次のいずれかの場合のみ、列 1 <演算子> 列 2 の条件でステートメント変換が発生します:

=、<、>、<=、>=、<>、<= >、LIKE
注: 等価転送の変換は BETWEEN には適していません。 LIKEにも向いていないかもしれませんが、それはまた後の話。

定等価転送もループ内で発生し、前のステップの出力が次のステップの入力として使用されます。

ソースコード、change_cond_ref_to_const() 関数については、/sql/sql_select.cc を参照してください。または /sql/sql_select.cc、propagate_cond_constants() 関数。

デッドコードを削除します

常に TRUE である条件は、次のようなステートメント変換を受けます:

WHERE 0=0 AND column1='y'
この場合、最初の条件が削除され、最後にそれが削除されます。 :

column1='y'
ソース コード、remove_eq_conds() については、/sql/sql_select.cc を参照してください。

常に FLASE である条件も、次のようなステートメント変換を受けます:

WHERE (0 = AND s1 = OR s1 = 7
括弧と最初の 2 つの条件は常に FLASE で、最後の条件は次のようになります:

WHERE s1 = 7

場合によっては、WHERE ステートメントが不可能な条件を表す場合、クエリ オプティマイザーは次のようにすべてのステートメントを削除することがあります:

WHERE (0 = AND s1 = 5)
この条件は決して存在しないため、 be が TRUE の場合、Impossible WHERE が EXPLAIN 分析に表示されます。簡単に言うと、MySQL は WHERE 条件が最適化されたことを示します

フィールドが NULL にならない場合、オプティマイザーは無関係な IS NULL 条件をすべて削除します。このように、条件

WHERE not_null_column IS NULL
は常に FLASE; 条件

WHERE not_null_column IS NOT NULL
は常に TRUE であるため、このフィールド クエリの条件も削除されます。例: OUT JOIN 外部結合、NOT NULL として定義されているフィールドにまだ NULL 値が含まれている場合、オプティマイザーは、この特殊な場合に IS NULL 条件を除外します。これは、All Impossible WHERE 条件が存在するためです。この領域には多くの可能性があります。例:

CREATE TABLE Table1 (column1 CHAR(1));

...

SELECT * FROM Table1 WHERE column1 = 'Popgo';

最適化 プロセッサはこの条件を排除しません。 CREATE TABLE 定義で不可能になっている場合でもクエリを実行します

組み合わせ可能な定数値

次の式はステートメント変換を受けます:

WHERE column1 =. 1 + 2

最後に:

WHERE column1 = 3
前に述べたように、オプティマイザはこのようなクエリ ステートメントを簡単にマージすることができ、結果を簡素化します。また、定数テーブル

は、クエリの SQL ステートメントの文字通りの意味だけでなく、定数テーブル (定数テーブル) の内容は次のように定義されます:

1. レコードがないか、1 つのレコードを持つテーブル

2. テーブルの式は WHERE 条件によって制約され、column = " という形式が含まれます。 constant"、テーブルの主キーのすべてのフィールド、または任意の一意のキーのすべてのフィールド (一意。キー フィールドは NOT NULL として定義されています)

たとえば、Table0 テーブルの定義には次が含まれます:

.. . PRIMARY KEY (column1, column2)

次に、クエリ式:

FROM Table0... WHERE column1=5 AND column2=7 ...

は定数テーブルを返します。もっと簡単に言うと、Table1 テーブルの定義に次が含まれている場合:

... unique_not_null_column INT NOT NULL UNIQUE
次に、クエリ式:

FROM Table1... WHERE unique_not_null_column=5
も定数テーブル (定数テーブル)。

このルールは、定数テーブル (定数テーブル) が最大 1 つのレコード値を持つことを意味します。 MySQL はまず、それが定数テーブル (定数テーブル) であるかどうかを評価し、その値を見つけます。このようにして、MySQL はこの値をクエリ ステートメントに挿入します。たとえば：

unique_not_not_null_column、table2.any_column
、table1、table2

、ここでtable1.unique_column =table2.any_column

およびtable1.unique_not_null_column = 5;テーブル (定数テーブル) の 2 番目の定義は、クエリ条件 unique_not_null_column を持つテーブル Table1 が定数テーブル (定数テーブル) であり、この値を取得することです。

値が失敗した場合、つまり Table1 に unique_not_null_column = 値がない場合、EXPLAIN 後の結果:

const テーブルを読み取った後に WHERE が不可能であることに気づきました
逆に、値が成功した場合、つまり unique_not_null_column = が入っている場合Table1 レコード値、MySQL は次のステートメントに変換されます:

SELECT 5, Table2.any_column

FROM Table1, Table2
WHERE 5 = Table2.any_column

AND 5 = 5;

実際、これは次のようになります。の良い例です。オプティマイザは、前述した定数の等価な受け渡しにより、いくつかのステートメント変換を実行します。また、なぜ最初に定数等価転送を記述する必要があるかというと、MySQL が定数テーブルとは何かを確認する前に行われるからです。オプティマイザーのステップの順序は異なる場合があります。

ただし、多くのクエリには定数テーブルへの参照がありません。今後、「定数」という言葉が言及されるときは常に、リテラル値または定数テーブルの内容を指すことに注意してください。

2. JOIN接続を最適化する

このセクションでは、JOIN 接続を最適化するさまざまな方法について説明します。注: JOIN 接続は、JOIN タイプだけを指すのではなく、すべての条件付きクエリのタイプも指します。これをアクセスタイプと呼ぶことを好む人もいます。

JOIN 接続タイプを決定する

クエリ条件式を評価するとき、MySQL はそれがどの JOIN 接続タイプに属するかを決定します。

次の JOIN タイプが最良から最悪の順にファイルに記録されます:

system: 定数テーブル (定数テーブル) のシステム タイプ
const: 定数テーブル (定数テーブル)
eq_ref: 一意の等価関係または主キー インデックス
ref: 等価関係のインデックス、このインデックスの値は NULL にすることはできません
ref_or_null: 等価関係のインデックス、このインデックスの値は NULL にすることができます
range: BETWEEN、IN、>=、LIKE、などの関連インデックスなど。
index: 順次スキャン インデックス
ALL: テーブル データ全体の順次スキャン

ソース コードについては、/sql/sql_select.h、enum join_type{} を参照してください。また、サブクエリの JOIN 接続タイプについては、ファイルに記録されていない部分がわずかにあります。

オプティマイザは、次のように JOIN 接続タイプを使用して駆動式を選択します:

SELECT *
FROM Table1
WHERE Indexed_column = AND unindexed_column = 6
Indexed_column の方が優れた JOIN 接続タイプであれば、次のような可能性が高くなります。運転表現モードになります。さまざまな例外も発生しますが、この説明は最初の単純な最適化ルールです。

ドライバーにとって最も意味のあることは何ですか? 次のクエリには 2 つの実行パスがあります:

最悪の実行プラン: 読み取りテーブルのすべての行をスキャンします。これは、Table1 のシーケンシャル スキャンまたは単純テーブル スキャンとも呼ばれます。各行をクエリして、indexed_column と unindexed_column の値がクエリ条件と一致するかどうかを確認します。

最適な実行プラン: インデックスを介して、indexed_column = value を持つレコードを取得します。これはインデックス検索とも呼ばれます。各行をクエリし、unindexed_column 列の値がクエリ条件と一致するかどうかを確認します。

インデックス付き検索は通常、シーケンシャル スキャンよりもアクセス回数が少なく、アクセスされるテーブルが巨大でインデックスが一意である場合、テーブル アクセスは非常に少なくなります。これが、適切な実行プランを持つアクセス テーブルの方が優れている理由であり、indexed_column がドライバーとしてよく使用される理由です。

結合とアクセスの方法

単一テーブル検索では、不適切な JOIN 結合実行の選択は、不適切な実行の選択よりもパフォーマンスに大きなダメージを与えます。そのため、MySQL 開発者は、クエリ内のテーブルが最適な順序で結合され、テーブル データをチェックするために最適なアクセス方法 (アクセス パスと呼ばれることが多い) が選択されることを確認するために、より多くの時間を費やします。テーブル結合の固定順序と、すべてのテーブルに対応するテーブル アクセス方法の組み合わせは、クエリ実行プラン (QEP) と呼ばれます。クエリ オプティマイザーの目的は、考えられるすべてのプランの中から最適な QEP を見つけることです。 JOIN 接続の優先順位の背後には、いくつかの一般的な概念があります。

各計画または計画の一部は COST として定義されます。計画のコストは、計画どおりにクエリを計算するために必要なリソースを大まかに反映します。主な要因は、クエリの計算時にアクセスされるレコードの総数です。 QEP をさまざまなコストに割り当てる方法が得られれば、それらを比較する方法が得られます。このように、オプティマイザーの目的は、考えられるすべての計画の中から最もコストの低い QEP を見つけることです。

MySQL では、最適な QEP 検索はボトムアップ アプローチで実装されます。オプティマイザは、最初に 1 つのテーブルのすべてのプランを確認し、次に 2 つのテーブルのすべてのプランを確認し、完全な最適な QEP が確立されるまでこれを繰り返します。クエリ内のテーブルと述語の一部のみを含むクエリ プランは、部分プランと呼ばれます。オプティマイザは、部分プランに追加するテーブルが増えるほどコストが高くなるという事実に基づいています (注: コストが高くなるにつれて、実行効率が低下します)。これにより、オプティマイザは、現在の最適な完全プランよりも低コストの部分プランのみを使用して、より多くのテーブルに拡張できるようになります。
最適な QEP を検索する主要なルートを完了するには、sql/sql_select.cc、find_best() を参照してください。考えられるすべての計画を徹底的に検索し、最終的に最適な計画が見つかることを保証します。

以下に、find_best() メソッドの疑似コードを説明します。これは再帰的であるため、入力変数の一部は、これまでの前の反復からの位置を示すためにマークされます。

remaining_tables = {t1, ..., tn} /* クエリで参照されるすべてのテーブル */

procedure find_best(
partial_plan in, /* in, これまでに結合されたテーブルの部分計画 */
partial_plan_cost, /* in,partial_plan のコスト */
remaining_tables, /* in,partial_plan で参照されていないテーブルのセット*/
best_plan_so_far, /* in/out、これまでに見つかった最良のプラン */
best_plan_so_far_cost)/* in/out、best_plan_so_far のコスト */
{
left_tables の各テーブル T について
{
/* のコストを計算しますテーブル T を使用します。
オプティマイザが考慮する要素には以下が含まれます:
テーブル内の行が多い (悪い)
これまでのテーブルと共通する重要な部分が多い (非常に良い)
WHERE 句に記載されている制限 (良い)
長いキー(良い)
固有キーまたは主キー (良い)
フルテキストキー (悪い)
場合によっては考慮する価値があるその他の要素:
キー内の列が多い
平均/最大キー長が短い
小さいテーブルファイル
いくつかのレベルinindex
すべての ORDER BY / GROUP 列はこのテーブルから取得されます */
cost = complex-series-of-calculations;
/* これまでのコストにコストを加算します。 */
partial_plan_cost+=cost;

if (partial_plan_cost >= best_plan_so_far_cost)
/*partial_plan_cost がすでに高すぎるため、検索を停止します */
続行;

partial_plan= best_access_method で部分計画を展開します。
Remaining_tables= Remaining_tables - テーブル T;
if (remaining_tables が空のセットではない)
{
find_best(partial_plan,partial_plan_cost,
Remaining_tables,
best_plan_so_far, best_plan_so_far_cost);
}
else
{
best_plan_so_far_cost= Partial_plan_cost;
best_plan_so_far= Partial_plan;
}
}
}

ここでのフィルタは、深さ優先検索アルゴリズムを使用しています。これにより、FROM フレーズ内のすべてのテーブルの評価が完了し、評価が現在最高の評価である場合、さらに差が生じて検索が停止されます。

ソースコード参照：/sql/table.h、struct st_table。 ：/sql/sql_sqlect.cc、make_join_statistics()。

find_best() と greedy_search() の直接（単純な）使用は、LEFT JOIN または RIGHT JOIN には使用されません。たとえば、MySQL 4.0.14 以降では、この場合、プロファイラーは LEFT JOIN を STRAHT JOIN に変換し、テーブルの順序を交換する可能性があります。 補足: LEFT JOIN と RIGHT JOIN の最適化。一つ键のこれらは範囲条件と呼ばれ、最もよく見られるのは、範囲>、>=、

column1 IN (1,2,3)

とこれは一样的:

column1 = OR column1 = OR column1 = 3
MySQL 同様の句を待ちます, 必要ありません

次の句のように、拡張子はインデックス (範囲検索) にも使用されます

column1 LIKE 'x%'

しかし、このようなことは実行されません:

column1 LIKE '%x%'
です。一致条件の最初の文字が通番であれば、何も問題はありません。

同様に、次の 2 つの句も同様ですEN 5 および7

和

column1 >= AND column1

クエリ条件で取得するインデックス キーが多すぎる場合、オプティマイザーは RANGE 結合タイプを ALL JOIN 結合タイプに変更することがあります。このような変換は、< 条件と複数レベルのセカンダリ インデックスで特に可能です。ソース コードを参照してください: /myisam/mi_range.c、mi_records_in_range() (MyISAM インデックス)。

INDEX 結合タイプ

次のクエリを考えてみましょう

SELECT column1 FROM Table1;
column1 にインデックスが追加されている場合、オプティマイザはテーブルの代わりに追加されたインデックスから値を取得することを選択することがあります (フルテーブルスキャン)。このようなインデックスを一般にカバリングインデックス(COVERING INDEX)と呼びます。 EXPLAIN Extra の説明では、MySQL は単純に INDEX という単語を使用してカバリング インデックス (COVERING INDEX) を表します。

ステートメント:

SELECT column1, column2 FROM Table1; インデックスが次のように定義されている場合にのみ、オプティマイザーは JOIN 接続タイプ INDEX を使用します。
つまり、クエリ対象のフィールド (column1、column2 など) にはインデックスを付ける必要があり、複数のインデックス付きフィールドは順序どおりではありません。したがって、検索上の考慮事項に関係なく、複数列インデックスをカバー インデックス (COVERING INDEX) として厳密に定義して使用する方が合理的です。

INDEX MERGE 結合タイプ

概要

インデックス マージ (INDEX MERGE) を使用して、テーブルの条件を次のように変換できる場合:

cond_1 OR cond_2 ... OR cond_N
変換の条件範囲スキャンには複数の cond_i (cond_1、cond_2...) 条件を使用できますが、条件のペア (cond_i、cond_j) が同じインデックスを使用することはありません。 cond_i 条件と cond_j 条件が同じインデックスを使用する場合、cond_i 条件または cond_j 条件を 1 つの範囲スキャンに結合でき、結合する必要はありません。

次のクエリはインデックス マージ (INDEX MERGE) を使用します。

SELECT * FROM t WHERE key1=c1 OR key2

SELECT * FROM t WHERE (key1= c1) OR key2 INDEX MERGE は範囲キーとして実装され、cond_i (cond_1, cond_2...) 条件で構築されるコンテナです。インデックス マージ (INDEX MERGE) を実行する場合、MySQL はキースキャンごとに行を取得し、それらを重複排除プロセスを通じて実行します。現在、重複を排除するために Unique クラスが使用されています。

INDEX MERGE Optimizer

次の条件のように、OR ステートメント内に異なるメンバーを持つキーを含めるように単一の SEL_TREE オブジェクトを構築することはできません:

key1 < c1 OR key2 < c2

MySQL 5.0 では、これらの条件はインデックス マージ (INDEX MERGE) メソッドとレンジ オプティマイザー (範囲オプティマイザー) 構造クラス SEL_IMERGE によって処理されます。 SEL_IMERGE は、複数の SEL_TREE オブジェクトの論理和を表し、次のように表されます:

sel_imerge_cond = (t_1 OR t_1 OR ... OR t_n)
各 t_i (t_1, t_2...) は SEL_TREE を表し、ペアはありません。 (t_i, t_j) 異なる SEL_TREE オブジェクトを 1 つの SEL_TREE オブジェクトにマージできます。

現在の実装方法は、分析されたクエリの一部として単一の SEL_TREE オブジェクトを構築できない場合にのみ SEL_IMERGE を構築します。単一の SEL_TREE オブジェクトを構築できることが判明した場合、SEL_TREE は直ちに破棄されます。これは実際には制限であり、最悪の場合の行取得戦略の使用につながる可能性があります。次のクエリ:

SELECT * FROM t WHERE (goodkey1=c1 OR Goodkey1=c2) AND badkey=c3
(goodkey1、goodkey1) のインデックス マージ (INDEX MERGE) が実行される場合でも、badkey のスキャンが選択されます。早く更新されること。

インデックス マージ (INDEX MERGE) オプティマイザーは、行にアクセスするためのインデックス マージ (INDEX MERGE) の可能なすべてのルートのリストを収集します。この SEL_IMERGE 構造体リストは次の条件を表します:

(t_11 OR t_12 OR ... OR t_1k) AND
(t_21 OR t_22 OR ... OR t_2l) AND
... OR t_mp)
の場合。 t_ij は SEL_IMERGE であり、条件は SEL_IMERGE オブジェクトです。

行の取得に使用される最小コストの SEL_IMERGE オブジェクト。

インデックス マージ (INDEX MERGE) コンストラクターの詳細については、ソース コード sql/opt_range.cc、imerge_list_and_list()、imerge_list_or_list()、および SEL_IMERGE クラスのメンバー関数を参照してください。

RANGE オプティマイザー

範囲 RANGE クエリの場合、MySQL オプティマイザーは次の形式で SEL_TREE オブジェクトを構築します:

range_cond = (cond_key_1 AND cond_key_2 AND ... AND cond_key_N)

各 cond_key_i はコンポーネントのキー条件です。 。 MySQL は、有用なキーごとに cond_key_i 条件を作成します。次に、この最も安い条件 cond_key_i が範囲 RANGE スキャンに使用されます。

単一の cond_key_i 条件は、SEL_ARG オブジェクトのポインターリンクされたネットワークによって表されます。各 SEL_ARG オブジェクトはキーの特定の部分を参照し、次の条件を表します:

sel_arg_cond= (inf_val < key_part_n AND key_part_n < sup_val) (1)
)
OR 4)

1.実装間隔には、上限または下限の臨界値がない場合や、臨界値が含まれる場合と含まれない場合があります。

2.次のキー コンポーネントに条件を設定した SEL_ARG オブジェクトの実装は、次のキー コンポーネントに条件を設定した SEL_ARG オブジェクトを対象としています。

3.この SEL_ARG オブジェクトと同じフィールドに間隔を持つ SEL_ARG オブジェクトを実装します (この SEL_ARG オブジェクトと同じフィールドに間隔を持つ SEL_ARG オブジェクト用です)。現在のオブジェクトと左のオブジェクトの間の間隔が互いに素です。 left_sel_arg_cond.sup_val <= inf_val。

4.この SEL_ARG オブジェクトと同じ領域に間隔をあけた SEL_ARG オブジェクトを実装します。現在のオブジェクトと右のオブジェクトの間の間隔が互いに素です。 left_sel_arg_cond.min_val >= max_val。

MySQL は、任意の深さのネストされた AND-OR 条件を上記の接続された形式に変換します。 row row回収アルゴリズムアルゴリズムインデックスマージ（インデックスマージ）には、次の2つの手順があります。

PREPARATIONフェーズ：「インデックスのみ」をアクティブ化します。 ) (key, rowid) ペア (key_i から))

{

if (クラスター化 PK スキャンなし ||

行がクラスター化 PK スキャン条件と一致しない)

put rowid into Unique;

}
}

deactivate 'index only';

行取得フェーズ:

Unique の各 rowid に対して
{
行を取得して出力に渡します;
}
if (clustered_pk_scan)
{
while (clustered_pk_scan の次の行を取得します)
行を出力に渡します;

ソース コード: sql/opt_range.cc、QUICK_INDEX_MERGE_SELECT クラス関数のインデックス マージ (INDEX MERGE) 行取得コードを参照してください。

3. 転置

MySQL は、単純なステートメント式の転置 (関係演算子のオペランドの順序を逆にする) をサポートしています。言い換えると:

WHERE - 5 = column1
このステートメントは次のように変換できます:


WHERE column1 = -5

ただし、MySQL は次のような演算による転置をサポートしていません:

WHERE 5 = - column1

そして、この文を同等に扱うことはできません:

WHERE column1 = -5

このような column = 定数式の転置は、インデックスの取得を改善するためです。この形式のステートメントにインデックス付きフィールドがある場合、MySQL はテーブルのサイズに関係なく、常にインデックス付きフィールドを使用します。 (例外: テーブルにレコードがない、またはレコードが 1 行しかない場合、それは定数テーブルであり、特別な処理が必要です。定数値と定数テーブルを参照してください)。

AND 関係

AND クエリ形式は、次のように、条件 1 AND 条件 2 です:

WHERE column1 = 'x' AND column2 = 'y'
このステップでは、オプティマイザーの意思決定プロセスについて説明します:

1 。どちらの条件にもインデックスが作成されていない場合は、順次スキャン (全テーブル スキャン) が使用されます。

2.前の点に加えて、条件の 1 つがより適切な JOIN 結合タイプを持つ場合は、JOIN 結合タイプのドライバーを選択します。 (「JOIN 接続タイプの決定」を参照)

3.最初の 2 つのポイントに加えて、両方の条件がインデックス付きで等しい JOIN 接続タイプを持っている場合 (注: JON 接続タイプには良い影響も悪い影響もあります)、ドライバーは最初に作成されたインデックスに基づいて選択されます。

オプティマイザーは、インデックスのクロスオーバーに基づいてインデックスのマージ (INDEX MERGE) も選択します。「INDEX MERGE 結合タイプ」を参照してください。 例は次のとおりです:

CREATE TABLE Table1 (s1 INT, s2 INT);

CREATE INDEX Index1 ON Table1 (s2);

CREATE INDEX Index2 ON Table1 (s1);

...
SELECT * FROM Table1 WHERE s1 = AND s2 = 5;

このクエリを解決するための戦略を選択するとき、s2 のインデックスが最初に作成されるため、オプティマイザはドライバーとして s2 = 5 を選択します。いつでも変更される可能性のあるルールではなく、カジュアルな効果として扱います。

OR 関係

OR クエリ形式は、次のように、条件 1 または条件 2 です。

WHERE column1 = 'x' OR column2 = 'y'
このクエリ オプティマイザーの決定は、順次完全テーブル スキャンを使用することです。

特定の状況下ではインデックス マージ (INDEX MERGE) を使用するオプションもあります。詳細については、「INDEX MERGE Optimizer」と「Index Merge Optimization」を参照してください。

2 つの条件のフィールドが同じ場合、上記の特定の状況は使用できません。次のように:

WHERE column1 = 'x' OR column1 = 'y'
この場合、ステートメントは RANG クエリであるため、インデックスが作成されます。このトピックは、IN 述語の説明で再び取り上げられます。

UNION クエリ

UNION を含むすべての SELECT クエリ ステートメントは個別に最適化されます。したがって、このクエリは次のようになります:

SELECT * FROM Table1 WHERE column1 = 'x'
UNION ALL
SELECT * FROM TABLE1 WHERE column2 = 'y'
column1 と column2 の両方にインデックスが付けられている場合、各 SELECT はそれぞれインデックス スキャンを使用します。セットが結合されます。注: このクエリでシーケンシャル スキャン OR の例が使用されている場合、このクエリは同じ結果セットを生成する可能性があります。 hot not（＆lt;＆gt;）論理的条件は次のとおりです, MySQL この条件では変換ステートメントは作成されません。 RANG を使用してクエリを実行するとより良い結果が得られると思われる場合は、ステートメントを自分で手動で変換する必要があります。

次のような論理条件もあります:

WHERE NOT (column1 != 5)

は次と同等です:

WHERE column1 = 5
この場合、MySQL はステートメント変換を実行しません。

上記 2 つの状況に対する新しい最適化手法を追加することを楽しみにしています。

ORDER BY ステートメント

通常、オプティマイザは行レコードが順序付けされていることを検出した場合、ORDER BY ステートメントの SORT プロセスもスキップします。ただし、確認すべき例外がいくつかあります。

例:

SELECT column1 FROM Table1 ORDER BY 'x';

オプティマイザは ORDER BY ステートメントを破棄しますが、これもデッド コード削除の例です。

例:

SELECT column1 FROM Table1 ORDER BY column1;
オプティマイザーは、column1 のインデックスが存在する場合、それを使用します。

例:

SELECT column1 FROM Table1 ORDER BY column1+1;

オプティマイザーは、column1 のインデックスが存在する場合、それを使用します。ただし、混同しないでください。インデックスはレコード値を検索するためにのみ使用されます。さらに、インデックスを順次スキャンするコストは、テーブル全体を順次スキャンするコストよりも安くなります (通常、インデックスのサイズはデータ値のサイズよりも小さくなります)。そのため、INDEX JOIN 接続タイプの方が優れています。 ALLタイプより最適化されています。 「JOIN 結合タイプの決定」を参照してください。

次のような並べ替え SORT もあります。および column2 インデックスがある場合、オプティマイザは column1 のインデックスに移動します。このクエリでは、column2 の値の順序はドライバーの選択に影響しません。

ソース コードを参照してください: /sql/sql_select.cc、test_if_order_by_key() および /sql/sql_select.cc、test_if_skip_sort_order()。

ORDER BY 最適化では、SORT ソート プロセスのコンテンツ メカニズムについて説明します。ここでは再度説明しません。ただし、バッファリングとクイックソートのメカニズムの動作について説明しているため、必ず読むことをお勧めします。

GROUP BY と関連条件

GROUP BY と関連条件 (HAVING、COUNT()、MAX()、MIN()、SUM()、AVG()、DISTINCT()) の主な説明は次のとおりです。最適化。

GROUP BY は、インデックスが存在する場合はそのインデックスを使用します。
GROUP BY はインデックスが存在しない場合に並べ替えを使用します。オプティマイザは、HASH テーブル ソートの使用を選択する場合があります。
GROUP BY x ORDER BY x の場合、GROUP BY は x でソートされるため、オプティマイザは ORDER BY が不要であると判断します。
オプティマイザーの WHERE ステートメントには、特定の HAVING 条件を転送するコードが含まれています。ただし、このコードは執筆時点では無効です。ソース コード: /sql/sql_select.cc、JOIN::optimize()、#ifdef HAVE_REF_TO_FIELDS の後ろを参照してください。

テーブル ハンドラーに有効な高速行数がある場合は、次のクエリを実行します:

SELECT COUNT(*) FROM Table1;
すべての行をスキャンせずに行数を取得できます。これは MyISAM テーブルにのみ当てはまりますが、InnoDB テーブルには当てはまりません。さらに、このクエリ

SELECT COUNT(column1) FROM Table1;

は、column1 が NOT NULL として定義されていない限り、同じ最適化は行われません。

MAX() と MIN() の新しい最適化メソッド。例:

SELECT MAX(column1)

FROM Table1

WHERE column1 < 'a';

column1 にインデックスが付いている場合、インデックス内の 'a' 値をクエリし、その前にインデックス キーを返すことで、最大値を簡単に見つけることができます。それ。

次の形式でクエリを最適化し、ステートメント変換を実行します:
SELECT DISTINCT column1 FROM Table1;
Into:

SELECT column1 FROM Table1 GROUP BY column1;
これら 2 つの条件が true の場合にのみ:

* GROUP BY はインデックスによって完了できます。これは、FROM ステートメントにテーブルが 1 つだけ存在し、WHERE ステートメントが存在しないことを意味します。
* LIMIT ステートメントはありません。

DISTINCT ステートメントは常に GROUP BY に変換されるわけではないため、DISTINCT を含むクエリ ステートメントが常に並べ替えられた結果セットを持つことを期待しないでください。ただし、クエリに ORDER BY NULL が含まれていない限り、GROUP BY 最適化ルールに依存できます。

3つ。その他の最適化

このセクションでは、その他のより特殊な最適化方法について説明します。

1. ref および eq_ref の NULL 値フィルタリング アクセス

このパートでは、ref および eq_ref 接続タイプの NULL 値フィルタリングの最適化方法について説明します。

初期の NULL 値のフィルタリング

次のような結合シーケンスがあると仮定します:

..., tblX, ..., tblY, ...
テーブル tblY が ref または eq_ref を介して結合されているとさらに深く仮定します。 type にアクセスします:

tblY.key_column = tblX.column
あるいは、複数のキー部分で ref type アクセスを使用します:

... AND tblY.key_partN = tblX.column AND ...
tblX.column は機能します。ヌル。 ref (または eq_ref) 型にアクセスすると、初期段階で NULL フィルタリングが適用されます。次の推論を行います:

(tblY.key_partN = tblX.column) => (tblX.column IS NOT NULL)
元の式は、テーブル tblX および tblY の現在の行レコードを読み取った後にのみチェックされます。 IS NOT NULL 述語は、テーブル tblX 内の現在の行レコードを読み取った後にのみチェックされます。テーブル tblX および tblY の結合ソートに他のテーブルがある場合、IS NOT NULL 述語チェックにより、それらのテーブルへのアクセスをスキップできます。

この機能の実装コードは次のとおりです:

ref アナライザー (メソッド update_ref_and_keys() を含む) は、KEY_FIELD::null_rejecting=TRUE を設定することによって、上記のようなこのタイプのクエリ方程式をチェックしてマークします。

JOIN 接続ソートを選択した後、add_not_null_conds() は適切な IS NOT NULL 述語を適切なテーブルの関連条件に追加します。

IS NOT NULL 述語をすべての方程式に追加すると、(実際に使用されるものではなく) ref アクセス タイプで使用される可能性があります。ただし、これは現在行われていません。

遅延 (遅延) NULL フィルタリング

ref アクセス タイプを通じてアクセスされるテーブル tblX クエリ プランがあるとします:

tblX.key_part1 = expr1 AND tblX.key_part2 = expr2 AND ...

呼び出し時インデックス 取得する前に、expri (expr1、expr2、expr3...) 値が NULL かどうかを判断します。一致する場合、検索は呼び出されず、一致が見つからなかった配列がすぐに返されます。

この最適化メソッドは、初期の NULL フィルタリングによって生成された null_rejecting 属性を再利用します。このチェックのソース コードについては、function join_read_always_key() を参照してください。

2. パーティション関連の最適化

このパートでは、MySQL パーティション関連の最適化について説明します。 MySQL5.1 パーティショニングに関連する概念と実装については、「パーティショニング」を参照してください。

パーティション プルーニング (枝刈り)

パーティション プルーニングの操作は次のように定義されます:

"パーティション テーブルのクエリを提供し、このパーティション テーブルの DDL ステートメントをクエリ内の WHERE または ON ステートメントと比較します。このクエリによってアクセスされる最小のパーティション セットを見つけます。 "

この方法で取得されたパーティション セットは、テーブルのすべてのパーティションのセットよりもはるかに小さいです。このパーティション セットは、後続のクエリ ステートメントでも使用されます。このパーティション セットに追加されていない他のパーティションにはアクセスできません。つまり、パーティションはプルーニングされています。このため、クエリの実行が高速化されます。

非トランザクション テーブル エンジン。??MyISAM にトランザクション ストレージ エンジンがない場合、ロックはパーティション テーブル全体に追加されます。理論的には、使用されているパーティションにロックを追加するだけで、パーティション プルーニングを使用して同時実行性を向上させることができます。しかし、この機能はまだ実装されていません。

パーティション プルーニングはテーブルのストレージ エンジンに依存しないため、この関数は MySQL クエリ オプティマイザーの一部です。次のセクションでは、パーティションのプルーニングの詳細について説明します。

パーティションプルーニングの概要

パーティションプルーニングの実装手順は次のとおりです:

1. WHERE ステートメントの条件を分析し、分析結果を説明する間隔グラフを作成します。

2.間隔グラフを通じて、各間隔で訪問されたパーティション (サブパーティションを含む) のセットを見つけます。

3.クエリに必要なパーティションのセットを構築します。

間隔グラフはボトムアップ方式で構築され、上記のステップの説明を表します。説明に続いて、最初に間隔グラフという用語を定義し、次にパーティション間隔を使用して間隔グラフを形成する方法を説明し、最後に間隔グラフのワークフローを説明します。

パーティション化間隔

単一点間隔

最も単純なケースから始めて、パーティション型 p_type とパーティション関数 p_func で表される、N 列を持つパーティション化されたテーブルを想定します。

CREATE TABLE t (columns)
PARTITION BY p_type(p_func(col1,col2,...colN)...);
次に、クエリの WHERE 条件が次の形式であると仮定します:

WHERE t。 Col1=const1 AND t.col2=const2 AND ... t.colN=constN
p_func(const1, const2 ... constN) を計算して、どのパーティションに WHERE 条件と同じレコードが含まれているかを確認できます。注: このプロセスは、すべてのパーティション タイプとすべてのパーティション機能に対して実行されます。

注: このプロセスは、WHERE 条件が上記の形式である場合にのみ機能し、テーブルの各列が任意の定数と等しいことが検証される必要があります (同じ定数が各列で同じである必要はありません)カラム）。たとえば、上記の例の WHERE ステートメントにcol1=const1 がない場合、p_func パーティション関数の値は計算されず、使用される実際のパーティション セットも制約されません。

インターバルウォーキング (ウォーキング)

次のように、パーティションテーブル t が列セット、パーティションタイプ p_type、およびパーティション関数 p_func として整数型フィールド int_col を使用するように定義されているとします。 (列)

PARTITION BY

p_type(p_func(int_col))
...
次の形式の WHERE 条件クエリがあるとします:

WHERE const1 <= int_col <= const2

条件を減らすことができます。この WHERE ステートメントを次の関係に変換することで、この状況を一連の単一点間隔 (単一点間隔) に変換します:

int_field=const1 OR

int_field=const1 + 1 OR
int_field=const1 + 2 OR
... OR
int_field=const2

ソースコードでは、この変換をインターバルウォーキング (ウォーキング) と呼びます。短い間隔で移動するコストは高くないため、パーティションの数を減らして小さなパーティションをスキャンできます。ただし、長い範囲を走査するのはあまり効率的ではなく、多数のパーティションをチェックする必要があり、その場合はすべてのパーティションがスキャンされる可能性があります。

次のパラメータは、範囲ウォーキング (ウォーキング) の値を決定します:

#define MAX_RANGE_TO_WALK=10

注: 次の条件関係は、上記の範囲ウォーキング (ウォーキング) のロジックも使用します:

const1 > ;= int_col > = const2

間隔マッピング (マッピング)

次のパーティション テーブル定義を仮定します:

CREATE TABLE t (columns)

PARTITION BY RANGE LIST (unary_ascending_function (column))

テーブル t に対するクエリ ステートメントは次の形式のいずれかです:

const1 <= t.column <= const2

t.column <= const2
const1 <= t.column

自己分割関数昇順です。以下を参照してください。 関係:

const1 <= t.col <= const2

=> p_func(const1) <=

p_func(t.column) <= p_func(const2)

A と B を使用してこれを表します。関係の左端と右端の部分は、次のように書き換えることができます:

A <= p_func(t.column) <= B

注: この例では、間隔はは閉じており、2 つの境界値があります。ただし、同様の推論を他のタイプの間隔に拡張することもできます。分範囲パーティショニング。各パーティションは、パーティション関数値 -x------x----------x---------->

の検索間隔の範囲の軸を占めます。 ---- x ============ = x ----------＆gt;

たとえば、LIST 分割では、各パーティションには分割関数値の軸上に設定された点が含まれており、各パーティションは次のように異なる交点を生成します。 -+---+ ----+ ----+----+----+---->

検索間隔 ----x===================x------>
検索間隔 [A, B]。使用されるパーティション セットにより、A から B まで実行され、この検索範囲内でポイントを収集するパーティションが決定されます。

サブパーティション間隔

前のセクションでは、基本的な WHERE 条件からアクティブなパーティション セットを推測するいくつかの方法について説明しました。すべては、これらのパーティションの推論方法が、範囲パーティション化 (RANGE パーティション化) と列挙パーティション化 (LIST パーティション化) のサブパーティションを除くサブパーティションに適していることを示しています。

各パーティションは同じ方法で分子パーティション化されているため、各パーティション内のどのサブパーティションがアクセスされるかを調べます。

WHERE 句から間隔へ

前の章では、パーティションとサブパーティション間隔を表す WHERE ステートメントからパーティション セットを推論する方法について説明しました。次に、WHERE ステートメントから範囲を抽出する方法を見てみましょう。

抽出されたプロセスは、MySQL オプティマイザーの一部である範囲アナライザー (RANGE Analyzer) を使用し、範囲 RANGE アクセスのプランを生成します。タスクが似ているからです。 WHERE ステートメントの 2 つの形式: RANGE アクセス タイプはインデックス範囲 (間隔) スキャンを使用し、パーティション プルーニング モジュールはパーティション間隔を使用して、どのパーティションが使用されるかを決定します。

パーティション プルーニングの場合、RANGE アナライザーは WHERE ステートメント、パーティションおよびサブパーティション関数で使用されるテーブルの列リストを使用して呼び出されます:

(part_col1, part_col2, ...part_colN,

subpart_col1, subpart_col2, ... subpart_colM)
レンジ アナライザー (RANGE Analyzer) の作業結果は SEL_ARG グラフと呼ばれます。これは非常に複雑な構造なので、ここでは説明しません。この文化的議論の現在の焦点は、すべてのパーティションを移動して、パーティションとサブパーティションの範囲を収集できることです。

次の例は、構造と移動プロセスを示しています。テーブル t が次のようにパーティション化されているとします。

CREATE TABLE t (..., pf INT, sp1 CHAR(5), sp2 INT, ... )

PARTITION BY LIST (pf)
SUBPARTITION BY HASH(sp1, sp2) (
パーティション p0 の値 (1)、
パーティション p1 の値 (2)、
パーティション p2 の値 (3)、
パーティション p3 の値 (4)、
パーティション p4 の値 (5)、
);
ここで、テーブル t に対する非常に複雑な WHERE ステートメント クエリを想定します:

pf=1 AND (sp1='foo' AND sp2 IN (40,50))

OR

(pf1=3 OR pf1=4) AND sp1 ='bar' AND sp2=33

OR

((pf=3 OR pf=4) AND sp1=5)

OR

p=8

SEL_ARG 画像は次のとおりです:

(ルート)
|               :
|パーティショニング : サブパーティショニング
|               :
|               :
|               :
|   +------+ : +-----------+ +--------+
---| pf=1 |----:-----| sp1='foo' |---| sp2=40 |
+------+ : +-----------+ +--------+
|        : |
|        : +--------+
|        : | sp2=50 |
|        : +--------+
|        :
|        :
+------+ : +-----------+ +--------+
| pf=3 |----:--+--| sp1='バー' |---| sp2=33 |
+------+ : |  +-----------+ +----------+
|        : |
+------+ : |
| pf=4 |----:--+
+------+ :
|        :
|        :
+------+ : +-----------+
| pf=8 |----:-----| sp1='baz' |
+------+ : +-----------+

上記图表、竖的边界（|）代表、横的（-）代表AND

】

🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜🎜🎜】 🎜pf=1 のゾーン間分析を実行し、分区 P0 の対応する集合に達し、sp1='foo' に右移動、🎜次は sp2=40 に移動します 🎜分析sp1='foo' AND sp2=40 エリア間、特定の SP1 子ドメインで実行されます。推論 1: 各分区構成セット P0 で、子分区 SP1 が「使用されています」 🎜下に sp2=50 に移動します 🎜 Analyzersp1= 'foo' 領域と sp2=50 領域の間、特定の SP2 子領域で実行されます。 推論 2: 集合 P0 を構成する各領域で、子領域 SP2 が "使用" されて pf=1 に戻り、その後 pf と呼ばれます。 =3 🎜3。🎜🎜pf=3 のゾーン分析を実行し、分区 P1 の対応する集合に到達します。sp1='bar' まで右移動します。🎜次は sp2=33 まで右移動します。🎜🎜分析sp1='bar' AND sp2=推論 3: セット P1 を構成する各セグメント セクションで、子セグメント SP3 が「使用中」🎜 pf=3 に移動し、その後 pf=4 🎜 4 に移動します。 🎜pf=4 の領域分析を実行し、領域 P2 の対応する集合を検出し、sp2='bar' に右に移動します。🎜移動と同様の推論を実行し、pf=3 で正しいことが確認されました。 sp1='bar' AND sp2=33 セクションを再度分析しますが、この操作は全体のパフォーマンスに大きな影響を与えません。 =8 の区間分析、区分け P3 の対応集合に到達、sp1='baz' に右移動🎜

sp1='baz' に到達したので、右に移動することも、サブパーティション間隔を構築することもできないことがわかります。 pf=8 を記録して返します
前のプロセスから、サブパーティションの範囲を制限できなくなったので、推論: P3 パーティション セット内の各パーティションでは、すべてのサブパーティションが有効であると想定されます。

6. pf=9 から下に移動してみて、最後まで到達したことがわかり、ツアー グラフが完成しました。

注: 特定の状況下では、範囲アナライザー (RANGE Analyzer) の結果には、OR または AND 演算子で構成される複数の SEL_ARG グラフが含まれます。これは、非常に複雑な WHERE ステートメント、または単一の範囲リストを構築できない WHERE ステートメントで発生します。この状況では、パーティション プルーニング コードは適切な操作を使用します。例:

SELECT * FROM t1 WHERE Partition_id=10 OR subpartition_id=20
この例では、単一の範囲は構築されませんが、パーティションはプルーニングされます (パーティション プルーニング) ) コードは、使用されるパーティション セットが結合であると正しく推論します。

パーティション内のすべてのサブパーティションには、partition_id=10 の行が含まれ、各パーティション内のサブパーティションには subpartition_id=20 の行が含まれます。

ソースコードでのパーティションプルーニングの実装

ソースコードの簡単な説明:

sql/opt_range.cc:
このコードには、From WHERE Clauses to Intervals メソッド prune_partitions() の実装が含まれています。 PartitionPruningModule コードから始まる、パーティション プルーニングに関する詳細なコード コメントが 1 行ずつあります:

sql/partition_info.h:

classpartition_info {
...
/*
使用されるビットマップ (つまり、プルーニングされていない)ここに、パーティション プルーニングの結果
が保存されます。
*/
MY_BITMAP used_pa​​rtitions;

/*
このパーティション テーブルで区間分析
を実行する関数へのポインター (opt_range .cc のコードで使用されます) )
*/
get_partitions_in_range_iter get_part_iter_for_interval;
get_partitions_in_range_iter get_subpart_iter_for_interval;

};
sql/sql_partition.cc:

このコードには、すべてのパーティション間隔分析タイプを実装するメソッドが含まれています。

パーティションの取得

パーティション化されたテーブルが一連のインデックスの取得 (つまり、ref、eq_ref、ref_or_null 結合アクセス メソッド) によってアクセスされる場合、MySQL はインデックスの取得にすべてのパーティションが必要かどうかを確認するか、特定のパーティションへのアクセスを制限します。 。例:

CREATE TABLE t1 (a INT, b INT);

INSERT INTO t1 VALUES (1,1),(2,2),(3,3);

CREATE TABLE t2 (
) keypart1 INT,
keypart2 INT,
KEY(keypart1, keypart2)
)
PARTITION BY HASH(keypart2);

INSERT INTO t2 VALUES (1,1),(2,2),(3,3);

クエリ条件は次のとおりです:

SELECT * FROM t1, t2
WHERE t2.keypart1=t1.a
AND t2.keypart2=t1.b;
次のアルゴリズムを使用して実行します:

(t1 の各レコードに対して:)
{
t2 ->index_read({current-value-of(t1.a), current-value-of(t1.b)});
while( t2->index_next_same() )
に行の組み合わせを渡しますクエリ出力;
}
Index_read() 呼び出しでは、パーティション テーブル ハンドルは、識別されたすべてのパーティション列 (この場合は単一の列 b) の値を取り出し、アクセスするパーティションを見つけます。このパーティションが削除されると、他のパーティションにアクセスできなくなります。

上記は MySQL Internals Optimizer の内容です。その他の関連記事については、PHP 中国語 Web サイト (www.php.cn) に注目してください。