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

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/3964.html