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);
由于与主键的相等性比较,这两个查询似乎都使用了主键查找,但这仅适用于其中的第一个:
不确定性的影响不仅限于
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
这种技术使变量在用于子句 中的比较之前被赋值一次 :SET optimizer_switch = 'derived_merge=off'; UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt SET col_a = some_expr WHERE id = @keyval;
如前所述,
WHERE
子句中的不确定表达式可能会阻止优化并导致表扫描。但是,如果其他表达式是确定性的,则可以部分优化该WHERE
子句。例如:
SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();
如果优化器可以使用partial_key
减少所选行的集合,
RAND()
则执行的次数会更少,这会减少不确定性对优化的影响。