虚拟机上程序跑不动，求优化程序

[benpao002]

测试数据如下
CUST_ID TYPE C_type R F M
1000022035 FINC 1000022035FINC 19 5 2619.5
1000007371 FUND 1000007371FUND 65 33 7857.63
1000010935 ABCD 1000010935ABCD 111 2 13393.88
1000009878 FUND 1000009878FUND 101 16 22776.07
1000086999 ABCD 1000086999ABCD 200 18 18868.9
1000015333 FINC 1000015333FINC 157 1 23044.26
1000000176 FUND 1000000176FUND 38 29 1541.59
1000036287 INNO 1000036287INNO 121 15 7601.34
1000018585 FINC 1000018585FINC 46 19 5974
1000013007 FINC 1000013007FINC 14 2 7790.95
1000044588 ABCD 1000044588ABCD 131 16 4872.83
1000020565 ABCD 1000020565ABCD 60 3 6516.62
1000086543 ABCD 1000086543ABCD 51 28 7688.32
1000082652 FINC 1000082652FINC 116 23 6672.82
1000037259 ABCD 1000037259ABCD 151 9 8767.72
1000078492 FINC 1000078492FINC 35 3 4613.98
1000053089 INNO 1000053089INNO 64 4 8000.12
1000023373 ABCD 1000023373ABCD 80 20 13348.91
1000016112 FUND 1000016112FUND 97 41 6958.97
1000070555 FINC 1000070555FINC 185 9 15088.13
1000012595 FINC 1000012595FINC 61 3 13111.62
1000035257 INNO 1000035257INNO 29 5 13612.72
1000084843 INNO 1000084843INNO 12 11 9202.32
1000026758 FINC 1000026758FINC 116 6 6148.55
1000035458 INNO 1000035458INNO 42 12 4076.75
1000056708 FUND 1000056708FUND 78 10 890.79
1000044768 FUND 1000044768FUND 102 24 11968.57
1000025022 FINC 1000025022FINC 119 19 21549.5
1000017543 ABCD 1000017543ABCD 64 10 8490.13
1000030079 INNO 1000030079INNO 76 26 5553.97
1000002432 FINC 1000002432FINC 133 8 6924.37
1000088290 ABCD 1000088290ABCD 22 39 4100.83
1000019673 FINC 1000019673FINC 34 20 10319.45
1000050817 FINC 1000050817FINC 131 26 10838.23
1000014588 INNO 1000014588INNO 87 26 8142.7
1000006726 FUND 1000006726FUND 94 12 155.42
1000073635 FINC 1000073635FINC 80 19 1540.57
1000039912 INNO 1000039912INNO 97 10 7195.78
1000044160 INNO 1000044160INNO 34 47 5872.89
1000006676 ABCD 1000006676ABCD 34 8 8702.41
1000014460 ABCD 1000014460ABCD 14 17 4565.58
1000019006 FINC 1000019006FINC 80 3 3677.92
1000007343 FINC 1000007343FINC 170 1 3289.29
1000069748 FUND 1000069748FUND 242 29 3811.78
1000001022 ABCD 1000001022ABCD 28 19 4045.45
1000038481 FUND 1000038481FUND 141 13 7175.09
1000016176 FUND 1000016176FUND 48 6 3454.74
1000041960 INNO 1000041960INNO 41 20 1435.24
1000028655 FUND 1000028655FUND 37 26 661.49
1000022715 ABCD 1000022715ABCD 176 2 7609.48
1000016068 FUND 1000016068FUND 57 2 3717.74
1000031305 FUND 1000031305FUND 125 2 12615.62
1000059113 FINC 1000059113FINC 49 18 6866.41
1000011743 ABCD 1000011743ABCD 23 21 9016.63
1000068949 INNO 1000068949INNO 11 16 7340.29
1000026857 ABCD 1000026857ABCD 127 29 14750.69
1000083960 FUND 1000083960FUND 20 9 9242.62
1000066538 ABCD 1000066538ABCD 80 6 24088.42
1000033781 FINC 1000033781FINC 124 35 6261.55
1000046597 ABCD 1000046597ABCD 41 3 34.07
1000085541 INNO 1000085541INNO 40 28 13049.87
1000080339 ABCD 1000080339ABCD 4 18 1045.7
1000060413 ABCD 1000060413ABCD 3 23 8093.88
1000014331 INNO 1000014331INNO 77 26 5146.14
1000024704 FINC 1000024704FINC 208 23 22934.47
1000002660 ABCD 1000002660ABCD 177 22 8430.75
1000020067 ABCD 1000020067ABCD 60 38 14854.19
1000002904 INNO 1000002904INNO 169 16 5089.62
1000078694 ABCD 1000078694ABCD 131 4 4951.14
1000081939 FUND 1000081939FUND 184 7 10523.84
1000021367 FINC 1000021367FINC 97 2 4547.06
1000048539 FUND 1000048539FUND 21 31 4008.51
1000003869 FUND 1000003869FUND 152 2 7533.7
1000051597 ABCD 1000051597ABCD 53 22 5775.29
1000078004 FINC 1000078004FINC 70 2 10829.21
1000001687 FINC 1000001687FINC 36 7 7114.01
1000020738 FUND 1000020738FUND 33 5 14569.43
1000034645 ABCD 1000034645ABCD 76 8 4387.82
1000050908 ABCD 1000050908ABCD 17 21 19406.77
1000085923 ABCD 1000085923ABCD 70 12 11740.37
1000047202 FINC 1000047202FINC 94 23 1516.91
1000063370 FUND 1000063370FUND 117 7 8538.33
1000071163 FUND 1000071163FUND 28 27 12087.62
1000072458 INNO 1000072458INNO 65 15 10802.66
1000074811 INNO 1000074811INNO 114 2 5482.55
1000008267 INNO 1000008267INNO 83 2 95.87
1000079342 INNO 1000079342INNO 51 38 9612.83
1000086488 INNO 1000086488INNO 100 35 931.49
1000065092 FUND 1000065092FUND 22 8 498.86
1000052032 FUND 1000052032FUND 27 34 1424.12
1000063197 INNO 1000063197INNO 47 23 15487.59
1000072519 ABCD 1000072519ABCD 7 10 19369.02
1000044220 ABCD 1000044220ABCD 21 18 7966.25
1000010107 ABCD 1000010107ABCD 185 25 630.34
1000053089 ABCD 1000053089ABCD 51 11 1577.04
1000088058 FUND 1000088058FUND 65 4 12093.7
1000045471 FINC 1000045471FINC 172 20 3245.27
1000017361 FUND 1000017361FUND 150 39 17702.66
1000085040 INNO 1000085040INNO 44 28 1660.34
1000003838 ABCD 1000003838ABCD 32 16 3212.38
1000085520 FUND 1000085520FUND 160 9 5185.38
1000068164 FUND 1000068164FUND 95 2 14109.58
1000008129 ABCD 1000008129ABCD 158 35 5038.13
1000066291 ABCD 1000066291ABCD 13 18 7453.82
1000060129 FINC 1000060129FINC 66 26 2521.97
1000026887 FINC 1000026887FINC 47 42 7021.99
1000012395 FINC 1000012395FINC 295 34 12300.29
1000044326 ABCD 1000044326ABCD 235 26 1570.38
1000030313 ABCD 1000030313ABCD 113 18 1733.62
1000021816 ABCD 1000021816ABCD 28 20 14689.03
1000024800 FUND 1000024800FUND 97 30 2508.29
1000030606 ABCD 1000030606ABCD 122 16 3146.24
1000073010 FUND 1000073010FUND 119 9 7175.81
1000072495 ABCD 1000072495ABCD 151 16 15799.19
1000020660 FUND 1000020660FUND 30 1 10839.49
1000004196 INNO 1000004196INNO 105 24 16336.35
1000033440 FUND 1000033440FUND 112 2 8312.03
1000002318 FINC 1000002318FINC 32 5 11336.63
1000079575 ABCD 1000079575ABCD 26 4 83.06
1000028547 FINC 1000028547FINC 69 27 25111.19
1000029319 FUND 1000029319FUND 91 7 8671.93
1000010170 ABCD 1000010170ABCD 112 20 19899
1000080909 FUND 1000080909FUND 160 17 13788.88
1000086787 ABCD 1000086787ABCD 107 9 14423.17
1000069147 INNO 1000069147INNO 83 7 13254.72
1000034824 FINC 1000034824FINC 20 21 1276.3
1000031687 FUND 1000031687FUND 24 20 884.41
1000048212 ABCD 1000048212ABCD 98 25 13636.32
1000019325 FINC 1000019325FINC 14 22 21255.6
1000085412 FINC 1000085412FINC 22 12 20566.53
1000034075 FUND 1000034075FUND 19 9 5614.43
1000039821 ABCD 1000039821ABCD 91 5 18842.29
1000010268 FUND 1000010268FUND 79 6 14667.01
1000009500 FINC 1000009500FINC 20 17 5369.72
1000027326 INNO 1000027326INNO 22 19 6526.2
1000051411 ABCD 1000051411ABCD 14 2 4461.84
1000084959 INNO 1000084959INNO 105 37 188.49
1000049201 ABCD 1000049201ABCD 113 5 12294.91
1000008491 FUND 1000008491FUND 11 10 14253.84
1000016962 INNO 1000016962INNO 104 15 1869.37
1000078121 INNO 1000078121INNO 223 25 7094.77
1000081718 ABCD 1000081718ABCD 26 12 4548.16
1000028174 FINC 1000028174FINC 50 42 4758.48
1000004954 FINC 1000004954FINC 145 29 9476.13
1000036116 INNO 1000036116INNO 9 8 4864.45
1000039905 INNO 1000039905INNO 194 34 21570.23
1000027612 FINC 1000027612FINC 165 38 12390.87
1000070955 FUND 1000070955FUND 135 8 24209.79
1000036775 ABCD 1000036775ABCD 97 12 1717.05
1000073378 FINC 1000073378FINC 31 26 8191.49
1000016869 ABCD 1000016869ABCD 69 19 2428.01
1000028992 ABCD 1000028992ABCD 57 20 12016.48
1000051931 FUND 1000051931FUND 171 7 932.72
1000000419 INNO 1000000419INNO 110 15 10684.28
1000077185 INNO 1000077185INNO 31 5 1660.17
1000015481 INNO 1000015481INNO 25 16 2448.91
1000034551 ABCD 1000034551ABCD 159 51 1486.68
1000001895 ABCD 1000001895ABCD 50 2 9937.29
1000031626 ABCD 1000031626ABCD 25 33 1478.46
1000021670 FINC 1000021670FINC 200 33 5687.6
1000074136 INNO 1000074136INNO 38 44 5094.93
1000082912 ABCD 1000082912ABCD 11 20 6014.07
1000006801 INNO 1000006801INNO 9 7 15781.43
1000038944 FUND 1000038944FUND 44 25 14686.39
1000021194 FINC 1000021194FINC 20 6 12898.48
1000059740 FINC 1000059740FINC 12 23 7883.53
1000034623 FINC 1000034623FINC 16 13 11356.11
1000077555 FINC 1000077555FINC 104 13 8586.82
1000016194 ABCD 1000016194ABCD 40 3 2472.48
1000062170 FUND 1000062170FUND 19 3 1775.35
1000056224 FINC 1000056224FINC 95 10 12450.4
1000007131 INNO 1000007131INNO 129 16 8226.39
1000060947 INNO 1000060947INNO 24 12 2418.66
1000009212 FINC 1000009212FINC 26 17 637.64
1000053014 INNO 1000053014INNO 70 1 9854.85
1000027868 FINC 1000027868FINC 27 18 3806.81
1000041435 INNO 1000041435INNO 98 9 5069.59
1000083923 FUND 1000083923FUND 30 22 1466.57
1000012331 INNO 1000012331INNO 10 9 8676.51
1000064166 INNO 1000064166INNO 156 8 1598.3

