exist优化 in mysql_mysql优化 exists/in改写join

最新推荐文章于 2021-06-14 17:41:01 发布
最新推荐文章于 2021-06-14 17:41:01 发布
阅读量690 收藏
点赞数 1
文章标签： exist优化 in mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33462804/article/details/112940499
版权
本文分析了一个存在性能问题的MySQL查询语句，该语句使用了EXISTS子查询。通过将EXISTS改写为JOIN，显著提高了查询效率，性能提升了2000倍。此外，还分享了其他类似的优化案例，如OR条件优化和IN子查询的JOIN改写，展示了优化查询结构对于提升数据库性能的重要性。
摘要由CSDN通过智能技术生成

问题语句

SELECT * FROM a

WHERE `type` = 'appointment'

AND `event` = 14

AND EXISTS (

SELECT * FROM b

WHERE a.`sheet_id` = b.`id`

AND `company_id` = 8

AND b.`deleted_at` IS NULL

)

ORDER BY a.id DESC

LIMIT 6;

解读执行计划

在exists类型的子查询的执行计划中，select_type一栏分别是PRIMARY和DEPENDENT SUBQUERY

DEPENDENT SUBQUERY的意思是：子查询，依赖于外层的查询;DEPEND SUBQUERY是依赖于SQL的主体部分，它的执行次数最大可能和SQL主体部分结果的行数一样多(这里因为有limit6，所以看起来主表扫描行数是6，如果去掉这个limit6的话，这个值是1500W)

上面这句话解释得通俗一点就是外连接先执行查询，然后把查询的结果集放入子查询内进行匹配；外查询每执行一次查询，就要来子查询匹配一次

join的执行计划中，select_type一栏都是simple

join的第一行的就是外表

从上面的对比可以看出，无论是

exists类型的子查询还是join，都基本可以看作遵循了第一行就是驱动表的规则(注意不是所有子查询都遵循这个规则，本篇只针对exists类型的dependent subquery)

分析

图一PRIMARY对应的表就是图二中的a表，DEPENDENT SUBQUERY表就是图二中的b表；a表有1500W行数据，b表有2W行数据

所以图一的SQL执行效率如此低下的原因就是大表驱动小表

优化

exists改写为join

SELECT a.*

FROM  a join b on a.`sheet_id` = b.`id`

WHERE a.`type` = 'appointment'

AND a.`event` = 14

AND b.`company_id` = 8

AND b.`deleted_at` IS NULL

ORDER BY a.`id` DESC

LIMIT 6;

由于a表作为内表，因此在a.`sheet_id`，a.`type`，a.`event`上创建联合索引；语句中出现了b表的本地谓词，所以b表的b.`company_id`，b.`deleted_at`上也要创建联合索引

优化结果，执行时间：117s→0.36s，性能提升了2000倍

这个语句有一个更极端的取值，在b.`company_id` = 2的时候，小表不会搜出任何满足条件的结果，在这种情况下，原语句执行时间在350s以上，而新语句仅需要0.03s，性能提升万倍

优化案例

今天优化的这批语句中，大多数是exists子查询的问题，可以看出这个研发小哥非常的喜欢用exists这种写法；前面的那个exists语句是泛用型，后面的exists语句加了些新花样

eg.

SELECT SUM(`xxxx`) AS ag

FROM a

WHERE EXISTS (

SELECT * FROM b

WHERE a.`delivery_sheet_id` = b.`id`

AND (`status` = 4

OR `is_rejected` = '1')

AND `company_id` = 8

AND b.`deleted_at` IS NULL

)

AND `status` IN (0, 4)

AND `collection_type` IN (2, 3)

AND a.`deleted_at` IS NULL;

or的优化通常改写union，但这里是求sum不能这么改，需要改写成2个语句然后求和；对应的列要建好索引

select c.ag+d.ag as ag from

