mysql查询优化
背景:ncsrvuserex有4w+条数据,ncmuserdaystat有100w+条数据
第一次查询
- select a.username,a.mname,a.college,a.mobno,a.cid from ncsrvuserex a,ncmuserdaystat b where a.username=b.username and b.stime<20171101 and b.stime>=20171001 and a.usertype=2 group by b.username;
数据出不来,太慢了,需要优化,换个思路,把有100w+条数据的表先优化。
第二次查询
- select a.username,a.mname,a.college,a.mobno,a.cid,a.usertype from ncsrvuserex a left join (select username from ncmuserdaystat where stime<20171201 and stime>=20171101 group by username)t on a.username=t.username group by t.username;
使用join,效果有一些,但是还不够。
第三次查询
- select a.username,a.mname,a.college,a.mobno,a.cid from (select username from ncmuserdaystat where stime<20171201 and stime>=20171101 group by username ) aa,ncsrvuserex a where aa.username=a.username
数据查询耗时30s+,效率还可以