?

?筆者覺得，分庫分表確實(shí)好的。但是，動(dòng)不動(dòng)搞分庫分表，太麻煩了。分庫分表雖然是提高數(shù)據(jù)庫性能的常規(guī)辦法，但是太麻煩了。所以，嘗試研究mysql的分區(qū)到底如何。

?之前寫過一篇文章，http://www.cnblogs.com/wangtao_20/p/7115962.html 討論過訂單表的分庫分表，折騰起來工作量挺大的，需要多少技術(shù)去折騰。做過的人才知道有多麻煩

?

? ?要按照什么字段切分，切分?jǐn)?shù)據(jù)后，要遷移數(shù)據(jù);分庫分表后，會(huì)涉及到跨庫、跨表查詢，為了解決查詢問題，又得用其他方案來彌補(bǔ)(比如為了應(yīng)對(duì)查詢得做用戶訂單關(guān)系索引表)。工作量確實(shí)不小。

?

? 從網(wǎng)上也可以看到，大部分實(shí)施過的人(成功的)的經(jīng)驗(yàn)總結(jié)：水平分表，不是必須的，能不做，盡量不做。

?

像阿里這些系統(tǒng)，數(shù)據(jù)庫單表數(shù)量十多億，達(dá)到瓶頸了，不得不做分庫分表，擴(kuò)容也方便。沒有選擇。 那么，針對(duì)起步階段的業(yè)務(wù)，技術(shù)人員不夠，產(chǎn)品還處在試錯(cuò)階段。是不是可以考慮一下分區(qū)方案。 筆者幾年前，也犯了思維錯(cuò)誤，在小公司做系統(tǒng)，產(chǎn)品還在開發(fā)，有待推向市場驗(yàn)證。那個(gè)時(shí)候，筆者就去考慮什么評(píng)論表數(shù)據(jù)量大的情況下要怎么做，其實(shí)傷腦，又費(fèi)時(shí)間，業(yè)務(wù)沒有做起來，其實(shí)沒多少用處。 架構(gòu)是演變出來的，不是設(shè)計(jì)出來的。企圖一開始就設(shè)計(jì)大炮，結(jié)果只有蚊子。筆者做試驗(yàn)看看mysql的分區(qū)到底是什么個(gè)原理。研究發(fā)現(xiàn)，其實(shí)跟分表差不多，比如按hash打散數(shù)據(jù)、按值范圍分散數(shù)據(jù)。

? ?

?一、探討分區(qū)的原理

?

了解分區(qū)到底在做什么，存儲(chǔ)的數(shù)據(jù)文件有什么變化，這樣知道分區(qū)是怎么提高性能的。

?

實(shí)際上：每個(gè)分區(qū)都有自己獨(dú)立的數(shù)據(jù)、索引文件的存放目錄。本質(zhì)上，一個(gè)分區(qū)，實(shí)際上對(duì)應(yīng)的是一個(gè)磁盤文件。所以分區(qū)越多，文件數(shù)越多。

?

現(xiàn)在使用innodb存儲(chǔ)較多，mysql默認(rèn)的存儲(chǔ)引擎從mysiam變?yōu)榱薸nnodb了。

?

以innodb來討論：

innodb存儲(chǔ)引擎一張表，對(duì)應(yīng)兩個(gè)文件：表名.ibd、表名.frm。

如果分區(qū)后，一個(gè)分區(qū)就單獨(dú)一個(gè)ibd文件，如下圖：

將fs_punch_in_log表拆分成4個(gè)分區(qū)，上圖中看到，每個(gè)分區(qū)就變成一個(gè)單獨(dú)的數(shù)據(jù)文件了。mysql會(huì)使用"#p#p1"來命名數(shù)據(jù)文件，1是分區(qū)的編號(hào)。總共4個(gè)分區(qū)，最大值是4。

分表的原理，實(shí)際上類似，一個(gè)表對(duì)應(yīng)一個(gè)數(shù)據(jù)文件。分表后，數(shù)據(jù)分散到多個(gè)文件去了。性能就提高了。

?

分區(qū)后的查詢語句

?

語句還是按照原來的使用。但為了提高性能。還是盡量避免跨越多個(gè)分區(qū)匹配數(shù)據(jù)。

?

如下圖，由于表是按照id字段分區(qū)的。數(shù)據(jù)分散在多個(gè)分區(qū)。現(xiàn)在使用user_id作為條件去查詢。mysql不知道到底分配在哪個(gè)分區(qū)。所以要去全部分區(qū)掃描，如果每個(gè)分區(qū)的數(shù)據(jù)量大，這樣就耗時(shí)很長了。

