MySQL表分割槽管理

一、什麼是表分割槽

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

二、為什麼要對錶進行分割槽

為了改善大型表以及具有各種訪問模式的表的可伸縮性，可管理性和提高資料庫效率。

分割槽的一些優點包括：

?與單個磁碟或檔案系統分割槽相比，可以儲存更多的資料。

?對於那些已經失去儲存意義的資料，通常可以透過刪除與那些資料有關的分割槽，很容易地刪除那些資料。相反地，在某些情況下，新增新資料的過程又可以透過為那些新資料專門增加一個新的分割槽，來很方便地實現。通常和分割槽有關的其他優點包括下面列出的這些。MySQL分割槽中的這些功能目前還沒有實現，但是在我們的優先順序列表中，具有高的優先順序；我們希望在5.1的生產版本中，能包括這些功能。

?一些查詢可以得到極大的最佳化，這主要是藉助於滿足一個給定WHERE語句的資料可以只儲存在一個或多個分割槽內，這樣在查詢時就不用查詢其他剩餘的分割槽。因為分割槽可以在建立了分割槽表後進行修改，所以在第一次配置分割槽方案時還不曾這麼做時，可以重新組織資料，來提高那些常用查詢的效率。

?涉及到例如SUM()和COUNT()這樣聚合函式的查詢，可以很容易地進行並行處理。這種查詢的一個簡單例子如
“SELECT salesperson_id, COUNT (orders) as order_total FROM sales
GROUP BY salesperson_id；”。透過“並行”，這意味著該查詢可以在每個分割槽上同時進行，最終結果只需透過總計所有分割槽得到的結果。

?透過跨多個磁碟來分散資料查詢，來獲得更大的查詢吞吐量。

三、分割槽型別

RANGE分割槽：基於屬於一個給定連續區間的列值，把多行分配給分割槽。

LIST分割槽：類似於按RANGE分割槽，區別在於LIST分割槽是基於列值匹配一個離散值集合中的某個值來進行選擇。

HASH分割槽：基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL 中有效的、產生非負整數值的任何表示式。

KEY分割槽：類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。

RANGE分割槽

基於屬於一個給定連續區間的列值，把多行分配給分割槽。

這些區間要連續且不能相互重疊，使用VALUES LESS THAN運算子來進行定義。以下是例項。

Sql程式碼：

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子句，該子句提供給所有大於明確指定的最高值的值：

Sql程式碼：

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位數字的工作程式碼用來表示普通（店內的）工人，三個數字程式碼表示辦公室和支援人員，四個數字程式碼表示管理層，你可以使用下面的語句建立該分割槽表：

Sql程式碼：

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 語句的一個例子如下所示：

Sql程式碼：

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分割槽時，有可能只能匹配整數列表。

Sql程式碼：

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”語句：

Sql程式碼：

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分割槽的採用上面的方案，下面的查詢將失敗：

Sql程式碼：

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分割槽結合起來生成複合的子分割槽也是可能的。

HASH分割槽

基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL 中有效的、產生非負整數值的任何表示式。

要使用HASH分割槽來分割一個表，要在CREATE TABLE 語句上新增一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數的表示式。它可以僅僅是欄位型別為MySQL整型的一列的名字。此外，你很可能需要在後面再新增一個“PARTITIONS num”子句，其中num是一個非負的整數，它表示表將要被分割成分割槽的數量。

Sql程式碼：

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設定，以便確保所有的行都能合適地插入到分割槽中。

LINER HASH

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

Sql程式碼：

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分割槽得到的資料分佈相比，各個分割槽間資料的分佈不大可能均衡。

KSY分割槽

類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。

Sql程式碼：

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）演算法得到，而不是透過模數演算法。

分割槽管理

刪除分割槽

ALERT TABLE users DROP PARTITION p0;刪除分割槽 p0。

重建分割槽

RANGE 分割槽重建

ALTER TABLE users REORGANIZE PARTITION
p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));將原來的 p0,p1 分割槽合併起來，放到新的
p0 分割槽中。

LIST 分割槽重建

ALTER TABLE users REORGANIZE PARTITION
p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));將原來的 p0,p1 分割槽合併起來，放到新的
p0 分割槽中。

HASH/KEY 分割槽重建

ALTER TABLE users REORGANIZE PARTITION
COALESCE PARTITION 2;用 REORGANIZE 方式重建分割槽的數量變成2，在這裡數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分割槽

新增 RANGE 分割槽

ALTER TABLE category ADD PARTITION
(PARTITION p4 VALUES IN (16,17,18,19)

DATA DIRECTORY = '/data8/data'

INDEX DIRECTORY = '/data9/idx');新增一個RANGE分割槽。

新增 HASH/KEY 分割槽

ALTER TABLE users ADD PARTITION PARTITIONS
8;將分割槽總數擴充套件到8個。

分解分割槽:

Reorganizepartition關鍵字可以對錶的部分分割槽或全部分割槽進行修改，並且不會丟失資料。分解前後分割槽的整體範圍應該一致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); ----不會丟失資料

合併分割槽:

Merge分割槽：把2個分割槽合併為一個。

alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

刪除表的所有分割槽:

Alter table emp remove partitioning;--不會丟失資料

重建分割槽：

這和先刪除儲存在分割槽中的所有記錄，然後重新插入它們，具有同樣的效果。它可用於整理分割槽碎片。

ALTER TABLE emp rebuild partitionp1,p2;

最佳化分割槽：

如果從分割槽中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT型別的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，並整理分割槽資料檔案的碎片。

ALTER TABLE emp optimize partition p1,p2;

分析分割槽：

讀取並儲存分割槽的鍵分佈。

ALTER TABLE emp analyze partition p1,p2;

修補分割槽：

修補被破壞的分割槽。

ALTER TABLE emp repairpartition p1,p2;

檢查分割槽：

可以使用幾乎與對非分割槽表使用CHECK TABLE 相同的方式檢查分割槽。

獲取mysql分割槽表資訊的幾種方法

1.
show create table 表名

可以檢視建立分割槽表的create語句

2.
show table status

可以檢視錶是不是分割槽表

3.檢視information_schema.partitions表

select

partition_name part,

partition_expression expr,

partition_description descr,

table_rows

from information_schema.partitions where

table_schema = schema()

and
table_name='test';

可以檢視錶具有哪幾個分割槽、分割槽的方法、分割槽中資料的記錄數等資訊

4.
explain partitions select語句

透過此語句來顯示掃描哪些分割槽，及他們是如何使用的.

MySQL表分割槽管理

相關文章