Mysql 未确定函数优化

Mysql函数调用优化

 MySQL函数在内部被标记为确定性或非确定性。非确定性函数如:RAND()、UUID()等，如果给它的参数固定值，它可以对不同的结果返回不同的结果被调用。确定函数如POW(),log()等，如果给它的参数固定值，会返回同一个结果被调用。不确定性函数可能会影响查询性能。

CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, co_name VARCHAR(100), PRIMARY KEY(id));

INSERT INTO t1(co_name) VALUES('A');
INSERT INTO t1(co_name) VALUES('B');
INSERT INTO t1(co_name) VALUES('C');
INSERT INTO t1(co_name) VALUES('E');
INSERT INTO t1(co_name) VALUES('F');
INSERT INTO t1(co_name) VALUES('G');

 

EXPLAIN
SELECT * FROM t1 WHERE id = POW(1,2);

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

EXPLAIN
SELECT * FROM t1 WHERE id = FLOOR(1 + RAND()*10);

第二个查询包含一个使用非确定性函数RAND()的表达式在查询中不是常量，但实际上对于表t的每一行都有一个新值。主键与随机值匹配的。这可能是0行，1行，或者多行。这容易导致查询使用全部扫描。

 

 

非确定函数的影响并不局限于选择语句。此UPDATE语句使用非确定性函数，用于选择要修改的行:
update t1 SET co_name= 'AB' WHERE id = FLOOR(1 + RAND() * 49);
但是，它可能更新零行、一行或多行，具体取决于id列值和RAND()序列中的值。

1. 非确定性函数不会产生常数值，所以优化器不能使用主键索引。

2. 未确定地执行的更新对于复制是不安全的。

3. InnoDB可能升级为一个范围键锁甚至是表锁，而不是对一个匹配的行使用单行锁。

 

为了避免多重函数计算，请使用以下方法：

1. 将包含非确定性函数的表达式保存到变量中。在where条件中使用这个变量。，
优化器可以作为一个常值:
SET @id= FLOOR(1 + RAND() *10);
update t1 set  co_name = 'AB' WHERE id = @id;

 

 

2. 将随机值赋给派生表中的变量，赋值一次，在WHERE子句的比较中使用之前:

 

UPDATE /*+ NO_MERGE(c) */ t1, (SELECT FLOOR(1 + RAND()*10) AS a) AS c
SET co_name = 'AB'
WHERE id = c.a;