两个表,test和test2
test中100万条数据,字段有id,networkid等
test2中有1200万数据,字段有testid,code等。
主键为bigint,没有索引
(1)分开查询
语句 select id from test2 where code = 'DEF7414551'
耗时:15.872ms
结果 78条
select id , networkid from test where id =
22805 or id =
30689 or id =
37472 or id =
59166 or id =
72242 or id =
81256 or id =
102388 or id =
178472 or id =
181171 or id =
206672 or id =
222898 or id =
234014 or id =
266944 or id =
276203 or id =
281212 or id =
291510 or id =
296095 or id =
349508 or id =
397866 or id =
428645 or id =
447432 or id =
519119 or id =
551511 or id =
561224 or id =
579833 or id =
630773 or id =
643295 or id =
667836 or id =
720533 or id =
732349 or id =
759244 or id =
789373 or id =
792593 or id =
793407 or id =
836867 or id =
840617 or id =
913377 or id =
978537 or id =
988522 or id =
1071365 or id =
1079249 or id =
1086032 or id =
1107726 or id =
1120802 or id =
1129816 or id =
1150948 or id =
1227032 or id =
1229731 or id =
1255232 or id =
1271458 or id =
1282574 or id =
1315504 or id =
1324763 or id =
1329772 or id =
1340070 or id =
1344655 or id =
1398068 or id =
1446426 or id =
1477205 or id =
1495992 or id =
1567679 or id =
1600071 or id =
1609784 or id =
1628393 or id =
1679333 or id =
1691855 or id =
1716396 or id =
1769093 or id =
1780909 or id =
1807804 or id =
1837933 or id =
1841153 or id =
1841967 or id =
1885427 or id =
1889177 or id =
1961937 or id =
2027097 or id =
2037082
耗时: 0.281s
结果39条
(2) inner join
查询语句如下:
select test.networkid , test2.id from test inner join test2 on test.id = test2.id where test2.code = 'DEF7414551'
意思是通过test2的code找到test的networkid。
耗时: 663.967s
查出39条。
查询语句如下:
select test.networkid , test2.id from test2 inner join teston test.id = test2.id where test2.code = 'DEF7414551'
意思是通过test2的code找到test的networkid。
耗时:607.324s
查出39条。
tmd
(3)子查询
select networkid ,id from test where id in ( select id from test2 where code = 'DEF7414551')
耗时:604.142s
查出39条