MySQL 數據庫分表分區

博主QQ：819594300

博客地址：http://zpf666.blog.51cto.com/

有什么疑問的朋友可以聯系博主，博主會幫你們解答，謝謝支持！一、分表

為什么要分表？

我們的數據庫數據越來越大，隨之而來的是單個表中數據太多。以至于查詢書讀變慢，而且由于表的鎖機制導致應用操作也搜到嚴重影響，出現了數據庫性能瓶頸。

什么是分表？

分表是將一個達標按照一定的規則分解成多張具有獨立存儲空間的實體表，每個表都對應三個文件，.MYD數據文件、.MYI索引文件、.frm表結構文件。這些表可以分布在同一塊磁盤上，也可以在不同主機的不同的磁盤上。

App讀寫的時候根據事先定義好的規則得到對應的表名，然后去操作它。

將單個數據庫表進行拆分，拆分成多個數據表，然后用戶訪問的時候，根據一定的算法（如用hash的方式，也可以用求余（取模）的方式），讓用戶訪問不同的表，這樣數據分散到多個數據表中，減少了單個數據表的訪問壓力。提升了數據庫訪問性能。分表的目的就在于此，減小數據庫的負擔，縮短查詢時間。

Mysql分表分為垂直切分和水平切分

垂直切分是指數據表列的拆分，把一張列比較多的表拆分為多張表。我們常常把常用的幾個列單獨放在一個表，不常用的單獨放在另外一個表。

水平拆分是指數據表行的拆分，把一張表的數據拆分成多張表來存放。通常情況下，我們使用hash算法和取模等方式來進行表的拆分。比如：比如一張有400W的用戶表users，為提高其查詢效率我們把其分成4張表users1，users2，users3，users4，通過用ID取模的方法把數據分散到四張表內Id%4= [0,1,2,3]，然后查詢,更新,刪除也是通過取模的方法來查詢。

分表的幾種方式？

1）mysql集群

它并不是分表，但是起到了和分表相同的作用。集群可分擔數據庫的操作次數，將任務分擔到多臺數據庫上。集群可以讀寫分離，減少讀寫壓力，從而提升數據庫性能。

2）預先估計會出現大量數據并且訪問頻繁的表，將其分為若干個表。

根據一定的算法（如用hash的方式，也可以用求余（取模）的方式）讓用戶訪問不同的表。

例如論壇里面發表帖子的表，時間長了這張表肯定很大，幾十萬，幾百萬都有可能。聊天室里面信息表，幾十個人在一起一聊一個晚上，時間長了，這張表的數據肯定很大。像這樣的情況很多。所以這種能預估出來的大數據量表，我們就事先分出個N個表，這個N是多少，根據實際情況而定。以聊天信息表為例：我們事先建100個這樣的表，message_00,message_01,message_02..........message_98,message_99.然后根據用戶的ID來判斷這個用戶的聊天信息放到哪張表里面，可以用hash的方式來獲得，也可以用求余的方式來獲得，方法很多。或者可以設計每張表容納的數據量是N條，那么如何判斷某張表的數據是否容量已滿呢？可以在程序段對于要新增數據的表，在插入前先做統計表記錄數量的操作，當<N條數據，就直接插入，當已經到達閥值，可以在程序段新創建數據庫表（或者已經事先創建好），再執行插入操作）。

3）利用merge存儲引擎來實現分表

如果要把已有的大數據量表分開是比較痛苦的，最痛苦的事就是改代碼，因為程序里面的sql語句已經寫好了，用merge存儲引擎來實現分表，這種方法比較適合。

注意：merge存儲引擎來實現分表有局限性，只針對myisam存儲引擎表。

Merge分表，分為一個主表和若干個子表，主表就是一個殼子，在邏輯上是包含子表的，但是主表不存放任何的數據，真正的數據存放在子表中。

我們可以通過主表插入和查詢數據，如果清楚分表規律，也可以直接操作子表。

下面我們來實現一個簡單的利用merge存儲引擎來實現分表的演示

1）創建一個完整表存儲著所有的成員信息

2）加入實驗數據（我們加入32行實驗數據）

