MySQL分區(qū)分表
為什么要分區(qū)和分表

我們的數(shù)據(jù)庫數(shù)據(jù)越來越大，隨之而來的是單個表中數(shù)據(jù)太多，以至于查詢速度過慢，而且由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也受到嚴(yán)重影響，出現(xiàn)數(shù)據(jù)庫性能瓶頸。

MySQL中有一種機(jī)制是表鎖定和行鎖定，是為了保證數(shù)據(jù)的完整性。表鎖定表示你們都不能對這張表進(jìn)行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數(shù)據(jù)操作完了，才能對這條數(shù)據(jù)進(jìn)行操縱。但出現(xiàn)這種情況時，我們可以考慮分表或者分區(qū)。

1、分表
什么是分表
分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表，每個表都對應(yīng)三個文件，MYD數(shù)據(jù)文件，MYI索引文件，frm表結(jié)構(gòu)文件。這些表可以分布在同一塊磁盤上，也可以在不同的機(jī)器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應(yīng)的表名，然后去操作他。
用單個數(shù)據(jù)庫表進(jìn)行拆分，拆分成多個數(shù)據(jù)表，然后用戶訪問的時候，根據(jù)一定的算法（如用hash算法的方式，也可以用求余（取模）的方式），讓用戶訪問不同的表，這樣數(shù)據(jù)分散到多個數(shù)據(jù)表中，減少了單個表的訪問壓力，提升了數(shù)據(jù)庫的訪問性能。分表的目的就在于此，減少數(shù)據(jù)庫的負(fù)擔(dān)，縮短查詢時間。

MySQL分表分為垂直切分和水平切分垂直切分是指數(shù)據(jù)表列的切分，把一張列比較多的表拆分為多張表。通常我們按照以下原則進(jìn)行垂直切分：把不常用的字段單獨放到一張表。把text，blob（binary large object，二進(jìn)制大對象）等大字段拆分出來放在附表中經(jīng)常組合查詢的列放在一張表中：垂直切分更多時候就應(yīng)該在數(shù)據(jù)表設(shè)計之初就執(zhí)行的步驟，然后查詢的時候用join關(guān)鍵起來即可。水平拆分是指數(shù)據(jù)表行的拆分，把一張表的數(shù)據(jù)拆成多張表來存放。水平拆分的原則通常情況下，我們使用hash，取模的方式來進(jìn)行表的拆分。比如一張擁有400W的用戶表users，為了提高其查詢效率，我們把其分為四張表 user1，user2，user3，user4通過ID取模的方法把數(shù)據(jù)分散到四張表內(nèi) id%4=[0,1,2,3]然后查詢，更新，刪除也是通過取模的方式來查詢部分業(yè)務(wù)邏輯也可以通過地區(qū)，年份等字段來進(jìn)行歸檔拆分；進(jìn)行拆分后的表，這是我們就要約束用戶的查詢行為。比如我們是按年來進(jìn)行拆分的，這個時候在頁面設(shè)計上就約束用戶必須要先選擇年，然后才能進(jìn)行查詢。分表的幾種方式1、mysql集群他并不是分表，但是起到了和分表相同的作用。集群可分擔(dān)數(shù)據(jù)庫的操作次數(shù)，將任務(wù)分擔(dān)到多臺數(shù)據(jù)庫上。集群可以讀寫分離，減少讀寫的壓力。從而提升數(shù)據(jù)庫的性能。2、預(yù)先估計會出現(xiàn)大數(shù)據(jù)量并且訪問頻繁的表，將其分為若干個表根據(jù)一定的算法（如hash的方式，也可以使用求余（取模）的方式）讓用戶訪問不同的表。3、利用merge存儲去引擎來實現(xiàn)分表merge分表，分為主表和子表，主表類似于一個殼子，邏輯上封裝了子表，實際上都是存儲在子表中的。我們可以通過主表插入和查詢數(shù)據(jù)，如果清除分表規(guī)律也可以直接操作子表。

2、分區(qū)
什么是分區(qū)
分區(qū)和分表相似，都是按照規(guī)則分解表。不同在與分表將大表分解若干個獨立的實體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個位置存放，分區(qū)后，表還是一張表，但數(shù)據(jù)散列到多個位置了。app讀寫的時候操作還是表名子，db自動去組織分區(qū)的數(shù)據(jù)。

分區(qū)主要的兩種數(shù)據(jù);水平分區(qū)：這種形式的分區(qū)是對表的行進(jìn)行分區(qū)，所有在表中定義的列在每個數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。垂直分區(qū)：這種分區(qū)形式一般來說是通過對表的垂直劃分來減少對目標(biāo)表的寬度，使某些特定的列，被劃分到特定的分區(qū)，每個分區(qū)都包括了其中的列所對應(yīng)的行。

