MySQL-5.7-8.2.1.18 Function Call Optimization

本文主要是介绍MySQL-5.7-8.2.1.18 Function Call Optimization，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL functions are tagged internally as deterministic or nondeterministic.

MySQL函数在内部被标记为确定性或非确定性。

A function is nondeterministic if, given fixed values for its arguments, it can return different results for different invocations.

如果给定函数参数的固定值，函数可以为不同的调用返回不同的结果，则该函数是不确定性的。

Examples of nondeterministic functions: RAND(), UUID().

不确定性函数的例子:RAND()， UUID()。

If a function is tagged nondeterministic, a reference to it in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join).

如果一个函数被标记为不确定性，则在WHERE子句中对它的引用将对每一行(从一个表中选择时)或行组合(从多个表连接中选择时)进行计算。

MySQL also determines when to evaluate functions based on types of arguments, whether the arguments are table columns or constant values.

MySQL还根据参数的类型决定什么时候对函数求值，参数是表列还是常量。

A deterministic function that takes a table column as argument must be evaluated whenever that column changes value.

当表列更改值时，必须对接受表列作为参数的确定性函数进行计算。

Nondeterministic functions may affect query performance.

不确定性函数可能会影响查询性能。

For example, some optimizations may not be available, or more locking might be required. The following discussion uses RAND() but applies to other nondeterministic functions as well.

例如，有些优化可能不可用，或者可能需要更多的锁定。下面的讨论使用RAND()，但也适用于其他不确定性函数。

Suppose that a table t has this definition:

假设表t有这样的定义

Consider these two queries:

考虑以下两个查询:

 Both queries appear to use a primary key lookup because of the equality comparison against the primary key, but that is true only for the first of them:

这两个查询似乎都使用了主键查找，因为对主键进行了相等比较，但这只对第一个查询是正确的:

• The first query always produces a maximum of one row because POW() with constant arguments is a constant value and is used for index lookup.

• 第一个查询总是生成最多一行，因为带有常量参数的POW()是一个常量值，用于索引查找。

• The second query contains an expression that uses the nondeterministic function RAND(), which is not constant in the query but in fact has a new value for every row of table t. Consequently, the query reads every row of the table, evaluates the predicate for each row, and outputs all rows for which the primary key matches the random value. This might be zero, one, or multiple rows, depending on the id column values and the values in the RAND() sequence.

• 第二个查询包含一个表达式,使用不确定性函数RAND(),这不是常数查询中但实际上有一个新值为每一行的表t。因此,查询读取表的每一行,评价谓词对于每一行,并输出所有行主键匹配的随机值。这可能是零、一或多行，取决于id列值和RAND()序列中的值。

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

不确定性的影响并不局限于SELECT语句。这个UPDATE语句使用一个非确定性函数来选择要修改的行:

Presumably the intent is to update at most a single row for which the primary key matches the expression. However, it might update zero, one, or multiple rows, depending on the id column values and the values in the RAND() sequence. 

这样做的目的大概是最多更新主键与表达式匹配的一行。但是，根据id列值和RAND()序列中的值，它可能更新0、1或多个行。

The behavior just described has implications for performance and replication:

才描述的行为对性能和复制有影响:

• Because a nondeterministic function does not produce a constant value, the optimizer cannot use strategies that might otherwise be applicable, such as index lookups. The result may be a table scan.

• 因为非确定性函数不会产生常量值，所以优化器不能使用可能适用的策略，例如索引查找。结果可能是表扫描。

• InnoDB might escalate to a range-key lock rather than taking a single row lock for one matching row.

• InnoDB可能会升级到一个范围键锁，而不是为一个匹配的行获取一个行锁。

• Updates that do not execute deterministically are unsafe for replication.

• 不能确定地执行的更新对于复制是不安全的。

The difficulties stem from the fact that the RAND() function is evaluated once for every row of the table. To avoid multiple function evaluations, use one of these techniques:

困难的根源在于RAND()函数对表中的每一行都进行一次计算。要避免多重函数计算，请使用以下技术之一:

Move the expression containing the nondeterministic function to a separate statement, saving the value in a variable. In the original statement, replace the expression with a reference to the variable, which the optimizer can treat as a constant value:

将包含不确定性函数的表达式移动到单独的语句中，将值保存在变量中。在原来的语句中，将表达式替换为变量的引用，优化器可以将其视为常量值:

Assign the random value to a variable in a derived table. This technique causes the variable to be assigned a value, once, prior to its use in the comparison in the WHERE clause:

将随机值赋给派生表中的一个变量。在WHERE子句中比较变量之前，这种技术会给变量赋值一次:

As mentioned previously, a nondeterministic expression in the WHERE clause might prevent optimizations and result in a table scan. However, it may be possible to partially optimize the WHERE clause if other expressions are deterministic. For example:

如前所述，WHERE子句中的不确定性表达式可能会阻止优化并导致表扫描。但是，如果其他表达式是确定性的，则可以部分优化WHERE子句。例如:

If the optimizer can use partial_key to reduce the set of rows selected, RAND() is executed fewer times, which diminishes the effect of nondeterminism on optimization. 

如果优化器可以使用partial_key来减少所选的行集，则RAND()执行的次数会更少，这就减少了不确定性对优化的影响。

这篇关于MySQL-5.7-8.2.1.18 Function Call Optimization的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！