3）下面我們進行分表，這里我們把member分成兩個子表tb_member1,tb_member2

4）創建主表tb_member

注：INSERT_METHOD,此參數

INSERT_METHOD= NO 表示該表不能做任何寫入操作只作為查詢使用，即只讀狀態；

INSERT_METHOD= LAST表示插入到最后的一張表里面；

INSERT_METHOD= first表示插入到第一張表里面。

Insert_method是指以后我們插入新的數據，則要遵守該配置項后面的參數執行。這也正是merge分表的缺陷，對于新插入的數據有局限性，而innodb分表則沒有這個局限性。

5）接下來，我們把原表中的數據分到兩個子表中去

6）查看兩個子表的數據

7）查看主表的數據

總結：主表只是一個外殼，存取數據發生在一個一個的子表里面，每個子表都有自已獨立的相關表文件，而主表只是一個殼，并沒有完整的相關表文件。我們看到的主表的數據，都是給用戶的一個假象，這也說明，分表對于客戶來說是透明的。主表和子表的關系就很類似與我們學過的lvs的調度器和所有的節點服務器。

8）刪除原表，修改主表表名（因為此時原表已經沒有用處了，把主表改成原表表名，這樣做的好處是不用修改APP了，這是merge的一大優勢）

說明：主表也包含有.frm文件，但是他有個特殊的文件是.mrg文件，這個文件不存放任何表的真實數據，而是存放的是所有額子表表名和插入方式。

本實驗總結：merge分表的順序是：

①先創建所需的所有子表→②再創建主表→③把原表的數據導入到新建的不同子表中→④刪除原表，把主表改名字為原表表名（這樣做的好處不用修改APP代碼，這是merge的優點）

其實innodb表也可以分表，步驟如下：

①先創建所需的所有子表→②把原表的數據導入到新建的不同子表中→③修改APP代碼，把客戶的請求對應到各個子表。

兩種不同點是，innodb不需要創建主表，但是需要修改APP代碼，比較麻煩，而merge不需要修改app，但是merge需要建主表，而innodb不需要。

二、分區

什么是分區？

分區和分表相似，都是按照規則分解表。不同在于分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，分區后，表還是一張表，但數據散列到多個位置了。app讀寫的時候操作的還是表名字，db自動去組織分區的數據。

分區主要有兩種形式：

水平分區（Horizontal Partitioning） 這種形式分區是對表的行進行分區，所有在表中定義的列在每個數據集中都能找到，所以表的特性依然得以保持。

舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。

垂直分區（Vertical Partitioning） 這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。

舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區，在保證它們數據相關性的同時還能提高訪問速度。

分區技術支持

①在5.6之前，使用這個參數查看當將配置是否支持分區

mysql>show variables like '%partition%';

+-----------------------+---------------+

|Variable_name          | Value |

+-----------------------+---------------+

|have_partition_engine | YES   |

+-----------------------+------------------+

說明：如果是yes表示你當前的配置支持分區

②在5.6及以采用后，則采用如下方式進行查看

說明：在顯示結果中，可以看到partition是ACTIVE的，表示支持分區。

下面我們先演示一個按照范圍(range)方式的表分區

1)創建range分區表

2)插入些數據（說明一下：處于分解點上額數據會被分配到下一個分區中，比如數據3不會放p0表，而是放p1分區）

3)到存放數據庫表文件的地方看一下

4)從information_schema系統庫中的partitions表中查看分區信息

5)從某個分區中查詢數據

6)新增分區

mysql>alter table 庫名.表名 add partition (partition 新增的分區名 values less than (n));

注意：n為條件是為具體的數字或者是maxvalues

7）刪除分區

注意：當刪除了一個分區，也同時刪除了該分區中所有的數據。

8)分區的合并

未分區表和分區表性能測試

1)創建一個未分區的表

2)創建分區表,按日期的年份拆分

3)通過存儲過程插入100萬條測試數據

創建存儲過程：

注意：RAND()函數在0和1之間產生一個隨機數，如果一個整數參數N被指定，它被用作種子值。每個種子產生的隨機數序列是不同的。

執行存儲過程load_part_tab向bdqn.tab1表插入數據:

