轉：Mysql 分割槽 分表相關總結

引述

前段時間專案需要，一直在研究mysql sharding，看了一些這方面的資料，也親自實驗測試了一些資料。在此，做個概括的筆記，方便以後回顧知識，其實大多是借鑑網路上各位前輩的，然後抱著學習態度去實踐，積累屬於自己的東西。

拆分策略選擇

其實拆分很靈活，有的是垂直切分，將一個庫拆成兩個或多個，將有相關聯的表放在一個庫裡。有的是水平切分將資料量大的表按照一定邏輯進行拆分。個人感覺垂直切分的相對來說緩解了IO的瓶頸，而水平切分，目的是減輕了單個表或某些表讀寫的壓力。 我們專案根據個人需求，採用的水平切分，沒有去分庫。之後要看看需要採用何種的切分了。 瞭解到的有： 分表、分割槽、MERGE引擎分表。

MERGE引擎分表

簡介

先介紹merge表，此方法只適用於MyISAM。我資料庫的表都是採用InnoDB引擎的，所以首先就被pass了，但是還是在這裡簡單介紹下吧。 mysql 5.1 手冊裡的說的

An alternative to a MERGE table is a partitioned table, which stores partitions of a single table in separate files. Partitioning enables some operations to be performed more efficiently and is not limited to the MyISAM storage engine.

改變到MERGE引擎表，意味著成為一個被分割槽的表，這樣將單一的表各分割槽儲存在分離的檔案中。分割槽可以使一些操作效率更顯著，並且不受MyISAM儲存引擎的限制。（蹩腳的英語，各位看官多擔待吧。）

以上應該是使用merge表的主要原因吧。

建立使用

能夠建立MERGE表的要求，首先是一組資料結構完全相同的表，並且儲存引擎為MyISAM。

讓我們先建立一個

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; 

之後查詢

mysql> SELECT * FROM total;
+---+---------+ | a | message |
+---+---------+
| 1 | Testing | | 2 | table |
| 3 | t1      | | 1 | Testing |
| 2 | table   | | 3 | t2 |
+---+---------+ 

你建立了total表，只是相當於在t1,t2的表的基礎上建立的，需要注意的是在單個表中的主鍵或唯一索引，放在MERGE後的total表中就不能再當唯一索引用了，這點應該比較好理解但還是要說一下的。 同時你可以drop或者ALTER TABLE tbl_name UNION=(...)改變表的資料集，這樣可以讓其動態變化，剔除不需要的。

使用場景

如果你的資料記錄呈現一定時間規律，比如每天產生的一些需要記錄的日誌，可能你只需要最近一個月的或者最近幾個月的，這樣你可以每天或者一定時間建立一個資料表，當需要查詢一段時間的資料，你只要將這段時間的資料表建立 一張總計的MERGE表。這樣資料集可以控制在可控的範圍呢，不錯吧。so easy。

分表

分表其實想法上很簡單，顧名思義就是將現有的一張資料量大的表去拆分。如果資料庫的效能瓶頸在幾個關鍵表上，這時你可以將分表列入你考慮的範圍。

遇到的問題

我說說我在實驗分表時遇到的問題和相關解決方式

1.如何去分表 根據什麼策略把現有表中的資料分到多個表中，並且還有考慮到以後的擴充套件性上。 德問上的這篇討論可以借鑑下，

是建立一張索引表，使用者id與資料庫id對應，（這裡他將相同結構的表分在了不同的資料庫中進一步減少壓力，但同時對於資料的同步也需要透過其他手段來解決），其本質也是分表了同時分庫了。這麼做的好處是便於以後的擴充套件，但損耗一點效能，因為會多一次查詢嘛。

個人想法，這樣索引表可能會成為新的瓶頸，除非使用者不會一直增長哈。 我的做法屬於另一種，寫了個演算法透過計算某列值，按照一定規律將資料大致均分在每個分表中。至於擴充套件性，寫演算法時候考慮進去了以後增加分表數的問題了。 選擇哪種策略，是要看自己的表的業務特點了，方法沒有絕對的優缺，還是要根據自己的需求選取。

2.分表之後主鍵的維護 分表之前，主鍵就是自動遞增的bigint型。所以主鍵的格式已經提早被確定了，像什麼uuid之類的就被直接pass掉了。 還有想過自己寫一個主鍵生成程式，利用Java 的Atomic原子量特性，但是考慮還需要增加工作量並且高併發下，這裡很可能是個隱患。 還有就是透過應用層上管理主鍵，如redis中有原子性的遞增。 網上較有名的策略是, 大致意思是使用一張名Tickets64的MyISAM儲存引擎表，專門用來儲存主鍵，資料只有一行，用的話透過

