利用 MySQL自定义变量高效查询

vlambda
2021-05-15

利用 MySQL自定义变量高效查询

上篇讲到了，估计很少人有用到，但是如果用好了也是可以辅助进行性能优化的。需要注意的是变量是基于连接会话的，而且可能存在一些意外的情况，需要小心使用。本篇介绍如何利用自定义变量进行查询优化，提高效率。

优化排序查询

自定义变量的一个重要特性是你可以同时将该变量的数学计算后的结果再赋值给该变量，类似于我们的 i = i + 1这种方式。下面是一个用于计算数据表行号的例子：

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum
FROM sakila.actor LIMIT 3;

actor_id	rownum
1	1
2	2
3	3

得到的结果也许看起来没什么意义，这是因为主键是从1自增的，因此行号和主键值是一样的。但是，这种方式可以用于做排序。例如需要查询饰演电影数量最多的前10名演员，通常的做法是像下面这样写：

SELECT actor_id, COUNT(*) as cnt
FROM sakila.film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10;

如果我们要获得相应的排名值的话，则可以引入变量来完成：

SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
SELECT actor_id,
    @curr_cnt := cnt AS cnt,
  @rank         := IF(@prev_cnt <> @curr_cnt, @rank+1, @rank) as rank,
  @prev_cnt := @curr_cnt AS dummy
FROM (
  SELECT actor_id, COUNT(*) AS cnt
  FROM sakila.film_actor
    GROUP BY actor_id
    ORDER BY cnt DESC
    LIMIT 10
) as der;

这里是将饰演电影的数量赋值给了 curr_cnt 变量，使用了prev_cnt 存储前一个演员的参演数量。排名从第一名开始的，如果后面的演员的数量和前一个演员的数量不同，则排名要往下（+1），如果相同则和前一个演员的排名相同。通过这种方式可以直接从查询结果中得到演员的排名，而不需要再从数据库查询做二次处理（当然也可以通过程序代码实现）。

避免重复获取刚刚修改的数据行

如果想在更新数据行的时候再重新获取数据行的信息，往往需要再读取一次数据库。这是因为 MySQL 不像 PostgreSQL 的 UPDATE RETURNING 功能可以同时返回更新后的数据行，而只是返回更新影响的行数。但是，我们可以通过自定义变量完成这样的操作。例如，获取刚刚被修改过更新时间的行，不使用自定义变量的话需要做一次额外的查询：

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

而使用自定义变量的时候可以避免这种情况：

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

虽然还是有一个查询操作，但是后面的查询操作不再需要访问数据库了。

懒加载的联合查询

假设我们需要写一个联合查询完成如下任务：在联合的分支上查找匹配的数据行，如果找到了就跳过其他分支。y这种情况发生在需要从热区数据或低频访问数据中查找（比如近期订单和历史订单）。这是下面针对用户查询的一个普通的 SQL：

SELECT id FROM users WHERE  id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;

这个查询会先从当前正在使用的用户表查询 id 为123的用户，然后在从已归档的用户表找同样 id 的用户。但是，这种写法比较低效，即便是在 users 表找到了想要找的用户，还是需要从users_archived 这个表再找一次，而实际用户 id 为123的只会存在其中的一张表中或两张表的数据是一样的。通过懒加载的联合查询，可以避免这种情况——只有在第一个分支没有找到数据时才进行第二个分支的查询。因此可以使用 MySQL 的 GREATEST 方法来作为查询结果的容器以避免多返回数据列。

SELECT GREATEST(@found := -1, id) AS id, users.name, 'users' as which_tb1
FROM users WHERE id = 123
UNION ALL
    SELECT id, users_archived.name, 'users_archived'
  FROM users_archived WHERE id = 123 AND @found IS NULL
UNION ALL
    SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL;

上述的查询如果第一行有结果，则@found 不会被赋值，因而是 NULL，从而执行第二次查询。而第三次的 UNION 实际没什么效果，只是为了将@found恢复到 NULL 值，以便这段 SQL 可以重复执行。另一个验证的方法是对同一张表进行这样的操作，可以发现实际只会返回一行数据或不返回数据（查询不到数据时）。

SELECT GREATEST(@found := -1, `id`) AS `id`, `infocenter_city`.`name`, 'city' as which_tb1 
FROM `infocenter_city` WHERE `id` = 460100 
UNION ALL 
    SELECT `id`, `infocenter_city`.`name`, 'infocenter_city' 
    FROM `infocenter_city` WHERE id = 460100 AND @found IS NULL 
UNION ALL 
    SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL