MySQL 関数は、内部的に決定論的または非決定的としてタグ付けされます。 関数に決定性がない状態とは、引数の値が固定されていても、呼び出しごとに異なる結果が返されることがある場合です。 非決定的関数の例: RAND()、UUID()。
関数が非決定的にタグ付けされている場合、WHERE 句でのその関数への参照は、行ごと (あるテーブルから選択する場合) または行の組合せごと (複数テーブル結合から選択する場合) に評価されます。
MySQL では、引数がテーブルのカラムであるか定数値であるかにかかわらず、引数のタイプに基づいて関数を評価するタイミングも決定されます。 テーブルのカラムを引数として取る決定的関数は、そのカラムの値が変更されるたびに評価される必要があります。
非決定的関数は、クエリーのパフォーマンスに影響を与える可能性があります。 たとえば、一部の最適化を使用できない場合や、より多くのロックが必要になる場合があります。 次の説明では、RAND() を使用しますが、他の非決定的関数にも適用されます。
テーブル t に次の定義があるとします:
CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));次の 2 つのクエリーについて考えてみます:
SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);主キーに対する等価比較のため、両方のクエリーで主キー参照が使用されているように見えますが、これは最初のクエリーにのみ当てはまります:
定数引数を持つ
POW()は定数値であり、インデックスルックアップに使用されるため、最初のクエリーでは常に最大 1 行が生成されます。2 番目のクエリーには、非決定的関数
RAND()を使用する式が含まれています。これはクエリーでは定数ではありませんが、実際にはテーブルtの各行に新しい値があります。 したがって、クエリーはテーブルのすべての行を読み取り、各行の述語を評価して、主キーがランダム値と一致するすべての行を出力します。 これは、idのカラム値およびRAND()順序内の値に応じて、ゼロ、1 または複数の行になります。
非決定の影響は、SELECT ステートメントに限定されません。 次の UPDATE ステートメントでは、非決定的関数を使用して、変更する行を選択します:
UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);
主キーが式と一致する単一の行のみを更新することを意図しています。 ただし、id カラムの値および RAND() 順序の値によっては、ゼロ、1 または複数の行が更新される場合があります。
ここで説明した動作は、パフォーマンスとレプリケーションに影響します:
非決定的関数では定数値が生成されないため、オプティマイザは、それ以外の場合には適用可能な戦略 (インデックス参照など) を使用できません。 結果はテーブルスキャンである可能性があります。
InnoDBは、一致する行に対して単一行ロックを取得するのではなく、範囲キーロックにエスカレートする場合があります。決定的に実行されない更新は、レプリケーションに対して安全ではありません。
問題は、RAND() 関数がテーブルのすべての行に対して 1 回評価されるという事実から発生します。 複数の関数の評価を回避するには、次のいずれかの方法を使用します:
-
非決定的関数を含む式を別のステートメントに移動し、値を変数に保存します。 元のステートメントで、式を変数への参照に置き換えます。この変数は、オプティマイザで定数値として処理できます:
SET @keyval = FLOOR(1 + RAND() * 49); UPDATE t SET col_a = some_expr WHERE id = @keyval; -
導出テーブルの変数にランダム値を割り当てます。 この方法では、
WHERE句での比較で変数を使用する前に、変数に値が一度割り当てられます:UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt SET col_a = some_expr WHERE id = dt.r;
前述のように、WHERE 句の非決定的な式によって最適化が妨げられ、テーブルスキャンが発生する可能性があります。 ただし、他の式が決定的である場合は、WHERE 句を部分的に最適化できます。 例:
SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();
オプティマイザが partial_key を使用して選択された行セットを減らすことができる場合、RAND() の実行回数が少なくなり、最適化に対する非決定の影響が低下します。