?

?

?

?

?

分區(qū)思路和分區(qū)語句

?

?

id字段的值范圍來分區(qū)：在1-2千萬分到p0分區(qū)，4千萬到-6千萬p1分區(qū)。6千萬到8千萬p2分區(qū)。依此推算下去。這樣可以分成很多的分區(qū)了。

為了保持線性擴(kuò)容方便。那么只能使用range范圍來算了。

?

sql如下

CREATE TABLE `fs_punch_in_log` ( `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵自增' , `user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '簽到的用戶id' , `punch_in_time` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '打卡簽到時(shí)間戳' , PRIMARY KEY (`id`) )

partition BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (40000000),
PARTITION p2? VALUES LESS THAN (80000000),?
PARTITION p3? VALUES LESS THAN (120000000),
PARTITION p4? VALUES LESS THAN MAXVALUE );

　　以上語句經(jīng)過筆者測驗(yàn)，注意點(diǎn)：

• ? ? ? ?按照hash均勻分散。傳遞給分區(qū)的hash()函數(shù)的值，必須是一個(gè)整數(shù)(hash計(jì)算整數(shù)計(jì)算，實(shí)現(xiàn)均勻分布）。上面的id字段就是表的主鍵，滿足整數(shù)要求。
• ? ? ? ?partition BY RANGE 中的partition BY表示按什么方式分區(qū)。RANGE告訴mysql，我使用范圍分區(qū)。

?

?

?

情況：如果表結(jié)構(gòu)已經(jīng)定義好了，里面有數(shù)據(jù)了，怎么進(jìn)行分區(qū)呢？使用alter語句修改即可，經(jīng)過筆者測驗(yàn)了。

?

ALTER TABLE `fs_punch_in_log` PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (40000000), PARTITION p2 VALUES LESS THAN (80000000), PARTITION p3 VALUES LESS THAN (120000000), PARTITION p4 VALUES LESS THAN MAXVALUE)

　　

注：由于表里面已經(jīng)存在數(shù)據(jù)了，進(jìn)行重新分區(qū)，mysql會(huì)把數(shù)據(jù)按照分區(qū)規(guī)則重新移動(dòng)一次，生成新的文件。如果數(shù)據(jù)量比較大，耗時(shí)間比較長。

?

?

?

二、四種分區(qū)類型

?

mysql分區(qū)包括四種分區(qū)方式：hash分區(qū)、按range分區(qū)、按key分區(qū)、list分區(qū)。

四種有點(diǎn)多，實(shí)際上，為了好記，把類再縮小點(diǎn)，就兩大類方式進(jìn)行分區(qū)：一種是計(jì)算hash值、一種是按照范圍值。

其實(shí)分庫分表的時(shí)候，也會(huì)用到兩大類，hash運(yùn)算分、按值范圍分。

?

?1、HASH分區(qū)

?

有常規(guī)hash和線性hash兩種方式。

?

?

• 常規(guī)hash是基于分區(qū)個(gè)數(shù)取模（%）運(yùn)算。根據(jù)余數(shù)插入到指定的分區(qū)。打算分4個(gè)分區(qū)，根據(jù)id字段來分區(qū)。

? ? ? ? ? ? ?怎么算出新插入一行數(shù)據(jù)，需要放到分區(qū)1，還是分區(qū)4呢? ?id的值除以4，余下1，這一行數(shù)據(jù)就分到1分區(qū)。

?

? ? ? ? ? ??常規(guī)hash，可以讓數(shù)據(jù)非常平均的分布每一個(gè)分區(qū)。比如分為4個(gè)取，取余數(shù)，余數(shù)總是0-3之間的值(總到這幾個(gè)分區(qū)去)。分配打散比較均勻。

?

? ? ? ? ? ? 但是也是有缺點(diǎn)的：由于分區(qū)的規(guī)則在創(chuàng)建表的時(shí)候已經(jīng)固定了，數(shù)據(jù)就已經(jīng)打散到各個(gè)分區(qū)。現(xiàn)在如果需要新增分區(qū)、減少分區(qū)，運(yùn)算規(guī)則變化了，原來已經(jīng)入庫的數(shù)據(jù)，就需要適應(yīng)新的運(yùn)算規(guī)則來做遷移。

? ? ? ? ? ? 實(shí)際上在分庫分表的時(shí)候，使用hash方式，也是數(shù)據(jù)量遷移的問題。不過還好。

? ? ? ? ? ? 針對(duì)這個(gè)情況，增加了線性hash的方式。

?

• 線性HASH(LINEAR HASH)稍微不同點(diǎn)。

