MySQL分庫分表

知識 11-28

什麼是分庫分表

分庫分表指的是將原有的單機單庫的單表橫向拆分到多機、多庫的多表

為什麼要分庫分表

一般情況下，分庫分表主要是為了防止:

單表數據量太大，行數過多，影響讀寫速度
單表數據量太大，磁碟佔用過多，難以存儲、備份、還原

什麼時候需要分庫分表

過早優化是萬惡之源。分庫分表成本極高，不到萬不得已，最好不要分庫分表。

分庫分表之前，我們可以先試試以下幾個選項。如果實在必要，再進行分庫分表。

分庫分表前的幾個選項

1. 什麼都不做

很多時候，分庫分表都是世上無事，庸人自擾。

一千萬數據量的表，對我們來說可能是個大表，對MySQL來說，可能毫無壓力。MySQL本身對錶容量沒有做限制，有的用戶甚至用MySQL跑著50億行以上的大表。

一般情況下，MySQL使用InnoDB存儲引擎，InnoDB默認的索引頁大小是16KB。InnoDB的索引使用的數據結構是B+Tree，一個索引節點存儲一個索引值加一個子節點地址。以INT類型的索引為例，一個索引節點佔用4+4=8個位元組，一個索引頁可以存放節點數16KB/8B=2K=2048，構成了一棵2048叉樹。

如果在磁碟上查找索引，1000萬的數據量需要定址log(10**7, 2048)=2.11次，也就是2次IO。100億的數據量需要定址log(10**10, 2048)=3.02次，也就是3次IO。

100億的數據量大約需要磁碟空間10**10*100B=1TB，索引佔用大約10**10*8B=80GB。

所以，只要磁碟、內存和膽子都夠大，用MySQL存儲100億的數據完全沒問題，也完全可以做到毫秒級的讀寫。

2. 優化業務

在對資料庫動手動腳前，先看看業務系統里是不是有不必要的或者可以整合的讀寫，是不是儲存了太多毫無價值的數據

3. 資料庫前端加緩存

適當添加緩存，可以極大地減少資料庫的讀取操作

4. 資料庫加索引

OLTP操作盡量避免全表掃面

5. 資料庫垂直切分

按業務分庫，按冷熱拆表

6. 表分區

將一個表在物理層拆分為多個表，對業務層完全透明。不同的表分區可以指定不同的物理磁碟，加大單表容量，提高並發讀寫速度，方便進行大表的備份與還原。

7. 換存儲引擎

MySQL默認的存儲引擎是InnoDB，在不開啟壓縮的情況下，空間浪費嚴重，一般1000萬行無索引的表佔用空間可達1GB。開啟壓縮後，大概可以節省一半的空間。換用ARCHIVE歸檔引擎，可以大幅提高壓縮率，但是不支持索引，無法進行OLTP查表。換用TokuDB引擎進行折中，可以在支持索引的情況下，大幅壓縮磁碟空間佔用，大約可以節省80%左右的存儲空間。

8. 讀寫分離

搭建MySQL集群，分離讀寫，主機寫，從機讀，主機寫入同步從機。多主機可提高可用性，多從機可提高並發。

走完以上八步，一般情況就不需要分庫分表了。

如何分庫分表

分庫分表主要有以下三種方式:

1. 手工分庫分表

手工建立多庫多表，按照特定的分庫規則，讀取或寫入相應的表。一般用分表鍵對分表數量取余確定分表名。需要大量修改業務層代碼，與業務層耦合嚴重。性能相對較高，但不易維護與擴展。

2. 中間件分庫分表

將手工分庫分表的邏輯抽象為一個中間件，對Java來說是自定義javax.sql.DataSource的實現，將分庫分表邏輯封裝在JDBC後面。對業務層相對透明，但是業界沒有太靠譜的免費解決方案。

3. 代理分庫分表

代理MySQL連接，業務層不連接真實的MySQL伺服器，連接的是MySQL伺服器的代理。MySQL代理伺服器根據配置好的分庫分表規則代理SQL請求，修改並轉發SQL到真實的MySQL伺服器，最後對接收到的請求結果進行歸併處理。對業務層完全透明，但是多開了一個服務，增加了維護成本。

結論