背景

公司一直維護(hù)著學(xué)生體測(cè)平臺(tái)，最近中標(biāo)一個(gè)項(xiàng)目，從原本零零散散的錄入體測(cè)數(shù)據(jù)，驟增到幾天內(nèi)上百萬(wàn)的用戶(hù)及上千萬(wàn)的體測(cè)數(shù)據(jù)涌入系統(tǒng)，結(jié)果各種異常、慢查、連接拒絕，層出不窮...

過(guò)多的慢sql會(huì)導(dǎo)致數(shù)據(jù)庫(kù)CPU飄升、連接無(wú)法及時(shí)釋放、連接池超出限制，原本幾毫秒的查詢(xún)也遲遲無(wú)法得到響應(yīng)，拖垮整個(gè)系統(tǒng)，而且系統(tǒng)中使用了消息隊(duì)列，還導(dǎo)致了消息堆積，重啟服務(wù)后，服務(wù)瞬間又被打滿(mǎn)，內(nèi)存飄升、頻繁的full GC...之前也整理過(guò)不少sql優(yōu)化的文章，但沒(méi)多少用戶(hù)時(shí)，很少去關(guān)注，用戶(hù)驟增時(shí)，還是經(jīng)歷了一番“洗禮”，這里記錄一下這幾天的優(yōu)化點(diǎn)。

索引使用

索引優(yōu)化，是一個(gè)老生常談的問(wèn)題了。項(xiàng)目中，利用云平臺(tái)或者配合druid搭建慢sql報(bào)警機(jī)制，可以篩選出執(zhí)行過(guò)慢的語(yǔ)句，然后，再借助explain去分析（有關(guān)explain的用法之前做過(guò)詳細(xì)講解：sql調(diào)優(yōu)之explain關(guān)鍵字詳解）。

利用explain，可以快速的分析出缺失的索引，比如檢查join或者order by中使用的字段，對(duì)于大表，有無(wú)索引會(huì)有幾十甚至上百倍的效率差異。但索引也并非越多越好，過(guò)多的索引會(huì)對(duì)插入及更新造成比較大的影響。

explain之后，type為ALL的，即未使用到索引，多數(shù)還是比較容易處理的。這里順帶列舉一些常見(jiàn)的索引失效場(chǎng)景：

未遵循最左前綴匹配導(dǎo)致索引失效

使用函數(shù)導(dǎo)致索引失效

select * from student where name = left('云端行筆666',4)。

計(jì)算導(dǎo)致索引失效

select * from student where id + 1 = 666。

類(lèi)型轉(zhuǎn)換導(dǎo)致索引失效

select * from student where convert(id,char) = '666'。

不等于(!= 或者<>)索引失效

select * from student where name != "云端行筆"。

like模糊匹配以通配符開(kāi)頭導(dǎo)致索引失效

select * from student where name like "%云端行筆"。

索引字段使用is not null導(dǎo)致失效

select * from student where name is not null。

OR前后存在非索引的列，索引失效

select * from student where id =1 or name = '云端'。

這里列舉兩個(gè)此次優(yōu)化的示例：

示例一：find_in_set

業(yè)務(wù)中有這樣一個(gè)場(chǎng)景，上級(jí)需要看到其所有下級(jí)的數(shù)據(jù)，如省及單位需要能查看其下所有市區(qū)縣的學(xué)校。為了方便，之前將學(xué)生所隸屬的學(xué)校以及其上級(jí)省市區(qū)單位，記錄在一個(gè)字段sponsor_ids中，然后通過(guò)find_in_set查詢(xún)，如下：

SELECT grade FROM enroll 
WHERE match_id = 60 AND FIND_IN_SET(36, sponsor_ids) 
GROUP BY grade ORDER BY grade

這樣設(shè)計(jì)，代碼寫(xiě)起來(lái)很方便，但數(shù)據(jù)量級(jí)大是，查詢(xún)效率極其低下，因?yàn)閒ind_in_set無(wú)法使用索引。

優(yōu)化：事先查詢(xún)所屬下級(jí)，然后通過(guò)in查詢(xún)使用索引

SELECT
	s.id
