背景

公司一直維護著學生體測平臺，最近中標一個項目，從原本零零散散的錄入體測數據，驟增到幾天內上百萬的用戶及上千萬的體測數據涌入系統，結果各種異常、慢查、連接拒絕，層出不窮...

過多的慢sql會導致數據庫CPU飄升、連接無法及時釋放、連接池超出限制，原本幾毫秒的查詢也遲遲無法得到響應，拖垮整個系統，而且系統中使用了消息隊列，還導致了消息堆積，重啟服務后，服務瞬間又被打滿，內存飄升、頻繁的full GC...之前也整理過不少sql優化的文章，但沒多少用戶時，很少去關注，用戶驟增時，還是經歷了一番“洗禮”，這里記錄一下這幾天的優化點。

索引使用

索引優化，是一個老生常談的問題了。項目中，利用云平臺或者配合druid搭建慢sql報警機制，可以篩選出執行過慢的語句，然后，再借助explain去分析（有關explain的用法之前做過詳細講解：sql調優之explain關鍵字詳解）。

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

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

未遵循最左前綴匹配導致索引失效

使用函數導致索引失效

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

計算導致索引失效

select * from student where id + 1 = 666。

類型轉換導致索引失效

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

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

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

like模糊匹配以通配符開頭導致索引失效

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

索引字段使用is not null導致失效

select * from student where name is not null。

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

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

這里列舉兩個此次優化的示例：

示例一：find_in_set

業務中有這樣一個場景，上級需要看到其所有下級的數據，如省及單位需要能查看其下所有市區縣的學校。為了方便，之前將學生所隸屬的學校以及其上級省市區單位，記錄在一個字段sponsor_ids中，然后通過find_in_set查詢，如下：

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

這樣設計，代碼寫起來很方便，但數據量級大是，查詢效率極其低下，因為find_in_set無法使用索引。

優化：事先查詢所屬下級，然后通過in查詢使用索引

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

雖然在查詢下級機構時，也使用了find_in_set，但機構表數據量有限，全表掃也無太大壓力，而enroll報名表使用in查詢后，效率明顯提升，由幾秒提升到幾十毫秒。

示例二：聯合索引

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

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

這句sql，是用于統計某次體測活動中，各個學校的報名人數。設計表時在match_id，sponsor_id上，分別建立的索引，報名人數不多時，效率還可以。但報名人數幾十萬之后，發現查詢效率明顯下降，需要幾秒鐘。explain分析如下：

從上述的分析可以看出，查詢使用了索引，但只用到了match_id這一個索引，而extra中顯示了using temporary，即使用了中間表進行分組，并未用到索引，數據量達到一定量級后，中間表也會很大，效率自然也就降低了。

為此，針對該查詢，建立了match_id和sponsor_id的聯合索引，explain發現，不在使用中間表，實際查詢效率也明顯提升，大概幾百毫秒。（使用到覆蓋索引，不需要回表查詢）

批量插入與更新（避免循環單條插入）

批量操作，是業務中很常見的，比如批量導入學生，簡單粗暴的一種做法就是，for循環，然后在循環中insert，如：

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

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

當插入百八十條數據時，不會覺得效有多低，但當插入上萬條數據時，循環插入可能需要一兩分鐘甚至更久，這就無法忍受了。正確的打開方式：

<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>

避免全表

業務中有這樣一個需求，導入學生時，需要對比學生編號是否有重復。之前的實現方式是將student表中的所有數據，全都查出來，加載到內存，在內存中與導入的數據逐條對比。上百萬的數據，而且還是select *，結果可想而知...

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

無能為力的SQL

業務中有這樣一個場景：學生參與體測活動，需要報名，因此，除了student學生表之外，還有一張enroll報名表，用于記錄學生在多個體測活動中的報名狀態。業務中需要統計所有未報名的學校，思路很簡單，就是學生表和報名表取差集，然后統計差集中有哪些學校，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

問題在于，一個地區會有幾百萬甚至上千萬的學生，如此龐大的兩張表取交集，太慢了.，優化好久也沒思路...（歡迎評論區指點迷津）。SQL無力了，最終選擇，將未報名的學校數據提前統計計算，放在緩存之中，有學生狀態或者報名狀態發生變更，則去更新。該思路也是類似于數據平臺，千萬級別的數據，實時統計，肯定會有效率問題，因此，往往會在數據服務中非實時計算。

總結

以上是平臺數據爆發期間，優化項目是的一些感悟，隨筆一記。