mysql數據庫分庫分表講解

本篇內容介紹了“mysql數據庫分庫分表講解”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

今天我來介紹常見的分散存儲的方法“分庫分表”，其中包括“分庫”和“分表”兩大類。

業務分庫

業務分庫指的是按照業務模塊將數據分散到不同的數據庫服務器。例如，一個簡單的電商網站，包括用戶、商品、訂單三個業務模塊，我們可以將用戶數據、商品數據、訂單數據分開放到三臺不同的數據庫服務器上，而不是將所有數據都放在一臺數據庫服務器上。

雖然業務分庫能夠分散存儲和訪問壓力，但同時也帶來了新的問題，接下來我進行詳細分析。

1.join 操作問題

業務分庫后，原本在同一個數據庫中的表分散到不同數據庫中，導致無法使用 SQL 的 join 查詢。

例如：“查詢購買了化妝品的用戶中女性用戶的列表”這個功能，雖然訂單數據中有用戶的 ID 信息，但是用戶的性別數據在用戶數據庫中，如果在同一個庫中，簡單的 join 查詢就能完成；但現在數據分散在兩個不同的數據庫中，無法做 join 查詢，只能采取先從訂單數據庫中查詢購買了化妝品的用戶 ID 列表，然后再到用戶數據庫中查詢這批用戶 ID 中的女性用戶列表，這樣實現就比簡單的 join 查詢要復雜一些。

2. 事務問題

原本在同一個數據庫中不同的表可以在同一個事務中修改，業務分庫后，表分散到不同的數據庫中，無法通過事務統一修改。雖然數據庫廠商提供了一些分布式事務的解決方案（例如，MySQL 的 XA），但性能實在太低，與高性能存儲的目標是相違背的。

例如，用戶下訂單的時候需要扣商品庫存，如果訂單數據和商品數據在同一個數據庫中，我們可以使用事務來保證扣減商品庫存和生成訂單的操作要么都成功要么都失敗，但分庫后就無法使用數據庫事務了，需要業務程序自己來模擬實現事務的功能。例如，先扣商品庫存，扣成功后生成訂單，如果因為訂單數據庫異常導致生成訂單失敗，業務程序又需要將商品庫存加上；而如果因為業務程序自己異常導致生成訂單失敗，則商品庫存就無法恢復了，需要人工通過日志等方式來手工修復庫存異常。

3. 成本問題

業務分庫同時也帶來了成本的代價，本來 1 臺服務器搞定的事情，現在要 3 臺，如果考慮備份，那就是 2 臺變成了 6 臺。

基于上述原因，對于小公司初創業務，并不建議一開始就這樣拆分，主要有幾個原因：

• 初創業務存在很大的不確定性，業務不一定能發展起來，業務開始的時候并沒有真正的存儲和訪問壓力，業務分庫并不能為業務帶來價值。

• 業務分庫后，表之間的 join 查詢、數據庫事務無法簡單實現了。

• 業務分庫后，因為不同的數據要讀寫不同的數據庫，代碼中需要增加根據數據類型映射到不同數據庫的邏輯，增加了工作量。而業務初創期間最重要的是快速實現、快速驗證，業務分庫會拖慢業務節奏。

有的架構師可能會想：如果業務真的發展很快，豈不是很快就又要進行業務分庫了？那為何不一開始就設計好呢？

其實這個問題很好回答，按照我前面提到的“架構設計三原則”，簡單分析一下。

首先，這里的“如果”事實上發生的概率比較低，做 10 個業務有 1 個業務能活下去就很不錯了，更何況快速發展，和中彩票的概率差不多。如果我們每個業務上來就按照淘寶、微信的規模去做架構設計，不但會累死自己，還會害死業務。

其次，如果業務真的發展很快，后面進行業務分庫也不遲。因為業務發展好，相應的資源投入就會加大，可以投入更多的人和更多的錢，那業務分庫帶來的代碼和業務復雜的問題就可以通過增加人來解決，成本問題也可以通過增加資金來解決。

第三，單臺數據庫服務器的性能其實也沒有想象的那么弱，一般來說，單臺數據庫服務器能夠支撐 10 萬用戶量量級的業務，初創業務從 0 發展到 10 萬級用戶，并不是想象得那么快。

而對于業界成熟的大公司來說，由于已經有了業務分庫的成熟解決方案，并且即使是嘗試性的新業務，用戶規模也是海量的，這與前面提到的初創業務的小公司有本質區別，因此最好在業務開始設計時就考慮業務分庫。例如，在淘寶上做一個新的業務，由于已經有成熟的數據庫解決方案，用戶量也很大，需要在一開始就設計業務分庫甚至接下來介紹的分表方案。

分表

