MySQL自定义变量小结

发表于 | 阅读次数:

前言

最近阅读《高性能MySQL》，有不少收获。用户自定义变量是一个可以用来存储内容的临时容器，在连接MySQL的整个过程中都存在。下面的话大部分是从书上搬下来的。

限制

有些场景我们不能使用自定义变量

  • 使用自定义变量的查询，无法使用查询缓存
  • 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名、和LIMIT子句中
  • 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
  • 如果使用连接池或者持久话连接，自定义变量可能让看起来毫无关系的代码发生交互（如果是这样，通常是代码bug或者连接池bug，这类情况确实可能发生，前面是书上原话，此处存疑）
  • 在5.0之前的版本，是大小写敏感的，所以要注意代码在不同MySQL版本间的兼容性问题
  • 不能显示地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL版本中也可能不一样。用户自定义变量的类型在赋值的时候会改变，MySQL的用户自定义变量是一个动态类型，最好定义时就赋初值
  • MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行
  • 复制的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。实际情况可能很让人困惑
  • 赋值符号:=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号
  • 使用未定义变量不会产生任何语法错误

适用场景

1.优化排名语句

用户自定义变量可以在给一个变量赋值的同时使用这个变量。用户自定义变量具有“左值”的特性。
来简单的实现一个rownumber的功能：

1
2
SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum FROM actor;

书中还给了一个复杂的例子来演示排名的写法，Leetcode的练习题中也有类似例子178. Rank Scores

2.避免重复查询刚刚更新的数据

例如

1
2
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM t1 FROM id = 1;

使用变量，可以改写为

1
2
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

改写后仍需要两个查询，需要两次网络来回，但是无需访问任何数据表

3.统计更新和插入的数量

1
INSERT INTO t1(c1, c2) VALUES(4, 4), (2,1), (3,1) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + (0 * (@x := @x + 1));

这个写法十分巧妙，每次由于冲突导致更新时对变量@x自增一次，然后再乘0来避免影响要更新的内容。同时MySQL的协议会返回被更改的总行数，所以不需要单独统计这个值。

4.确定取值的顺序

用户自定义变量的一个最常见问题是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。
例如：

1
2
3
4
SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 as cnt
FROM actor
WHERE @rownum <= 1

结果只返回了两条结果，似乎符合预期（实际读取变量和赋值不在同一阶段，但是没有影响到结果）。而

1
2
3
4
5
SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 as cnt
FROM actor
WHERE @rownum <= 1
ORDER BY first_name;

却返回了200条结果，而且有cnt超过1的条目，通过explain语句我们发现

using filesort是造成这个问题的原因，WHERE条件是在文件排序操作之前取值的（ORDER BY导致一次性选取了所有满足WHERE条件的语句，换句话说，WHERE处的@rownum只读取了一次，值为0，所有数据都符合条件，WHERE执行在SELECT之前，之后不停给@rownum赋新值，我是这样理解的）。如果first_name有索引则结果正常，使用了覆盖索引并且没有触发filesort。

5.编写偷懒的UNION

下面的查询会在两个地方查找一个用户——一个用户表，一个长时间不活跃的用户表，不活跃的用户表的目的是为了实现更高效的归档：

1
2
3
SELECT id FROM users WHERE id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;

上面的查询即使在users表中已经找到了记录，也还是会去users_archived中再查找一次。我们可以用UNION查询来抑制，只有当第一个表中没有数据时，才在第二个表中查询。只要在第一个表中找到记录就定义一个变量@found通过在结果列中做一次赋值来实现，然后将赋值放在函数GREATEST中来避免返回额外的数据。为了明确结果来自哪一个表，新增了一个包含表名的列。最后需要在查询的末尾将变量重置为NULL，保证遍历时不干扰后面的结果。

1
2
3
4
5
6
SELECT GREATEST(@found := −1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
SELECT id, 'users_archived'FROM users_archived WHERE id = 1 AND @found IS NULL
UNION ALL
SELECT 1, 'reset' FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;

6.用户自定义变量的其他用处

  • 查询运行是计算总数和平均值
  • 模拟GROUP语句中的函数FITST()和LAST()
  • 对大量数据做一些数据计算
  • 计算一个大表的MD5散列值
  • 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变成0
  • 模拟读/写游标
  • 在SHOW语句的WHERE子句中加入变量值

7.总结

先了解SQL语句的执行顺序才能读懂这部分，其他用处我还没有试验。

8.参考资料

《高性能MySQL》