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可能会随统计信息变化而生产不同的执行计划。
