MySQL數據庫表分區注意事項總結

表分區與數據庫分區是不一樣的那麼碰到表分區使用時我們要注意一些什麼事情呢，今天我們來看一篇關於MySQL數據庫表分區注意事項的細節。

1、分區列索引約束
若表有primary key或unique key，則分區表的分區列必須包含在primary key或unique key列表裡，這是為了確保主鍵的效率，否則同一主鍵區的東西一個在Ａ分區，一個在Ｂ分區，顯然會比較麻煩。

2、各分區類型條件
range 每個分區包含那些分區表達式的值位於一個給定的連續區間內的行。這些區間要連續且不能相互重疊

list只支持整形字段或返回整形數的表達式，每個分區列表裡的值列表必須整數

hash類型只支持整形字段或返回整形數的表達式

key類型只支持列名形式（可一個或多個列名），不支持表達式

3、分區可用函數
ABS()

CEILING() (see CEILING() and FLOOR(), immediately following this list)

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

DATEDIFF()

EXTRACT()

FLOOR() (see CEILING() and FLOOR(), immediately following this list)

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

WEEKDAY()

YEAR()

YEARWEEK()

注意：

因為分區函數不包括FROM_UNIXTIME函數，所以用時間戳轉時間來分區就無法實現了，只能用date或者datetime來分區

例如按年我們可以用：

PARTITION BY RANGE (YEAR(date))

按月：

PARTITION BY RANGE(date div 100)   

#div 會把日期變成整數，例如：2014-12-01 -> 20141201、100就是從後面去掉兩位，最後結果是201412

一個訂單做分區的例子：

CREATE TABLE `order` (
  `order_id` bigint(19) NOT NULL DEFAULT '0' COMMENT '訂單ID：年月日時分秒12位+7位隨機數',
  `date` date NOT NULL DEFAULT '0000-00-00' COMMENT '訂單日期',
  `amount` int(11) DEFAULT NULL COMMENT '支付金額，單位分',
  `status` tinyint(1) DEFAULT '0' COMMENT '0:等待支付 1:支付成功 2:支付失敗 3:驗證失敗',
  `addtime` int(10) DEFAULT NULL COMMENT '訂單添加時間',
  PRIMARY KEY (`order_id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
因為我們沒法用時間戳來做按時間分區，所以添加了一個date字段，這個字段和order_id一起作為主鍵，我們知道分區的列一定要放到主鍵裡面去的。下面我們用date計算成年月組合來分區

ALTER TABLE order PARTITION BY RANGE( date DIV 100)
(
    PARTITION p_2014_06 VALUES LESS THAN (201407),
    PARTITION p_2014_07 VALUES LESS THAN (201408),
    PARTITION p_2014_08 VALUES LESS THAN (201409),
    PARTITION p_2014_09 VALUES LESS THAN (201410),
    PARTITION p_2014_10 VALUES LESS THAN (201411),
    PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);
以上 LESS THAN MAXVALUE 設置了最後一個分區p_catch_all，所以不能用add的方式來添加分區了，以下語句不可用：

ALTER TABLE order ADD PARTITION (PARTITION p_2014_11 VALUES LESS THAN (201412));
只能把最後的p_catch_all分區拆分成兩個，這樣還有一個好處就是在p_catch_all分區的數據不會丟失。數據的合並與拆分用REORGANIZE PARTITION進行。

alter table order reorganize partition p_catch_all into
(
    partition p_2014_11 values less than (201412),
    partition p_catch_all values less than maxvalue
);
合並分區：

alter table order reorganize partition p_2014_10,p_2014_11,p_catch_all into
(
    partition p_catch_test values less than MAXVALUE
);
為什麼不分到p_catch_all去？因為會報分區以存在。

為什麼合並的時候要帶上最後一個分區p_catch_all？因為除了最後一個分區，其他重組的分區范圍不能改變總范圍。

刪除分區但是不刪除數據：

alter table 表名 remove partitioning
注意：上面語句在5.5可以執行，5.6好像有問題，要先測試一下

分區之後，where條件是一個范圍的話分區是不起作用的，如 where date >= '2014-01-01' And date <= '2014-01-31'

一定要用 = 或者 in 條件才行 where date = '2014-01-01' 或者 where date in ('2014-01-01', '2014-01-02', '2014-01-03'...)

補充: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分區得到的數據分布相比，各個分區間數據的分布不大可能均衡。

KEY分區

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