-
MySQL优化经验: 使用连接代替关联子查询
2021-02-22 15:12:07如果我们想要选取出各商品种类中高于该商品种类的平均销售单价的商品,可以用如下代码实现: SELECT product_type, product_name, sale_price FROM product AS p1 WHERE sale_price > (SELECT AVG(sale_price)MySQL优化经验: 使用连接代替关联子查询
一、什么是关联子查询
看一个例子,以下是一个商品清单表,包含的列有商品编号、商品名称、商品类型和商品价格
如果我们想要选取出各商品种类中高于该商品种类的平均销售单价的商品,可以用如下代码实现:SELECT product_type, product_name, sale_price FROM product AS p1 WHERE sale_price > (SELECT AVG(sale_price) FROM product AS p2 WHERE p1.product_type =p2.product_type GROUP BY product_type);
该语句中
WHERE
字段中包含了一个子查询,子查询中使用到了主查询中的p1
表,这种子查询被称为关联子查询。如果按照正常的先执行子查询,再执行主查询的方法执行显然无法识别p1表,因此关联子查询的执行顺序和正常SQL语句是不同的。二、关联子查询语句的执行顺序
在《简单易懂教你学会SQL关联子查询》这篇博客中,博主详细阐述了关联子查询的执行顺序。在阅读本文前建议读者先理解关联子查询的执行顺序。
总结如下:- 首先执行不带WHERE的主查询
- 根据主查询结果,依次将每项的product_type代入子查询,匹配product_type并求均价
- 将子查询结果再与主查询结合执行完整的SQL语句
可以看出,关联子查询是一类特殊的查询,其执行逻辑不同。
我们再审视一遍这种执行顺序会发现其中的问题。
由于主查询的p1表中有很多商品具有同样的product_type,而每一项都要去匹配子查询,找到该product_type的均价,因此,子查询做了很多次重复的计算,product_type和其均价应该只算一次即可,因此可以对此作出优化。优化方案
综上,product_type和每个类型的均价应该只计算一次,可以使用JOIN的方式,将子查询作为一个子表在执行FROM的时候与原表做内连接,这样可以控制每个商品类型的均价只计算一次,代码如下:
SELECT p1.product_type, product_name, sale_price FROM product AS p1, (SELECT product_type, AVG(sale_price) AS avg_price FROM product GROUP BY product_type) AS p2 WHERE p1.product_type = p2.product_type AND p1.sale_price > p2.avg_price;
数据来源:datawhale SQL组队学习
-
select 1 from ... sql语句中的1代表什么意思?
2020-07-16 16:45:34我们都知道,用exists代替in可以提高sql语句的执行效率,例如如下两个例子: 检索部门所在地为 NEW YORK’的员工信息。 使用IN select * from scott.emp where deptno in ( select deptno from scott.dept where ...我们都知道,用exists代替in可以提高sql语句的执行效率,例如如下两个例子:
检索部门所在地为 NEW YORK’的员工信息。使用IN
select * from scott.emp where deptno in ( select deptno from scott.dept where loc='NEW YORK');
使用 exists
select * from scott.emp where exists ( select 1 from scott.dept where scott.dept.deptno=scott.emp.deptno and loc='NEW YORK');
注意,这里出现了一个特殊用法select 1 ?
比如说,使用select 1 from table的结果是临时得到1列(列的值为1),其行数为表的记录数(行数),如果配合exists 语句则可以快速查询结果是否存在,而结果的具体数据不涉及到。
就像我上述提供的例子,它只查询验证dept表的字段deptno和emp的字段deptno是否有相等的情况,并且loc=‘NEW YORK’,而不需要知道dept表和emp表哪些记录存在那样的情况,也不需要知道相等情况下其他字段的值。在应用中,效率比select * 快。
扩展: select 1 from table;与select anycol(目的表集合中的任意一行) from table;与select * from table 从作用上来说是没有差别的,都是查看是否有记录,一般是作条件查询用的。select 1 from 中的1是一常量(可以为任意数值),查到的所有行的值都是它,但从效率上来说,1>anycol>*,因为不用查字典表。 -
索引会在什么情况下失效?(InnoDB,mysql8.0)
2019-07-21 13:54:53条件中有or 如果条件中有or,其中一个字段是索引字段,另一个是非索引字段,此时索引不会被使用,全...但其实完全可以用union来代替: select * from xd_student where id = 2 union select * from xd_studen...条件中有or
- 如果条件中有or,其中一个字段是索引字段,另一个是非索引字段,此时索引不会被使用,全表扫描。
- 如果条件中有or,左右是两个不同的独立索引字段,此时两个索引字段都会用。
但其实完全可以用union来代替:
select * from xd_student where id = 2 union select * from xd_student where room = 401
- 如果条件中有or,用的都是同一个索引字段,索引有用。
但其实完全可以用in来代替or:
select * from xd_student where id in (2,4);
使用like
如果以%开头,索引不能用;以%结束,索引能用但是不用。
列类型是字符串,没有使用引号
只有加链引号才会用索引。
索引字段表达式
【补充】
查看索引的使用情况show status like ‘Handler_read%’;
handler_read_key:这个值越大说明使用索引查询到的次数越多 handler_read_rnd_next:这个值越高,说明查询低效
- 如果条件中有or,其中一个字段是索引字段,另一个是非索引字段,此时索引不会被使用,全表扫描。
-
mysql jdbc怎么用问号传参_java – 如何在JDBC预准备语句中转义文字问号(‘?’)...
2021-02-07 09:24:44我想创建一个JDBC PreparedStatement,如:SELECT URL,LOCATE ( '?...我可以使用CHAR(63)代替’?’但我认为额外的函数调用会减慢SQL执行速度.有没有办法逃脱第一次?编辑:下面的代码测试dkatzel的断言是什么?...我想创建一个JDBC PreparedStatement,如:
SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC
哪一号?是文字和第二?是一个参数.我可以使用CHAR(63)代替’?’但我认为额外的函数调用会减慢SQL执行速度.有没有办法逃脱第一次?
编辑:
下面的代码测试dkatzel的断言是什么?字符串中的字符不被视为标记:
public class Test {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test");
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE TABLE Links(URL VARCHAR(255) PRIMARY KEY,pageId BIGINT)");
stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar?baz',1)");
stmt.executeUpdate("INSERT INTO Links(URL,pageId) VALUES('http://foo.bar/baz',1)");
stmt.close();
PreparedStatement ps = conn
.prepareStatement("SELECT URL,LOCATE ( '?', URL ) pos FROM Links WHERE pageId=? ORDER BY pos ASC");
ps.setLong(1, 1);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + ":" + rs.getInt(2));
}
rs.close();
ps.close();
conn.close();
}
}
输出:
http://foo.bar/baz:0
http://foo.bar?baz:15
似乎dkatzel是正确的.我搜索了JDBC Spec并且找不到任何提及的那个?如果参数标记在引号内,则会被忽略,但我发现的少数PreparedStatement解析器实现(MySql,c-JDBC,H2)似乎都将单引号中的文本排除在考虑范围内作为参数标记.
-
Mysql索引优化的总结
2019-07-31 18:47:351.如果条件中有or,即使其中有条件带索引也不会使用(这也... 也可以用 union 或者 union all 视情况来代替 如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union... -
mysql必学十大必会_mysql必知必会(下)
2021-02-08 08:53:13分组数据是什么分组数据是可以将数据分为多个逻辑组,以便对每个组进行汇聚计算的语句为什么用这个前面提到的知识都是割裂开的,单独的知识点,遇到一点稍微复杂一点的逻辑就不能胜任了,例如,有多个供应商提供多种... -
MySQL各个过滤条件的执行顺序
2019-01-20 08:59:10第一:from 第二:join on (无则略过,内联,左联,右联) ...第六步:having(分组以后就不能用where,别问我为什么.where都执行完了还怎么执行!!!所以出现了一个代替where的方法 而且里面可以写函数) 第七步:order ... -
mysql查询优化非索引_「mysql优化专题」单表查询优化的一些小总结,非索引设计(3)...
2021-02-08 14:13:49单表查询优化:(关于索引,后面再...(1)写sql要明确需要的字段,要多少就写多少字段,而不是滥用 select *(2)可以用使用连接(JOIN)来代替子查询(3)使用分页语句:limit start , count 或者条件 where子句时,有什么... -
非索引查询如何提高效率_单表查询优化的一些小总结,非索引设计
2021-01-15 16:44:37单表查询优化:(关于索引,后面再...(1)写sql要明确需要的字段,要多少就写多少字段,而不是滥用 select *(2)可以用使用连接(JOIN)来代替子查询(3)使用分页语句:limit start , count 或者条件 where子句时,有什么... -
oracle中的视图
2017-11-17 10:45:41视图也是一张表,不过是虚拟表,这张虚拟表的内容是由查询的内容定义的。 视图的创建: ...CREATE VIEW [Current Product List] AS ...其实视图可以被查询代替的,只是有时候这个查询语句总是使用,所以就 -
sql子查询
2018-04-22 16:07:001 什么是子查询 将一个子查询的结果拥有查询条件中。 2 子查询存在的问题 ...一般情况下可以用连接代替子查询。 SELECT * FROM t1 WHERE t1.a1 NOT in (SELECT a2 FROM t2 ) 优化后: SELECT * FROM t... -
SQL —— #{} 和 ${}
2020-11-17 21:15:55sql语句可以使用#{} 或${}进行传参,那么他们有什么区别,使用上有什么需要注意的地方呢? 解决 在预编译中的处理是不一样的。#{} 在预处理时,会把参数部分用一个占位符 ? 代替,变成如下的 sql 语句:select * ... -
SQL查询优化
2010-09-02 12:55:26我们来讨论一下select ... from ... where ... exists ....这个了。Exists的查询速度要比in强得多,这是众所周知的事,但我们并不可以说完全用exists来代替in。为什么这么说呢?我们先... -
mysql+没有被+标记_mysql没(+)的符号么
2021-02-05 15:27:38mysql没有(+)的符号么?oracle中语句可以这样写:SELECT * fromscript a,cust_info bwhere a.cust_...那么用什么来代替呢?附错误信息:---------------------------错误---------------------------SQL 执行错误 # ... -
mysql or 查询_MySQL中使用OR连接查询条件不会让索引失效
2021-01-18 19:25:57直觉上觉得虽然or连接的查询条件不能使用联合索引,但感觉两个单独的索引是可以用的。看了一些文章,感觉一直很疑惑。于是试了一下,发现果然如此。explain SELECT * from t WHERE id = 1 or uid = 2;idselect_... -
MySQL命令大全
2018-01-15 11:19:17mysqldump -u 用名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名... -
SQL in ASP: LIKE, NOT LIKE & BETWEEN
2004-08-31 03:51:00你已经在上面取出w打头记录的例子中看到了LIKE的用法。...假设你想取出5位数字的SKU号码,而且其开头是1结尾是5,那么你可以用下划符(_)代替%符号:SQL = "SELECT * FROM Products WHERE p_sku LIKE 1___5 -
在ASP中使用SQL语句之3:LIKE、NOT LIKE和 BETWEEN
2004-06-23 10:49:00你已经在上面取出w打头记录的例子中看到了LIKE的用法。...假设你想取出5位数字的SKU号码,而且其开头是1结尾是5,那么你可以用下划符(_)代替%符号:SQL = "SELECT * FROM Products WHERE p_sku LIKE 1___5 -
MYSQL常用命令大全
2011-05-30 13:31:24也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK。 2、你可以使用光标上下键调出以前的命令。但以前我用过的一个MYSQL旧版本不支持。我现在用的是mysql-3.23.27-beta-win。 二、显示... -
MySql基本查询、连接查询、子查询、正则表达查询讲解
2017-08-24 18:38:30用户可以根据自己对数据的需求,使用不同的查询方式。通过不同的查询方式,可以获得不同的数据。MySQL中是使用SELECT语句来查询数据的。在这一章中将讲解的内容包括。 1、查询语句的基本语法 2、在单表上查询数据 3... -
经典全面的SQL语句大全
2009-11-23 16:31:09假如你想选出10条记录(也许是每次页面装载时的10条链接的列表),你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成,但是 SELECT 语句只显示一种可能(这里的... -
springmybatis
2015-09-05 06:54:28无论是用过的hibernate,mybatis,你都可以法相他们有一个共同点: 1. 从配置文件(通常是XML配置文件中)得到 sessionfactory. 2. 由sessionfactory 产生 session 3. 在session 中完成对数据的增删改查和事务提交等. 4.... -
SQL查询安全性及性能优化
2012-03-07 20:51:39查找:可以理解为用索引进行查找 因此查找效率高于索引扫描效率 执行计划的意义 对于我们开发高质量SQL是很有帮助的 首先可以帮助我们查看SQL语句是否利用到索引,比如很复杂的SQL语句中有些用到索引,但是执行... -
Linux操作系统基础教程
2013-04-08 21:34:26关於通讯用的指令.........................................................................................................21 十. 编译器( Compiler )....................................................... -
韩顺平oracle学习笔记
2018-07-08 00:16:482)我想把emp所有的(增,删,改,查/insert,delete,update,select这四个一起可以用all代替) 操作权限赋给leng grant all on emp to leng;(此时登陆用户为scott) 案例2:这时我想把权限收回来怎么办呢, 这时用... -
Oracle_Database_11g完全参考手册.part1/3
2012-08-18 17:29:1313.2.4 用NOTEXISTS代替NOTIN 13.3 自然连接和内部连接 13.4 UNION、INTERSECT和MINUS 13.4.1 IN子查询 13.4.2 UNION、INTERSECT和MiNUS的限制 第14章 一些复杂的技术 14.1 复杂的分组 14.2 使用临时表 14.3 使用... -
常用类型判断以及一些有用的工具方法
2021-01-10 01:54:24其实都可以用 Object.prototype.toString.call 来判断,所以写在了一起: <pre><code> javascript // Add some isType methods: isArguments, isFunction, isString, isNumber, isDate, isRegExp, is...
-
一周内被程序员疯转5.6W次,最终被大厂封杀!
-
零基础一小时极简以太坊智能合约开发环境搭建并开发部署
-
【ssm项目源码】商店管理系统.zip
-
MT7681_IoT_Package_v1.30
-
PROGRAM GUIDE_MIXER PROCESSOR_G2D驱动使用手册.pdf
-
工程制图 AutoCAD 2012 从二维到三维
-
数据结构方阵.cpp
-
Liunx 优化思路与实操步骤
-
IDEA 先定义一个方法,自动创建方法
-
全志 A31S 开发板配套资料
-
图形图像处理.docx
-
数据结构约瑟夫.cpp
-
1_Linux操作系统上机考试题二.doc
-
研究生半年体会
-
从价关税计算器beta2
-
MySQL 数据库权限管理(用户高级管理和精确访问控制)
-
C/C++反汇编解密
-
react学习网站
-
Docker从入门到精通
-
WhatsApp将会在 WhatsApp 中展示一个横幅,提供更多隐私信息