轉(zhuǎn)：Mysql 分區(qū) 分表相關(guān)總結(jié)

引述

前段時間項目需要，一直在研究mysql sharding，看了一些這方面的資料，也親自實驗測試了一些數(shù)據(jù)。在此，做個概括的筆記，方便以后回顧知識，其實大多是借鑒網(wǎng)絡(luò)上各位前輩的，然后抱著學(xué)習(xí)態(tài)度去實踐，積累屬于自己的東西。

拆分策略選擇

其實拆分很靈活，有的是垂直切分，將一個庫拆成兩個或多個，將有相關(guān)聯(lián)的表放在一個庫里。有的是水平切分將數(shù)據(jù)量大的表按照一定邏輯進(jìn)行拆分。個人感覺垂直切分的相對來說緩解了IO的瓶頸，而水平切分，目的是減輕了單個表或某些表讀寫的壓力。 我們項目根據(jù)個人需求，采用的水平切分，沒有去分庫。之后要看看需要采用何種的切分了。 了解到的有： 分表、分區(qū)、MERGE引擎分表。

MERGE引擎分表

簡介

先介紹merge表，此方法只適用于MyISAM。我數(shù)據(jù)庫的表都是采用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引擎表，意味著成為一個被分區(qū)的表，這樣將單一的表各分區(qū)存儲在分離的文件中。分區(qū)可以使一些操作效率更顯著，并且不受MyISAM存儲引擎的限制。（蹩腳的英語，各位看官多擔(dān)待吧。）

以上應(yīng)該是使用merge表的主要原因吧。

創(chuàng)建使用

能夠創(chuàng)建MERGE表的要求，首先是一組數(shù)據(jù)結(jié)構(gòu)完全相同的表，并且存儲引擎為MyISAM。

讓我們先創(chuàng)建一個

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 |
+---+---------+ 

你創(chuàng)建了total表，只是相當(dāng)于在t1,t2的表的基礎(chǔ)上創(chuàng)建的，需要注意的是在單個表中的主鍵或唯一索引，放在MERGE后的total表中就不能再當(dāng)唯一索引用了，這點應(yīng)該比較好理解但還是要說一下的。 同時你可以drop或者ALTER TABLE tbl_name UNION=(...)改變表的數(shù)據(jù)集，這樣可以讓其動態(tài)變化，剔除不需要的。

使用場景

如果你的數(shù)據(jù)記錄呈現(xiàn)一定時間規(guī)律，比如每天產(chǎn)生的一些需要記錄的日志，可能你只需要最近一個月的或者最近幾個月的，這樣你可以每天或者一定時間創(chuàng)建一個數(shù)據(jù)表，當(dāng)需要查詢一段時間的數(shù)據(jù)，你只要將這段時間的數(shù)據(jù)表創(chuàng)建 一張總計的MERGE表。這樣數(shù)據(jù)集可以控制在可控的范圍呢，不錯吧。so easy。

分表

分表其實想法上很簡單，顧名思義就是將現(xiàn)有的一張數(shù)據(jù)量大的表去拆分。如果數(shù)據(jù)庫的性能瓶頸在幾個關(guān)鍵表上，這時你可以將分表列入你考慮的范圍。

遇到的問題

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

1.如何去分表 根據(jù)什么策略把現(xiàn)有表中的數(shù)據(jù)分到多個表中，并且還有考慮到以后的擴(kuò)展性上。 德問上的這篇討論可以借鑒下，

• 《mysql 分表，拆分策略都有哪些？各在什么情況下應(yīng)用？》
• 《又拍網(wǎng)拆分策略》

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

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

2.分表之后主鍵的維護(hù) 分表之前，主鍵就是自動遞增的bigint型。所以主鍵的格式已經(jīng)提早被確定了，像什么uuid之類的就被直接pass掉了。 還有想過自己寫一個主鍵生成程序，利用Java 的Atomic原子量特性，但是考慮還需要增加工作量并且高并發(fā)下，這里很可能是個隱患。 還有就是通過應(yīng)用層上管理主鍵，如redis中有原子性的遞增。 網(wǎng)上較有名的策略是《Ticket Servers: Distributed Unique Primary Keys on the Cheap》, 大致意思是使用一張名Tickets64的MyISAM存儲引擎表，專門用來存儲主鍵，數(shù)據(jù)只有一行，用的話通過

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

