mysql高级变量查询(MySQL 使用自定义变量进行查询优化)
mysql高级变量查询
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; |
得到的结果也许看起来没什么意义,这是因为主键是从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 用自定义变量进行查询优化的资料请关注开心学习网其它相关文章!
- myeclipse连接mysql数据库的方法(教你用eclipse连接mysql数据库)
- mysql几种连接方式(简单谈谈mysql左连接内连接)
- mysql支持存储表情(MySQL如何插入Emoji表情)
- mysql为啥使用b+树(MySQL用B+树作为索引结构有什么好处)
- mysql给字段加默认值(MySQL 如何处理隐式默认值)
- 修改mysql安装服务名称(Apache为mysql以及自己的项目设置虚拟路径)
- mysql 查询语法常见问题(MySQL 异常有这一篇就够了!)
- navicatformysql使用视图(Navicat for MySQL的使用教程详解)
- MySQL中ROUND四舍五入函数需要注意的地方
- mysql中如何进行模糊查询(MySQL模糊查询用法大全正则、通配符、内置函数)
- mysql常见错误分析(分析MySQL抛出异常的几种常见解决方式)
- deepin数据库编程(deepin20.1系统安装MySQL8.0.23超详细的MySQL8安装教程)
- MYSQL中GROUP BY不包含所有的非聚合字段时的注意事项
- mysql数据库与表的基本操作总结(Mysql、Oracle中常用的多表修改语句总结)
- MySQL自定义函数
- mysql读写分离代码实现(MySQL Router实现MySQL的读写分离的方法)
- 19号线二期全线电通 轨道交通项目最新进展来了(19号线二期全线电通)
- 涉及3条地铁线路 成都这4座轨道交通站点有新名字了(涉及3条地铁线路)
- 来了 成都轨道交通5条线路刷新 进度条(成都轨道交通5条线路刷新)
- 一部手机两套系统 OPPO Find X3的正确打开方式你知道吗(一部手机两套系统)
- OPPO用户看过来 汇总几个春节实用技巧,轻松搞定多设备联动玩法(汇总几个春节实用技巧)
- 北京旅游攻略(北京旅游攻略5日游及其花费)
热门推荐
- 日常操作docker命令(SEATA事务服务DOCKER部署的过程详解)
- ASP.NET中application对象的用法
- vue的弹框怎么用(vue自定义弹框效果确认框、提示框)
- phpstudy详细教程学习(phpstudy的php版本自由修改的方法)
- socket和tcp什么关系(TCP socket SYN队列和Accept队列区别原理解析)
- php项目开发实例(php项目中类的自动加载实例讲解)
- python柱状图第四节(python使用Plotly绘图工具绘制柱状图)
- 如何找到某个HTML元素所绑定的事件
- docker创建容器使用教程(Docker容器连接实现步骤解析)
- sql语言运算符主要内容(详解SQL EXISTS 运算符)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9