將不同業務數據分散存儲到不同的數據庫服務器，能夠支撐百萬甚至千萬用戶規模的業務，但如果業務繼續發展，同一業務的單表數據也會達到單臺數據庫服務器的處理瓶頸。例如，淘寶的幾億用戶數據，如果全部存放在一臺數據庫服務器的一張表中，肯定是無法滿足性能要求的，此時就需要對單表數據進行拆分。

單表數據拆分有兩種方式：垂直分表和水平分表。示意圖如下：

??

為了形象地理解垂直拆分和水平拆分的區別，可以想象你手里拿著一把刀，面對一個蛋糕切一刀：

• 從上往下切就是垂直切分，因為刀的運行軌跡與蛋糕是垂直的，這樣可以把蛋糕切成高度相等（面積可以相等也可以不相等）的兩部分，對應到表的切分就是表記錄數相同但包含不同的列。例如，示意圖中的垂直切分，會把表切分為兩個表，一個表包含 ID、name、age、sex 列，另外一個表包含 ID、nickname、description 列。

• 從左往右切就是水平切分，因為刀的運行軌跡與蛋糕是平行的，這樣可以把蛋糕切成面積相等（高度可以相等也可以不相等）的兩部分，對應到表的切分就是表的列相同但包含不同的行數據。例如，示意圖中的水平切分，會把表分為兩個表，兩個表都包含 ID、name、age、sex、nickname、description 列，但是一個表包含的是 ID 從 1 到 999999 的行數據，另一個表包含的是 ID 從 1000000 到 9999999 的行數據。

上面這個示例比較簡單，只考慮了一次切分的情況，實際架構設計過程中并不局限切分的次數，可以切兩次，也可以切很多次，就像切蛋糕一樣，可以切很多刀。

單表進行切分后，是否要將切分后的多個表分散在不同的數據庫服務器中，可以根據實際的切分效果來確定，并不強制要求單表切分為多表后一定要分散到不同數據庫中。原因在于單表切分為多表后，新的表即使在同一個數據庫服務器中，也可能帶來可觀的性能提升，如果性能能夠滿足業務要求，是可以不拆分到多臺數據庫服務器的，畢竟我們在上面業務分庫的內容看到業務分庫也會引入很多復雜性的問題；如果單表拆分為多表后，單臺服務器依然無法滿足性能要求，那就不得不再次進行業務分庫的設計了。

分表能夠有效地分散存儲壓力和帶來性能提升，但和分庫一樣，也會引入各種復雜性。

1. 垂直分表

垂直分表適合將表中某些不常用且占了大量空間的列拆分出去。例如，前面示意圖中的 nickname 和 description 字段，假設我們是一個婚戀網站，用戶在篩選其他用戶的時候，主要是用 age 和 sex 兩個字段進行查詢，而 nickname 和 description 兩個字段主要用于展示，一般不會在業務查詢中用到。description 本身又比較長，因此我們可以將這兩個字段獨立到另外一張表中，這樣在查詢 age 和 sex 時，就能帶來一定的性能提升。

垂直分表引入的復雜性主要體現在表操作的數量要增加。例如，原來只要一次查詢就可以獲取 name、age、sex、nickname、description，現在需要兩次查詢，一次查詢獲取 name、age、sex，另外一次查詢獲取 nickname、description。

不過相比接下來要講的水平分表，這個復雜性就是小巫見大巫了。

2. 水平分表

水平分表適合表行數特別大的表，有的公司要求單表行數超過 5000 萬就必須進行分表，這個數字可以作為參考，但并不是絕對標準，關鍵還是要看表的訪問性能。對于一些比較復雜的表，可能超過 1000 萬就要分表了；而對于一些簡單的表，即使存儲數據超過 1 億行，也可以不分表。但不管怎樣，當看到表的數據量達到千萬級別時，作為架構師就要警覺起來，因為這很可能是架構的性能瓶頸或者隱患。

水平分表相比垂直分表，會引入更多的復雜性，主要表現在下面幾個方面：

• 路由

水平分表后，某條數據具體屬于哪個切分后的子表，需要增加路由算法進行計算，這個算法會引入一定的復雜性。

常見的路由算法有：

范圍路由：選取有序的數據列（例如，整形、時間戳等）作為路由的條件，不同分段分散到不同的數據庫表中。以最常見的用戶 ID 為例，路由算法可以按照 1000000 的范圍大小進行分段，1 ~ 999999 放到數據庫 1 的表中，1000000 ~ 1999999 放到數據庫 2 的表中，以此類推。

范圍路由設計的復雜點主要體現在分段大小的選取上，分段太小會導致切分后子表數量過多，增加維護復雜度；分段太大可能會導致單表依然存在性能問題，一般建議分段大小在 100 萬至 2000 萬之間，具體需要根據業務選取合適的分段大小。

