MYSQL在創(chuàng)建表時使用PATITION BY子句定義每個分區(qū)存放的數(shù)據(jù)。在執(zhí)行查詢時研侣，優(yōu)化器會過濾那些沒有我們需要數(shù)據(jù)的分區(qū)
分區(qū)的一個主要目的是將數(shù)據(jù)按照一個較粗的粒度分在不同的表中，這樣無論是查詢還是批量刪除整個分區(qū)的數(shù)據(jù)都很方便
下面的場景中莺掠，分區(qū)可以起到非常大的作用：

• 表非常大筛欢，無法全部存放在內(nèi)存中。
• 表的最后部分有熱點(diǎn)數(shù)據(jù)榨咐，其他都是歷史數(shù)據(jù)。
• 分區(qū)表更容易維護(hù)谴供】樽拢可以單獨(dú)對一個獨(dú)立分區(qū)進(jìn)行優(yōu)化、查詢桂肌、刪除数焊、修復(fù)。
• 分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上崎场。
• 可以使用分區(qū)表避免某些特殊的瓶頸佩耳，例如innoDB的單個索引互斥訪問

分區(qū)表也有一些限制

• 一個表最多有1024個分區(qū)
• 如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來谭跨。
• 分區(qū)表中無法使用外鍵約束

分區(qū)表原理

SELECT

當(dāng)查詢一個分區(qū)表時干厚，分區(qū)層先打開并鎖住所有的底層表李滴，優(yōu)化器先判斷是否可以過濾部分分區(qū)，然后再調(diào)用對應(yīng)的存儲引擎接口訪問各個分區(qū)的數(shù)據(jù)

INSERT

當(dāng)寫入一條記錄時蛮瞄，分區(qū)層先打開并鎖住所有的底層表所坯，然后確定哪個分區(qū)接收這條記錄，再將記錄寫入對應(yīng)的底層表

DELETE

當(dāng)刪除一條記錄時挂捅，分區(qū)層先打開并鎖住所有的底層表芹助，然后確定哪個分區(qū)接收這條記錄，再將記錄寫入對應(yīng)底層表

UPDATE

當(dāng)更新一條記錄時闲先，分區(qū)層先打開并鎖住所有的底層表状土，MYSQL先確定需要更新的記錄在哪個分區(qū)，然后取出數(shù)據(jù)并更新饵蒂，再判斷更新后的數(shù)據(jù)再哪個分區(qū)声诸，最后對底層表進(jìn)行寫入操作酱讶，并對原數(shù)據(jù)所在的底層進(jìn)行刪除操作

分區(qū)表的類型

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

• LIST分區(qū)：基于屬于一個給定離散區(qū)間的列值泻肯，把多行分配給分區(qū)渊迁。

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

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

range分區(qū)

最常用的分區(qū)惕医，下面是range分區(qū)的應(yīng)用實(shí)例

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ù)值「現(xiàn)在，store_id 列值大于或等于16（定義了的最高值）的所有行都將保存在分區(qū)p3中抬伺。在將來的某個時候螟够，當(dāng)商店數(shù)已經(jīng)增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區(qū)峡钓。

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拉鹃、當(dāng)需要刪除一個分區(qū)上的“舊的”數(shù)據(jù)時,只刪除分區(qū)即可淆九。如果你使用上面最近的那個例子給出的分區(qū)方案统锤，你只需簡單地使用ALTER TABLE employees DROP PARTITION p0來刪除所有在1991年前就已經(jīng)停止工作的雇員相對應(yīng)的所有行。對于有大量行的表炭庙，這比運(yùn)行一個如DELETE FROM employees WHERE YEAR (separated) <= 1990這樣的一個DELETE查詢要有效得多饲窿。
2、想要使用一個包含有日期或時間值焕蹄，或包含有從一些其他級數(shù)開始增長的值的列逾雄。
3、經(jīng)常運(yùn)行直接依賴于用于分割表的列的查詢腻脏。例如鸦泳，當(dāng)執(zhí)行一個如SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id這樣的查詢時，MySQL可以很迅速地確定只有分區(qū)p2需要掃描永品，這是因為余下的分區(qū)不可能包含有符合該WHERE子句的任何記錄做鹰。

LIST分區(qū)

假定有20個音像店，分布在4個有經(jīng)銷權(quán)的地區(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”語句：
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ū)音像店工作雇員相關(guān)的所有記錄（行）可以使用查詢ALTER TABLE employees DROP PARTITION pWest來進(jìn)行刪除，它與具有同樣作用的DELETE（刪除）查詢DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)比起來献宫，要有效得多钥平。
【要點(diǎn)】：如果試圖插入列值（或分區(qū)表達(dá)式的返回值）不在分區(qū)值列表中的一行時，那么“INSERT”查詢將失敗并報錯姊途。例如涉瘾，假定LIST分區(qū)的采用上面的方案，下面的查詢將失斀堇肌：
INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21)
這是因為“store_id”列值21不能在用于定義分區(qū)pNorth, pEast, pWest,或pCentral的值列表中找到立叛。要重點(diǎn)注意的是，LIST分區(qū)沒有類似如VALUES LESS THAN MAXVALUE這樣的包含其他值在內(nèi)的定義寂殉。將要匹配的任何值都必須在值列表中找到囚巴。

