21.MySQL优化Function Call Optimization

最新推荐文章于 2025-03-18 17:41:48 发布

原创最新推荐文章于 2025-03-18 17:41:48 发布 · 232 阅读

CC 4.0 BY-SA版权

75 篇文章

订阅专栏

本文深入探讨了MySQL中的确定性和非确定性函数，详细解释了它们的定义和工作原理。非确定性函数如RAND()可能导致查询优化困难和性能下降，通过实例展示了如何优化这类函数的使用，以提高查询效率。

介绍

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()序列中的值。

非确定性函数也会影响UPDATE语句：

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

上述语句的目的可能是更新多行，但是其结果可能是更新0行，1行，或者多行，这取决于id主键的值以及RAND()的值。

上述行为可能对性能产生影响：

造成优化困难的原因是对于表的每一行 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()则执行的次数会减少，这会减少非确定性对优化的影响。