MySQL數(shù)據(jù)庫表分區(qū)注意事項大全【推薦】

表分區(qū)與數(shù)據(jù)庫分區(qū)是不一樣的那么碰到表分區(qū)使用時我們要注意一些什么事情呢，今天我們來看一篇關于MySQL數(shù)據(jù)庫表分區(qū)注意事項的細節(jié)。

1、分區(qū)列索引約束

若表有primary key或unique key，則分區(qū)表的分區(qū)列必須包含在primary key或unique key列表里，這是為了確保主鍵的效率，否則同一主鍵區(qū)的東西一個在Ａ分區(qū)，一個在Ｂ分區(qū)，顯然會比較麻煩。

2、各分區(qū)類型條件

range 每個分區(qū)包含那些分區(qū)表達式的值位于一個給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊

list只支持整形字段或返回整形數(shù)的表達式，每個分區(qū)列表里的值列表必須整數(shù)

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

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

3、分區(qū)可用函數(shù)

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

注意：

因為分區(qū)函數(shù)不包括FROM_UNIXTIME函數(shù)，所以用時間戳轉(zhuǎn)時間來分區(qū)就無法實現(xiàn)了，只能用date或者datetime來分區(qū)

例如按年我們可以用：

PARTITION BY RANGE (YEAR(date))

按月：

PARTITION BY RANGE(date div 100)  

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

一個訂單做分區(qū)的例子：

