MySQL 嵌套查询优化案例

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

点击关注公众号,利用碎片时间学习

嵌套查询简单来说就是有子查询的SQL语句,子查询可以出现在SLECT, FROM或者WHERE 子句中,也可以单独用WITH子句来定义一个子查询。使用时子查询可以将一个复杂的查询拆分成一个个独立的部分,逻辑上更易于理解以及代码的维护和重复使用。有利于程序查询缓存,减少锁的竞争,减少查询冗余,应用层面相当于实现哈希关联。更容易对数据库进行拆分,做到高可用,易拓展,解耦。但是子查询另一个很明显的问题就是效率比较低,比如创建临时表和查询时重复扫表。所以我们可以根据不同的嵌套查询类型进行查询优化。

嵌套查询的几种主要类型

1.类型A:子查询返回的值是一个聚集函数的结果,并且与外查询独立,即子查询不使用外查询表中的字段。例如:

SELECT *
FROM exam e
WHERE e.grade = (SELECT max(grade) FROM exams)

这里查询执行时先遍历exam表,过程中exam表的每个元组都要再遍历得出最高的分数,即重复遍历表和计算。

由于子查询没有用到外查询的表exam, 所以可以单独估算子查询的开销,即看作一个常数。

所以可以直接定义该子查询为一个常量,如下:

DEFINE m = (SELECT max(grade) FROM exams)
SELECT *
FROM exam e
WHERE e.grade = m

2.类型N:子查询不包含与外查询的任何连接操作,并且没有聚集函数。例如:

SELECT *
FROM exam e
WHERE e.department IN (SELECT f.id 
                       FROM department f 
                       WHERE f.name="computer science")

这种类型的优化可以通过使用连接操作,如下:

SELECT *
FROM exam e,department f 
WHERE e.department = f.id AND f.name="computer science")

3.类型J:子查询依赖于外部查询的表,但是子查询没有聚集函数。如下:

SELECT OrderNr
FROM Orders O
WHERE ProdNr IN (SELECT ProdNr
                 FROM Shipping S
                 WHERE S.ShipNr = O.OrderNr
                 AND S.Date= current date)

对于Orders表中的每个元组来说,都要检索一次Shipping表,效率非常低下。可以转换成连接操作:

SELECT OrderNr
FROM Orders O,Shipping S
WHERE O.ProdNr = S.ProdNr
                AND S.ShipNr = O.OrderNr
                AND S.Date= current date

4.依赖连接dependent join

对于普通的连接操作,其公式为

MySQL 嵌套查询优化案例

而在嵌套查询中,还会出现依赖连接,即右表要先从左表中生成或引用左表,再与左表连接,公式为:

MySQL 嵌套查询优化案例

假设查询每个公司的最贵的产品,并返回产品和公司名:

SELECT DISTINCT c.cname, px.pname
FROM company c, product px
WHERE c.cid = px.cid
and px.price = (SELECT max(p.price)
FROM product p
WHERE c.cid=p.cid);

生成的查询计划如下

MySQL 嵌套查询优化案例

这里对product使用了聚集函数后又再与两个表连接再使用聚集函数,是一种nest loop join,进行了大量重复计算。所以可以直接将子查询放到From语句中,与另外两个表直接连接,这样查询计划会都使用更加高效的hash join的方式生成连接表再聚集。

来源:blog.csdn.net/xushiyu1996818/

article/details/120714528

推荐:

主流Java进阶技术(学习资料分享)

MySQL 嵌套查询优化案例
PS:因为公众号平台更改了推送规则,如果不想错过内容,记得读完点一下“在看”,加个“星标”,这样每次新文章推送才会第一时间出现在你的订阅列表里。“在看”支持我们吧!

原文始发于微信公众号(Java笔记虾):MySQL 嵌套查询优化案例