MySQLのクエリを定数で最適化するにはどうすればいいですか？

注意：最初の質問は議論の余地がありますが、適切なものについては一番下までスキャンしてください。

最適化したいクエリがあります。これは次のようになります。

select cols from tbl where col = "some run time value" limit 1;

どのキーが使用されているのかを知りたいのですが、説明のために何を渡しても、定数を指定しているため、where句を何も最適化できません（ "不可能なWHERE気付き…"）。

で一定の最適化を行わないようにmysqlに指示する方法はありますか説明する？
私は何かが足りないのですか？
必要な情報を入手するためのより良い方法はありますか？

編集： `EXPLAIN`は定数値から得られるクエリプランを私に与えているようです。クエリはストアドプロシージャの一部であるため（そしてspocsのIIRCクエリプランは呼び出される前に生成されます）、値が一定ではないため、これは役に立ちません。私が欲しいのは、実際の値がどうなるかわからないときにオプティマイザがどのクエリプランを生成するかを見つけることです。

私はなにが足りないのですか？

編集2：他の場所で尋ねると、MySQLは、クエリプランを再利用させようとしない限り、常にクエリプランを再生成するようです。ストアドプロシージャでも。これから私の質問は議論の余地がないように思えます。

しかし、それは私が実際に知りたかったことにはなりません。 特定のクエリ内で定数である値を含むクエリを最適化するにはどうすればよいですか。？ - たとえば、私のクライアント側のコードが `where`節に数字を入れてクエリを生成しているとします。場合によっては、その数によって、不可能なwhere句が発生することもあります。 Explainを使用して、照会が最適化されている程度を調べる方法はありますか。

私はすぐに私が見ている最善のアプローチは、存在する/存在しないケースの完全なマトリックスのためにそれに対して `EXPLAIN`を実行することでしょう。実際には、手作業で行うのは困難でエラーが発生しやすいため、これはあまり良い解決策ではありません。

ソース

3 回答

指定した値が列に含まれていないため、定数ではないため、「不可能なWHEREに気付いた」と表示されます。 1）列に存在する値を使うか、2） `col = col`と言うだけです。

explain select cols from tbl where col = col;

ソース

_ _ たとえば、クライアント側のコードがwhere句に数値を含むクエリを生成しているとします。

数によっては不可能なwhere句になることもあればそうでないこともあります。

Explainを使用して、照会が最適化されている程度を調べる方法はありますか。 _ _

`MySQL`はバインドされたパラメータの値ごとに異なるクエリプランを構築します。

このhttp://s.petrunia.net/blog/?p=16[article]では、 `MySQL`オプティマイザが何をするのかのリストを読むことができます。

    Action                                      When

    Query parse                                 PREPARE
    Negation elimination                        PREPARE
    Subquery re-writes                          PREPARE

    Nested JOIN simplification                  First EXECUTE
    OUTER->INNER JOIN conversions               First EXECUTE

    Partition pruning                           Every EXECUTE
    COUNT/MIN/MAX elimination                   Every EXECUTE
    Constant subexpression removal              Every EXECUTE
    Equality propagation                        Every EXECUTE
    Constant table detection                    Every EXECUTE
    ref access analysis                         Every EXECUTE
    range/index_merge analysis and optimization Every EXECUTE
    Join optimization                           Every EXECUTE

このリストにはもう一つ欠けているものがあります。

MySQL`は_全ての JOIN`繰り返しでクエリプランを再構築することができます：そのような 各レコードの範囲チェック。

テーブルに複合インデックスがある場合

CREATE INDEX ix_table2_col1_col2 ON table2 (col1, col2)

そしてこのようなクエリ：

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 = t1.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

、 MySQL`は（t1.value1、t1.value2_lowerbound） から（t1.value1、t1.value2_upperbound） へのインデックス RANGE`アクセスを使用しません。代わりに、それはインデックス（REF）アクセスを（t1.value）に使用し、間違った値を除外します。

しかし、あなたがこのようにクエリを書き直すならば：

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 <= t1.value1
        AND t2.col1 >= t2.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

そうすると、 MySQL 'は table1`_の各レコードについてインデックス RANGE`アクセスを再確認し、その場で RANGE`アクセスを使用するかどうかを決定します。

あなたは私のブログでこれらの記事でそれについて読むことができます：

Selecting タイムゾーンのタイムスタンプ-タイムゾーンなしでタイムスタンプを除外するために粗いフィルタリングを使用する方法
*Emulating SKIP SCAN *-MySQL`で SKIP SCAN`アクセスメソッドをエミュレートする方法
http://explainextended.com/2009/03/12/analytic-functions-optimizing-lag-lead-first_value-last_value/[*Analytic 関数：LAG、LEAD、FIRST_VALUE、LAST_VALUE *の最適化-Oracleの分析関数を「MySQL」でエミュレートする方法
Advanced 行サンプリング-MySQL`の各グループから N`レコードを選択する方法

これらすべての事柄は「各レコードの範囲チェック」を採用しています

*あなたの質問に戻って：*定数が与えられる前に計画がないので、どの計画が `MySQL`がすべての与えられた定数に対して使用するかを言う方法はありません。

残念ながら、バインドされたパラメータの値ごとに1つのクエリプランを使用するようにMySQLに強制する方法はありません。

STRAIGHT_JOIN`と FORCE INDEX`節を使うことで JOIN`の順番と INDEX`の選択を制御できますが、それらはインデックス上の特定のアクセスパスを強制したり、 `IMPOSSIBLE WHERE`を禁止したりすることはありません。

一方、すべての JOIN`では、 MySQL`は NESTED LOOPS`だけを採用しています。つまり、正しい `JOIN`順序を構築したり、正しいインデックスを選択したりすると、おそらく MySQL`はすべての `IMPOSSIBLE WHERE`の恩恵を受けるでしょう。

ソース

_ クエリに対してのみ一定であるが、どの値が使用されるかを事前に知らない場合、クエリを最適化するにはどうすればよいですか？ _

特定の列にインデックスを使用することにより（あるいは、与えられた列を常に一緒に照会する場合は列の組み合わせに対しても）。インデックスがある場合、クエリプランナーはそれらを潜在的に使用します。

「不可能な」値について：問い合わせプランナは、与えられた値はいくつかの情報源からの表にはないと結論付けることができます。

特定の列にインデックスがある場合は、特定の値がインデックス内のどの値よりも大きいまたは小さい（最小/最大値はインデックスから抽出するのに一定の時間がかかります）
間違った型を渡した場合（数値を求めている場合）列をテキストと等しくする）

PS. 一般に、クエリプランの作成後は条件が変更されている可能性があり、より優れたクエリプランが存在する可能性があるため、クエリプランの作成はコストがかからず、再利用するよりも再作成する方が適しています。

ソース