MySQL的分區(qū)類型
1、RANGE分區(qū)
基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給給分區(qū)。這些區(qū)間要連續(xù)且不能相互重疊，使用values less than操作符進(jìn)行定義。
2、LIST分區(qū)
類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇。
LIST分區(qū)通過使用“PARTITION BY LIST"（expr）來實現(xiàn)，其中expr 是某列值或一個基于某個列值、并返回一個整數(shù)值的表達(dá)式，然后通過：VALUES IN （value_list）”的方式來定義某個分區(qū)，其中“value——list“是一個通過逗號分隔的整數(shù)列表。
3、Hash分區(qū)
這種模式允許DBA通過對表的一個或多個列的Hash Key進(jìn)行計算，最后通過這個Hash碼不同的數(shù)值對應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。
hash分區(qū)的目的是將數(shù)據(jù)均勻分步到預(yù)先定義的各個分區(qū)中，保證各分區(qū)的數(shù)據(jù)的數(shù)據(jù)量大致一致。在RANGE和LIST分區(qū)中，必須明確指定一個給定的列值或列值集合應(yīng)該保存在哪一個分區(qū)中；而在RANGE和LIST分區(qū)中MySQL自動完成這個動作，用戶所要定一個列值或者表達(dá)式，以及指定被分區(qū)的表將要被分割成分區(qū)的數(shù)量。
4、Key分區(qū)
Key分區(qū)和Hash相似，不同在于hash分區(qū)是用戶自定義函數(shù)進(jìn)行分區(qū)，Key分區(qū)使用mysql數(shù)據(jù)庫提供的分區(qū)進(jìn)行分區(qū)，NDB clust使用MD5函數(shù)進(jìn)行分區(qū)，對于其他存儲引擎mysql使用內(nèi)部的hash函數(shù)。

以上四種分區(qū)方式中，分區(qū)的條件必須是×××，如果不是×××，需要通過函數(shù)將其轉(zhuǎn)換為整行。5、columns 分區(qū)mysql5.5開始支持COLUMNS分區(qū)，可視為RANGE和LIST分區(qū)的進(jìn)化，COLUMNS分區(qū)可以直接使用×××數(shù)據(jù)進(jìn)行分區(qū)。COLUMNS分區(qū)支持一下數(shù)據(jù)類型。所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。日期類型，如DATE和DATETIME。其余日期類型不支持。字符串類型，如CHAR,VARCHAR,BINARY,和VARBINSRY。BOLB和TEXT類型不支持。COLUMNS可以使用多個列進(jìn)行分區(qū)。

MySQL分表和分區(qū)有什么區(qū)別
1、實現(xiàn)方式上
1、mysql的分表是真正意義上的分表，一張表分成很多表以后，每一個小表都是完整的一張表，都對應(yīng)三個文件，一個是MYD數(shù)據(jù)文件，MYI索引文件，frm表結(jié)構(gòu)文件
2、分區(qū)不一樣，一張大表進(jìn)行分區(qū)后，他還是一張表，不會變成二張表，但是他存放數(shù)據(jù)的區(qū)塊變多了。
2、在數(shù)據(jù)上
1、分表后，數(shù)據(jù)都是存放在分表里，總表只是一個外殼，存放數(shù)據(jù)發(fā)生在一個一個的分表里。
2、分區(qū)呢，不存在分表的概念，分區(qū)只不過把存放數(shù)據(jù)的文件分成許多的小塊，分區(qū)后的表呢，還是一張表，數(shù)據(jù)處理還是有自己完成。
3、提高性能上
1、分表后，單表的并發(fā)能力提高了，磁盤IO性能提高了。
為什么并發(fā)提高了，因為查詢一次所花的時間變短了，如果出現(xiàn)高并發(fā)的花，總表可以根據(jù)不同的查詢，將并發(fā)壓力分到不同的小表里面。
2、mysql提出了分區(qū)的概念，主要是想突破磁盤的IO瓶頸，提高磁盤的讀寫能力，提高磁盤的讀寫能力來增加mysql的性能。
在這一點上，分區(qū)和分表的側(cè)重點不同，分表重點是存取數(shù)據(jù)時，如何提高mysql并發(fā)能力上，而分區(qū)是突破磁盤的讀寫能力，從而達(dá)到提高mysql性能的目的。
4、實現(xiàn)難易程度上
1、分表的方法有很多，用merge來分表，是最簡單的一種方式。這種方式跟分區(qū)的難以程度差不多，并且對程序代碼來說可以做到透明。如果是用其他分表方式就比分區(qū)麻煩了。
2、分區(qū)實現(xiàn)是比較簡單的，建立分區(qū)表，跟建平常的表沒有什么區(qū)別，并且對開代碼端來說是透明的。
mysql分表和分區(qū)有什么聯(lián)系
1、都是提高mysql的性能，在高并發(fā)的狀態(tài)下都有一個良好的表現(xiàn)
2、分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表。我們可以采取分區(qū)的方式，訪問量不大，但是表數(shù)據(jù)較多的表，我們可以采取分區(qū)的方式等。
3、分表技術(shù)是比較麻煩的，需要手動去創(chuàng)建子表，app服務(wù)端讀寫的時候需要計算子表名。采用merge好一些，但也要創(chuàng)建子表和配置子表間的union關(guān)系。
4、表分區(qū)相當(dāng)于分表，操作方便，不需要創(chuàng)建子表。

轉(zhuǎn)載于:https://blog.51cto.com/13214087/2149994

《新程序員》：云原生和全面數(shù)字化實踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

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

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