drop table if exists crfm1;
create table crfm1
(
cust_id varchar(20),
type varchar(20),
coun int(10),
c_type varchar(50),
r int(10),
f int(10),
m decimal(20,2)
);
INSERT INTO crfm1(cust_id,type,coun,c_type,r,f,m)
select cust_id,type,count(c_type),c_type,r,f,m from cssj group by c_type;

drop table if exists crfm2;
create table crfm2
(
cust_id varchar(20),
r int(10),
f int(10),
m decimal(20,2)
);
INSERT INTO crfm2(cust_id,r,f,m)
select cust_id,sum(r),sum(f),sum(m) from crfm1 group by cust_id;

drop table if exists crfm3;
create table crfm3
(
cust_id varchar(20),
r int(10),
r1 int(10),
f int(10),
f1 int(10),
m decimal(20,2),
m1 decimal(20,2)
);
INSERT INTO crfm3(cust_id,r,r1,f,f1,m,m1)
select a.cust_id,r,a.r1,f,b.f1,m,c.m1
from
(select * from crfm2)d,
(select cust_id,
(case
when r<=30 then 5
when r>30 and r<=60 then 4
when r>60 and r<=180 then 3
when r>180 and r<=360 then 2
when r>360 then 1
end)r1 from crfm2)a,
(select cust_id,
(case
when f<=12 then 1
when f>12 and f<=22 then 2
when f>22 and f<=33 then 3
when f>33 and f<=48 then 4
when f>48 then 5
end)f1 from crfm2)b,
(select cust_id,
(
case
when m<=5578.53 then 1
when m>5578.53 and m<=10509.02 then 2
when m>10509.02 and m<=15852.05 then 3
when m>15852.05 and m<=23399.88 then 4
when m>23399.88 then 5
end)m1 from crfm2)c
where a.cust_id=b.cust_id and a.cust_id=c.cust_id and a.cust_id=d.cust_id;
如上所述，上面是测试数据，下面是代码，这个代码在我个人电脑上跑完全没问题，但是在虚拟机上进行运行，crfm3 insert在数据量比较大的情况下比方说200000条的情况下完全跑不动，20000条勉强能跑，急求改良程序解决办法，谢谢！

