轉：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.如何去分表 根據什么策略把現有表中的數據分到多個表中，并且還有考慮到以后的擴展性上。 德問上的這篇討論可以借鑒下，

• 《mysql 分表，拆分策略都有哪些？各在什么情況下應用？》
• 《又拍網拆分策略》

是建立一張索引表，用戶id與數據庫id對應，（這里他將相同結構的表分在了不同的數據庫中進一步減少壓力，但同時對于數據的同步也需要通過其他手段來解決），其本質也是分表了同時分庫了。這么做的好處是便于以后的擴展，但損耗一點性能，因為會多一次查詢嘛。

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

2.分表之后主鍵的維護 分表之前，主鍵就是自動遞增的bigint型。所以主鍵的格式已經提早被確定了，像什么uuid之類的就被直接pass掉了。 還有想過自己寫一個主鍵生成程序，利用Java 的Atomic原子量特性，但是考慮還需要增加工作量并且高并發下，這里很可能是個隱患。 還有就是通過應用層上管理主鍵，如redis中有原子性的遞增。 網上較有名的策略是《Ticket Servers: Distributed Unique Primary Keys on the Cheap》, 大致意思是使用一張名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 的區別我想大家應該都能知道的。

另一種解決方式，是使用《shardbatis插件》,它是開源的，可以實現數據水平切分功能，有興趣的朋友可以了解下。

分區表

從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 分區方案時，還有一個需要考慮的，在mysql的bug中有一個關于mysql分區表查詢緩存的bug: 《Partitioning + Query Cache》，因為這個問題，mysql已經將分區表的查詢緩存disable了，無論你是否開啟查詢緩存，都不會啟用查詢緩存。如果你在意這點，請慎重選擇方案。

真實案例

網上一些好的sharding實例，附上鏈接，與君共享 《Database Sharding at Netlog, with MySQL and PHP》。《又拍網架構中的數據庫分庫設計》。 《Amazon's Dynamo》。 《Ticket Servers: Distributed Unique Primary Keys on the Cheap》。 有些需要翻下墻才能看，至于怎么翻墻相信各路大神都有自己的方法哈。

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

note:后續還會考慮寫個如何去在數據庫層實際操作，建立分區分表以及數據導入測試相關的心得



https://my.oschina.net/OpenSourceBO/blog/353464
https://my.oschina.net/u/914897/blog/492421
http://haitian299.github.io/2016/05/26/mysql-partitioning/
http://www.itmmd.com/201411/208.html