范圍路由的優點是可以隨著數據的增加平滑地擴充新的表。例如，現在的用戶是 100 萬，如果增加到 1000 萬，只需要增加新的表就可以了，原有的數據不需要動。

范圍路由的一個比較隱含的缺點是分布不均勻，假如按照 1000 萬來進行分表，有可能某個分段實際存儲的數據量只有 1000 條，而另外一個分段實際存儲的數據量有 900 萬條。

Hash 路由：選取某個列（或者某幾個列組合也可以）的值進行 Hash 運算，然后根據 Hash 結果分散到不同的數據庫表中。同樣以用戶 ID 為例，假如我們一開始就規劃了 10 個數據庫表，路由算法可以簡單地用 user_id % 10 的值來表示數據所屬的數據庫表編號，ID 為 985 的用戶放到編號為 5 的子表中，ID 為 10086 的用戶放到編號為 6 的字表中。

Hash 路由設計的復雜點主要體現在初始表數量的選取上，表數量太多維護比較麻煩，表數量太少又可能導致單表性能存在問題。而用了 Hash 路由后，增加字表數量是非常麻煩的，所有數據都要重分布。

Hash 路由的優缺點和范圍路由基本相反，Hash 路由的優點是表分布比較均勻，缺點是擴充新的表很麻煩，所有數據都要重分布。

配置路由：配置路由就是路由表，用一張獨立的表來記錄路由信息。同樣以用戶 ID 為例，我們新增一張 user_router 表，這個表包含 user_id 和 table_id 兩列，根據 user_id 就可以查詢對應的 table_id。

配置路由設計簡單，使用起來非常靈活，尤其是在擴充表的時候，只需要遷移指定的數據，然后修改路由表就可以了。

配置路由的缺點就是必須多查詢一次，會影響整體性能；而且路由表本身如果太大（例如，幾億條數據），性能同樣可能成為瓶頸，如果我們再次將路由表分庫分表，則又面臨一個死循環式的路由算法選擇問題。

• join 操作

水平分表后，數據分散在多個表中，如果需要與其他表進行 join 查詢，需要在業務代碼或者數據庫中間件中進行多次 join 查詢，然后將結果合并。

• count() 操作

水平分表后，雖然物理上數據分散到多個表中，但某些業務邏輯上還是會將這些表當作一個表來處理。例如，獲取記錄總數用于分頁或者展示，水平分表前用一個 count() 就能完成的操作，在分表后就沒那么簡單了。常見的處理方式有下面兩種：

count() 相加：具體做法是在業務代碼或者數據庫中間件中對每個表進行 count() 操作，然后將結果相加。這種方式實現簡單，缺點就是性能比較低。例如，水平分表后切分為 20 張表，則要進行 20 次 count(*) 操作，如果串行的話，可能需要幾秒鐘才能得到結果。

記錄數表：具體做法是新建一張表，假如表名為“記錄數表”，包含 table_name、row_count 兩個字段，每次插入或者刪除子表數據成功后，都更新“記錄數表”。

這種方式獲取表記錄數的性能要大大優于 count() 相加的方式，因為只需要一次簡單查詢就可以獲取數據。缺點是復雜度增加不少，對子表的操作要同步操作“記錄數表”，如果有一個業務邏輯遺漏了，數據就會不一致；且針對“記錄數表”的操作和針對子表的操作無法放在同一事務中進行處理，異常的情況下會出現操作子表成功了而操作記錄數表失敗，同樣會導致數據不一致。

此外，記錄數表的方式也增加了數據庫的寫壓力，因為每次針對子表的 insert 和 delete 操作都要 update 記錄數表，所以對于一些不要求記錄數實時保持精確的業務，也可以通過后臺定時更新記錄數表。定時更新實際上就是“count() 相加”和“記錄數表”的結合，即定時通過 count() 相加計算表的記錄數，然后更新記錄數表中的數據。

• order by 操作

水平分表后，數據分散到多個子表中，排序操作無法在數據庫中完成，只能由業務代碼或者數據庫中間件分別查詢每個子表中的數據，然后匯總進行排序。

實現方法

和數據庫讀寫分離類似，分庫分表具體的實現方式也是“程序代碼封裝”和“中間件封裝”，但實現會更復雜。讀寫分離實現時只要識別 SQL 操作是讀操作還是寫操作，通過簡單的判斷 SELECT、UPDATE、INSERT、DELETE 幾個關鍵字就可以做到，而分庫分表的實現除了要判斷操作類型外，還要判斷 SQL 中具體需要操作的表、操作函數（例如 count 函數)、order by、group by 操作等，然后再根據不同的操作進行不同的處理。例如 order by 操作，需要先從多個庫查詢到各個庫的數據，然后再重新 order by 才能得到最終的結果。

“mysql數據庫分庫分表講解”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！