mysql分區之RANGE分區講解
按照RANGE分區的表是通過如下一種方式進行分區的，每個分區包含那些分區表達式的值位於一個給定的連續區間內的行。這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。在下面的幾個例子中，假定你創建了一個如下的一個表，該表保存有20家音像店的職員記錄，這20家音像店的編號從1到20。
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
);
根據你的需要，這個表可以有多種方式來按照區間進行分區。一種方式是使用store_id 列。例如，你可能決定通過添加一個PARTITION BY RANGE子句把這個表分割成4個區間，如下所示:
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分區在如下場合特別有用：
· 當需要刪除“舊的”數據時。如果你使用上面最近的那個例子給出的分區方案，你只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0;”來刪除所有在1991年前就已經停止工作的雇員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR(separated) <= 1990;”這樣的一個DELETE查詢要有效得多。
· 想要使用一個包含有日期或時間值，或包含有從一些其他級數開始增長的值的列。
· 經常運行直接依賴於用於分割表的列的查詢。例如，當執行一個如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”這樣的查詢時，MySQL可以很迅速地確定只有分區p2需要掃描，這是因為余下的分區不可能包含有符合該WHERE子句的任何記錄。注釋：這種優化還沒有在MySQL 5.1源程序中啟用，但是，有關工作正在進行中。
