引子
在《容易引起雪崩的兩個處理》裡，我提到一個慢查詢的問題。本文先從整潔架構的角度講講慢查詢sql完成的功能以及設計，再介紹對sql進行的實施測試現象以及思考。
設計講解
一見楊過誤終身，有多少萌妹子敗給了一個痴字。金庸筆下的痴兒怨女數量之多就需要單獨申請伺服器，用獨立儲存單元進行儲存。
先說神鵰俠侶的楊過，陸無雙和程英兩姐妹苦等他16年最終等來了他和小龍女撒了一波狗糧而去；郭芙含怨20多年；完顏萍一生為楊過夢繞魂牽；郭襄做了尼姑；最慘公孫綠萼，為救他而死，用生命換來的解藥楊過轉身扔進絕情谷底，公孫綠萼在天上看到此情此景也會氣的罵娘吧。
再說雪山飛狐的胡斐，出家的袁紫衣；為救他慘死的程靈素……
從領域驅動設計的角度講，上面都是值物件。今天不講值物件，講實體。所以先拉回來。
功能簡單來說如上圖，就是從頁面發起查詢，查詢入口有多個。有個頁面是查詢角色的仇人，比如楊過的仇人有金輪法王、公孫止、趙志敬……；有個頁面是查詢角色撩過的妹子，比如楊過撩過的妹子有凌洪波、陸無雙、程英……；有些人在幾個查詢頁面都能查到，比如李莫愁既是楊過撩過的妹子，又是楊過的仇人。這裡我們把一種頁面叫一個請求方。
請求方發起更新查詢請求，比如請求方要查詢楊過的所有仇人。請求經過轉接系統將請求落庫後轉發到MQ。MQ非同步返回包含楊過、胡斐在內的所有仇人的結果。轉接系統根據結果反查請求資訊，將結果儲存。請求方3s後再發起結果查詢，這時就會根據請求條件直接返回楊過的所有仇人。這裡值得注意的是一次請求返回的結果有的100多條，有的200多條。就是說楊過、胡斐等這些金庸筆下的人物目前仇人列表總數是100多條；楊過、胡斐等這些金庸筆下的人物目前撩過的妹子列表總數是200多條。編輯還在不斷錄入資料，過一段時間，資料會變化。
功能的設計整體採用整潔架構中的事件溯源的變通方法。對於發起請求，只有增加和查詢操作；對於回執結果，因為查詢只會查詢最新回執。所以一個請求方結果返回後會將所有之前的老資料更新為is_new=N，新插入記錄is_new=Y。
回執結果記錄資料表設計如下：
問題描述
這個查詢系統受到不少人的喜愛，不知不覺間，查詢次數過多，事件溯源的設計導致回執結果記錄資料表積累了358萬條資料。之前沒有索引，結果出現一次請求查詢20多秒，更新處理直接把資料庫拖掛了。
在《容易引起雪崩的兩個處理》裡，我講解過給is_new加上索引，雖然is_new只有N和Y兩個值，總體資料區分度不高，但是對於這種N和Y的值佔比是萬比一比例，只會查其中少的那部分，卻是比普通索引有更高的效率。
問題來了，請求方的查詢條件是where is_new=Y and 角色名=楊過 and 查詢來源=仇人。我是該建立三個欄位聯合索引還是兩個欄位聯合索引還是單個索引？
反直覺！經過試驗：
1>is_new、角色名、查詢來源 三個欄位加索引
2>is_new、查詢來源兩個欄位加索引
3>is_new、角色名兩個欄位加索引
4>is_new單個欄位加索引
四種情況sql執行速度在ms級別沒有任何差別！幾次測試都是6ms返回！而更新時間雖然增加了建索引的時間，反而耗時大大減少！
原理分析
查詢時間分析
上面所列的索引新增方式都是索引全中，假設is_new的資料共400多條，某查詢來源的資料是一二百條，某角色名大概十幾條。B+樹底層：
1> is_new、角色名、查詢來源 三個欄位加索引時索引命中十幾條，然後通過主鍵查到資料返回
2> is_new、查詢來源 兩個欄位加索引時索引命中一二百條，然後掃描這一二百條資料，查到需要的十幾條資料返回
3>is_new、角色名 兩個欄位加索引時索引命中二十幾條，然後掃描這二十幾條資料，查到需要的十幾條資料返回
4>is_new單個欄位加索引時索引命中400多條，然後掃描這400多條資料，查到需要的十幾條資料返回
之所以單個索引和聯合索引查詢結果區別不大呢，是因為掃描的資料共400多條，按照資料庫的處理能力來說不算什麼，這6ms時間主要花在了組裝資料和傳輸資料上。如果查詢條件不命中(返回資料條數為0)，查詢時間幾乎為0！
更新時間分析
上面提到每次查詢都會把上次結果的is_new更新為N，新資料插入時is_new=Y。所以讀寫比例為1:1。更新效能就是不能不考慮的問題。當然最重要的是更新操作是資料庫被拖掛的罪魁禍首。
update 回執結果記錄資料表 set is_new=N where is_new=Y and 查詢來源=仇人。
我測試了一下：
1> is_new、角色名、查詢來源 三個欄位加索引時更新時間90ms。
2> is_new、查詢來源 兩個欄位加索引時更新時間60ms。
3>is_new、角色名 兩個欄位加索引時更新時間60ms。
4>is_new單個欄位加索引時更新時間30ms。
這個更新操作，涉及索引重建。層數越多越慢不難理解。但是為什麼時間是幾十毫秒級別呢？沒建索引之前查詢都要20s。原因是B+樹是樹形結構。示意圖如下所示，宣告：下面的解釋只是針對這個問題一個腦補過程，實際上有很多不嚴謹的地方。比如B+樹還有最底層的葉子節點來存放資料。葉子節點之間有雙向連結串列，與主題無關，沒畫那麼細。
更新操作會首先進行一個查詢，is_new=Y，然後會在is_new=Y這個範圍內將is_new=Y涉及的一二百條資料，再到is_new=N下面自己的位置進行插入。如果是三層，每層的資料都需要先找自己的位置，最慢。這裡面沒有畫的葉子節點是從左到右按id順序排序的。如果只有一層is_new=Y的整個直接放到is_new=N下面的最後就可以了，最快。這就解釋了四種更新方式的更新時間差異。
但是為什麼加了索引和完全不加索引之間有有多於千倍的效能差異呢？更新操作的時間也主要是花在查詢上。如果完全不加索引，一二百條資料每條插入前先進行查詢，查詢要全表掃描，358萬條資料，16K為一個記憶體換頁。我就不具體算了，但是要進行很多次記憶體換頁才能查出來。還要乘以資料條數。而加了索引，因為有is_new=Y條件，進行一次記憶體換頁就可以了。因為is_new=Y資料總共就400多條，1個記憶體頁是可以存下的。所以一二百條資料中下一條就不需要記憶體換頁了，查詢總共就需要1次記憶體換頁，基本不花什麼時間。剩下的就是一條條插入具體位置了。
最終結論
通過上面比較，自然是隻加單索引is_new最高效。
往期推薦