FROM
( SELECT * FROM sponsor WHERE `status` = 1 AND  superior_id IS NOT NULL order by level asc ) s,
  ( SELECT @pid := #{sponsorId} ) pd
WHERE
FIND_IN_SET( superior_id, @pid ) != 0
AND @pid := concat( @pid, ',', id )

SELECT grade FROM enroll 
WHERE match_id = 60 AND  sponsor_id in (36) 
GROUP BY grade ORDER BY grade

雖然在查詢(xún)下級(jí)機(jī)構(gòu)時(shí)，也使用了find_in_set，但機(jī)構(gòu)表數(shù)據(jù)量有限，全表掃也無(wú)太大壓力，而enroll報(bào)名表使用in查詢(xún)后，效率明顯提升，由幾秒提升到幾十毫秒。

示例二：聯(lián)合索引

還有一些其他情況，比如：

SELECT sponsor_id, count(sponsor_id) AS sponsor_count FROM enroll 
WHERE match_id = 60 
GROUP BY sponsor_id

這句sql，是用于統(tǒng)計(jì)某次體測(cè)活動(dòng)中，各個(gè)學(xué)校的報(bào)名人數(shù)。設(shè)計(jì)表時(shí)在match_id，sponsor_id上，分別建立的索引，報(bào)名人數(shù)不多時(shí)，效率還可以。但報(bào)名人數(shù)幾十萬(wàn)之后，發(fā)現(xiàn)查詢(xún)效率明顯下降，需要幾秒鐘。explain分析如下：

從上述的分析可以看出，查詢(xún)使用了索引，但只用到了match_id這一個(gè)索引，而extra中顯示了using temporary，即使用了中間表進(jìn)行分組，并未用到索引，數(shù)據(jù)量達(dá)到一定量級(jí)后，中間表也會(huì)很大，效率自然也就降低了。

為此，針對(duì)該查詢(xún)，建立了match_id和sponsor_id的聯(lián)合索引，explain發(fā)現(xiàn)，不在使用中間表，實(shí)際查詢(xún)效率也明顯提升，大概幾百毫秒。（使用到覆蓋索引，不需要回表查詢(xún)）

批量插入與更新（避免循環(huán)單條插入）

批量操作，是業(yè)務(wù)中很常見(jiàn)的，比如批量導(dǎo)入學(xué)生，簡(jiǎn)單粗暴的一種做法就是，for循環(huán)，然后在循環(huán)中insert，如：

for (int i = 0; i < 50000; i++){
  Student student = new Student("云端行筆" + i,24,"北京市" + i,i + "號(hào)");
  studentMapper.insert(student);
}

<insert id="add" parameterType="com.peng.Student">
  INSERT INTO TEST(ID,Student) VALUES(#{id},#{student});
</insert>

當(dāng)插入百八十條數(shù)據(jù)時(shí)，不會(huì)覺(jué)得效有多低，但當(dāng)插入上萬(wàn)條數(shù)據(jù)時(shí)，循環(huán)插入可能需要一兩分鐘甚至更久，這就無(wú)法忍受了。正確的打開(kāi)方式：

<insert id="batchAdd" parameterType="java.util.List">
  INSERT INTO TEST(ID,Student)
VALUES
  <foreach collection="list" item="item" index="index" separator="," >
    (#{item.id},#{item.student})
    </foreach>
</insert>

避免全表

業(yè)務(wù)中有這樣一個(gè)需求，導(dǎo)入學(xué)生時(shí)，需要對(duì)比學(xué)生編號(hào)是否有重復(fù)。之前的實(shí)現(xiàn)方式是將student表中的所有數(shù)據(jù)，全都查出來(lái)，加載到內(nèi)存，在內(nèi)存中與導(dǎo)入的數(shù)據(jù)逐條對(duì)比。上百萬(wàn)的數(shù)據(jù)，而且還是select *，結(jié)果可想而知...

這種掃全表、select *的做法，回頭來(lái)看，其實(shí)是很初級(jí)的，但實(shí)際開(kāi)發(fā)中，很多時(shí)候，為了圖省事兒，可能就隨手把坑埋下去了...

無(wú)能為力的SQL

業(yè)務(wù)中有這樣一個(gè)場(chǎng)景：學(xué)生參與體測(cè)活動(dòng)，需要報(bào)名，因此，除了student學(xué)生表之外，還有一張enroll報(bào)名表，用于記錄學(xué)生在多個(gè)體測(cè)活動(dòng)中的報(bào)名狀態(tài)。業(yè)務(wù)中需要統(tǒng)計(jì)所有未報(bào)名的學(xué)校，思路很簡(jiǎn)單，就是學(xué)生表和報(bào)名表取差集，然后統(tǒng)計(jì)差集中有哪些學(xué)校，SQL如下：

select distinct(s.sponsor_id) from student s
LEFT JOIN enroll e on e.student_id = s.id
where s.status = 1 and e.id is null

問(wèn)題在于，一個(gè)地區(qū)會(huì)有幾百萬(wàn)甚至上千萬(wàn)的學(xué)生，如此龐大的兩張表取交集，太慢了.，優(yōu)化好久也沒(méi)思路...（歡迎評(píng)論區(qū)指點(diǎn)迷津）。SQL無(wú)力了，最終選擇，將未報(bào)名的學(xué)校數(shù)據(jù)提前統(tǒng)計(jì)計(jì)算，放在緩存之中，有學(xué)生狀態(tài)或者報(bào)名狀態(tài)發(fā)生變更，則去更新。該思路也是類(lèi)似于數(shù)據(jù)平臺(tái)，千萬(wàn)級(jí)別的數(shù)據(jù)，實(shí)時(shí)統(tǒng)計(jì)，肯定會(huì)有效率問(wèn)題，因此，往往會(huì)在數(shù)據(jù)服務(wù)中非實(shí)時(shí)計(jì)算。

總結(jié)

以上是平臺(tái)數(shù)據(jù)爆發(fā)期間，優(yōu)化項(xiàng)目是的一些感悟，隨筆一記。