MySQL 使用自定义变量进行查询优化
作者:岛上码农 发布时间:2024-01-25 05:24:41
目录
优化排序查询
避免重复获取刚刚修改的数据行
懒加载的联合查询
优化排序查询
自定义变量的一个重要特性是你可以同时将该变量的数学计算后的结果再赋值给该变量,类似于我们的 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;
得到的结果也许看起来没什么意义,这是因为主键是从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
来源:https://juejin.cn/post/6961757377645576228
猜你喜欢
- 前言今天在编码中,看到了一个非常经典的接口用法如下,于是查阅了相关资料,发现此种写法为接口型函数,本文对此做了细致的阐述。// A Gett
- Flask框架介绍Flask诞生于2010年,是Armin ronacher用Python语言基于Werkzeug工具箱编写的轻量级Web开
- 实时画图import matplotlib.pyplot as pltax = [] # 定义一个 x 轴的空列表用来接收动态
- 群里有人提出这么一个需求:每天都会传过来一份 Word 文档,里面有多个 Excel 附件,需要把 Excel 内容读取出来。第一反应是使用
- 今天晚上,笔者接到客户的一个需要,那就是:对多分类结果的每个类别进行指标评价,也就是需要输出每个类型的精确率(precision),召回率(
- 前言数据分析就是将数据以各种图表的形式展现给领导,供领导做决策用,因此熟练掌握饼图、柱状图、线图等图表制作是一个数据分析师必备的技能。Pyt
- 爬虫与反爬虫,这相爱相杀的一对,简直可以写出一部壮观的斗争史。而在大数据时代,数据就是金钱,很多企业都为自己的网站运用了反爬虫机制,防止网页
- 实现CBOW模型类初始化:初始化方法的参数包括词汇个数 vocab_size 和中间层的神经元个数 hidden_size。首先生成两个权重
- 这主要是写给我自己的,防止以后入坑,耗费时间。本文主要谈的是怎样安装Python解释器和Python开发工具PyCharm。 本机系统
- api文档 https://sms-activate.org/cn/api2要使用SMSActivateAPI库从sms-activate.
- <% dim total(7,2) total(1,1)=200 total(2,1)=800
- 具体代码见下。在此程序中,由于使用了变量,我们需将全部聊友的昵称用“,”(逗号)来隔开,储存到application("visit
- MySQL是一个非常流行的小型关系型数据库管理系统,2008年1月16号被Sun公司收购。目前MySQL被广泛地应用在Internet上的中
- 索引和切片相当于是对数组中内容的读(read)或者查询(inquiry)。是我们获取有用信息(demanded infomation)的重要
- 一: 安装命令jupyter: pip install jupyter如果缺少依赖,缺啥装啥二: 运行 jupyter notebook首先
- 相信用过Gmail的人都知道Gmail有一个草稿自动保存的功能,每过一段时间,Gmail都会自动保存邮件草稿,这样在一些突 * 况下就能快速地
- 描述event代表事件的状态,例如触发event对象的元素、鼠标的位置及状态、按下的键等等。event对象只在事件发生的过程中才有效。eve
- 引言什么是数据结构?数据结构是指相互之间存在着一种或多种关系的数据元素的集合和该集合中数据元素之间的关系组成。简单来说,数据结构就是设计数据
- 如何让页面超时并指定一个超时时间?下面就是利用缓冲的程序页面事例: <%@ OutputCache Du
- 随着JS与XHTML的应用普及,越来越多的web界面应用技术出现在网站上,比如我们常见的日历控件,搜索下拉框等,这些web界面应用技术大大的