CREATE TABLE `order` (
 `order_id` bigint(19) NOT NULL DEFAULT '0' COMMENT '訂單ID：年月日時分秒12位 7位隨機數(shù)',
 `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;

因為我們沒法用時間戳來做按時間分區(qū)，所以添加了一個date字段，這個字段和order_id一起作為主鍵，我們知道分區(qū)的列一定要放到主鍵里面去的。下面我們用date計算成年月組合來分區(qū)

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 設置了最后一個分區(qū)p_catch_all，所以不能用add的方式來添加分區(qū)了，以下語句不可用：
ALTER TABLE order ADD PARTITION (PARTITION p_2014_11 VALUES LESS THAN (201412));

只能把最后的p_catch_all分區(qū)拆分成兩個，這樣還有一個好處就是在p_catch_all分區(qū)的數(shù)據(jù)不會丟失。數(shù)據(jù)的合并與拆分用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
);

合并分區(qū)：

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去？因為會報分區(qū)以存在。

為什么合并的時候要帶上最后一個分區(qū)p_catch_all？因為除了最后一個分區(qū)，其他重組的分區(qū)范圍不能改變總范圍。
刪除分區(qū)但是不刪除數(shù)據(jù)：

alter table 表名 remove partitioning

注意：上面語句在5.5可以執(zhí)行，5.6好像有問題，要先測試一下

分區(qū)之后，where條件是一個范圍的話分區(qū)是不起作用的，如 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表的四種分區(qū)類型

一、什么是表分區(qū)

通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個小表。mysql5.1開始支持數(shù)據(jù)表分區(qū)了。 如：某用戶表的記錄超過了600萬條，那么就可以根據(jù)入庫日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當然也可根據(jù)其他的條件分區(qū)。

二、為什么要對表進行分區(qū)

為了改善大型表以及具有各種訪問模式的表的可伸縮性，可管理性和提高數(shù)據(jù)庫效率。

分區(qū)的一些優(yōu)點包括：

與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。

對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過刪除與那些數(shù)據(jù)有關的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。通常和分區(qū)有關的其他優(yōu)點包括下面列出的這些。MySQL分區(qū)中的這些功能目前還沒有實現(xiàn)，但是在我們的優(yōu)先級列表中，具有高的優(yōu)先級；我們希望在5.1的生產(chǎn)版本中，能包括這些功能。

一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定WHERE語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進行修改，所以在第一次配置分區(qū)方案時還不曾這么做時，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。

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

通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。

三、分區(qū)類型

RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

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

HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負整數(shù)值的任何表達式。

KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

RANGE分區(qū)

基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

這些區(qū)間要連續(xù)且不能相互重疊，使用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)
);

按照這種分區(qū)方案，在商店1到5工作的雇員相對應的所有行被保存在分區(qū)P0中，商店6到10的雇員保存在P1中，依次類推。注意，每個分區(qū)都是按順序進行定義，從最低到最高。這是PARTITION BY RANGE 語法的要求；在這點上，它類似于C或Java中的“switch … case”語句。對于包含數(shù)據(jù)(72, ‘Michael', ‘Widenius', '1998-06-25′, NULL, 13)的一個新行，可以很容易地確定它將插入到p2分區(qū)中，但是如果增加了一個編號為第21的商店，將會發(fā)生什么呢？在這種方案下，由于沒有規(guī)則把store_id大于20的商店包含在內(nèi)，服務器將不知道把該行保存在何處，將會導致錯誤。 要避免這種錯誤，可以通過在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 表示最大的可能的整數(shù)值?，F(xiàn)在，store_id 列值大于或等于16（定義了的最高值）的所有行都將保存在分區(qū)p3中。在將來的某個時候，當商店數(shù)已經(jīng)增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區(qū)。在幾乎一樣的結(jié)構(gòu)中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code 列值的連續(xù)區(qū)間。例如——假定2位數(shù)字的工作代碼用來表示普通（店內(nèi)的）工人，三個數(shù)字代碼表示辦公室和支持人員，四個數(shù)字代碼表示管理層，你可以使用下面的語句創(chuàng)建該分區(qū)表：

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

在這個例子中, 店內(nèi)工人相關的所有行將保存在分區(qū)p0中，辦公室和支持人員相關的所有行保存在分區(qū)p1中，管理層相關的所有行保存在分區(qū)p2中。在VALUES LESS THAN 子句中使用一個表達式也是可能的。這里最值得注意的限制是MySQL 必須能夠計算表達式的返回值作為LESS THAN ()比較的一部分；因此，表達式的值不能為NULL 。由于這個原因，雇員表的hired, separated, job_code,和store_id列已經(jīng)被定義為非空（NOT NULL）。除了可以根據(jù)商店編號分割表數(shù)據(jù)外，你還可以使用一個基于兩個DATE （日期）中的一個的表達式來分割表數(shù)據(jù)。例如，假定你想基于每個雇員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實現(xiàn)這種分區(qū)模式的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年前雇傭的所有雇員的記錄保存在分區(qū)p0中，1991年到1995年期間雇傭的所有雇員的記錄保存在分區(qū)p1中， 1996年到2000年期間雇傭的所有雇員的記錄保存在分區(qū)p2中，2000年后雇傭的所有工人的信息保存在p3中。
RANGE分區(qū)在如下場合特別有用：1）、當需要刪除一個分區(qū)上的“舊的”數(shù)據(jù)時,只刪除分區(qū)即可。如果你使用上面最近的那個例子給出的分區(qū)方案，你只需簡單地使用”ALTER TABLE employees DROP PARTITION p0;”來刪除所有在1991年前就已經(jīng)停止工作的雇員相對應的所有行。對于有大量行的表，這比運行一個如”DELETE FROM employees WHERE YEAR (separated) = 1990;”這樣的一個DELETE查詢要有效得多。 2）、想要使用一個包含有日期或時間值，或包含有從一些其他級數(shù)開始增長的值的列。3）、經(jīng)常運行直接依賴于用于分割表的列的查詢。例如，當執(zhí)行一個如”SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”這樣的查詢時，MySQL可以很迅速地確定只有分區(qū)p2需要掃描，這是因為余下的分區(qū)不可能包含有符合該WHERE子句的任何記錄。

注釋：這種優(yōu)化還沒有在MySQL 5.1源程序中啟用，但是，有關工作正在進行中。

LIST分區(qū)

類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。

LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實現(xiàn)，其中“expr”是某列值或一個基于某個列值、并返回一個整數(shù)值的表達式，然后通過“VALUES IN (value_list)”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(shù)列表。 注釋：在MySQL 5.1中，當使用LIST分區(qū)時，有可能只能匹配整數(shù)列表。

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個有經(jīng)銷權的地區(qū)，如下表所示：

====================
地區(qū) 商店ID 號
北區(qū) 3, 5, 6, 9, 17
東區(qū) 1, 2, 10, 11, 19, 20
西區(qū) 4, 12, 13, 14, 18
中心區(qū) 7, 8, 15, 16
====================

要按照屬于同一個地區(qū)商店的行保存在同一個分區(qū)中的方式來分割表，可以使用下面的“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)
);

這使得在表中增加或刪除指定地區(qū)的雇員記錄變得容易起來。例如，假定西區(qū)的所有音像店都賣給了其他公司。那么與在西區(qū)音像店工作雇員相關的所有記錄（行）可以使用查詢“ALTER TABLE employees DROP PARTITION pWest；”來進行刪除，它與具有同樣作用的DELETE（刪除）查詢“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多?！疽c】：如果試圖插入列值（或分區(qū)表達式的返回值）不在分區(qū)值列表中的一行時，那么“INSERT”查詢將失敗并報錯。例如，假定LIST分區(qū)的采用上面的方案，下面的查詢將失?。?br />

Sql代碼：

INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

這是因為“store_id”列值21不能在用于定義分區(qū)pNorth, pEast, pWest,或pCentral的值列表中找到。要重點注意的是，LIST分區(qū)沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。
LIST分區(qū)除了能和RANGE分區(qū)結(jié)合起來生成一個復合的子分區(qū)，與HASH和KEY分區(qū)結(jié)合起來生成復合的子分區(qū)也是可能的。

HASH分區(qū)

基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負整數(shù)值的任何表達式。

要使用HASH分區(qū)來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數(shù)的表達式。它可以僅僅是字段類型為MySQL整型的一列的名字。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句，其中num是一個非負的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。

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子句，那么分區(qū)的數(shù)量將默認為1。例外：對于NDB Cluster（簇）表，默認的分區(qū)數(shù)量將與簇數(shù)據(jù)節(jié)點的數(shù)量相同，這種修正可能是考慮任何MAX_ROWS設置，以便確保所有的行都能合適地插入到分區(qū)中。

LINER HASH

MySQL還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于，在“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，當使用線性哈希功能時，記錄將要保存到的分區(qū)是num 個分區(qū)中的分區(qū)N，其中N是根據(jù)下面的算法得到： 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，使用線性哈希分區(qū)且有4個分區(qū)，是通過下面的語句創(chuàng)建的： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6; 現(xiàn)在假設要插入兩行記錄到表t1中，其中一條記錄col3列值為'2003-04-14′，另一條記錄col3列值為'1998-10-19′。第一條記錄將要保存到的分區(qū)確定如下： V = POWER(2, CEILING(LOG(2,7))) = 8 N = YEAR('2003-04-14′) (8 – 1) = 2003 7 = 3 (3 >= 6 為假（FALSE）: 記錄將被保存到#3號分區(qū)中) 第二條記錄將要保存到的分區(qū)序號計算如下： 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分區(qū)中) 按照線性哈希分區(qū)的優(yōu)點在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有極其大量（1000吉）數(shù)據(jù)的表。它的缺點在于，與使用常規(guī)HASH分區(qū)得到的數(shù)據(jù)分布相比，各個分區(qū)間數(shù)據(jù)的分布不大可能均衡。

KEY分區(qū)

類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

Sql代碼：

CREATE TABLE tk (
  col1 INT NOT NULL,
  col2 CHAR(5),
  col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

在KEY分區(qū)中使用關鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法

總結(jié)

以上就是本文關于mysql數(shù)據(jù)庫表分區(qū)注意事項的全部內(nèi)容，感興趣的朋友可以參閱：MySQL刪除表數(shù)據(jù)的方法、幾個比較重要的MySQL變量、MYSQL子查詢和嵌套查詢優(yōu)化實例解析等，希望對大家有所幫助。有什么問題請留言，歡迎大家交流討論。