欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL优化--NOT EXISTS和LEFT JOIN方式差异

程序员文章站 2022-10-19 13:36:05
在MySQL中,我们可以将NOT EXISTS语句转换为LEFT JOIN语句来进行优化,哪为什么会有性能提升呢? 使用NOT EXISTS方式SQL为: 而使用LEFT JOIN方式SQL为: 从查询效果来看,NOT EXISTS 方式耗时29.38秒,而LEFT JOIN方式耗时1.20秒,性能 ......

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

使用NOT EXISTS方式SQL为:

SELECT count(1)
FROM t_monitor m
WHERE NOT exists
(SELECT 1
FROM t_alarm_realtime AS a
WHERE a.resource_id=m.resource_id
AND a.resource_type=m.resource_type
AND a.monitor_name=m.monitor_name)

而使用LEFT JOIN方式SQL为:

SELECT count(1)
FROM t_monitor m
LEFT JOIN t_alarm_realtime AS a
ON a.resource_id=m.resource_id
AND a.resource_type=m.resource_type
AND a.monitor_name=m.monitor_name
WHERE a.resource_id is NULL

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

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

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: m
partitions: NULL
type: index
possible_keys: NULL
key: idx_id_name_type
key_len: 119
ref: NULL
rows: 578436
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: a
partitions: NULL
type: eq_ref
possible_keys: idx_id_name_type
key: idx_id_name_type
key_len: 119
ref: cmdb.m.resource_id,cmdb.m.monitor_name,cmdb.m.resource_type
rows: 1
filtered: 100.00
Extra: Using index

 

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

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m
partitions: NULL
type: index
possible_keys: NULL
key: idx_id_name_type
key_len: 119
ref: NULL
rows: 578436
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: eq_ref
possible_keys: idx_id_name_type
key: idx_id_name_type
key_len: 119
ref: cmdb.m.resource_id,cmdb.m.monitor_name,cmdb.m.resource_type
rows: 1
filtered: 100.00
Extra: Using where; 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可能会随统计信息变化而生产不同的执行计划。