MySQL的表分區

標籤：

什麼是表分區
通俗地講表分區是將一大表，根據條件分割成若干個小表。mysql5.1開始支援資料表分區了。
如：某使用者表的記錄超過了600萬條，那麼就可以根據入庫日期將表分區，也可以根據所在地將表分區。當然也可根據其他的條件分區。

 

分區類型
 
· RANGE分區：基於屬於一個給定連續區間的列值，把多行分配給分區。 
· LIST分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。 
· HASH分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效、產生非負整數值的任何錶達式。
· KEY分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL 伺服器提供其自身的雜湊函數。必須有一列或多列包含整數值。

 

 

1.RANGE分區

基於屬於一個給定連續區間的列值，把多行分配給分區。這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。以下是執行個體。

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT NOT NULL,    store_id INT NOT NULL)   partition BY RANGE (store_id) (    partition p0 VALUES LESS THAN (6),    partition p1 VALUES LESS THAN (11),    partition p2 VALUES LESS THAN (16),    partition p3 VALUES LESS THAN (21));

 

按照這種資料分割配置，在商店1到5工作的僱員相對應的所有行被儲存在分區P0中，商店6到10的僱員儲存在P1中，依次類推。注意，每個分區都是按順序進行定義，從最低到最高。這是PARTITION BY RANGE 文法的要求；在這點上，它類似於C或Java中的“switch ... case”語句。
       對於包含資料(72, ‘Michael‘, ‘Widenius‘, ‘1998-06-25‘, NULL, 13)的一個新行，可以很容易地確定它將插入到p2分區中，但是如果增加了一個編號為第21的商店，將會發生什麼呢？在這種方案下，由於沒有規則把store_id大於20的商店包含在內，伺服器將不知道把該行儲存在何處，將會導致錯誤。 要避免這種錯誤，可以通過在CREATE TABLE語句中使用一個“catchall” VALUES LESS THAN子句，該子句提供給所有大於明確指定的最高值的值：

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT NOT NULL,    store_id INT NOT NULL)   PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN MAXVALUE)；

 

MAXVALUE 表示最大的可能的整數值。現在，store_id 列值大於或等於16（定義了的最高值）的所有行都將儲存在分區p3中。在將來的某個時候，當商店數已經增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區。
       在幾乎一樣的結構中，你還可以基於僱員的工作代碼來分割表，也就是說，基於job_code 列值的連續區間。例如——假定2位元字的工作代碼用來表示普通（店內的）工人，三個數字代碼錶示辦公室和技術服務人員，四個數字代碼錶示管理層，你可以使用下面的語句建立該分區表：

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT NOT NULL,    store_id INT NOT NULL)   PARTITION BY RANGE (job_code) (    PARTITION p0 VALUES LESS THAN (100),    PARTITION p1 VALUES LESS THAN (1000),    PARTITION p2 VALUES LESS THAN (10000))；

 

在這個例子中, 店內工人相關的所有行將儲存在分區p0中，辦公室和技術服務人員相關的所有行儲存在分區p1中，管理層相關的所有行儲存在分區p2中。
       在VALUES LESS THAN 子句中使用一個運算式也是可能的。這裡最值得注意的限制是MySQL 必須能夠計算運算式的傳回值作為LESS THAN (<)比較的一部分；因此，運算式的值不能為NULL 。由於這個原因，僱員表的hired, separated, job_code,和store_id列已經被定義為非空（NOT NULL）。
       除了可以根據商店編號分割表資料外，你還可以使用一個基於兩個DATE （日期）中的一個的運算式來分割表資料。例如，假定你想基於每個僱員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實現這種分區模式的CREATE TABLE 語句的一個例子如下所示：

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT,    store_id INT)   PARTITION BY RANGE (YEAR(separated)) (    PARTITION p0 VALUES LESS THAN (1991),    PARTITION p1 VALUES LESS THAN (1996),    PARTITION p2 VALUES LESS THAN (2001),    PARTITION p3 VALUES LESS THAN MAXVALUE)；

 

在這個方案中，在1991年前僱傭的所有僱員的記錄儲存在分區p0中，1991年到1995年期間僱傭的所有僱員的記錄儲存在分區p1中， 1996年到2000年期間僱傭的所有僱員的記錄儲存在分區p2中，2000年後僱傭的所有工人的資訊儲存在p3中。
RANGE分區在如下場合特別有用：
      1）、 當需要刪除一個分區上的“舊的”資料時,只刪除分區即可。如果你使用上面最近的那個例子給出的資料分割配置，你只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經停止工作的僱員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR (separated) <= 1990；”這樣的一個DELETE查詢要有效得多。
      2）、想要使用一個包含有日期或時間值，或包含有從一些其他級數開始增長的值的列。
      3）、經常運行直接依賴於用於分割表的列的查詢。例如，當執行一個如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時，MySQL可以很迅速地確定只有分區p2需要掃描，這是因為餘下的分區不可能包含有符合該WHERE子句的任何記錄。
注釋：這種最佳化還沒有在MySQL 5.1來源程式中啟用，但是，有關工作進行中中

 

 

LIST分區

類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。
      LIST分區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基於某個列值、並返回一個整數值的運算式，然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。
注釋：在MySQL 5.1中，當使用LIST分區時，有可能只能匹配整數列表。

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT,    store_id INT)；

 

假定有20個音像店，分布在4個有經銷權的地區，如下表所示：
====================
地區      商店識別碼
------------------------------------
北區      3, 5, 6, 9, 17
東區      1, 2, 10, 11, 19, 20
西區      4, 12, 13, 14, 18
中心區   7, 8, 15, 16
====================
要按照屬於同一個地區商店的行儲存在同一個分區中的方式來分割表，可以使用下面的“CREATE TABLE”語句：

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT,    store_id INT)   PARTITION BY LIST(store_id)    PARTITION pNorth VALUES IN (3,5,6,9,17),    PARTITION pEast VALUES IN (1,2,10,11,19,20),    PARTITION pWest VALUES IN (4,12,13,14,18),    PARTITION pCentral VALUES IN (7,8,15,16))；

 

這使得在表中增加或刪除指定地區的僱員記錄變得容易起來。例如，假定西區的所有音像店都賣給了其他公司。那麼與在西區音像店工作僱員相關的所有記錄（行）可以使用查詢“ALTER TABLE employees DROP PARTITION pWest；”來進行刪除，它與具有同樣作用的DELETE （刪除）查詢“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。
【要點】：如果試圖插入列值（或分區運算式的傳回值）不在分區值列表中的一行時，那麼“INSERT”查詢將失敗並報錯。例如，假定LIST分區的採用上面的方案，下面的查詢將失敗：

INSERT INTO employees VALUES(224, ‘Linus‘, ‘Torvalds‘, ‘2002-05-01‘, ‘2004-10-12‘, 42, 21);

這是因為“store_id”列值21不能在用於定義分區pNorth, pEast, pWest,或pCentral的值列表中找到。要重點注意的是，LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內的定義。將要匹配的任何值都必須在值列表中找到。

LIST分區除了能和RANGE分區結合起來產生一個複合的子分區，與HASH和KEY分區結合起來產生複合的子分區也是可能的。

 

MySQL的表分區