三种对象关联查询的性能比较

24条数据

 

1. 通过sql 子查询(50ms~100ms)

user_id_list = [(item.sender_id)for item in blogs]
up_dict = dict([(up.user_id,up) for up in UserProfile.objects.filter(user__id__in=user_id_list)])

for item in blogs:
       up_dict.get(usr.id).tinyAvatar()

 

jsonResultMblog cost:0.0902099609375
jsonResultMblog cost:0.05744099617
jsonResultMblog cost:0.0614869594574
jsonResultMblog cost:0.0682320594788
jsonResultMblog cost:0.0663239955902
jsonResultMblog cost:0.0864617824554
jsonResultMblog cost:0.132340908051
jsonResultMblog cost:0.124838113785
jsonResultMblog cost:0.130415201187
jsonResultMblog cost:0.129449129105
jsonResultMblog cost:0.135273933411
jsonResultMblog cost:0.154536008835
jsonResultMblog cost:0.154953956604
jsonResultMblog cost:0.151277065277
jsonResultMblog cost:0.0779540538788
jsonResultMblog cost:0.0804190635681
jsonResultMblog cost:0.0980219841003
jsonResultMblog cost:0.0625741481781
jsonResultMblog cost:0.0752508640289
jsonResultMblog cost:0.0725920200348
jsonResultMblog cost:0.0729160308838
jsonResultMblog cost:0.0652828216553
jsonResultMblog cost:0.0736339092255
jsonResultMblog cost:0.0584070682526
jsonResultMblog cost:0.0770168304443
jsonResultMblog cost:0.0629048347473
jsonResultMblog cost:0.0647010803223
jsonResultMblog cost:0.062164068222
jsonResultMblog cost:0.0646250247955
jsonResultMblog cost:0.0683400630951
jsonResultMblog cost:0.0662410259247
jsonResultMblog cost:0.0608901977539
jsonResultMblog cost:0.0670750141144
jsonResultMblog cost:0.0653350353241
jsonResultMblog cost:0.0649511814117
jsonResultMblog cost:0.0961151123047

 

2.通过memcached查询(80ms~100ms)

jsonResultMblog cost:0.099879026413
jsonResultMblog cost:0.104919910431
jsonResultMblog cost:0.0879209041595
jsonResultMblog cost:0.097589969635
jsonResultMblog cost:0.102517843246
jsonResultMblog cost:0.0977580547333
jsonResultMblog cost:0.0932738780975
jsonResultMblog cost:0.108583927155
jsonResultMblog cost:0.0961599349976
jsonResultMblog cost:0.0941219329834
jsonResultMblog cost:0.0790798664093
jsonResultMblog cost:0.0797939300537
jsonResultMblog cost:0.0863299369812
jsonResultMblog cost:0.086991071701
jsonResultMblog cost:0.0951809883118
jsonResultMblog cost:0.105861902237
jsonResultMblog cost:0.112595081329
jsonResultMblog cost:0.112322092056
jsonResultMblog cost:0.122483968735
jsonResultMblog cost:0.124871969223
jsonResultMblog cost:0.0887880325317
jsonResultMblog cost:0.0906360149384
jsonResultMblog cost:0.106253147125
jsonResultMblog cost:0.118382930756
jsonResultMblog cost:0.0967090129852
jsonResultMblog cost:0.0891940593719
jsonResultMblog cost:0.108466148376
jsonResultMblog cost:0.110771894455
jsonResultMblog cost:0.0849730968475
jsonResultMblog cost:0.0689418315887
jsonResultMblog cost:0.0869989395142
jsonResultMblog cost:0.087278842926

 

3. 直接通过db查询（200ms~300ms）

