由于not in 会进行全表扫描, 针对数据大的表,是非常耗时间.
特此给大家推荐一种优化方法
优化之前 [not in]
SELECT `a`.`machine_id`,`a`.`machine_code`,`a`.`address`,`a`.`machine_name` FROM `comm_machine` `a` WHERE `a`.`machine_type` = 16 AND `a`.`usercode` = 99901 AND `machine_id` not in(select machine_id from `machine_package` where `tmp_id`=11 and `delete_time` is null)
优化之后 采用 b.machine_id is null 来进行判断
SELECT `a`.`machine_id`,`a`.`machine_code`,`a`.`address`,`a`.`machine_name` FROM `comm_machine` `a` LEFT JOIN (select * from `machine_package` where `tmp_id`='11' and `delete_time`>0) as `b` ON `a`.`machine_id`=`b`.`machine_id` WHERE `a`.`machine_type` = 16 AND `a`.`usercode` = 99901 AND `b`.`machine_id` is null
由于数据量少,优化效果不明显, 但是查询速度确实增快了