Mysql関数呼び出しの最適化の詳細な説明

関数呼び出しの最適化

MySQL 関数は、内部的に決定論的または非決定論的としてマークされます。引数に固定値を与えられた関数が、呼び出しごとに異なる結果を返す場合、その関数は未定義です。非決定論的関数の例: RAND() 、 UUID() 。

関数が非決定的としてマークされている場合、 WHERE句内の関数への参照は、各行 (1 つのテーブルから選択する場合) または行の組み合わせ (複数のテーブル結合から選択する場合) に対して評価されます。

MySQL は、引数の型 (引数がテーブル列であるか定数値であるか) に基づいて、関数を評価するタイミングも決定します。テーブル列の値が変更されるたびに、テーブル列を引数として受け取る決定論的関数を評価する必要があります。

非決定論的関数はクエリのパフォーマンスに影響を与える可能性があります。たとえば、一部の最適化が利用できない場合や、より多くのロックが必要になる場合があります。以下の説明ではRAND()を使用しますが、他の不確実性関数にも適用できます。

テーブル t の定義が次のようになっているとします。

テーブル t を作成します (id INT NOT NULL PRIMARY KEY、col_a VARCHAR(100))。

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

tから*を選択します。WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

両方のクエリは、主キーとの等価比較により主キー検索を使用しているように見えますが、これは最初のクエリにのみ適用されます。

• 定数引数を持つPOW()は定数値を取り、インデックス検索に使用されるため、最初のクエリでは常に最大 1 行が生成されます。
• 2 番目のクエリには、非決定論的関数RAND()この関数はクエリ内の定数ではありませんが、実際にはテーブルの各行に新しい値 t があります。したがって、クエリはテーブルのすべての行を読み取り、各行の述語を評価し、主キーがランダム値と一致するすべての行を出力します。 id 列の値と RAND() シーケンスの値に応じて、0 行、1 行、またはそれ以上の行になります。
  

非決定性の影響はSELECTステートメントに限定されません。このUPDATEステートメントは、非決定論的関数を使用して変更する行を選択します。

t を更新します。set col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

おそらく、主キーが式と一致する行を最大 1 つ更新することが意図されています。ただし、 id列の値とRAND()シーケンスの値に応じて、0 行、1 行、またはそれ以上の行が更新される場合があります。

今説明した動作は、パフォーマンスとレプリケーションに影響を及ぼします。

• 非決定論的関数は定数値を生成しないため、オプティマイザーはインデックス シークなどの適用可能な他の戦略を使用できません。結果はテーブルスキャンになる可能性があります。
• 一致する行に対して単一行ロックを取得する代わりに、 InnoDB範囲キー ロックにアップグレードする場合があります。
• 実行された更新はレプリケーションにとって安全ではないと判断できませんでした。
  

困難さは、 RAND()テーブルの各行に対して関数を 1 回評価するという事実から生じます。複数関数の評価を回避するには、次のいずれかの手法を使用します。

• 非決定的関数を含む式を別のステートメントに移動し、値を変数に格納します。元のステートメントで、式を、オプティマイザーが定数値として扱うことができる変数への参照に置き換えます。

@keyval = FLOOR(1 + RAND() * 49) を設定します。
UPDATE t SET col_a = some_expr WHERE id = @keyval;

• 派生テーブル内の変数にランダムな値を割り当てます。この手法により、変数はWHERE句の比較に使用される前に値が割り当てられます。

optimizer_switch = 'derived_merge=off' を設定します。
t を更新します (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
col_a = some_expr を設定します。WHERE id = @keyval;

前述したように、 WHERE句内の非決定論的な式は最適化を妨げ、テーブル スキャンを引き起こす可能性があります。ただし、他の式が決定論的である場合、 WHERE句は部分的に最適化できます。例えば：

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

オプティマイザーがpartial_keyを使用して選択された行のセットを削減できる場合、 RAND()実行回数が少なくなり、不確実性が最適化に与える影響を軽減できます。

上記はMySQL関数呼び出しの最適化の詳細な説明です。MySQL関数呼び出しの最適化の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。