Detailed explanation of Mysql function call optimization

Function call optimization

MySQL functions are marked internally as deterministic or nondeterministic. If a function given fixed values ​​for its arguments can return different results for different calls, it is undefined. Examples of nondeterministic functions: RAND() , UUID() .

If a function is marked non-deterministic, references to the function in a WHERE clause are evaluated for each row (when selecting from one table) or combination of rows (when selecting from a multi-table join).

MySQL also determines when to evaluate a function based on the type of its arguments (whether the arguments are table columns or constant values). Whenever a table column changes value, the deterministic function taking the table column as an argument must be evaluated.

Non-deterministic functions may affect query performance. For example, some optimizations might not be available, or more locking might be required. The following discussion uses RAND() but is also applicable to other uncertainty functions.

Assume a table t has the following definition:

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

Consider the following two queries:

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

Both queries appear to use the primary key lookup due to the equality comparison with the primary key, but this only applies to the first query:

• The first query always produces at most one row because POW() with a constant argument takes a constant value and is used for index lookups.
• The second query contains an expression using a non-deterministic function, RAND() which is not a constant in the query but actually has a new value t for each row of the table. Therefore, the query reads every row of the table, evaluates the predicate for each row, and outputs all rows where the primary key matches the random value. Depending on the id column value and the values ​​in the RAND() sequence, this can be zero, one, or more rows.
  

The effects of nondeterminism are not limited to SELECT statements. This UPDATE statement uses a nondeterministic function to select the rows to modify:

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

Presumably the intention is to update at most one row where the primary key matches the expression. However, it may update zero, one, or more rows, depending on the id column value and the values ​​in RAND() sequence.

The behavior just described has implications for performance and replication:

• Because a nondeterministic function does not produce a constant value, the optimizer cannot use other strategies that might be applicable, such as index seeks. The result may be a table scan.
• Instead of acquiring a single-row lock for a matching row, InnoDB might upgrade to a range-key lock.
• The update performed could not be determined to be unsafe for replication.
  

The difficulty arises from the fact that RAND() evaluates the function once for each row of the table. To avoid multi-function evaluation, use one of the following techniques:

• Move expressions containing nondeterministic functions to separate statements and store the values ​​in variables. In the original statement, replace the expression with a reference to a variable that the optimizer can treat as a constant value:

SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;

• Assign random values ​​to variables in a derived table. This technique causes the variable to be assigned a value before it is used in a comparison in the WHERE clause:

SET optimizer_switch = 'derived_merge=off';
UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
SET col_a = some_expr WHERE id = @keyval;

As mentioned previously, nondeterministic expressions in the WHERE clause may prevent optimization and cause a table scan. However, the WHERE clause can be partially optimized if the other expressions are deterministic. For example:

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

If the optimizer can use partial_key to reduce the set of selected rows, RAND() will be executed fewer times, which can reduce the impact of uncertainty on the optimization.

The above is a detailed explanation of MySQL function call optimization. For more information about MySQL function call optimization, please pay attention to other related articles on 123WORDPRESS.COM!