MySQL分區表

標籤：

　　當資料庫資料量漲到一定數量時，效能就成為我們不能不關注的問題，如何最佳化呢？ 常用的方式不外乎那麼幾種：

　　1、分表，即把一個很大的表達資料分到幾個表中，這樣每個表資料都不多。 

　　　　優點：提高並發量，減小鎖的粒度

　　　　缺點：代碼維護成本高，相關sql都需要改動

　　2、分區，所有的資料還在一個表中，但實體儲存體資料根據一定的規則存放在不同的檔案中，檔案也可以放到另外磁碟上

　　　　優點：代碼維護量小，基本不用改動，提高IO輸送量

　　　　缺點：表的並發程度沒有增加

　　3、拆分業務，這個本質還是分表。

　　　　優點：長期支援更好

　　　　缺點：代碼邏輯重構，工作量很大

　　當然，每種情況都有合適的應用情境，需要根據具體業務具體選擇。由於分表和拆分業務和mysql本身關係不大屬於業務層面，我們只說和資料庫關係最緊密的方式：表分區。不過使用表分區有個前提就是你的資料庫必須支援。那麼，怎麼知道我的資料庫是否支援表分區呢 ？ 請執行下面命令　　

show plugins;  ---在mysql控制台中執行

據說5.4一下的版本是另外一個命令，不過我沒有測試

 show variables like ‘%part%‘;

 　　資料庫的表分區一般有兩種方式：縱向和橫向。縱向就是把表中不同欄位分到不同資料檔案中。橫向是把表中前一部分資料放到一個檔案中，另一部分資料放到一個檔案中。mysql只支援後後一種方式，橫向拆分。

1、建立分區表

 　　如果要使用表的分區優勢，不但要資料庫版本支援分區，關鍵要建分區表，這個表和普通表不一樣，並且必須建表的時候就要指定分區，否則無法把普通表改成分區表。那麼，如果建立一個分區表呢？ 其他很簡單，請看下面建表語句

1 CREATE TABLE `T_part` (2     `f_id` INT DEFAULT NULL,3     `f_name` VARCHAR (20) DEFAULT NULL,4     PRIMARY KEY (`f_id`)5 ) ENGINE = myisam DEFAULT CHARSET = utf8 6 PARTITION BY RANGE (f_id)(        -----指定分區方式7     PARTITION p0  VALUES less THAN (10),-- 分了兩個區8     PARTITION p1  VALUES less THAN (20)9 )

　　上面語句建了一個“T_part”表，有兩個欄位f_id和f_name，並且根據RANGE方式把表分成兩個區p0、p1，當f_id小於10放入p0分區，當f_id大於0小於20放入分區p1. 那麼當f_id大於20的資料放入哪個分區呢？ 你猜對了，insert語句會報錯。

　　看到了吧，建立分區表就這麼簡單！當然，你隨時可以添加刪除分區，不過要注意，刪除分區的時候會把當前分區下所有資料都刪除。

alter table T_part add partition(partition p2 values less than (MAXVALUE));  ---新增分區alter table T_part DROP partition p2; ----刪除分區

 

2、表分區的幾種方式

 　　mysql支援5種分區方式：RANGE分區、LIST分區、HASH分區、LINEAR HASH分區和KEY分區。每種分區都有自己的使用情境。

　　1）RANGE分區：

　　　　RANGE分區的表是通過如下一種方式進行分區的，每個分區包含那些分區運算式的值位於一個給定的連續區間內的行。這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。

　　　　上面的例子就是RANGE分區.

　　2）LIST分區：

　　　　MySQL中的LIST分區在很多方面類似於RANGE分區。和按照RANGE分區一樣，每個分區必須明確定義。它們的主要區別在於，LIST分區中每個分區的定義和選擇是基於某列的值從屬於一個值列表集中的一個值，而RANGE分區是從屬於一個連續區間值的集合。LIST分區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基於某個列值、並返回一個整數值的運算式，然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。

CREATE TABLE `T_list` (    `f_id` INT DEFAULT NULL,    `f_name` VARCHAR (20) DEFAULT NULL,    PRIMARY KEY (`f_id`)) ENGINE = myisam DEFAULT CHARSET = utf8 PARTITION by list(f_id)(  PARTITION p0 VALUES in(1,2,3),  ----區間值不能重複  PARTITION p1 VALUES in(4,5,6));

　　3)HASH分區：

　　　　HASH分區主要用來確保資料在預先確定數目的分區中平均分布。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該儲存在哪個分區中；而在HASH分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被雜湊的列值指定一個列值或運算式，以及指定被分區的表將要被分割成的分區數量。要使用HASH分區來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數的運算式。它可以僅僅是欄位類型為MySQL 整型的一列的名字。此外，你很可能需要在後面再添加一個“PARTITIONS num”子句，其中num 是一個非負的整數，它表示表將要被分割成分區的數量。

