MySQL 使用自定义变量进行查询优化的解决办法

这篇文章主要为大家详细介绍了MySQL 使用自定义变量进行查询优化的简单示例，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随数据库教程的小编来看看吧！

优化排序查询

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

代码如下：

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

MySQL 使用自定义变量进行查询优化

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

代码如下：

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

MySQL 使用自定义变量进行查询优化

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

代码如下：

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

MySQL 使用自定义变量进行查询优化

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

代码如下：

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;

MySQL 使用自定义变量进行查询优化

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

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

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

代码如下：

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

MySQL 使用自定义变量进行查询优化

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

代码如下：

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

MySQL 使用自定义变量进行查询优化

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

懒加载的联合查询

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

代码如下：

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

MySQL 使用自定义变量进行查询优化

这个查询会先从当前正在使用的用户表查询 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;

MySQL 使用自定义变量进行查询优化

上述的查询如果第一行有结果，则@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

MySQL 使用自定义变量进行查询优化

以上就是MySQL 使用自定义变量进行查询优化的详细内容，更多关于MySQL 用自定义变量进行查询优化的资料请关注512笔记其它相关文章！

注：关于MySQL 使用自定义变量进行查询优化的简单示例的内容就先介绍到这里，更多相关文章的可以留意