excel數(shù)據(jù)導(dǎo)出sql優(yōu)化分2篇，本文有sql優(yōu)化篇

假設(shè)有A,B,C,D,E5張表，需要導(dǎo)出這5張表的部分信息，查詢條件只涉及到A,B,C的相關(guān)字段,約定A,B,C,D,E對應(yīng)關(guān)系都為1對1.
A表字段：ID,TYPEA,NAMEA....;
B表字段：ID,AID,TYPEB,NAMEB...;(AID為A表主鍵)
C表字段: ID,AID,BID,TYPEC,NAMEC...;(AID為A表主鍵,BID為B表主鍵)
D表字段：ID,AID,BID,TYPED,NAMED...;(AID為A表主鍵,BID為B表主鍵)
E表字段：ID,AID,BID,TYPEE,NAMEE...;(AID為A表主鍵,BID為B表主鍵)
如何導(dǎo)出？
1 簡單的實現(xiàn)步驟如下：
1>left join A.B.C,獲得ABC的相關(guān)信息.

<select id="getABC" ......>
    select  *  from A left join B on A.ID  = B.AID
    left join C on C.BID = B.AID and C.AID = B.AID
    <where>
      <if test="TYPEA != NULL  and TYPEA!=''">
        and A.TYPEA = #{TYPEA}
      </if>
     <if test="TYPEB != NULL and TYPEB!='' ">
        and B.TYPEB = #{TYPEB}
      </if>
     <if test="NAMEC!= NULL and NAMEC !='' ">
        and C.NAMEC   CONCAT('%','${NAMEC}','%' )
      </if>
    </where>
</select>

2>循環(huán)獲得DE數(shù)據(jù)，然后在組裝excel信息

/** 偽代碼 **/
public void getABCDEList(ABC abc) {
    List<ABCDE> resultList = new ArrayList<>();
    List<ABC> listABC = ABCMapper.getABCList(ABC abc);
    for(ABC abc:listABC){
        D d = DMappger.getDByID(abc.AID, abc.BID);
        E e = EMappger.getDByID(abc.AID, abc.BID);
        ABCDE abcde = new ABCDE();
        //TODO 根據(jù)abc,d,e組裝abcde數(shù)據(jù)
        resultList.add(abcde);
    }
    //TODO excel一次性寫入skip,resultList
}

初看起來，也沒什么問題，但是當(dāng)數(shù)據(jù)量快速增漲的時候，發(fā)現(xiàn)excel導(dǎo)出的時間會比較慢，而且當(dāng)數(shù)據(jù)條數(shù)過W的時候，甚至?xí)霈F(xiàn)導(dǎo)出空頁的情況。
其實這種簡單的寫法有3個問題：
1>一次性導(dǎo)出數(shù)據(jù)可能會OOM,換成分批寫入excel
2>left join效率低下，尤其是2張表以上,select * 的寫法應(yīng)該具體到對應(yīng)的字段
3>for循環(huán)獲取數(shù)據(jù)會循環(huán)調(diào)用sql，執(zhí)行時間長
針對上述3個問題我們做進(jìn)一步的優(yōu)化，稱為優(yōu)化版本:
1> 針對sql條件我們做優(yōu)化，拆分為2個查詢方法，并且加上分頁,去掉*用法，left join去掉
方法一： getAB

<select id="getAB" ......>
   select  A.ID as AID,A.TYPEA,A.NAMEA,
   B.ID as BID, B.TYPEB,B.NAMEB from 
  A , B  
   <where>
     AND A.ID  = B.AID
     <if test="TYPEA != NULL  and TYPEA!=''">
       and A.TYPEA = #{TYPEA}
     </if>
    <if test="TYPEB != NULL and TYPEB!='' ">
       and B.TYPEB = #{TYPEB}
     </if>
   </where>
order by A.ID desc
limit #{skip},#{limit}
</select>

方法二：getABC