? ? ? ? ?實(shí)際上線性hash算法，就是我們memcache接觸到的那種一致性hash算法。使用虛擬節(jié)點(diǎn)的方式，解決了上面hash方式分區(qū)時(shí)，當(dāng)新增加分區(qū)后，涉及到的數(shù)據(jù)需要大量遷移的問題。也不是不需要遷移，而是需要遷移的數(shù)據(jù)量小。

?

? ? ? ? ?在技術(shù)實(shí)現(xiàn)上：線性哈希功能使用的一個(gè)線性的2的冪（powers-of-two）運(yùn)算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。

?

? ? ? ? ? ?線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于，在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字。

?

?

兩者也有有相同的地方：

?

• ? ?都是均勻分布的，預(yù)先指定n個(gè)分區(qū)，然后均勻網(wǎng)幾個(gè)分區(qū)上面分布數(shù)據(jù)。根據(jù)一個(gè)字段值取hash值，這樣得到的結(jié)果是一個(gè)均勻分布的值。后面添加新的分區(qū)多少需要考慮數(shù)據(jù)遷移。?

?

• ? ?常規(guī)HASH和線性HASH，因?yàn)槎际怯?jì)算整數(shù)取余的方式，那么增加和收縮分區(qū)后，原來的數(shù)據(jù)會(huì)根據(jù)現(xiàn)有的分區(qū)數(shù)量重新分布。

?

• ? ? HASH分區(qū)不能刪除分區(qū)，所以不能使用DROP PARTITION操作進(jìn)行分區(qū)刪除操作；

?

考慮以后遷移數(shù)據(jù)量少，使用線性hash。

?

?

?

2、按照range范圍分區(qū)

?

范圍分區(qū)，可以自由指定范圍。比如指定1-2000是一個(gè)分區(qū)，2000到5000千又是一個(gè)分區(qū)。范圍完全可以自己定。后面我要添加新的分區(qū)，很容易嗎?

?

?

?

3、按key分區(qū)

?

? ?類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

?

4、按list方式分區(qū)

?

可以把list看成是在range方式的基礎(chǔ)上的改進(jìn)版。list和range本質(zhì)都是基于范圍，自己控制范圍。

range是列出范圍，比如1-2000范圍算一個(gè)分區(qū)，這樣是一個(gè)連續(xù)的值。

而list分區(qū)方式是枚舉方式。可以指定在1,5,8,9,20這些值都分在第一個(gè)分區(qū)。從list單詞的字面意思命名暗示就是列表，指定列表中出現(xiàn)的值都分配在第幾個(gè)分區(qū)。

?

?

三、如何根據(jù)業(yè)務(wù)選擇分區(qū)類型

?

1、何時(shí)選擇分區(qū),何時(shí)選擇分表

?

分表還是比分區(qū)更加靈活。在代碼中可以自己控制。一般分表會(huì)與分庫結(jié)合起來使用的。在進(jìn)行分表的時(shí)候，順帶連分庫方案也一起搞定了。

分表分庫，性能和并發(fā)能力要比分區(qū)要強(qiáng)。分表后，有個(gè)麻煩點(diǎn)：自己需要修改代碼去不同的表操作數(shù)據(jù)。

比如用戶表分表后，計(jì)劃分4個(gè)表，每個(gè)表4千萬用戶。按照用戶編號(hào)取模為4。

代碼很多處要做專門的匹配如下：
? ? ?每次操作用戶資料，先要根據(jù)uid算出是哪個(gè)表名稱。然后再去寫sql查詢。 當(dāng)然，是可以使用數(shù)據(jù)庫中間件來做完成分庫、分表。應(yīng)用代碼不用修改。大部分中間件是根據(jù)他們自己的業(yè)務(wù)特點(diǎn)定制的，拿來使用，不見得適合自己的業(yè)務(wù)。所以目前缺少通用的。
? ? ?如果使用分區(qū)的方式。代碼不用修改。sql還是按照原來的方式寫。mysql內(nèi)部自動(dòng)做了匹配了。