CREATE TABLE `T_hash` (    `f_id` INT DEFAULT NULL,    `f_name` VARCHAR (20) DEFAULT NULL,    PRIMARY KEY (`f_id`)) ENGINE = myisam DEFAULT CHARSET = utf8 PARTITION BY HASH(f_id) ---可以指定多列PARTITIONS 4；---分區個數

　　　　“expr”還可以是MySQL 中有效任何函數或其他運算式，只要它們返回一個既非常數、也非隨機數的整數。（換句話說，它既是變化的但又是確定的）。但是應當記住，每當插入或更新（或者可能刪除）一行，這個運算式都要計算一次；這意味著非常複雜的運算式可能會引起效能問題，尤其是在執行同時影響大量行的運算（例如批量插入）的時候。最有效率的雜湊函數是只對單個表列進行計算，並且它的值隨列值進行一致地增大或減小，因為這考慮了在分區範圍上的“修剪”。也就是說，運算式值和它所基於的列的值變化越接近，MySQL就可以越有效地使用該運算式來進行HASH分區。

　　4）LINEAR HASH分區：

　　　　MySQL還支援線性雜湊功能，它與常規雜湊的區別在於，線性雜湊功能使用的一個線性2的冪（powers-oftwo）運演算法則，而常規 雜湊使用的是求雜湊函數值的模數。線性雜湊分割和常規雜湊分割在文法上的唯一區別在於，在“PARTITION BY” 子句中添加“LINEAR”關鍵字.

　　5）KEY分區：

　　　　按照KEY進行分區類似於按照HASH分區，除了HASH分區使用的使用者定義的運算式，而KEY分區的 雜湊函數是由MySQL 伺服器提供。MySQL 簇（Cluster）使用函數MD5()來實現KEY分區；對於使用其他儲存引擎的表，伺服器使用其自己內部的 雜湊函數，這些函數是基於與PASSWORD()一樣的運演算法則。

　　　　KEY分區的文法和HASH文法類似，只是把關鍵字改成KEY。　　

CREATE TABLE `T_key` (    `f_id` INT DEFAULT NULL,    `f_name` VARCHAR (20) DEFAULT NULL,    PRIMARY KEY (`f_id`)) ENGINE = myisam DEFAULT CHARSET = utf8 PARTITION BY LINEAR key(f_id)PARTITIONS 3；

　　6）子分區：

　　　　子分區的意思就是在分區的基礎上再次分區。且每個分區必須有相同個數的子分區。

CREATE TABLE `T_part` (    `f_id` INT DEFAULT NULL,    `f_name` VARCHAR (20) DEFAULT NULL,    PRIMARY KEY (`f_id`)) PARTITION BY RANGE (f_id)SUBPARTITION BY HASH(F_ID)SUBPARTITIONS 2(    PARTITION p0     VALUES         less THAN (10),    PARTITION p1    VALUES        less THAN (20))

　　　　上面語句的意思是，建立兩個range分區，每個分區根據hash有分別有兩個子分區，實際上整個表分成2×2=4個分區。當然，要詳細定義每個分區屬性也是可以的

CREATE TABLE `T_part` (    `f_id` INT DEFAULT NULL,    `f_name` VARCHAR (20) DEFAULT NULL,    PRIMARY KEY (`f_id`)) PARTITION BY RANGE (f_id)SUBPARTITION BY HASH(F_ID)(    PARTITION p0     VALUES less THAN (10)    (        SUBPARTITION s0             DATA DIRECTORY = ‘/disk0/data‘             INDEX DIRECTORY = ‘/disk0/idx‘,        SUBPARTITION s1             DATA DIRECTORY = ‘/disk1/data‘             INDEX DIRECTORY = ‘/disk1/idx‘    ),    PARTITION p1    VALUES less THAN (20)    (        SUBPARTITION s2            DATA DIRECTORY = ‘/disk0/data‘             INDEX DIRECTORY = ‘/disk0/idx‘,        SUBPARTITION s3             DATA DIRECTORY = ‘/disk1/data‘             INDEX DIRECTORY = ‘/disk1/idx‘    ))

　　　　這樣可以對每個分區指定具體儲存磁碟。前提磁碟是存在的。

　　

　　MySQL 中的分區在禁止空值（NULL）上沒有進行處理，無論它是一個列值還是一個使用者定義運算式的值。一般而言，在這種情況下MySQL 把NULL視為0。如果你希望迴避這種做法，你應該在設計表時不允許空值；最可能的方法是，通過聲明列“NOT NULL”來實現這一點。

 

 

　　

MySQL分區表