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性能。

发表评论