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

引述

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

成都創(chuàng)新互聯(lián)公司專(zhuān)注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、佳木斯網(wǎng)絡(luò)推廣、微信小程序定制開(kāi)發(fā)、佳木斯網(wǎng)絡(luò)營(yíng)銷(xiāo)、佳木斯企業(yè)策劃、佳木斯品牌公關(guān)、搜索引擎seo、人物專(zhuān)訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；成都創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供佳木斯建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：bm7419.com

拆分策略選擇

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

MERGE引擎分表

簡(jiǎn)介

先介紹merge表，此方法只適用于MyISAM。我數(shù)據(jù)庫(kù)的表都是采用InnoDB引擎的，所以首先就被pass了，但是還是在這里簡(jiǎn)單介紹下吧。 mysql 5.1 手冊(cè)里的說(shuō)的

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引擎表，意味著成為一個(gè)被分區(qū)的表，這樣將單一的表各分區(qū)存儲(chǔ)在分離的文件中。分區(qū)可以使一些操作效率更顯著，并且不受MyISAM存儲(chǔ)引擎的限制。（蹩腳的英語(yǔ)，各位看官多擔(dān)待吧。）

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

創(chuàng)建使用

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

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

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)建的，需要注意的是在單個(gè)表中的主鍵或唯一索引，放在MERGE后的total表中就不能再當(dāng)唯一索引用了，這點(diǎn)應(yīng)該比較好理解但還是要說(shuō)一下的。 同時(shí)你可以drop或者ALTER TABLE tbl_name UNION=(...)改變表的數(shù)據(jù)集，這樣可以讓其動(dòng)態(tài)變化，剔除不需要的。

使用場(chǎng)景

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

分表

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

遇到的問(wèn)題

我說(shuō)說(shuō)我在實(shí)驗(yàn)分表時(shí)遇到的問(wèn)題和相關(guān)解決方式

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

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

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

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

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

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

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

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

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

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

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

分區(qū)表

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

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

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

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

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

Partitioning makes it possible to store more data in one table than can be held on a single disk or file system partition. 相比一張表，只能存放在一塊硬盤(pán)或者文件系統(tǒng)分區(qū)內(nèi)。分區(qū)方式讓存儲(chǔ)更多數(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ù)通過(guò)dropping掉僅僅包含此數(shù)據(jù)的分區(qū)方式，更容易的被移除。反之，通過(guò)添加新的分區(qū)來(lái)存儲(chǔ)一些新的數(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. 這句翻譯起來(lái)很吃力，我就說(shuō)下大致意思吧，當(dāng)你以某列分區(qū)之后，查詢語(yǔ)句where中如果可以指定特有分區(qū)或者一個(gè)范圍的話，查詢會(huì)得到優(yōu)化。其實(shí)也好理解，因?yàn)槟阍趙here中指定分區(qū)，查詢就會(huì)只去檢索你指定的那塊分區(qū)，其他的數(shù)據(jù)不會(huì)去檢索。后部分說(shuō)的是可以在創(chuàng)建好的分區(qū)上修改分區(qū)，使其更合理。

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

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

真實(shí)案例

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

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

note:后續(xù)還會(huì)考慮寫(xiě)個(gè)如何去在數(shù)據(jù)庫(kù)層實(shí)際操作，建立分區(qū)分表以及數(shù)據(jù)導(dǎo)入測(cè)試相關(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

分享名稱(chēng)：轉(zhuǎn)：Mysql分區(qū)分表相關(guān)總結(jié)
標(biāo)題路徑：http://bm7419.com/article14/igcpde.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、自適應(yīng)網(wǎng)站、網(wǎng)站建設(shè)、網(wǎng)站排名、靜態(tài)網(wǎng)站、小程序開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都app開(kāi)發(fā)公司