[唐诗三百首]

在crfm2表的cust_id字段上应有索引.

[benpao002]

引用 1 楼 ap0405140 的回复:

在crfm2表的cust_id字段上应有索引.

嗯，这个想到了，呆会去试下，可否使用临时表来改良程序，如果可以，该怎么写呢

[吉普赛的歌]

SELECT cust_id,
       r,
       r1,
       f,
       f1,
       m,
       m1
FROM   crfm2
		,r
       ,CASE 
            WHEN r <= 30 THEN 5
            WHEN r > 30 AND r <= 60 THEN 4 
            WHEN r > 60 AND r <= 180 THEN 3 
            WHEN r > 180 AND r <= 360 THEN 2 
            WHEN r > 360 THEN 1 
        END AS r1
        ,f
        ,CASE 
            WHEN f <= 12 THEN 1 
            WHEN f > 12 AND f <= 22 THEN 2 
            WHEN f > 22 AND f <= 33 THEN 3 
            WHEN f > 33 AND f <= 48 THEN 4  WHEN f > 48 THEN 5 
            END 
        AS f1
        ,
        CASE 
            WHEN m <= 5578.53 THEN 1
            WHEN m > 5578.53 AND m <= 10509.02 THEN 2 
            WHEN m > 10509.02 AND m <= 15852.05 THEN 3 
            WHEN m > 15852.05   AND m <= 23399.88 THEN 4 
            WHEN m > 23399.88 THEN 5 
            END AS m1

这样能不能快点？

[OwenZeng_DBA]

数据库慢和很多方面都有关系，虚拟机只是其中一项

[moshangdanqing]

在cust_id上添加字段