对表拆分几种方法比较

 

select userid/100000 as table1,count(1) from product_info group by userid/100000
9    18167368
2    7140316
6    31814659
5    24005210
0    3671600
7    10783200
8    8842812
4    11629923
3    4940024
1    5719535
10    9811797
--结果不均匀

select right(userid,1) as table1,count(1) from product_info group by right(userid,1)
1    14022271
0    14478542
9    13021378
7    14907917
6    14771805
8    13261945
2    13118075
5    12581792
3    13034306
4    13328413

select (userid%10) as table1,count(1) from product_info group by (userid%10)
7    14907917
8    13261945
0    14478542
3    13034306
1    14013992
4    13328413
-1    8279
6    14771805
5    12581792
9    13021378
2    13118075

select (userid%20) as table1,count(1) from product_info group by (userid%20)
19    6826655
17    6879335
9    6194723
2    6558178
0    7239936
15    7043968
13    5967309
6    7847672
5    5537824
16    6924133
14    6003895
3    7066997
12    6559897
1    7452838
10    7238606
7    8028582
8    6896273
18    6365672
4    7324518
11    6561154
-1    8279

userid%10效率远高于right(userid,1),前者8秒，后者8分钟