1、前言
- 繼上回，小明自從面試的時候被問到如何進行索引調優後，小明通過學習資料庫調優-索引篇一文掌握了索引和索引優化相關知識。做好準備後他又去一家新的公司面試......
- 面試官：小明，你履歷上寫了你會資料庫調優，你都是怎麼調優的？
- 小明：加索引，索引優化......
- 面試官：除此之外還有麼？索引優化有什麼瓶頸嗎？如果索引優化已經無法提高性能要怎麼辦呢？
- 在上文中我們提到過為什麼SQL會慢的原因之一有資料過多，當資料庫表的資料量達到一定量級時，由于查詢次元較多，即使添加了索引，做了索引優化，資料庫的性能仍然不佳。此時就要考慮其他方式來減少資料庫的負擔，縮短查詢時間。本篇文章給大家介紹資料庫大表治理的解決方案
2、資料治理方式
官方說明為500萬-800萬會影響資料庫性能，而我們将超過2000萬行或10GB的表定義為大表。當某張表達到大表标準時，就需要對該表進行治理，大表治理的方式一般分為資料歸檔、資料切分兩種。
2.1 資料歸檔
資料歸檔是一種簡單、高效的資料治理方案。通過将曆史無用的資料歸檔到獨立的歸檔表中，可以顯著減小主表的資料量，這些歸檔的資料可以直接删除，也可以在某張表中存檔一段時間。在實際項目中，可以手動歸檔一批資料，也可以通過定時任務腳本定期将一定時間之前的無用資料進行歸檔。例如，對使用者消息表，我們可以将一年前的資料進行歸檔，進而大幅降低主表的資料量。
2.2資料切分
資料切分的基本思想就要把一個資料庫切分成多個部分放到不同的資料庫或資料表上，進而緩解單一資料庫表的性能問題。一般而言，針對海量資料的資料庫，如果是因為表多導緻資料多，容易導緻資料庫連接配接數過大，帶寬有壓力時，這時候适合使用垂直切分，即把關系緊密（比如同一子產品）的表切分出來放在一個庫上。如果表并不多，但每張表的資料非常多，這時候适合水準切分，即把表的資料按某種規則切分到多個資料表上。當然，實際項目中更多是這兩種混合一起使用，這時候需要根據實際情況設計對應的切分方案。資料切分根據其切分類型，可以分為兩種方式：垂直（縱向）切分和水準（橫向）切分。
2.2.1 水準切分
當資料庫資料量行數巨大，存在單庫讀寫、存儲性能瓶頸，這時候就需要進行水準切分了，水準切分分為庫内分表和分庫分表，是根據表内資料内在的邏輯關系，将同一個表按不同的條件分散到多個資料庫或多個表中，每個表中隻包含一部分資料，進而使得單個表的資料量變小，達到分布式的效果。
2.2.2 垂直切分
垂直切分又可細分為垂直分庫、垂直分表。
- 垂直分庫：就是根據業務耦合性，将業務關聯度低的不同表存儲在不同的資料庫。與"微服務"相似，每個微服務使用單獨的一個資料庫。
- 垂直分表：是基于資料庫中的"列"進行，在字段很多的情況下（一個表超過20個字段），通過"大表拆小表"，将比較常用的字段放到一張表中，比較不常用的字段放到擴充表中，這樣更便于開發與維護，減少了磁盤IO，也能使MYSQL避免跨頁問題(MySQL底層是通過資料頁存儲的，一條記錄占用空間過大會導緻跨頁，造成額外的性能開銷。這裡就不做過多贅述)。進而提升了資料庫性能。
3、資料切分項目實踐
3.1 背景介紹
為了應對站内信消息資料庫資料量超過21億、單表7000萬的挑戰，我們決定進行資料切分項目。在這一背景下，我們詳細規劃了項目的實踐步驟。
3.2 切分方案設計
3.2.1 選擇水準切分
考慮到站内信消息表的龐大資料量和未來的增量，我們決定采用水準切分方式，将站内信庫表由3個庫每個庫10張表拆分為4個庫，每個庫256張表。這樣的設計在單表2000萬為最大容量的基礎上，能夠容納200億以上的資料，即使不進行任何歸檔操作也能支撐5年。
3.2.2 分庫分表規則
我們制定了詳細的分庫分表規則，以確定資料切分後的合理管理和查詢效率。分庫規則采用了userId % 1024取餘，再對userId / 256取整的方式，這樣設計可以避免資料不均勻。分表規則是userId % 256。
3.3 資料遷移政策
由于資料量巨大，同時老庫沒有全局唯一鍵，資料同步隻能通過代碼腳本來操作。我們采用了分段查詢的方式，通過ID進行分段查詢，以提高資料遷移效率。
3.4資料雙寫
資料雙寫就是将一條資料同時寫入兩個資料庫或表中，以保證兩個資料庫的增量資料一緻性。雙寫一般需要考慮以下兩種情況：
3.4.1 切讀之前
在切分之前，由于老庫已經采用了分庫分表，我們需要保證資料的一緻性。為此，在新表中增加了一個字段unionId作為資料唯一鍵，其值由userId、"_"、id拼接組成，例如：老表的一條資料userId=123456，id=123456；unionId=“123456_123456”。在插入和更新資料時，我們需要進行一系列操作以保證新老庫資料的一緻性。
- 插入資料： 資料先寫老庫，在老庫成功寫入資料後，提取老庫的userId和id組成全局唯一鍵unionId并指派到新庫資料對象後再執行寫新庫邏輯。
- 更新資料： 更新邏輯都是通過userId和id來更新的，在新庫中根據unionId截取字段得到對應老庫的userId和id。然後再對這條資料進行更新操作，更新的資料内容取老庫全字段資料，這樣可以保證資料一緻性。
3.4.2 切讀之後
在切分之後，更新資料的邏輯需要進一步考慮。需要根據新庫中的unionId，截取字段拿到對應老庫的userId與id，再根據這些資訊去更新老庫的資料。這樣的雙寫機制確定了資料在切分前後的一緻性。
- 插入資料： 資料先寫老庫，在老庫成功寫入資料後，提取老庫的userId和id組成全局唯一鍵unionId并指派到新庫資料對象後再執行寫新庫邏輯。
- 更新資料： 更新邏輯是通過userId和id來更新的，此時新庫更新完資料後，因為新庫的ID不是老庫對應的ID，需要根據新庫中的unionId，截取字段拿到對應老庫的userId與id，再根據userId與id去更新老庫的資料。
3.5業務切流量
為了確定業務的平穩過渡，我們通過配置開關的方式切換業務讀新庫資料，并監控業務是否正常運作。同時，我們及時通知下遊關聯方切換到新資料庫，主要包括binlog或大資料、實時資料等數倉離線表的相關依賴。
3.6停寫老庫
在停止對老庫資料寫入之前，我們進行了充分的檢查，確定老庫業務沒有流量通路（包含binlog等離線表資料同步流量）。通過配置開關，我們控制停止對老庫資料的寫入，并監控業務是否正常運作。
3.7回收資源&&清理代碼
一旦業務監控正常，老庫任何流量通路後，我們可以下線老資料庫。這包括删除老資料源的配置、寫入資料的代碼以及相關開關的代碼。這一步驟的實施需要慎重，確定業務的平穩運作。
3.8項目收益
通過水準切分站内信消息表，我們取得了明顯的項目收益：
- 資料量下降顯著： 新站内信表資料從老表的6000萬+降至230萬+，單表資料減少95%。
- 慢SQL問題解決： 慢SQL從每月80+降為零，系統性能得到顯著提升。
- 接口查詢性能大幅提升： 切換到查詢新表後，95線耗時提升35%，99線耗時提升50%。
四、資料切分優缺點
4.1 優點：
- 降低耦合性： 垂直切分能夠根據業務子產品将不同表存儲在不同的資料庫，進而降低系統各子產品的耦合性。每個業務子產品使用獨立資料庫，有利于子產品間的獨立開發、維護和更新。
- 易于維護： 垂直切分使得對不同業務表的管理更為精細，可以根據業務的重要性和特點，對不同資料庫進行不同程度的監控和維護。這有助于及時發現和解決問題，提高系統的穩定性和可維護性。
- 提升性能： 水準切分能夠有效提升高并發場景下的性能。通過将資料分散到多個資料庫或表中，減小單一資料庫表的資料量，有助于提升IO、資料庫連接配接數和單機硬體資源的瓶頸，提高系統的并發處理能力。
- 靈活擴充： 垂直切分可以更靈活地擴充系統。由于不同的業務表存儲在不同的資料庫中，可以根據業務增長的需要，對不同資料庫進行獨立擴充，而不影響整體系統的運作。
4.2 缺點：
- 無法跨庫JOIN： 水準切分會導緻部分表無法直接進行JOIN操作，增加了在業務層通過接口聚合的開發複雜度。解決方式包括通過緩存、消息隊列等手段進行異步聚合。
- 分布式事務處理複雜： 在涉及多個資料庫的分布式事務場景中，資料一緻性的維護變得更為複雜。此時，需要采用分布式事務管理工具或通過應用層設計保障事務的一緻性。
- 資料多次擴充難度大： 随着業務的發展，對資料庫的再次擴充可能會帶來不小的難度。特别是在水準切分的情況下，需要謹慎規劃資料的再次擴充，以避免系統過度複雜。
- 查詢依賴分片鍵： 水準切分的查詢性能高度依賴分片鍵。如果查詢沒有包含分片鍵，可能會導緻查詢速度下降。是以，在進行資料切分時，需要考慮查詢模式，盡量使查詢與分片鍵比對。
4.3 适用場景：
在選擇是否進行資料切分時，需要根據具體業務場景和需求綜合考慮。适用場景包括但不限于：
- 資料量龐大，存在單庫讀寫、存儲性能瓶頸的情況。
- 系統需要靈活擴充，不同業務表之間關聯性不強。
- 高并發場景下，需要提升系統的并發處理能力。
- 對系統穩定性和可維護性有較高要求。
總體而言，資料切分是一項綜合性的政策，需要根據實際情況權衡其優缺點，選擇最适合的方案。
5、總結
綜合考慮資料庫優化大表治理的方案，我們得出以下結論：
- 謹慎使用資料切分： 資料切分是一項強有力的工具，但并非所有表都需要進行切分。在設計初期，應提前評估業務未來一到兩年的增長情況，考慮是否能通過其他手段提升資料庫性能，如硬體更新、網絡更新、讀寫分離、索引優化等。僅在資料量達到單表瓶頸時，再考慮分庫分表，以避免"過度設計"和"過早優化"。
- 合理選擇切分方式： 切分方式應根據實際情況選擇，水準切分适用于資料量龐大的場景，能一定程度提升IO、資料庫連接配接數和單機硬體資源瓶頸；垂直切分則能降低業務系統耦合性，對不同業務的資料進行分級管理、維護、監控、擴充等。
- 詳細考慮優缺點： 資料切分具有一定的優點，如提升系統穩定性和負載能力，但也伴随着一些缺點，例如部分表無法join、分布式事務處理複雜、資料擴充難度大等。在實施前需充分考慮這些因素，權衡利弊。
- 精心規劃項目實踐： 在實施資料切分項目時，需要細緻規劃切分方案、資料遷移、資料雙寫、業務切流量、停寫老庫等步驟。
- 實作明顯收益： 通過對站内信消息資料表的水準切分實踐，我們取得了明顯的項目收益，包括資料量大幅下降、慢SQL歸零、接口查詢性能提升等。這充分表明，在合适的場景下，資料切分是一項有效的優化手段。
綜上所述，資料庫大表治理是一項綜合性工程，需要慎重謹慎地選擇和實施各項方案，以最大程度提升資料庫性能，確定系統的穩定性和可維護性。
作者介紹
Chen·Small-K，後端研發專家
來源:微信公衆号:拍碼場
出處:https://mp.weixin.qq.com/s/Rg7Le324KKoY-P1nXCTn0g