來取。并且設(shè)置了兩個庫，相同的方法，只是每次增長的步長不同，防止一個宕掉，還可以穩(wěn)定運行。 其他較好的文章 《數(shù)據(jù)庫分庫分表(sharding)系列(二) 全局主鍵生成策略》,《關(guān)于主鍵管理》,《分庫分表(sharding)后主鍵全局唯一性的解決方案》

2.動態(tài)選擇表名 表分好之后，問題又來了，數(shù)據(jù)庫層我們的項目使用的是Mybatis框架。SQL語句都寫在了xml文件中，現(xiàn)在我需要動態(tài)的設(shè)置表名。 其實設(shè)置mybatis本身，就可以解決這個問題

statementType STATEMENT，PREPARED 或 CALLABLE 的一個。這會讓 MyBatis 分別使用 Statement，PreparedStatement 或 CallableStatement，默認(rèn)值：PREPARED

只要把屬性statementType設(shè)置為STATEMENT，表名就可以以參數(shù)形式傳入。傳入?yún)?shù)時要以美元符${columnName}這樣傳入?yún)?shù)，至于Statement，PreparedStatement 的區(qū)別我想大家應(yīng)該都能知道的。

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

分區(qū)表

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

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

在我的理解，如果把一張表分區(qū)之后，不同分區(qū)放在不同磁盤位置上，對整體的讀取是否更有益？

分區(qū)表優(yōu)缺點

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

Partitioning makes it possible to store more data in one table than can be held on a single disk or file system partition. 相比一張表，只能存放在一塊硬盤或者文件系統(tǒng)分區(qū)內(nèi)。分區(qū)方式讓存儲更多數(shù)據(jù)成為了可能。

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. 失效的數(shù)據(jù)通過dropping掉僅僅包含此數(shù)據(jù)的分區(qū)方式，更容易的被移除。反之，通過添加新的分區(qū)來存儲一些新的數(shù)據(jù)，這種方式更加容易。

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. 這句翻譯起來很吃力，我就說下大致意思吧，當(dāng)你以某列分區(qū)之后，查詢語句where中如果可以指定特有分區(qū)或者一個范圍的話，查詢會得到優(yōu)化。其實也好理解，因為你在where中指定分區(qū)，查詢就會只去檢索你指定的那塊分區(qū)，其他的數(shù)據(jù)不會去檢索。后部分說的是可以在創(chuàng)建好的分區(qū)上修改分區(qū)，使其更合理。

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

這兩篇文章寫的比較不錯，《MySQL分區(qū)表的優(yōu)缺點》，《mysql分區(qū)表對分區(qū)函數(shù)的限制》。 在選擇mysql 分區(qū)方案時，還有一個需要考慮的，在mysql的bug中有一個關(guān)于mysql分區(qū)表查詢緩存的bug: 《Partitioning + Query Cache》，因為這個問題，mysql已經(jīng)將分區(qū)表的查詢緩存disable了，無論你是否開啟查詢緩存，都不會啟用查詢緩存。如果你在意這點，請慎重選擇方案。

真實案例

網(wǎng)上一些好的sharding實例，附上鏈接，與君共享 《Database Sharding at Netlog, with MySQL and PHP》?！队峙木W(wǎng)架構(gòu)中的數(shù)據(jù)庫分庫設(shè)計》。 《Amazon's Dynamo》。 《Ticket Servers: Distributed Unique Primary Keys on the Cheap》。 有些需要翻下墻才能看，至于怎么翻墻相信各路大神都有自己的方法哈。

以上是關(guān)于，mysql三個拆分方案的總結(jié)，資料方面都是自己查找的所以不免有些會不準(zhǔn)確，如有發(fā)現(xiàn)請務(wù)必告知，希望與各位共成長~~~。

note:后續(xù)還會考慮寫個如何去在數(shù)據(jù)庫層實際操作，建立分區(qū)分表以及數(shù)據(jù)導(dǎo)入測試相關(guān)的心得



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