重新开贴规范发SQL求解决！如题，下面这个SQL语句，当数据很大时，就会卡死崩溃，请大家看看如何能优化。SQL cod
重新开贴规范发SQL求解决！
如题，下面这个SQL语句，当数据很大时，就会卡死崩溃，请大家看看如何能优化。
- SQL code
insert into qftjb (areaid,areaname,hid,year,fzname,houproperties,projectid,project,sfyid,projectfee,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dece,total)Select areaid, areaname, houseid as hid, year, fzname,if(houproperties='0','公','私') as houproperties,projectid,project,sfyid,FORMAT(projectfee,2) as projectfee, FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr, FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug, FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece, FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As totalFrom ( Select tmpMain.*, areaname, fzname,houproperties,project,sfyid, (Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* Calculate the ShouldRent */ From ( Select h.areaid, h.houseid, h.year, projectid, SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan, SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb, SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar, SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr, SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May, SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun, SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul, SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug, SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep, SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct, SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov, SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece, SUM(jfje) As sumyear /* All rent in the year */ From ( Select * From ( Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1 /* That's cross join, try to get all years */ ) hh, project p /* That's cross join, try to get all projects */ Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */ or (hh.houproperties = '1' and projectid ='1002')) ) h Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4) Group By h.areaid, h.houseid, h.year, projectid ) tmpMain /* Here we got the main result */ Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */ Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */ Join area a On tmpMain.areaid = a.rowid /* For get the area's name */) tmpAll/* Here you can write some condition */Order By areaid, houseid, year, projectid;
以下是部分结果
- SQL code
INSERT INTO `qftjb` VALUES ('37', '1', '南小巷', 'houseid', '2012', '李敏', '公', '1001', '房租', null, '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '135.00', '1,620.00');INSERT INTO `qftjb` VALUES ('38', '1', '南小巷', 'houseid', '2012', '李敏', '公', '1003', '卫生费', null, '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '6.00', '72.00');INSERT INTO `qftjb` VALUES ('39', '2', '北区', 'houseid', '2011', '冯小彦', '私', '1002', '设施费', null, '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '14.38', '172.54');
以下是explain select
- SQL code
id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY <derived2> ALL 8628 Using filesort2 DERIVED <derived3> ALL 8376 2 DERIVED hh ALL 2764 2 DERIVED pp eq_ref PRIMARY PRIMARY 12 tmpMain.projectid 1 2 DERIVED a eq_ref PRIMARY PRIMARY 4 tmpMain.areaid 1 3 DERIVED <derived4> ALL 8628 Using temporary; Using filesort3 DERIVED c ALL 36 4 DERIVED p range PRIMARY PRIMARY 12 3 Using where4 DERIVED <derived5> ALL 5338 Using where5 DERIVED <derived6> ALL 2 5 DERIVED house ALL 2764 6 DERIVED <derived7> ALL 3 Using temporary7 DERIVED charge ALL 36 Using temporary8 UNION No tables used UNION RESULT <union7,8> ALL
[解决办法]
建议拆开一步一步来 也好分析具体哪一步卡死的
[解决办法]
太长了，有耐心看完的人不多。
建议楼主直接说明一下想实现的查询功能。这样或者有其它方面可以更快捷的实现。
[解决办法]
只有拆开1个1个运行，看看瓶颈是什么，似乎没有用到索引
[解决办法]
分拆的方法具体的看那块语句运行有问题的。找出瓶颈了。
[解决办法]