如题,下面这个SQL语句,当数据很大时,就会卡死崩溃,请大家看看如何能优化。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 total
From (
  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;

解决方案 »

  1.   

    贴出你的explain select ...以供分析。
      

  2.   

    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');
      

  3.   


    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');
      

  4.   

    这么长的sql代码啊,怎么不考虑键视图或存储过程啊
      

  5.   


    修改了一下SQL。
    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 total
    From (
      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;
    结果:INSERT INTO `qftjb` VALUES ('36', '1', '南小巷', '1-2-12', '2011', '李敏', '公', '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 ('37', '1', '南小巷', '1-2-12', '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 ('39', '2', '北区', '1-2-13', '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');
      

  6.   

    你的EXPLAIN的结果贴在哪儿了?
      

  7.   

    1 PRIMARY <derived2> ALL 8628 Using filesort
    2 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 filesort
    3 DERIVED c ALL 36
    4 DERIVED p range PRIMARY PRIMARY 12 3 Using where
    4 DERIVED <derived5> ALL 5338 Using where
    5 DERIVED <derived6> ALL 2
    5 DERIVED house ALL 2764
    6 DERIVED <derived7> ALL 3 Using temporary
    7 DERIVED charge ALL 36 Using temporary
    8 UNION No tables used
    UNION RESULT <union7,8> ALL 好了,贴好了
      

  8.   


    晕,最上面的贴不出来了,依次是:
    id   select_type   table   type  possible_keys   key   key_len   ref   rows   Extra
      

  9.   

    子查询太多,相互之间join之后,需要扫描的行上亿了。
      

  10.   

    先执行内层,在以结果执行外层,不知道可以不 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'))