<select id="getABC" ......>
    select  A.ID as AID,A.TYPEA,A.NAMEA,
   B.ID as BID, B.TYPEB,B.NAMEB ,
  C.ID as CID, C.TYPEC,C.NAMEC ,
  from A,B,C
  from A left join B on 
  left join C on 
    <where>
       and A.ID  = B.AID
       and C.BID = B.AID 
      and C.AID = B.AID
      <if test="TYPEA != NULL  and TYPEA!=''">
        and A.TYPEA = #{TYPEA}
      </if>
     <if test="TYPEB != NULL and TYPEB!='' ">
        and B.TYPEB = #{TYPEB}
      </if>
     <if test="NAMEC!= NULL and NAMEC !='' ">
        and C.NAMEC   CONCAT('%','${NAMEC}','%' )
      </if>
    </where>
  order by A.ID desc
  limit #{skip},#{limit}
</select>

2>循環(huán)自分頁獲取數(shù)據(jù)

/** 偽代碼 **/
public void  getABCDEList(ABC abc) {
    List<ABCDE> resultList = new ArrayList<>();
    List<ABC> listABC = new ArrayList<>();
   //是否需要獲取C信息
    boolean needC =true;
    //對比測試50，100，200，300，400，500，1000條，發(fā)現(xiàn)200效果更佳執(zhí)行
    //總時間最短
    int skip = 0;
    int limit = 200; 
    int pageNum = 1;
    boolean hasNext = true;
    while(hasNext) {
        //NAMEC存在
        skip = (pageNum - 1) * limit;
        //根據(jù)查詢條件調(diào)用對應(yīng)的方法
        if(null != abc.NAMEC  && "".equls(abc.NAMEC)) {
         listABC = ABCMapper.getABCList(abc,skip,limit);
         needC = false;
       } else {
         listABC = ABCMapper.getABList(abc, skip,limit);
       }
      //調(diào)用批量獲取list方法
      resultList = getABCDEList(listABC);
     //TODO excel分批次寫入文件
    if(listABC.size()  <  limit)
       hasNext = false;
   } else {
     //分頁自動加1
      pageNum += 1;
  }
    return resultList;
}

方法二：根據(jù)listABC獲得List<ABCDE>

private List<ABCDE>  getABCDEList(List<ABC> listABC){
  //循環(huán)獲取A.B 對應(yīng)ID信息
      List<Integer> AIDS = new ArrayList<>();
      List<Integer> BIDS = new ArrayList<>();
     for(ABC abc:listABC) {
        AIDS.add(abc.AID);
        BIDS.add(abc.BID);
     }
    // 批量獲取C.D.E數(shù)據(jù)
     List<C> listC = new ArrayList<>();
     List<D> listD = new ArrayList<>();
     List<E> listE = new ArrayList<>();
     //按照AID+BID放置到對應(yīng)的map結(jié)構(gòu)里
     Map<String,C> mapC = new HashMap<>();
     Map<String,D> mapD = new HashMap<>();
     Map<String,E> mapE = new HashMap<>();
     //數(shù)據(jù)獲取和組裝
      if(needC){
         listC = CMapper.getListCByIDS(AIDS,BIDS);
         for(C c:listC) {
            mapC.put(String.valueOf(c.AID) + "-" + String.valueOf(c.AID),c);
          }
     }
    listD = DMapper.getListCByIDS(AIDS,BIDS);
    listE = EMapper.getListCByIDS(AIDS,BIDS);
    for(D d:listD) {
            mapD.put(String.valueOf(d.AID) + "-" + String.valueOf(d.AID),d);
     }
    for(E e:listE) {
            mapE.put(String.valueOf(e.AID) + "-" + String.valueOf(e.AID),e);
    }
     //TODO 根據(jù)CDE 對應(yīng)ID
    for(ABC abc:listABC){
        ABCDE abcde = new ABCDE();
        //TODO 根據(jù)abc,(mapC),mapD,mapE裝abcde數(shù)據(jù)
        resultList.add(abcde);
    }
   return resultList;
}

后記：關(guān)于excel寫入分批寫入的優(yōu)化，會開專門的篇幅概述，敬請期待！