sql语句in
在今天之前sql一直用in语句,知道今天遇到一张数据量很大的表查了三分钟才查出来,这才意识到数据库优化有多重要.作为一名开发人员,首先从优化sql语句开始。
之前用in写sql是这样的
select * from m_package where userId in( select id from sys_user where newDorm='2号楼' and `newRoomNumber` = 'N413') ;
执行结果:/* 0 rows affected, 18 rows found. Duration for 1 query: 0.407 sec. */
exists写sql语句
select * from m_package as pack where
exists (select id from sys_user as user where newDorm='2号楼' and `newRoomNumber` = 'N413' and pack.userId = user.id);
执行结果:/* 0 rows affected, 18 rows found. Duration for 1 query: 0.297 sec. */
inner join写sql语句
select * from m_package as pack inner join sys_user as user where pack.userId = user.id and newDorm='2号楼' and `newRoomNumber` = 'N413';
执行结果:/* 0 rows affected, 18 rows found. Duration for 1 query: 0.234 sec. */
可以看出来执行效率 inner join > exists > in
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)