MySQL的表分區詳解。本站提示廣大學習愛好者：（MySQL的表分區詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL的表分區詳解正文
1、甚麼是表分區
淺顯地講表分區是將一年夜表，依據前提朋分成若干個小表。mysql5.1開端支撐數據表分區了。
如：某用戶表的記載跨越了600萬條，那末便可以依據入庫日期將表分區，也能夠依據地點地將表分區。固然也可依據其他的前提分區。
2、為何要對表停止分區
為了改良年夜型表和具有各類拜訪形式的表的可伸縮性，可治理性和進步數據庫效力。
分區的一些長處包含：
1）、與單個磁盤或文件體系分區比擬，可以存儲更多的數據。
2）、關於那些曾經掉去保留意義的數據，平日可以經由過程刪除與那些數據相關的分區，很輕易地刪除那些數據。相反地，在某些情形下，添加新數據的進程又可以經由過程為那些新數據專門增長一個新的分區，來很便利地完成。平日和分區有關的其他長處包含上面列出的這些。MySQL分區中的這些功效今朝還沒有完成，然則在我們的優先級列表中，具有高的優先級；我們願望在5.1的臨盆版本中，能包含這些功效。
3）、一些查詢可以獲得極年夜的優化，這重要是借助於知足一個給定WHERE語句的數據可以只保留在一個或多個分區內，如許在查找時就不消查找其他殘剩的分區。由於分區可以在創立了分區表落後行修正，所以在第一次設置裝備擺設分區計劃時還不曾這麼做時，可以從新組織數據，來進步那些經常使用查詢的效力。
4）、觸及到例如SUM()和COUNT()如許聚合函數的查詢，可以很輕易地停止並行處置。這類查詢的一個簡略例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。經由過程“並行”，這意味著該查詢可以在每一個分區上同時停止，終究成果只需經由過程總計一切分區獲得的成果。
5）、經由過程跨多個磁盤來疏散數據查詢，來取得更年夜的查詢吞吐量。
3、分區類型
· 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源法式中啟用，然則，有關任務正在停止中。
2.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個有經銷權的地域，以下表所示：
====================
地域 市肆ID 號
------------------------------------
北區 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分區聯合起來生成復合的子分區也是能夠的。
3.HASH分區
基於用戶界說的表達式的前往值來停止選擇的分區，該表達式應用將要拔出到表中的這些行的列值停止盤算。這個函數可以包括MySQL 中有用的、發生非負整數值的任何表達式。
要應用HASH分區來朋分一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，個中“expr”是一個前往一個整數的表達式。它可以僅僅是字段類型為MySQL 整型的一列的名字。另外，你極可能須要在前面再添加一個“PARTITIONS num”子句，個中num 是一個非負的整數，它表現表將要被朋分成份區的數目。
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 HASH(store_id)
PARTITIONS 4；
假如沒有包含一個PARTITIONS子句，那末分區的數目將默許為1。 破例： 關於NDB Cluster（簇）表，默許的分區數目將與簇數據節點的數目雷同，
這類修改能夠是斟酌任何MAX_ROWS 設置，以便確保一切的行都能適合地拔出到分區中。
1.)LINER HASH
MySQL還支撐線性哈希功效，它與慣例哈希的差別在於，線性哈希功效應用的一個線性的2的冪（powers-of-two）運算軌則，而慣例 哈希應用的是求哈希函數值的模數。
線性哈希分區和慣例哈希分區在語法上的獨一差別在於，在“PARTITION BY” 子句中添加“LINEAR”症結字。
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 LINEAR HASH(YEAR(hired))
PARTITIONS 4；
假定一個表達式expr, 當應用線性哈希功效時，記載將要保留到的分區是num 個分區中的分區N，個中N是依據上面的算法獲得：
1. 找到下一個年夜於num.的、2的冪，我們把這個值稱為V ，它可以經由過程上面的公式獲得：
2. V = POWER(2, CEILING(LOG(2, num)))
（例如，假定num是13。那末LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，則V = POWER(2,4), 即等於16）。
3. 設置 N = F(column_list) & (V - 1).
4. 當 N >= num:
· 設置 V = CEIL(V / 2)
· 設置 N = N & (V - 1)
例如，假定表t1，應用線性哈希分區且有4個分區，是經由過程上面的語句創立的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
如今假定要拔出兩行記載到表t1中，個中一筆記錄col3列值為'2003-04-14'，另外一筆記錄col3列值為'1998-10-19'。第一筆記錄將要保留到的分區肯定以下：
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 為假（FALSE）: 記載將被保留到#3號分區中)
第二筆記錄將要保留到的分區序號盤算以下：
V = 8
N = YEAR('1998-10-19') & (8-1)
= 1998 & 7
= 6
(6 >= 4 為真（TRUE）: 還須要附加的步調)
N = 6 & CEILING(5 / 2)
= 6 & 3
= 2
(2 >= 4 為假（FALSE）: 記載將被保留到#2分區中)
依照線性哈希分區的長處在於增長、刪除、歸並和拆分分區將變得加倍快捷，有益於處置含有極端年夜量（1000吉）數據的表。它的缺陷在於，與應用
慣例HASH分區獲得的數據散布比擬，各個分區間數據的散布不年夜能夠平衡。
4.KSY分區
相似於按HASH分區，差別在於KEY分區只支撐盤算一列或多列，且MySQL 辦事器供給其本身的哈希函數。必需有一列或多列包括整數值。
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
在KEY分區中應用症結字LINEAR和在HASH分區中應用具有異樣的感化，分區的編號是經由過程2的冪（powers-of-two）算法獲得，而不是經由過程模數算法。