退出去mysql的交互式模式，然后在進入交互式模式，執行下面的命令

4）向test2.tab2表中插入數據

5)測試SQL性能

總結：結果表明分區表比未分區表的執行時間少很多。

6）通過explain語句來分析執行情況

總結：explain語句顯示了SQL查詢要處理的記錄數目可以看出分區表比未分區表的明顯掃描的記錄要少很多。

7)創建索引后情況測試

總結：創建索引后分區表比未分區表相差不大（但是數據量越大差別會明顯些）

Mysql分區的類型

1、range分區（范圍分區）

作用：把一個連續的列值中的多行分配給分區，列區間連續并且不重疊。

例子：

總結：p0到p3分區都是按順序進行定義，從最低到最高，不允許從最高到最低，處于分界點的值自動放入下一個分區，比如store_id是16的自動放入p3分區，但是如果插入store_id是21的數據，就會報錯，因為沒有包含21的分區，為了避免這種錯誤，我們一般都把range分區的最后一個分區設置為maxvalue分區，把前面所有分區都不包括的該值分到maxvalue分區，避免報錯。

如果創表的時候沒有創建maxvalue分區，則用如下命令添加即可：

2、list分區（列表分區）

作用：基于列值匹配一個離散值集合中某個值來進行選擇

例子：

總結：list分區依據地區把數據很容易的按地區劃分開，比如公司打算把西部的店面全部出售，則就只需要把pwest刪除即可，非常方便。注意的地方是如果視圖插入的store_id列值不屬于任何一個分區，mysql會報錯，插入失敗。list分區沒有range分區類型的maxvalue(最大值)分區，要匹配的列值必須是創建表時幾個分區已經有的值。

使用下面的語句刪除pwest分區，它與具有同樣作用的DELETE （刪除）查詢

“deletefrom employees2 where store_id in (7,8,15,16)；”比起來，要有效得多。

用另一種刪除方法刪除pnorch分區

3、hash分區

作用：對表的一個或多個列的hash key進行計算，最后通過這個hash 碼不同數值對應的數據區域進行分區。

例子：

總結：hash分區不需要指定分區的集合，mysql會自動完成分區工作，用戶只需要定一個列值或表達式，以及分區的數量，默認分區的數量是1（即不劃分分區）。++

上圖中的那個例子說明：b列的數值取年份÷分區數量，然后去余數。比如分區的數量是4，則余數則只能有0、1、2、3，自動把余數為0的放p0分區，余數為1的放p1分區等等。其中mysql會自動創建p0、p1、p2、p3分區，名字就叫p0、p1、p2、p3。

查一個表中某個指定分區中的所有數據：

從information_schema庫中的partitions表里面查詢bdqn.employess3表的每個分區的具體情況：

4、key分區

作用：與hash分區很相似，不同的是hash分區是用戶自定義函數進行分區。而key使用mysql數據庫提供的函數進行分區。NDB cluster使用md5函數來分區，對于其他存儲引擎mysql使用內部的hash函數。

例子：

總結：range、list、hash、key四種分區中，分區的條件必須是整數，如果不是整數需要通過函數將其轉換成整數。

5、columns分區

說明：mysql5.5版本開始支持columns分區，可視為range和list分區的進化，columns分區可以直接使用非整數數據進行分區。

Columns分區支持以下數據類型：

• 所有×××，如INT SMALLINTTINYINT BIGINT。FLOAT和DECIMAL則不支持。

• 日期類型，如DATE和DATETIME。其余日期類型不支持。

• 字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。

• COLUMNS可以使用多個列進行分區。

分區時，將不同分區放到不同存儲位置：

①建表前，提前創建好存儲目錄，并授權給mysql:

②創建表

注：使用mysql默認的存儲引擎inodb時候，只需要指定data directory 就可以，因為inodb的數據和索引在一個文件中。但是創建表格時指定engine=myisam時，修改分區的存儲位置，需要同時指定datadirectory 和 index directory。

總結：把一個表的所有數據通過分區劃分到不同目錄（目錄在不同磁盤上），可以提高I/O性能，提高磁讀寫能力，讓幾個磁盤都能同時工作，提高mysql的性能。