(SELECT SUM(a.`xxxx`) AS ag

FROM  a join b

on a.`delivery_sheet_id` = b.`id`

where

b.`status` = 4

ANDb.`company_id` = 8

AND b.`deleted_at` IS NULL

AND a.`status` IN (0, 4)

AND a.`collection_type` IN (2, 3)

AND a.`deleted_at` IS NULL) c,

(

SELECT SUM(a.`xxxx`) AS ag

FROM a join b

on a.`delivery_sheet_id` = b.`id`

where

b.`is_rejected` = '1'

ANDb.`company_id` = 8

AND b.`deleted_at` IS NULL

AND a.`status` IN (0, 4)

AND a.`collection_type` IN (2, 3)

AND a.`deleted_at` IS NULL) d;

优化结果，执行时间：18s→0.2s

in改写join的思路和exists差不多

这里没有现成的例子，粘贴一篇郑松华老师公众号的分析过来

原语句

SELECT

COUNT( * ) AS totalNum,

sum( CASE WHEN F.ALARM_LEVEL = 1 THEN 1 ELSE 0 END ) AS LEVELS1,

sum( CASE WHEN F.ALARM_LEVEL = 2 THEN 1 ELSE 0 END ) AS LEVELS2,

sum( CASE WHEN F.ALARM_LEVEL = 3 THEN 1 ELSE 0 END ) AS LEVELS3,

sum( CASE WHEN F.DEAL_STATE = 0 THEN 1 ELSE 0 END ) AS DESTS

FROM

F

LEFT JOIN  DC ON DC.ID = F.CONST_ID

LEFT JOIN  V ON V.ID = F.VEHICLE_ID

LEFT JOIN  AREA ON AREA.ID = V.SYS_DIVISION_ID

WHERE

DC.ID IS NOT NULL

AND V.ID IS NOT NULL

AND F.DEAL_STATE = 0

AND ALARM_LEVEL IN ( 1, 2, 3 )

AND F.VEHICLE_ID IN (

SELECT

VEHICLE_ID

FROM

GVLK

WHERE

GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )

UNION

SELECT

VEHICLE_ID

FROM

UVLK

WHERE

USER_ID = 'ff8080816091b09c0161f9b825750a9a'

)

AND date( F.ALARM_TIME ) BETWEEN '2000-01-01'

AND '2018-08-14'

AND AREA.PATH LIKE CONCAT( ( SELECT ARE.PATH FROM  ARE WHERE ARE.ID = '0' ), '%' )

执行计划如下

改写如下(in改join)

explain extended

SELECT

COUNT( * ) AS totalNum,

sum( CASE WHEN F.ALARM_LEVEL = 1 THEN 1 ELSE 0 END ) AS LEVELS1,

sum( CASE WHEN F.ALARM_LEVEL = 2 THEN 1 ELSE 0 END ) AS LEVELS2,

sum( CASE WHEN F.ALARM_LEVEL = 3 THEN 1 ELSE 0 END ) AS LEVELS3,

sum( CASE WHEN F.DEAL_STATE = 0 THEN 1 ELSE 0 END ) AS DESTS

FROM

F

straight_join (

SELECT

VEHICLE_ID

FROM

GVLK

WHERE

GROUP_ID IN ( SELECT GROUP_ID FROM GULK WHERE USER_ID = 'ff8080816091b09c0161f9b825750a9a' )

UNION

SELECT

VEHICLE_ID

FROM

UVLK

WHERE

USER_ID = 'ff8080816091b09c0161f9b825750a9a'

) s on F.VEHICLE_ID = s.VEHICLE_ID

straight_join DC ON DC.ID = F.CONST_ID

straight_join V ON V.ID = F.VEHICLE_ID

straight_join AREA ON AREA.ID = V.SYS_DIVISION_ID

WHERE

DC.ID IS NOT NULL

AND V.ID IS NOT NULL

AND F.DEAL_STATE = 0

AND ALARM_LEVEL IN ( 1, 2, 3 )

