left join 替代 not in 真的能提高性能吗?
如题,小白sql优化是真的有疑问。
两个表的描述
-
表A
数量:10750条记录(现有)
索引如图所示:
-
表B
数量:18523条记录(现有)
索引如图所示:
实现:
表A的主键id是表B的逻辑外键,需要查出表A中id不在B中的A记录。
使用not in 的sql (好的,表A就是camera 表 B就是catalog)
select 0 as id, 0 as parent_id,camera.camera_name as name, camera.id as camera_id,
camera.longitude,camera.latitude,camera.device_id as gate_server_id,camera.online_status
from t_camera camera
where camera.del_flag = 0 and camera.id not in (select camera_id from t_catalog where del_flag = 0 and type = 0 and user_id = 0);
使用left join 的sql
select 0 as id, 0 as parent_id,camera.camera_name as name, camera.id as camera_id,
camera.longitude,camera.latitude,camera.device_id as gate_server_id,camera.online_status
from t_camera camera
left join t_catalog catalog on camera.id = catalog.camera_id and catalog.type =0 and catalog.del_flag = 0 and catalog.user_id = 0
where camera.del_flag = 0 and catalog.id is null;
查询时间:
not in 使用 0.280s
left join 使用 0.295s
explain:
not in:
left join
结论
从时间来看两个语句都差不多,但总体上每次执行是not in 比 left join 快那么一点点。从explain上看,索引的引用一样,fitered参数上看,catalog的表的值在not in上更大(这里应该是越大越好吧?),但是从rows参数(这个参数是扫描的记录行数吧?)上看,left join扫描的行数更少,从这上面的描述能看出两个语句的优劣嘛?
(记录一下,或许有一天我能解答吧)