mysql 分區類型詳解

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

partition p3 values less than maxvalue

maxvalue 表示最大的可能的整數值。現在，store_id 列值大于或等于16（定義了的最高值）的所有行都将儲存在分區p3中。在将來的某個時候，當商店數已經增長到25, 30, 或更多 ，可以使用alter table語句為商店21-25, 26-30,等等增加新的分區。

在幾乎一樣的結構中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code 列值的連續區間。例如——假定2位數字的工作代碼用來表示普通（店内的）勞工，三個數字代碼表示辦公室和支援人員，四個數字代碼表示管理層，你可以使用下面的語句建立該分區表：

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

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),

在這個方案中，在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分區時，有可能隻能比對整數清單。

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

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

hash分區

基于使用者定義的表達式的傳回值來進行選擇的分區，該表達式使用将要插入到表中的這些行的列值進行計算。這個函數可以包含mysql 中有效的、産生非負整數值的任何表達式。

要使用hash分區來分割一個表，要在create table 語句上添加一個“partition by hash (expr)”子句，其中“expr”是一個傳回一個整數的表達式。它可以僅僅是字段類型為mysql 整型的一列的名字。此外，你很可能需要在後面再添加一個“partitions num”子句，其中num 是一個非負的整數，它表示表将要被分割成分區的數量。

partition by hash(store_id)

partitions 4；

如果沒有包括一個partitions子句，那麼分區的數量将預設為1。 例外： 對于ndb cluster（簇）表，預設的分區數量将與簇資料節點的數量相同，

這種修正可能是考慮任何max_rows 設定，以便確定所有的行都能合适地插入到分區中。

liner hash

mysql還支援線性哈希功能，它與正常哈希的差別在于，線性哈希功能使用的一個線性的2的幂（powers-of-two）運算法則，而正常 哈希使用的是求哈希函數值的模數。

線性哈希分區和正常哈希分區在文法上的唯一差別在于，在“partition by” 子句中添加“linear”關鍵字。

partition by linear hash(year(hired))

下面的内容是詳細的算法，可以隻做了解

=======================================

假設一個表達式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分區得到的資料分布相比，各個分區間資料的分布不大可能均衡。

key分區

類似于按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）算法得到，而不是通過模數算法。