Documentation Home
MySQL 8.0 参考手册
MySQL 8.0 参考手册  / 第8章优化  / 8.2 优化SQL语句  / 8.2.1 优化 SELECT 语句  /  8.2.1.20 函数调用优化

8.2.1.20 函数调用优化

MySQL 函数在内部被标记为确定性或非确定性。如果给定参数的固定值，则函数是不确定的，它可以为不同的调用返回不同的结果。非确定性函数的示例： RAND(), UUID().

WHERE如果一个函数被标记为不确定的，则对每一行（从一个表中选择时）或行组合（从多表连接中选择时）评估子句中 对它的引用。

MySQL 还根据参数类型确定何时评估函数，无论参数是表列还是常量值。每当该列更改值时，必须评估将表列作为参数的确定性函数。

非确定性函数可能会影响查询性能。例如，某些优化可能不可用，或者可能需要更多锁定。以下讨论使用 RAND()但也适用于其他非确定性函数。

假设一个表t有这样的定义： 

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

考虑这两个查询： 

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

由于与主键的相等性比较，这两个查询似乎都使用了主键查找，但这仅适用于其中的第一个：

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

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

不确定性的影响不仅限于 SELECT陈述。此 UPDATE语句使用非确定性函数来选择要修改的行： 

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

据推测，其目的是最多更新主键与表达式匹配的一行。但是，它可能会更新零行、一行或多行，具体取决于 id列值和 RAND()序列中的值。

刚刚描述的行为对性能和复制有影响：

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

  • InnoDB可能会升级为范围键锁，而不是为一个匹配的行采用单行锁。

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

困难源于这样一个事实， RAND()即为表的每一行计算一次该函数。为避免多重函数评估，请使用以下技术之一：

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

    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()则执行的次数会更少，这会减少不确定性对优化的影响。