| 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;
得到的结果也许看起来没什么意义，这是因为主键是从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
以上就是MySQL 使用自定义变量进行查询优化的详细内容，更多关于MySQL 用自定义变量进行查询优化的资料请关注脚本之家其它相关文章！
