mysql not exists优化_MySQL优化--NOT EXISTS和LEFT JOIN方式差异

于 2021-01-19 05:59:55 发布
阅读量1.3k 收藏 1
点赞数 1
文章标签： mysql not exists优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35819860/article/details/113213170
版权
本文通过实际案例对比了MySQL中NOTEXISTS与LEFTJOIN两种查询方式的性能表现，发现LEFTJOIN方式能显著减少执行时间。文章深入分析了这两种方式的执行计划和过程，指出在外层数据量大时，LEFTJOIN方式更优。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

在MySQL中，我们可以将NOT EXISTS语句转换为LEFT JOIN语句来进行优化，哪为什么会有性能提升呢？

使用NOT EXISTS方式SQL为：

SELECT count(1)FROMt_monitor mWHERE NOT exists(SELECT 1

FROM t_alarm_realtime ASaWHERE a.resource_id=m.resource_idAND a.resource_type=m.resource_typeAND a.monitor_name=m.monitor_name)

而使用LEFT JOIN方式SQL为：

SELECT count(1)FROMt_monitor mLEFT JOIN t_alarm_realtime ASaON a.resource_id=m.resource_idAND a.resource_type=m.resource_typeAND a.monitor_name=m.monitor_nameWHERE a.resource_id is NULL

从查询效果来看，NOT EXISTS 方式耗时29.38秒，而LEFT JOIN方式耗时1.20秒，性能提升25倍左右。

查看NOT EXISTS方式的执行计划：

*************************** 1. row ***************************id:1select_type:PRIMARY

table: m

partitions:NULLtype:indexpossible_keys:NULL

key: idx_id_name_type

key_len:119ref:NULLrows:578436filtered:100.00Extra: Usingwhere; Using index

*************************** 2. row ***************************id:2select_type: DEPENDENT SUBQUERYtable: a

partitions:NULLtype: eq_ref

possible_keys: idx_id_name_typekey: idx_id_name_type

key_len:119ref: cmdb.m.resource_id,cmdb.m.monitor_name,cmdb.m.resource_type

rows:1filtered:100.00Extra: Usingindex

查看LEFT JOIN方式的执行计划：

*************************** 1. row ***************************id:1select_type: SIMPLEtable: m

partitions:NULLtype:indexpossible_keys:NULL

key: idx_id_name_type

key_len:119ref:NULLrows:578436filtered:100.00Extra: Usingindex

*************************** 2. row ***************************id:1select_type: SIMPLEtable: a

partitions:NULLtype: eq_ref

possible_keys: idx_id_name_typekey: idx_id_name_type

key_len:119ref: cmdb.m.resource_id,cmdb.m.monitor_name,cmdb.m.resource_type

rows:1filtered:100.00Extra: Usingwhere; Not exists; Using index

使用SQL PROFILE查看NOT EXISTS 执行过程：

+--------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+

| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out |

+--------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000029 | 0.001000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000025 | 0.000000 | 0.000999 | 0 | 0 | 0 | 0 |

| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| end | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| query end | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| closing tables | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| freeing items | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| logging slow query | 0.000059 | 0.000000 | 0.000000 | 0 | 0 | 0 | 16 |

| cleaning up | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

+--------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+

使用SQL PROFILE查看LEFT JOIN 执行过程：

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+

| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out |

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+

| starting | 0.000162 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| checking permissions | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| checking permissions | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Opening tables | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| init | 0.000049 | 0.001000 | 0.000000 | 0 | 0 | 0 | 0 |

| System lock | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| optimizing | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| statistics | 0.000050 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| preparing | 0.000037 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| executing | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| Sending data | 1.200899 | 1.547764 | 0.124981 | 7460 | 116 | 0 | 8608 |

| end | 0.000103 | 0.000000 | 0.000000 | 2 | 0 | 0 | 0 |

| query end | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| closing tables | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 |

| freeing items | 0.000039 | 0.000000 | 0.000000 | 2 | 0 | 0 | 8 |

| logging slow query | 0.000052 | 0.000000 | 0.000000 | 0 | 0 | 0 | 24 |

| cleaning up | 0.000030 | 0.000000 | 0.000000 | 1 | 0 | 0 | 0 |

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+

两种执行方式对比：

1、从执行计划来看，两个表都使用了索引，区别在于NOT EXISTS使用“DEPENDENT SUBQUERY”方式，而LEFT JOIN使用普通表关联的方式

2、从执行过程来看，LEFT JOIN方式主要消耗Sending data的上，在NOT EXISTS方式主要消耗在"executing"和“Sending data”两项上，受限于PROFILE只能记录100行结果，因此超过57万个"executing"和“Sending data”的组合项没有显示，虽然每次"executing"和“Sending data”的组合项消耗时间较少(约50毫秒)，但由于执行次数较高，导致最终执行时间较长(50μs*578436=28921800us=28.92s)

如何在NOT EXISTS和LEFT JOIN中选择：

1、当外层数据较少时，子查询循环次数较少，使用NOT EXISTS并不会导致严重的性能问题，推荐使用NOT EXISTS方式。

2、当外层数据较大时，子查询消耗随外层数据量递增，查询性能较差，推荐使用LEFT JOIN方式

总结：

按照存在即合理是客观唯心主义的理论，NOT EXISTS以更直观地方式实现业务需求，在SQL复杂度上要远低于LEFT JOIN，且在生产执行计划时，NOT EXISTS方式相对更稳定些，LEFT JOIN可能会随统计信息变化而生产不同的执行计划。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值