jsonResultMblog cost:0.343060970306
jsonResultMblog cost:0.233538150787
jsonResultMblog cost:0.238397121429
jsonResultMblog cost:0.195857048035
jsonResultMblog cost:0.215302944183
jsonResultMblog cost:0.206098794937
jsonResultMblog cost:0.164169073105
jsonResultMblog cost:0.240158081055
jsonResultMblog cost:0.254534959793
jsonResultMblog cost:0.246053934097
jsonResultMblog cost:0.263314008713
jsonResultMblog cost:0.25541806221
jsonResultMblog cost:0.252425193787
jsonResultMblog cost:0.25540304184
jsonResultMblog cost:0.257708072662
jsonResultMblog cost:0.226022005081
jsonResultMblog cost:0.241744041443
jsonResultMblog cost:0.270080089569
jsonResultMblog cost:0.226583957672
jsonResultMblog cost:0.243985891342
jsonResultMblog cost:0.253029823303
jsonResultMblog cost:0.259675979614
jsonResultMblog cost:0.258327007294
jsonResultMblog cost:0.169590950012
jsonResultMblog cost:0.144936084747
jsonResultMblog cost:0.216696977615
jsonResultMblog cost:0.212412118912

 

 

如果并发量加大的时候memcached的方式影响不大，但子查询的性能稍微差些。

 

ab -n500  -c100  "http://7599.t.duitang.com/hot/masn/?page=1&page_size=24&_type="

memcached:

jsonResultMblog cost:0.0827958583832
jsonResultMblog cost:0.0879380702972
jsonResultMblog cost:0.0952310562134
jsonResultMblog cost:0.0857689380646
jsonResultMblog cost:0.0986568927765
jsonResultMblog cost:0.100155830383
jsonResultMblog cost:0.0887188911438
jsonResultMblog cost:0.100232124329
jsonResultMblog cost:0.0943441390991
jsonResultMblog cost:0.082291841507
jsonResultMblog cost:0.0796449184418
jsonResultMblog cost:0.0760190486908
jsonResultMblog cost:0.0820999145508
jsonResultMblog cost:0.096864938736
jsonResultMblog cost:0.0914039611816
jsonResultMblog cost:0.088830947876

select in查询：

jsonResultMblog cost:0.117630958557
jsonResultMblog cost:0.124488115311
jsonResultMblog cost:0.126966953278
jsonResultMblog cost:0.116402864456
jsonResultMblog cost:0.118353843689
jsonResultMblog cost:0.136372804642
jsonResultMblog cost:0.128796100616
jsonResultMblog cost:0.136584043503
jsonResultMblog cost:0.108927965164
jsonResultMblog cost:0.123663187027
jsonResultMblog cost:0.109059810638
jsonResultMblog cost:0.127702951431
jsonResultMblog cost:0.101098060608
jsonResultMblog cost:0.128295183182
jsonResultMblog cost:0.105464935303
jsonResultMblog cost:0.0974659919739
jsonResultMblog cost:0.108411073685
jsonResultMblog cost:0.167189121246
jsonResultMblog cost:0.119005918503
jsonResultMblog cost:0.129583120346
jsonResultMblog cost:0.130043983459
jsonResultMblog cost:0.193385124207
jsonResultMblog cost:0.116989850998
jsonResultMblog cost:0.0914499759674
jsonResultMblog cost:0.0764319896698
jsonResultMblog cost:0.112426042557
jsonResultMblog cost:0.102033853531

 

 

 

 

优化前
-----------------------------
jsonResultMblog cost:0.49513220787
jsonResultMblog cost:0.500645875931
jsonResultMblog cost:0.506482124329
jsonResultMblog cost:0.570677995682
jsonResultMblog cost:0.500324010849
jsonResultMblog cost:0.547006845474
jsonResultMblog cost:0.566956043243
jsonResultMblog cost:0.600795984268
jsonResultMblog cost:0.501075983047
jsonResultMblog cost:0.479939937592
jsonResultMblog cost:0.410140991211
jsonResultMblog cost:0.513122081757

优化后
-----------------------------
jsonResultMblog cost:0.222725868225
jsonResultMblog cost:0.272092103958
jsonResultMblog cost:0.211454153061
jsonResultMblog cost:0.248133897781
jsonResultMblog cost:0.193365097046
jsonResultMblog cost:0.230565071106
jsonResultMblog cost:0.237985134125
jsonResultMblog cost:0.230352878571
jsonResultMblog cost:0.25757598877
jsonResultMblog cost:0.236615896225
jsonResultMblog cost:0.232418060303
jsonResultMblog cost:0.241884946823
jsonResultMblog cost:0.239605903625
jsonResultMblog cost:0.228952884674