MySQL性能优化-函数调用优化

函数调用在MySQL内部分为确定性函数和不确定性函数。如果一个函数，对于给定的固定参数值，多次调用，返回的结果值不同，那么这样的函数就称之为不确定性函数，比如RAND(), UUID()。返回的结果值相同，则为确定性函数，比如POW(1,2)。

1. 确定性函数与不确定性函数的主要区别

先看一个例子，表结构如下：

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

两个查询SQL，第一个SQL使用确定性函数POW()，第二个SQL使用不确定性函数RAND()，如下：

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

• 对于第一个SQL，POW(1,2) 函数返回的是一个常量值，因此这个SQL最多返回一条记录。
• 对于第二个SQL，RAND()是一个不确定函数，对于不确定函数，表t中的每一行记录与条件进行匹配时，都会对where条件的值重新进行计算，因此第二个SQL有可能返回0条，1条或者多条记录。
• 使用不确定性函数，无法走索引，大部分场景都是全表扫描，性能低下。

2. 不确定性函数的影响

1. 不确定性函数不能返回一个常量值，优化器无法采用合适的优化策略，比如索引查找，导致全表扫描，影响性能。
2. 在InnoDB表中，对于只有一行记录的匹配，不确定性函数可能会将单行的行锁升级成一个范围锁。
3. 在基于statement的binlog格式复制时，使用不确定性函数会导致主从数据不一致。

3. 不确定性函数的优化

1. 使用变量来存储不确定函数的返回值，优化器在处理变量时，因为其值已经确定，可以当作一个常量来处理。

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

2. 将不确定函数转移到业务代码中，业务执行不确定函数，将返回的结果(常量)作为参数，拼接到SQL中，提高SQL性能。