下面兩種分區(qū)基本用不上，看不看隨意了友扰。

HASH分區(qū)

基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)彤叉，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個函數(shù)可以包含MySQL 中有效的村怪、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式秽浇。
要使用HASH分區(qū)來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句甚负，其中“expr”是一個返回一個整數(shù)的表達(dá)式柬焕。它可以僅僅是字段類型為MySQL整型的一列的名字审残。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句斑举，其中num是一個非負(fù)的整數(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ù)量將默認(rèn)為1璧坟。例外：對于NDB Cluster（簇）表，默認(rèn)的分區(qū)數(shù)量將與簇數(shù)據(jù)節(jié)點(diǎn)的數(shù)量相同赎懦，這種修正可能是考慮任何MAX_ROWS設(shè)置雀鹃，以便確保所有的行都能合適地插入到分區(qū)中。

LINER HASH
MySQL還支持線性哈希功能励两，它與常規(guī)哈希的區(qū)別在于黎茎，線性哈希功能使用的一個線性的2的冪（powers-of-two）運(yùn)算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)当悔。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于傅瞻，在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字。
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;
假設(shè)一個表達(dá)式expr先鱼，當(dāng)使用線性哈希功能時俭正，記錄將要保存到的分區(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. 設(shè)置 N = F(column_list) & (V – 1). 4. 當(dāng) N >= num: · 設(shè)置 V = CEIL(V / 2) · 設(shè)置 N = N & (V – 1) 例如留搔，假設(shè)表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)在假設(shè)要插入兩行記錄到表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)點(diǎn)在于增加括眠、刪除彪标、合并和拆分分區(qū)將變得更加快捷，有利于處理含有極其大量（1000吉）數(shù)據(jù)的表掷豺。它的缺點(diǎn)在于捞烟，與使用常規(guī)HASH分區(qū)得到的數(shù)據(jù)分布相比薄声，各個分區(qū)間數(shù)據(jù)的分布不大可能均衡。

KSY分區(qū)

類似于按HASH分區(qū)题画，區(qū)別在于KEY分區(qū)只支持計算一列或多列默辨，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值苍息。
Sql代碼：
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
在KEY分區(qū)中使用關(guān)鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用廓奕，分區(qū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法档叔。

使用分區(qū)表時的注意事項

• NULL值會使分區(qū)過濾無效

有如下分區(qū)表
CREATE TABLE sales (
order_date DATE,
...
)
ENGINE=InnoDB PARTTITION BY RANGE(YEAR(order_date)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE );
假設(shè)存在order_date為NULL的記錄桌粉，那么這些記錄會被存放到第一個分區(qū)。下面的查詢where order_date between '2012-01-01' and '2012-01-31'會檢查兩個分區(qū)p_2010和p_2012衙四，而不是一個铃肯。檢查第一個分區(qū)是因為YEAR()函數(shù)在接收非法值的時候可能會返回NULL值，那么這個范圍的值可能會返回NULL而被放到第一分區(qū)了

• 分區(qū)列和索引列不匹配
  如果索引列和分區(qū)列不匹配传蹈，則無法進(jìn)行分區(qū)過濾押逼。假設(shè)在列a上定義了索引，而在列b上進(jìn)行分區(qū)惦界。因為每個分區(qū)都有其獨(dú)立的索引挑格，所以掃描列b上的索引就需要掃描每一個分區(qū)內(nèi)對應(yīng)的索引。

• 分區(qū)的成本可能很高

一圖流

image.png

查詢優(yōu)化

分區(qū)最大的優(yōu)點(diǎn)是優(yōu)化器可以根據(jù)分區(qū)函數(shù)來過濾一些分區(qū)沾歪，在where中帶入分區(qū)列漂彤，即使看似多余也要帶上，這樣可以讓優(yōu)化器過濾無須訪問的分區(qū)
使用explain partitions可以觀察優(yōu)化器是否執(zhí)行了分區(qū)過濾

有如下分區(qū)表
mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) ,
PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) ,
PARTITION p6 VALUES LESS THAN (2001)
PARTITION p7 VALUES LESS THAN (2002) ,
PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) ,
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );

沒有使用分區(qū)過濾

上面的查詢訪問了所有分區(qū)灾搏。下面的查詢會使用分區(qū)過濾

使用分區(qū)過濾

優(yōu)化器并不是萬能的挫望，下面的查詢where條件不能過濾分區(qū)

無法過濾分區(qū)

mysql只能在使用分區(qū)函數(shù)的列本身進(jìn)行比較時才能過濾分區(qū)，而不能根據(jù)表達(dá)式的值去過濾分區(qū)狂窑。所以只需要把上面的查詢等價地改寫成下面形式即可

一個很重要的原則是：
即使在創(chuàng)建時可以使用表達(dá)式媳板，但在查詢時卻只能根據(jù)列來過濾分區(qū)。