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對(duì)應(yīng)關(guān)系都為1對(duì)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 簡(jiǎn)單的實(shí)現(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ù)量快速增漲的時(shí)候闹瞧，發(fā)現(xiàn)excel導(dǎo)出的時(shí)間會(huì)比較慢，而且當(dāng)數(shù)據(jù)條數(shù)過W的時(shí)候展辞，甚至?xí)霈F(xiàn)導(dǎo)出空頁的情況奥邮。
其實(shí)這種簡(jiǎn)單的寫法有3個(gè)問題：
1>一次性導(dǎo)出數(shù)據(jù)可能會(huì)OOM,換成分批寫入excel
2>left join效率低下，尤其是2張表以上,select * 的寫法應(yīng)該具體到對(duì)應(yīng)的字段
3>for循環(huán)獲取數(shù)據(jù)會(huì)循環(huán)調(diào)用sql，執(zhí)行時(shí)間長(zhǎng)
針對(duì)上述3個(gè)問題我們做進(jìn)一步的優(yōu)化洽腺，稱為優(yōu)化版本:
1> 針對(duì)sql條件我們做優(yōu)化脚粟，拆分為2個(gè)查詢方法核无，并且加上分頁,去掉*用法，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;
//對(duì)比測(cè)試50藕坯，100团南，200，300霹购，400佑惠，500，1000條齐疙，發(fā)現(xiàn)200效果更佳執(zhí)行
//總時(shí)間最短
int skip = 0;
int limit = 200;
int pageNum = 1;
boolean hasNext = true;
while(hasNext) {
//NAMEC存在
skip = (pageNum - 1) * limit;
//根據(jù)查詢條件調(diào)用對(duì)應(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 {
//分頁自動(dòng)加1
pageNum += 1;
}
return resultList;
}
方法二：根據(jù)listABC獲得List<ABCDE>
private List<ABCDE> getABCDEList(List<ABC> listABC){
//循環(huán)獲取A.B 對(duì)應(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放置到對(duì)應(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 對(duì)應(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)化，會(huì)開專門的篇幅概述赌厅，敬請(qǐng)期待轿塔！