REPLACE INTO Tickets64 (stub) VALUES ('a'); SELECT LAST_INSERT_ID(); 

來取。並且設定了兩個庫，相同的方法，只是每次增長的步長不同，防止一個宕掉，還可以穩定執行。 其他較好的文章 《資料庫分庫分表(sharding)系列(二) 全域性主鍵生成策略》,,《分庫分表(sharding)後主鍵全域性唯一性的解決方案》

2.動態選擇表名 表分好之後，問題又來了，資料庫層我們的專案使用的是Mybatis框架。SQL語句都寫在了xml檔案中，現在我需要動態的設定表名。 其實設定mybatis本身，就可以解決這個問題

statementType STATEMENT，PREPARED 或 CALLABLE 的一個。這會讓 MyBatis 分別使用 Statement，PreparedStatement 或 CallableStatement，預設值：PREPARED

只要把屬性statementType設定為STATEMENT，表名就可以以引數形式傳入。傳入引數時要以美元符${columnName}這樣傳入引數，至於Statement，PreparedStatement 的區別我想大家應該都能知道的。

另一種解決方式，是使用,它是開源的，可以實現資料水平切分功能，有興趣的朋友可以瞭解下。

分割槽表

從mysql5.1之後，提供了一種partition引擎的表，看這句

In effect, different portions of a table are stored as separate tables in different locations. 實際上，一個表的各個部分可以以單獨的個體表儲存在不同的位置（略微蹩腳）

在我的理解，如果把一張表分割槽之後，不同分割槽放在不同磁碟位置上，對整體的讀取是否更有益？

分割槽表優缺點

這裡主要是看的mysql手冊，我也就起到了個翻譯的作用。

Partitioning makes it possible to store more data in one table than can be held on a single disk or file system partition. 相比一張表，只能存放在一塊硬碟或者檔案系統分割槽內。分割槽方式讓儲存更多資料成為了可能。

Data that loses its usefulness can often be easily removed from a partitioned table by dropping the partition (or partitions) containing only that data. Conversely, the process of adding new data can in some cases be greatly facilitated by adding one or more new partitions for storing specifically that data. 失效的資料透過dropping掉僅僅包含此資料的分割槽方式，更容易的被移除。反之，透過新增新的分割槽來儲存一些新的資料，這種方式更加容易。

Some queries can be greatly optimized in virtue of the fact that data satisfying a given WHERE clause can be stored only on one or more partitions, which automatically excludes any remaining partitions from the search. Because partitions can be altered after a partitioned table has been created, you can reorganize your data to enhance frequent queries that may not have been often used when the partitioning scheme was first set up. This ability to exclude non-matching partitions (and thus any rows they contain) is often referred to as partition pruning, and was implemented in MySQL 5.1.6. 這句翻譯起來很吃力，我就說下大致意思吧，當你以某列分割槽之後，查詢語句where中如果可以指定特有分割槽或者一個範圍的話，查詢會得到最佳化。其實也好理解，因為你在where中指定分割槽，查詢就會只去檢索你指定的那塊分割槽，其他的資料不會去檢索。後部分說的是可以在建立好的分割槽上修改分割槽，使其更合理。

Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. 那些聚集函式，比如SUM(),COUNT() 容易被並行處理。（聽起來很酷哦）

這兩篇文章寫的比較不錯，《MySQL分割槽表的優缺點》，。 在選擇mysql 分割槽方案時，還有一個需要考慮的，在mysql的bug中有一個關於mysql分割槽表查詢快取的bug: ，因為這個問題，mysql已經將分割槽表的查詢快取disable了，無論你是否開啟查詢快取，都不會啟用查詢快取。如果你在意這點，請慎重選擇方案。

真實案例

網上一些好的sharding例項，附上鍊接，與君共享 。《又拍網架構中的資料庫分庫設計》。 。 。 有些需要翻下牆才能看，至於怎麼翻牆相信各路大神都有自己的方法哈。

以上是關於，mysql三個拆分方案的總結，資料方面都是自己查詢的所以不免有些會不準確，如有發現請務必告知，希望與各位共成長~~~。

note:後續還會考慮寫個如何去在資料庫層實際操作，建立分割槽分表以及資料匯入測試相關的心得



https://my.oschina.net/OpenSourceBO/blog/353464
https://my.oschina.net/u/914897/blog/492421