オプティマイザー
この記事では、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 (等しい値で渡すことができます)、前の文の式は次のようになります:
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 条件が存在するためです。この領域には多くの可能性があります。例:
...
SELECT * FROM Table1 WHERE column1 = 'Popgo';最適化 プロセッサはこの条件を排除しません。 CREATE TABLE 定義で不可能になっている場合でもクエリを実行します
最後に:
WHERE column1 = 3
前に述べたように、オプティマイザはこのようなクエリ ステートメントを簡単にマージすることができ、結果を簡素化します。また、定数テーブル
は、クエリの SQL ステートメントの文字通りの意味だけでなく、定数テーブル (定数テーブル) の内容は次のように定義されます:
1. レコードがないか、1 つのレコードを持つテーブル
たとえば、Table0 テーブルの定義には次が含まれます:
.. . PRIMARY KEY (column1, column2)
次に、クエリ式: は定数テーブルを返します。もっと簡単に言うと、Table1 テーブルの定義に次が含まれている場合:
... unique_not_null_column INT NOT NULL UNIQUE
次に、クエリ式:
FROM Table1... WHERE unique_not_null_column=5
も定数テーブル (定数テーブル)。
unique_not_not_null_column、table2.any_column
、table1、table2
および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 は次のステートメントに変換されます:
FROM Table1, Table2
WHERE 5 = Table2.any_column
ただし、多くのクエリには定数テーブルへの参照がありません。今後、「定数」という言葉が言及されるときは常に、リテラル値または定数テーブルの内容を指すことに注意してください。
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;
}
}
}
ソースコード参照：/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
クエリ条件で取得するインデックス キーが多すぎる場合、オプティマイザーは 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 Optimizer key1 < c1 OR key2 < c2 sel_imerge_cond = (t_1 OR t_1 OR ... OR t_n) インデックス マージ (INDEX MERGE) オプティマイザーは、行にアクセスするためのインデックス マージ (INDEX MERGE) の可能なすべてのルートのリストを収集します。この SEL_IMERGE 構造体リストは次の条件を表します: 行の取得に使用される最小コストの SEL_IMERGE オブジェクト。 各 cond_key_i はコンポーネントのキー条件です。 。 MySQL は、有用なキーごとに cond_key_i 条件を作成します。次に、この最も安い条件 cond_key_i が範囲 RANGE スキャンに使用されます。 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。 { 行がクラスター化 PK スキャン条件と一致しない) } ソース コード: sql/opt_range.cc、QUICK_INDEX_MERGE_SELECT クラス関数のインデックス マージ (INDEX MERGE) 行取得コードを参照してください。 そして、この文を同等に扱うことはできません: WHERE column1 = 'x' AND column2 = 'y' 1 。どちらの条件にもインデックスが作成されていない場合は、順次スキャン (全テーブル スキャン) が使用されます。 3.最初の 2 つのポイントに加えて、両方の条件がインデックス付きで等しい JOIN 接続タイプを持っている場合 (注: JON 接続タイプには良い影響も悪い影響もあります)、ドライバーは最初に作成されたインデックスに基づいて選択されます。 オプティマイザーは、インデックスのクロスオーバーに基づいてインデックスのマージ (INDEX MERGE) も選択します。「INDEX MERGE 結合タイプ」を参照してください。 例は次のとおりです: CREATE INDEX Index1 ON Table1 (s2); ... OR 関係 OR クエリ形式は、次のように、条件 1 または条件 2 です。 WHERE column1 = 'x' OR column2 = 'y' 特定の状況下ではインデックス マージ (INDEX MERGE) を使用するオプションもあります。詳細については、「INDEX MERGE Optimizer」と「Index Merge Optimization」を参照してください。 WHERE column1 = 'x' OR column1 = 'y' UNION クエリ UNION を含むすべての SELECT クエリ ステートメントは個別に最適化されます。したがって、このクエリは次のようになります: SELECT * FROM Table1 WHERE column1 = 'x' は次と同等です: 通常、オプティマイザは行レコードが順序付けされていることを検出した場合、ORDER BY ステートメントの SORT プロセスもスキップします。ただし、確認すべき例外がいくつかあります。 例: SELECT column1 FROM Table1 ORDER BY column1; 例: オプティマイザーは、column1 のインデックスが存在する場合、それを使用します。ただし、混同しないでください。インデックスはレコード値を検索するためにのみ使用されます。さらに、インデックスを順次スキャンするコストは、テーブル全体を順次スキャンするコストよりも安くなります (通常、インデックスのサイズはデータ値のサイズよりも小さくなります)。そのため、INDEX JOIN 接続タイプの方が優れています。 ALLタイプより最適化されています。 「JOIN 結合タイプの決定」を参照してください。 次のような並べ替え SORT もあります。および column2 インデックスがある場合、オプティマイザは column1 のインデックスに移動します。このクエリでは、column2 の値の順序はドライバーの選択に影響しません。 GROUP BY と関連条件 GROUP BY と関連条件 (HAVING、COUNT()、MAX()、MIN()、SUM()、AVG()、DISTINCT()) の主な説明は次のとおりです。最適化。 SELECT COUNT(*) FROM Table1; MAX() と MIN() の新しい最適化メソッド。例: FROM Table1 column1 にインデックスが付いている場合、インデックス内の 'a' 値をクエリし、その前にインデックス キーを返すことで、最大値を簡単に見つけることができます。それ。 次の形式でクエリを最適化し、ステートメント変換を実行します: DISTINCT ステートメントは常に GROUP BY に変換されるわけではないため、DISTINCT を含むクエリ ステートメントが常に並べ替えられた結果セットを持つことを期待しないでください。ただし、クエリに ORDER BY NULL が含まれていない限り、GROUP BY 最適化ルールに依存できます。 3つ。その他の最適化 1. ref および eq_ref の NULL 値フィルタリング アクセス 初期の NULL 値のフィルタリング 次のような結合シーケンスがあると仮定します: ..., tblX, ..., tblY, ... ref アナライザー (メソッド update_ref_and_keys() を含む) は、KEY_FIELD::null_rejecting=TRUE を設定することによって、上記のようなこのタイプのクエリ方程式をチェックしてマークします。 呼び出し時インデックス 取得する前に、expri (expr1、expr2、expr3...) 値が NULL かどうかを判断します。一致する場合、検索は呼び出されず、一致が見つからなかった配列がすぐに返されます。 この方法で取得されたパーティション セットは、テーブルのすべてのパーティションのセットよりもはるかに小さいです。このパーティション セットは、後続のクエリ ステートメントでも使用されます。このパーティション セットに追加されていない他のパーティションにはアクセスできません。つまり、パーティションはプルーニングされています。このため、クエリの実行が高速化されます。 非トランザクション テーブル エンジン。??MyISAM にトランザクション ストレージ エンジンがない場合、ロックはパーティション テーブル全体に追加されます。理論的には、使用されているパーティションにロックを追加するだけで、パーティション プルーニングを使用して同時実行性を向上させることができます。しかし、この機能はまだ実装されていません。 パーティション プルーニングはテーブルのストレージ エンジンに依存しないため、この関数は MySQL クエリ オプティマイザーの一部です。次のセクションでは、パーティションのプルーニングの詳細について説明します。 間隔グラフはボトムアップ方式で構築され、上記のステップの説明を表します。説明に続いて、最初に間隔グラフという用語を定義し、次にパーティション間隔を使用して間隔グラフを形成する方法を説明し、最後に間隔グラフのワークフローを説明します。 CREATE TABLE t (columns) 注: このプロセスは、WHERE 条件が上記の形式である場合にのみ機能し、テーブルの各列が任意の定数と等しいことが検証される必要があります (同じ定数が各列で同じである必要はありません)カラム）。たとえば、上記の例の WHERE ステートメントにcol1=const1 がない場合、p_func パーティション関数の値は計算されず、使用される実際のパーティション セットも制約されません。 インターバルウォーキング (ウォーキング) 次のように、パーティションテーブル t が列セット、パーティションタイプ p_type、およびパーティション関数 p_func として整数型フィールド int_col を使用するように定義されているとします。 (列) p_type(p_func(int_col)) 条件を減らすことができます。この WHERE ステートメントを次の関係に変換することで、この状況を一連の単一点間隔 (単一点間隔) に変換します: int_field=const1 + 1 OR ソースコードでは、この変換をインターバルウォーキング (ウォーキング) と呼びます。短い間隔で移動するコストは高くないため、パーティションの数を減らして小さなパーティションをスキャンできます。ただし、長い範囲を走査するのはあまり効率的ではなく、多数のパーティションをチェックする必要があり、その場合はすべてのパーティションがスキャンされる可能性があります。 次のパラメータは、範囲ウォーキング (ウォーキング) の値を決定します: #define MAX_RANGE_TO_WALK=10 注: 次の条件関係は、上記の範囲ウォーキング (ウォーキング) のロジックも使用します: const1 > ;= int_col > = const2 間隔マッピング (マッピング) 次のパーティション テーブル定義を仮定します: CREATE TABLE t (columns) テーブル t に対するクエリ ステートメントは次の形式のいずれかです: t.column <= const2 const1 <= t.col <= const2 => p_func(const1) <= p_func(t.column) <= p_func(const2) 注: この例では、間隔はは閉じており、2 つの境界値があります。ただし、同様の推論を他のタイプの間隔に拡張することもできます。分範囲パーティショニング。各パーティションは、パーティション関数値 -x------x----------x----------> 検索間隔 ----x===================x------> 前のセクションでは、基本的な WHERE 条件からアクティブなパーティション セットを推測するいくつかの方法について説明しました。すべては、これらのパーティションの推論方法が、範囲パーティション化 (RANGE パーティション化) と列挙パーティション化 (LIST パーティション化) のサブパーティションを除くサブパーティションに適していることを示しています。 各パーティションは同じ方法で分子パーティション化されているため、各パーティション内のどのサブパーティションがアクセスされるかを調べます。 パーティション プルーニングの場合、RANGE アナライザーは WHERE ステートメント、パーティションおよびサブパーティション関数で使用されるテーブルの列リストを使用して呼び出されます: subpart_col1, subpart_col2, ... subpart_colM) PARTITION BY LIST (pf) pf=1 AND (sp1='foo' AND sp2 IN (40,50)) (ルート) 上記图表、竖的边界（|）代表、横的（-）代表AND 】 sp1='baz' に到達したので、右に移動することも、サブパーティション間隔を構築することもできないことがわかります。 pf=8 を記録して返します 6. pf=9 から下に移動してみて、最後まで到達したことがわかり、ツアー グラフが完成しました。 注: 特定の状況下では、範囲アナライザー (RANGE Analyzer) の結果には、OR または AND 演算子で構成される複数の SEL_ARG グラフが含まれます。これは、非常に複雑な WHERE ステートメント、または単一の範囲リストを構築できない WHERE ステートメントで発生します。この状況では、パーティション プルーニング コードは適切な操作を使用します。例: ソースコードでのパーティションプルーニングの実装 ソースコードの簡単な説明: sql/opt_range.cc: sql/partition_info.h: classpartition_info { /* }; このコードには、すべてのパーティション間隔分析タイプを実装するメソッドが含まれています。 パーティションの取得 パーティション化されたテーブルが一連のインデックスの取得 (つまり、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 ( INSERT INTO t2 VALUES (1,1),(2,2),(3,3); クエリ条件は次のとおりです: SELECT * FROM t1, t2 上記は MySQL Internals Optimizer の内容です。その他の関連記事については、PHP 中国語 Web サイト (www.php.cn) に注目してください。
次の条件のように、OR ステートメント内に異なるメンバーを持つキーを含めるように単一の SEL_TREE オブジェクトを構築することはできません:
MySQL 5.0 では、これらの条件はインデックス マージ (INDEX MERGE) メソッドとレンジ オプティマイザー (範囲オプティマイザー) 構造クラス SEL_IMERGE によって処理されます。 SEL_IMERGE は、複数の SEL_TREE オブジェクトの論理和を表し、次のように表されます:
各 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 のスキャンが選択されます。早く更新されること。
(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 オブジェクトです。
単一の cond_key_i 条件は、SEL_ARG オブジェクトのポインターリンクされたネットワークによって表されます。各 SEL_ARG オブジェクトはキーの特定の部分を参照し、次の条件を表します:
sel_arg_cond= (inf_val < key_part_n AND key_part_n < sup_val) (1)
)
OR 4)
}
行取得フェーズ:
Unique の各 rowid に対して
{
行を取得して出力に渡します;
}
if (clustered_pk_scan)
{
while (clustered_pk_scan の次の行を取得します)
行を出力に渡します;
3. 転置
MySQL は、単純なステートメント式の転置 (関係演算子のオペランドの順序を逆にする) をサポートしています。言い換えると:
WHERE - 5 = column1
このステートメントは次のように変換できます:
WHERE column1 = -5
WHERE column1 = -5
AND クエリ形式は、次のように、条件 1 AND 条件 2 です:
このステップでは、オプティマイザーの意思決定プロセスについて説明します:
SELECT * FROM Table1 WHERE s1 = AND s2 = 5;
このクエリ オプティマイザーの決定は、順次完全テーブル スキャンを使用することです。
2 つの条件のフィールドが同じ場合、上記の特定の状況は使用できません。次のように:
この場合、ステートメントは RANG クエリであるため、インデックスが作成されます。このトピックは、IN 述語の説明で再び取り上げられます。
UNION ALL
SELECT * FROM TABLE1 WHERE column2 = 'y'
column1 と column2 の両方にインデックスが付けられている場合、各 SELECT はそれぞれインデックス スキャンを使用します。セットが結合されます。注: このクエリでシーケンシャル スキャン OR の例が使用されている場合、このクエリは同じ結果セットを生成する可能性があります。 hot not（＆lt;＆gt;）論理的条件は次のとおりです, MySQL この条件では変換ステートメントは作成されません。 RANG を使用してクエリを実行するとより良い結果が得られると思われる場合は、ステートメントを自分で手動で変換する必要があります。
WHERE column1 = 5
この場合、MySQL はステートメント変換を実行しません。
上記 2 つの状況に対する新しい最適化手法を追加することを楽しみにしています。
SELECT column1 FROM Table1 ORDER BY 'x';
オプティマイザーは、column1 のインデックスが存在する場合、それを使用します。
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 の後ろを参照してください。
すべての行をスキャンせずに行数を取得できます。これは MyISAM テーブルにのみ当てはまりますが、InnoDB テーブルには当てはまりません。さらに、このクエリ
SELECT COUNT(column1) FROM Table1;
SELECT DISTINCT column1 FROM Table1;
Into:
SELECT column1 FROM Table1 GROUP BY column1;
これら 2 つの条件が true の場合にのみ:
* GROUP BY はインデックスによって完了できます。これは、FROM ステートメントにテーブルが 1 つだけ存在し、WHERE ステートメントが存在しないことを意味します。
* LIMIT ステートメントはありません。
このセクションでは、その他のより特殊な最適化方法について説明します。
このパートでは、ref および eq_ref 接続タイプの NULL 値フィルタリングの最適化方法について説明します。
テーブル 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 述語チェックにより、それらのテーブルへのアクセスをスキップできます。
この最適化メソッドは、初期の NULL フィルタリングによって生成された null_rejecting 属性を再利用します。このチェックのソース コードについては、function join_read_always_key() を参照してください。
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 条件クエリがあるとします:
int_field=const1 + 2 OR
... OR
int_field=const2
const1 <= t.column
自己分割関数昇順です。以下を参照してください。 関係:
の検索間隔の範囲の軸を占めます。 ---- x ============ = x ----------＆gt;
検索間隔 [A, B]。使用されるパーティション セットにより、A から B まで実行され、この検索範囲内でポイントを収集するパーティションが決定されます。
レンジ アナライザー (RANGE Analyzer) の作業結果は SEL_ARG グラフと呼ばれます。これは非常に複雑な構造なので、ここでは説明しません。この文化的議論の現在の焦点は、すべてのパーティションを移動して、パーティションとサブパーティションの範囲を収集できることです。
SUBPARTITION BY HASH(sp1, sp2) (
パーティション p0 の値 (1)、
パーティション p1 の値 (2)、
パーティション p2 の値 (3)、
パーティション p3 の値 (4)、
パーティション p4 の値 (5)、
);
ここで、テーブル t に対する非常に複雑な WHERE ステートメント クエリを想定します:
| :
|パーティショニング : サブパーティショニング
| :
| :
| :
| +------+ : +-----------+ +--------+
---| pf=1 |----:-----| sp1='foo' |---| sp2=40 |
+------+ : +-----------+ +--------+
| : |
| : +--------+
| : | sp2=50 |
| : +--------+
| :
| :
+------+ : +-----------+ +--------+
| pf=3 |----:--+--| sp1='バー' |---| sp2=33 |
+------+ : | +-----------+ +----------+
| : |
+------+ : |
| pf=4 |----:--+
+------+ :
| :
| :
+------+ : +-----------+
| pf=8 |----:-----| sp1='baz' |
+------+ : +-----------+
前のプロセスから、サブパーティションの範囲を制限できなくなったので、推論: P3 パーティション セット内の各パーティションでは、すべてのサブパーティションが有効であると想定されます。
SELECT * FROM t1 WHERE Partition_id=10 OR subpartition_id=20
この例では、単一の範囲は構築されませんが、パーティションはプルーニングされます (パーティション プルーニング) ) コードは、使用されるパーティション セットが結合であると正しく推論します。
パーティション内のすべてのサブパーティションには、partition_id=10 の行が含まれ、各パーティション内のサブパーティションには subpartition_id=20 の行が含まれます。
このコードには、From WHERE Clauses to Intervals メソッド prune_partitions() の実装が含まれています。 PartitionPruningModule コードから始まる、パーティション プルーニングに関する詳細なコード コメントが 1 行ずつあります:
...
/*
使用されるビットマップ (つまり、プルーニングされていない)ここに、パーティション プルーニングの結果
が保存されます。
*/
MY_BITMAP used_partitions;
このパーティション テーブルで区間分析
を実行する関数へのポインター (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:
) keypart1 INT,
keypart2 INT,
KEY(keypart1, keypart2)
)
PARTITION BY HASH(keypart2);
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) の値を取り出し、アクセスするパーティションを見つけます。このパーティションが削除されると、他のパーティションにアクセスできなくなります。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
