最近，一朋友整天因為CPU 99%搞得茶飯不思，夜不能寐，找到我”訴苦“。聽完成，心中一陣竊喜：是時候展現真正的實力了（其實是練手的機會來了）。半推半就應承了下來。
01 分析問題
幾分鐘瞭解下來，大概情況如下：會員可以推薦其他使用者註冊，會員有一個屬性--活躍度，使用者觀看視訊，簽到等等行為時，這個屬性會動態變化，其中有個需求是統計所有下級活躍度。會員模型如下，模型比較簡單，使用使用sqlserver 2016。
瞭解需求後，再來看看佔cpu高的查詢。其中佔cpu 時間最多的就是下面這條sql，查詢某個使用者所有下級的活躍度之和。這條語句使用遞迴查詢，那是比較耗時的，另外如果層級太多還有如下錯誤：語句被終止。完成執行語句前已用完最大遞迴 100。
1 WITH T 2 AS( 3 SELECt DataID, avtivenewss FROM User WHERE DataID = 4167 4 UNION ALL 5 SELECT u.DataID, u.avtivenewss 6 FROM User U INNER JOIN T ON U.parentUserid=T.DataID 7 ) 8 SELECT sum(avtivenewss) FROM T
當前使用者1W+。
02 失敗的嘗試:字首法
幾乎沒思考，就想到了一個方法：字首法。一個使用者的所有後代使用同一個字首。增加一個欄位 paths，以使用者編號為基礎，格式為：/1/2/3/，使用者註冊時使用記錄上級的paths + 上級id 生成自己的paths, 增加paths的索引，這樣更具此欄位就可以查詢我的所有下級了，再寫個腳步初始化。非常簡單，三下五除二就解決了。上線效果非常明顯。
但是好景真短，沒兩天cpu 又高了。一查，好多查詢都沒有走索引，明明欄位已經加了索引。一看資料，原來是索引長度限制問題。使用者居然有好幾十個層級，且還在不斷增加，長度超過索引最大長度後，索引失效。
當前使用者10W+。
04 大膽猜測，小心嘗試
會員關係是一棵樹，不管怎麼遍歷，效率都有限。如果把樹拉平，使用者與所有後代都建立一個關係，效能會怎麼樣呢。為了避免再次打臉，悄悄的開始了嘗試。增加模型tree。
這需要在使用者註冊時，新增與所有祖祖輩輩的關係。比如 4167使用者註冊，需要一次新增14條關係記錄。而查詢使用者的所有子子孫孫時，也會非常方便。
編寫指令碼初始化資料，增加切換開關，戰戰兢兢的上線了。經過一個高峰，cpu居然都在10%以下，完全沒有壓力嘛，終於可以亮出臉來了。這是典型的空間換時間。但是高興之餘，心中閃過一個念頭--這個表的膨脹速度有點快，它會有極限麼。不過，馬上被另一個念頭壓制了：小網站能有多大量！
有個什麼定律來著：凡事只要有可能出錯，那就一定會出錯。果不其然，10天后同樣的問題再次發生。cpu幾乎爆滿。只能不停重啟，最後乾脆下掉了這個統計功能，為此還被懟了一頓，甚至說實在不行換mysql吧。我竟無言以對。此時關係表總數為10億。
當前使用者100W+。
05 終級方案：分表
其實對於分表這樣的事兒，一直都有想過，就是沒幹過，另外也一直以為是sum()引起的cpu問題。後來諮詢了公司做交易的同學，傳說他們每天處理2kw的訂單，基本思路也是根據使用者id分了2048張表。反正也沒轍了，擼起袖子搞起來吧。分析了tree 表只有兩個查詢場景，查使用者所有後代及查使用者所有祖先。分別以ancestor ，descendant 分256 張表。使用者註冊時，把關係分別寫到個分表中。
編寫指令碼初始化兩個緯度的分表資料，再次戰戰兢兢的上線了。經過數個高峰，效果比預期好太多了。徹底告別了cpu告警。
經歷了幾次失敗，早以沒了高興勁兒，心中有個疑問，如果是sum引起的cpu跑滿的話，現在sum並沒有減少，但是cpu為啥清閒了呢？這背後一定有原因的。
06 刨根問底
帶著上面的疑問，對比了一些監控資料，發現了一個可疑的地方，磁碟請求減少了數百次。那麼磁碟與cpu有什麼樣的關係呢？
查詢相關資料後，得出一個猜測：
未分表前，每次查詢所有後代（祖先時）時，因為資料在磁碟上非常分散，page cache 命中率低，磁碟預讀失效，所以一次查詢要進行很多次磁碟隨機讀，大量的io操作，cpu就要進行大量的上下文切換，從而導致cpu跑滿。
分表後，每次查詢所有後代（祖先時）時，每次都在特定的分表中查詢，資料在磁碟上非常緊湊，磁碟預讀發揮最大效能，page cache 命中率高，io次資料大量減少，cpu上下文切換次數減少，沒了壓力，cpu自然清閒了。
最後打個廣告：哈囉出行，base 杭州， java 我們的部門有大量HC（供應鏈，本地生活方向都有）, 歡迎私聊哈。微信：jijunjian
I have a dream to be a good programmer。
