mysql中对not in语句的优化

最新推荐文章于 2023-10-15 14:57:03 发布

原创最新推荐文章于 2023-10-15 14:57:03 发布 · 673 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

11 篇文章

订阅专栏

本文探讨了在SQL查询中使用not in语句可能导致的效率低下问题，并提供了一种优化方案。通过替换为左连接并检查连接字段是否为空，可以显著提高查询性能。这种优化方法适用于需要排除特定记录的复杂查询场景。

1.使用not in语句查询效率太低，可以使用下面的方式进行优化
优化前：

SELECT sum(a.salary) as nursingCount,b.account_no, FROM ns_order_service_record a 
INNER JOIN ptcs_extended.ns_nursing_workers b ON a.nursing_id = b.nursing_id 
 WHERE a.id not in {
select record_id from ns_wage_service_rel 
}  and b.shop_id = ? and a.service_end_date <= ? GROUP BY a.nursing_id order by a.service_start_date

优化后：

SELECT sum(a.salary) as nursingCount,b.account_no FROM ns_order_service_record a 
INNER JOIN ptcs_extended.ns_nursing_workers b ON a.nursing_id = b.nursing_id 
left JOIN ns_wage_service_rel d ON a.id = d.record_id WHERE d.record_id is null and b.shop_id = ? and a.service_end_date <= ? GROUP BY a.nursing_id order by a.service_start_date

优化后下面这句是重点：

left JOIN ns_wage_service_rel d ON a.id = d.record_id WHERE d.record_id is null