MySQL分庫分表原理
- 前言
-
- 資料庫為什麼要分庫分表
- 影響資料庫性能的三個要素
-
- 資料量
- 磁盤
- 資料庫連接配接
- 1、分庫分表的原理和實作
-
- 1.1 什麼是分區、分表、分庫
-
- 分區
- 分表
- 分庫
- 1.2 什麼時候考慮使用分區？
-
- 分區解決的問題
- 分區的實作方式（簡單），例如：
- 1.3 分庫分表概述
- 1.4 适用場景
- 1.5 業務分庫（又叫mysql垂直拆分）
- 1.6 業務分表（又叫水準拆分）
-
- 1.6.1 業務分表帶來的問題：
- 1.6.2 路由問題
- 1.6.2 資料庫操作問題
- 1.6.3 資料庫分庫分表的實作方法
- 2 讀寫分離的原理和實作
-
- 2.1、什麼是讀寫分離
- 2.2、為什麼要讀寫分離呢？
- 2.3、什麼時候要讀寫分離？
- 2.4 主從複制、讀寫分離的基本設計
- 3 分庫分表、讀寫分離總結：
-
- 1.分區
- 2.分表
- 3.分庫
- 4.讀寫分離方案
前言
資料庫為什麼要分庫分表
mysql推薦的是單表百萬級存儲，當單表的資料量達到1000W或100G以後，由于查詢次元較多，即使添加從庫、優化索引，做很多操作時性能仍下降嚴重。
例如淘寶網，海量的資料存儲和通路成為系統設計的瓶頸問題，日益增長的業務資料，對資料庫造成了恒大的負載。同時對于系統的穩定性和擴充性提出了很高的要求。
随着時間和業務的發展，資料庫中的表會越來越多，表中的資料量也會越來越大，單台伺服器的資源（CPU、磁盤IO、記憶體、網絡IO、事務數、連接配接數）總是有限的，最終資料庫所能承載的資料量、資料處理能力都将遭遇瓶頸。
分表、分庫和讀寫分離可以有效地減小單台資料庫的壓力。
影響資料庫性能的三個要素
資料量
MySQL單庫資料量在5000萬以内性能比較好,超過門檻值後性能會随着資料量的增大而變弱。
MySQL單表的資料量是500w-1000w之間性能比較好,超過1000w性能也會下降。
解決途徑：分庫分表–水準拆分
磁盤
因為單個服務的磁盤空間是有限制的,如果并發壓力下,所有的請求都通路同一個節點,肯定會對磁盤IO造成非常大的影響。
解決途徑：讀寫分離（主從複制）
資料庫連接配接
資料庫連接配接是非常稀少的資源,如果一個庫裡既有使用者、商品、訂單相關的資料,當海量使用者同時操作時,資料庫連接配接就很可能成為瓶頸。
解決途徑：分庫分表–垂直拆分
1、分庫分表的原理和實作
1.1 什麼是分區、分表、分庫
分區
就是把一張表的資料分成N個區塊，在邏輯上看最終隻是一張表，但底層是由N個實體區塊組成的，分區實作比較簡單，資料庫mysql、oracle等很容易就可支援。
分表
就是把一張表按一定的規則分解成N個具有獨立存儲空間的實體表。系統讀寫時需要根據定義好的規則得到對應的字表明，然後操作它。
分庫
一旦分表，一個庫中的表會越來越多
1.2 什麼時候考慮使用分區？
一張表的查詢速度已經慢到影響使用的時候。
sql經過優化
資料量大
表中的資料是分段的
對資料的操作往往隻涉及一部分資料，而不是所有的資料
分區解決的問題
主要可以提升查詢效率
分區的實作方式（簡單），例如：
mysql5 開始支援分區功能
CREATE TABLE sales (
id INT AUTO_INCREMENT,
amount DOUBLE NOT NULL,
order_day DATETIME NOT NULL,
PRIMARY KEY(id, order_day)
) ENGINE=Innodb
PARTITION BY RANGE(YEAR(order_day)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE);
1.3 分庫分表概述
讀寫分離分散資料庫讀寫操作壓力，分庫分表分散存儲壓力，以及資料庫連接配接壓力
1.4 适用場景
類似讀寫分離，分庫分表也是确定沒有其他優化空間之後才采取的優化方案。那如果業務真的發展很快豈不是很快要進行分庫分表了？
那為何不一開始就設計好呢？
按照架構設計的“三原則”（簡單原則，合适原則，演化原則），簡單分析一下：
首先，這裡的“如果”事實上發生的機率比較低，做10個業務有一個業務能活下去就很不錯了，更何況快速發展，和中彩票的機率差不多。
如果我們每個業務上來就按照淘寶、微信的規模去做架構設計，不但會累死自己，還會害死業務。
其次，如果業務真的發展很快，後面進行分庫分表也不遲。因為業務發展好，相應的資源投入就會加大，可以投入更多的人和更多的錢，那業務分庫帶來的代碼和業務複雜問題就可以通過加人來解決，成本問題也可以通過增加資金來解決。
什麼情況下要開始準備分庫分表呢？
例如，你的交易業務表以及有1000W的資料了，而且資料的增長速度，按照每個月50W條的資料增長，那麼就要和DBA商議準備做資料遷移和分庫分表的打算了。以為一年後将會有1600W條資料,是以必須要分庫分表了。
1.5 業務分庫（又叫mysql垂直拆分）
1.6 業務分表（又叫水準拆分）
1.6.1 業務分表帶來的問題：
垂直分表–>增加表操作的次數、
水準分表–>路由問題
1.6.2 路由問題
1.6.2 資料庫操作問題
1.6.3 資料庫分庫分表的實作方法
類似讀寫分離，具體實作也是“程式代碼封裝”和“中間件封裝”，但具體實作複雜一些，因為還有要判斷SQL中具體操作的表，具體操作(例如count、order by、group by等)，根據具體操作做不同的處理。
2 讀寫分離的原理和實作
2.1、什麼是讀寫分離
讀寫分離，基本的原理是讓主資料庫處理事務性增、改、删操作（INSERT、UPDATE、DELETE），而從資料庫處理SELECT查詢操作。資料庫複制被用來把事務性操作導緻的變更同步到叢集中的從資料庫。
2.2、為什麼要讀寫分離呢？
因為資料庫的“寫”（寫10000條資料到oracle可能要3分鐘）操作是比較耗時的。
但是資料庫的“讀”（從oracle讀10000條資料可能隻要5秒鐘）。
是以讀寫分離，解決的是，資料庫的寫入，影響了查詢的效率。
2.3、什麼時候要讀寫分離？
資料庫不一定要讀寫分離，如果程式使用資料庫較多時，而更新少，查詢多的情況下會考慮使用，利用資料庫 主從同步 。可以減少資料庫壓力，提高性能。當然，資料庫也有其它優化方案。memcache 或是 表折分，或是搜尋引擎。都是解決方法。
2.4 主從複制、讀寫分離的基本設計
在實際的生産環境中，對資料庫的讀和寫都在同一個資料庫伺服器中，是不能滿足實際需求的。無論是在安全性、高可用性還是高并發等各個方面都是完全不能滿足實際需求的。
是以，通過主從複制的方式來同步資料，再通過讀寫分離來提升資料庫的并發負載能力。
一台主、多台從，主提供寫操作，從提供讀操作。
讀寫分離的實作：
我們隻需要實作讀寫分離，主從複制資料一般由資料庫級來實作同步，當然也可以自己去實作同步，隻是需要考慮的點比較多。
3 分庫分表、讀寫分離總結：
1.分區
對業務透明，分區隻不過把存放資料的檔案分成了許多小塊，根據一定的規則把資料檔案(MYD)和索引檔案（MYI）進行了分割，分區後的表呢，還是一張表。
2.分表
當資料量大到一定程度的時候，都會導緻處理性能的不足，這個時候就沒有辦法了，隻能進行分表處理。也就是把資料庫當中資料根據按照分庫原則分到多個資料表當中，這樣，就可以把大表變成多個小表，不同的分表中資料不重複，進而提高處理效率。
3.分庫
分表和分區都是基于同一個資料庫裡的資料分離技巧，對資料庫性能有一定提升，但是随着業務資料量的增加，原來所有的資料都是在一個資料庫上的，網絡IO及檔案IO都集中在一個資料庫上的，是以CPU、記憶體、磁盤IO、網絡IO都可能會成為系統瓶頸。
當業務系統的資料容量接近或超過單台伺服器的容量、QPS/TPS接近或超過單個資料庫執行個體的處理極限等此時，往往是采用垂直和水準結合的資料拆分方法，把資料服務和資料存儲分布到多台資料庫伺服器上。
4.讀寫分離方案
當資料庫讀遠大于寫，查詢多的情況，就可以考慮主資料負責寫操作，從資料庫負責讀操作，一主多重，進而把資料讀寫分離，最後還可以結合redis等緩存來配合分擔資料的讀操作，大大的降低後端資料庫的壓力。