AND date( F.ALARM_TIME ) BETWEEN '2000-01-01'

AND '2018-08-14'

AND AREA.PATH LIKE CONCAT( ( SELECT ARE.PATH FROM ARE WHERE ARE.ID = '0' ), '%' )

曹不空
关注
MySQL中IN和EXISTS语法改写（SQL优化案例）
清平乐的技术专栏
04-22 3050
一、IN和EXISTS说明 （1）IN和NOT IN常用于where表达式中，其作用是查询某个范围内的数据。 （2）IN和NOT IN语句分别可以用EXISTS和NOTEXISTS 进行改写 （3）EXISTS某些情况下可以比IN提高运行效率； 而用NOT EXISTS都比NOT IN要快 （4）如果两个表中一个较小，一个是大表， 则建议子查询表大的用exists，子查询表小的用in 【注意】具体用法可以参考博文《MySQL中EXISTS的用法》 二、示例 表A（小表），表B（大表） 示例一： -- I
mysql in转化为join_mysql 优化例子：IN 换 INNER JOIN
weixin_33456137的博客
01-27 629
今天撸代码时，遇到SQL问题：要将A表查询的ID，匹配B表的ID，并将B表全部内容查询出来：未优化前：MySQL[xxuer]>SELECT->COUNT(*)->FROM->t_cmdb_app_version->WHERE->idIN(SELECT->pid->...
mysql in 改为 join_MySQL:in与join的优化
weixin_32028323的博客
02-07 1275
原sql：mysql> select count(*) as num from gp as p where p.goods_id in (select goods_id from gg where gg.category_id = 62) and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0 LIMIT 1;+...
mysql in 改写_Case：update中把in改写成join性能提高数倍
weixin_28773055的博客
02-05 357
(1)优化前如下一条SQL，把从1985-05-21入职前的员工薪资都增加500，执行约20.70 s，从执行计划中可以看出对表salaries进行的是索引全扫描，扫描行数约260W行。mysql>updatesalariessetsalary=salary+500whereemp_noin(selectemp_nofromemployeeswherehire_da...
简记MYSQL调优--IN改为INEER JOIN ON
heihei36的专栏
07-09 516
近期有一个关于排行榜的需求，有以下几点需求： 一、排行榜只显示前500名 二、同一用户发布多个文章，取点赞量最高的那个文章 三、点赞数相同，先达到这个点赞数的前在前 建表语句及表设计： CREATE TABLE `t_article_thumbup_cnt` ( `user_id` bigint(20) NOT NULL, `article_id` varchar(128) N...
mysql in 子查询优化_mysql in 子查询 效率慢 优化（转）
weixin_30119251的博客
01-27 995
mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：例如：表A(小表)，表B(大表)1...
MySQL优化之exists、in
qq_41797857的博客
05-20 207
MySQL优化之exists、in 很多关于MySQL关于exists和in的介绍中，都说使用exists优于in，其实这种说法是不准确的，要根据实际场景来进行分析。 以前使用的方式： --出现笛卡儿积，占用内存 select * from A a, B b where a.id = b.id; 优化原则：小表驱动大表，即小的数据集驱动大的数据集。 使用in的情况： select * from...
MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
老叶茶馆
04-19 3415
继续给MySQL 8.0吹吹水MySQL在8.0.16版本之前，对 IN 和 EXISTS 处理是不一样的，EXISTS只能采用子查询方式，所以执行计划中能看到DEPENDENT SUB...
mysql 优化in 和exists
net@core的博客
05-22 1708
in 和exists in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。 一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 例如： 表A（小表），表B（大表）1： ...
msql实战优化（一）用leftjoin 代替notin 或notexists
牧码人博客@luckyhe.com
09-10 1239
文章目录优化背景业务需求分析总结 《 msql实战优化（一）用leftjoin 代替notin 或notexists》首发牧马人博客转发请加此提示 优化背景 header表:100w数据量，并且表结构有265个字段。（祖传表设计不想吐槽） line表：行数据明细,数据量90w。(每一条明细有个个headerid关联了header表) rule表：规则表，小量数据。 所有关键字段都建了索引。如果没有索引这样的数据量会慢的跟蜗牛一样。 业务需求 我要取得与line表中业务规则为rule表的所有header数据。
mysql in查询速度慢，试试改用join
XuYYY的博客
10-22 1108
这是在一个项目中遇到的问题，项目原来的代码使用了in进行查询，速度非常慢，后面我使用left join之后速度得到很大的提升 修改前： SELECT `c`.*, pir_total_amount, CASE WHEN pci.count IS NULL THEN 0 ELSE pci.count END AS contract_count FROM zt_contract_info c LEFT JOIN ( SELECT contract_id, count( 1 ) AS
MySQL:in与join的优化
Super菜的专栏
06-29 2184
原sql： mysql> select count(*) as num from gp as p where p.goods_id in (select goods_id from gg where gg.category_id = 62) and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0 LIMIT 1; +---
MySQL 子查询in改为join，优化效率
qq_39822451的博客
12-26 4014
mysql> explain delete from t1 where id in (select id from t2 where name='aa'); mysql> explain delete t1.* from t1 inner join t2 where t1.id=t2.id and t2.name='aa';
IN 语句转行成JOIN
meng的博客
06-14 781
有些时候，一个子查询可以被重写为JOIN，例如： SELECT OrderID FROM Orders where EmployeeID IN (select EmployeeID from Employees where EmployeeID > 3); 如果知道OrderID是唯一的，即主键或者唯一索引，那么SQL语句会被重写为Join形式。 SELECT OrderID FROM Orders join Employees where Orders.EmployeeID = Emplo.
mysql in查询太慢, 使用join优化
sinat_36454672的博客
10-23 1560
mysql中查询 in()参数太多, 导致查询很慢, 使用join优化。 在实例中in查询话费2s, 优化后0.4s SELECT * from A LEFT JOIN B ON A.B_ID = B.ID where B.uuid in (001,002,003....) 优化后 SELECT * from A INNER JOIN ( select id from B where uuid = 001 union select id from B where uuid = 002 u
mysql In和EXISTS优化
keyboard专栏
10-26 1482
SELECT c.CustomerId, CompanyName FROM Customers c WHERE EXISTS( SELECT OrderID FROM Orders o WHERE o.CustomerID = cu.CustomerID) 这里面的EXISTS是如何运作呢？子查询返回的是OrderId字段，可是外面的查询要找的是CustomerID和Comp
mysql in转化为join_对于mysql优化之IN换INNER JOIN的案例分享
weixin_28748867的博客
01-21 423
软件安装：装机软件必备包SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。今天撸代码时，遇到SQL问题：要将A表查询的ID，匹配...
mysql的查询优化方法
纵横的博客
11-16 1215
前言；mysql的性能优化包括： 索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬件优化，应用层面优化（web服务器，缓存）等等。这里的记录的优化技巧更适用于开发人员，都是从网络上收集和自己整理的，主要是查询语句上面的优化，其它层面的优化技巧在此不做记录。 查询的开销指标： 执行时间 检查的行数 返回的行数 查询优化 1、索引优化 建立索引来优化查询 如何选择合适的列建立索引？ 1.在where、group by 、order by 、on从句出现的列 2.索引字段越小越好 3.离散度大的列放到联
exist优化 in mysql_MySQL IN/EXISTS优化问题
weixin_39944375的博客
01-14 280
MySQL IN/EXISTS优化问题where条件in操作，当子查询的条件中有IN操作时效率低下，sql语句：SELECTa.idFROMA aINNER JOIN B b ON a.id = b.idWHERE条件AND a.id IN (SELECT c.id FROM C c LEFT JOIN D d ON c.id=d.id WHERE 条件)尝试EXISTS同样效率低下，sql语句...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值