MySQL优化--NOT EXISTS和LEFT JOIN方式差异
在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可能会随统计信息变化而生产不同的执行计划。
上一篇: Linux Shell基础篇——变量