? ? ?非常適合業(yè)務(wù)剛剛起步的時(shí)候，能不能做起來，存活期是多久不知。不用把太多精力花費(fèi)在分庫分表的適應(yīng)上。 考慮到現(xiàn)在業(yè)務(wù)才起步，使用分區(qū)不失為一種既省事又能提高數(shù)據(jù)庫并發(fā)能力的辦法。等以后業(yè)務(wù)發(fā)展起來了，數(shù)據(jù)量過億了，那個(gè)時(shí)候經(jīng)濟(jì)實(shí)力已增強(qiáng)，再做改進(jìn)方案不遲。 架構(gòu)是演變出來的，不是設(shè)計(jì)出來的。適應(yīng)當(dāng)前業(yè)務(wù)的方案，就是好的方案。 過度設(shè)計(jì)也是一種負(fù)擔(dān)：很多技術(shù)，企圖一開始就設(shè)計(jì)出一個(gè)多大量的系統(tǒng)，實(shí)際上沒有那種量，為了顯示自己技術(shù)牛逼。 總結(jié)：訪問量不大，但是數(shù)據(jù)表行數(shù)很多。可以使用分區(qū)的方式。訪問量大，數(shù)據(jù)量大，可以重構(gòu)成分表的方式。
? ? 這是因?yàn)殡m然數(shù)據(jù)量多，但是訪問量不大，如果使用分表的話，要修改代碼很多地方，弄起來太麻煩了。投入多，產(chǎn)出少就沒必要了。

2、如何選擇適合自己的分區(qū)類型 使用分區(qū)和分表一樣的思想：盡量讓數(shù)據(jù)均勻分散，這樣達(dá)到分流、壓力減小的效果。如果不能均勻分布，某個(gè)分區(qū)的操作量特別大，出現(xiàn)單點(diǎn)瓶頸。 雖然4種類型的分區(qū)方式。其實(shí)總共兩大類，按范圍分區(qū)和按hash運(yùn)算分區(qū)。 range范圍分區(qū)，適合按照范圍來切分?jǐn)?shù)據(jù)。比如按時(shí)間范圍分區(qū)。 hash，適合均勻分散數(shù)據(jù)。使用hash分區(qū)，麻煩點(diǎn)是后續(xù)增加分區(qū)，數(shù)據(jù)要遷移。有了線性hash分區(qū)法，這個(gè)遷移量減低了很多。 以用戶表為例子，如果要使用分區(qū)方案。改使用哪種分區(qū)類型呢？
? ? 考慮到user_id一般不會(huì)設(shè)計(jì)成自增數(shù)字。有人會(huì)奇怪，怎么不是自增的，我見過好多用戶編號(hào)都是自增的！ 的確，有自增數(shù)字做uid的，不過一般是開源系統(tǒng)為了省事，比如discuz、ecshop等。人家沒那么多工作量給你設(shè)計(jì)用戶編號(hào)方案。 自增的用戶編號(hào)，由于是每次加1進(jìn)行遞增的。這規(guī)律太明顯了，很容易被別有用途的人猜測user_id。再說，別人一看就知道你有多少用戶!? 有個(gè)原則，設(shè)計(jì)編號(hào)的時(shí)候，盡量不要讓外部知道你的生成規(guī)律。比如訂單號(hào)，如果是逐個(gè)加1的訂單號(hào)，外界可以猜測出你的業(yè)務(wù)訂單總數(shù)出來。 說一個(gè)自增用戶編號(hào)的例子。筆者曾經(jīng)在一家上市互聯(lián)網(wǎng)公司，有幾千萬的用戶，uid過去是discuz那一套自增的方式。后來不得不改掉user_id的生成方式。筆者當(dāng)時(shí)負(fù)責(zé)了這個(gè)改造方案。 ? ? 不是自增的數(shù)字，會(huì)是這種：注冊(cè)一個(gè)是1897996，再注冊(cè)一個(gè)是9689898，外界完全找不到數(shù)字的規(guī)律。 不是自增的編號(hào)，如果使用范圍來分區(qū)，各個(gè)分區(qū)的數(shù)據(jù)做不到均勻分布的。原因如下： 比如說用戶編號(hào)為1-200000000的用戶分配到p1分區(qū)，20000000-40000000分配到p2分區(qū)，40000000-60000000分配到p3區(qū)，這樣類推下去。 由于用戶編號(hào)不是自增，注冊(cè)分配到的用戶編號(hào)，可能是1到2千萬之間的數(shù)字，也可能是4千萬到6千萬之間的一個(gè)數(shù)字。如果被分配到4千萬到6千萬的數(shù)字會(huì)更多，那么各個(gè)分區(qū)給到的數(shù)據(jù)是不均勻的。 故不好使用范圍來分區(qū)。 比較好的辦法是，使用hash取模，user_id%分區(qū)數(shù)。數(shù)據(jù)就可以分散均勻到4個(gè)分區(qū)去了。

? ?

?

轉(zhuǎn)載于:https://www.cnblogs.com/wangtao_20/p/7119469.html

總結(jié)

以上是生活随笔為你收集整理的mysql分区方案的研究的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。