Mysql在創(chuàng)建表時(shí)使用partition by子句定義每個(gè)分區(qū)存放的數(shù)據(jù)轴合，將數(shù)據(jù)安裝一個(gè)較粗的刻度分在不同的表中。這樣做可以將相關(guān)的樹(shù)存放在一起独柑，另外如果想一次批量刪除整個(gè)分區(qū)的數(shù)據(jù)也將變的方便盈厘。

使用場(chǎng)景：

1、 表非常大以至于無(wú)法將全部的數(shù)據(jù)放在內(nèi)存中或者表中的最后部分的數(shù)據(jù)才是熱數(shù)據(jù)其他都是歷史數(shù)據(jù)盗胀；

2艘蹋、 分區(qū)表的數(shù)據(jù)可以放在不同的物理機(jī)上，更好的利用多個(gè)硬件設(shè)備票灰；

3女阀、 使用分區(qū)表來(lái)比年某些特殊的瓶頸，如innoDB的單個(gè)索引的互斥訪問(wèn)屑迂；

優(yōu)勢(shì)：

1浸策、與單個(gè)磁盤(pán)或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)惹盼。

2的榛、對(duì)于那些已經(jīng)失去保存意義的數(shù)據(jù)，通陈呷瘢可以通過(guò)刪除與那些數(shù)據(jù)有關(guān)的分區(qū)夫晌，很容易地刪除那些數(shù)據(jù)。相反地昧诱，在某些情況下晓淀，添加新數(shù)據(jù)的過(guò)程又可以通過(guò)為那些新數(shù)據(jù)專門增加一個(gè)新的分區(qū)，來(lái)很方便地實(shí)現(xiàn)盏档。通常和分區(qū)有關(guān)的其他優(yōu)點(diǎn)包括下面列出的這些凶掰。MySQL分區(qū)中的這些功能目前還沒(méi)有實(shí)現(xiàn)，但是在我們的優(yōu)先級(jí)列表中蜈亩，具有高的優(yōu)先級(jí)懦窘；

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

4臊岸、涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢橙数，可以很容易地進(jìn)行并行處理。這種查詢的一個(gè)簡(jiǎn)單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales

GROUP BY salesperson_id帅戒；”灯帮。通過(guò)“并行”，這意味著該查詢可以在每個(gè)分區(qū)上同時(shí)進(jìn)行逻住，最終結(jié)果只需通過(guò)總計(jì)所有分區(qū)得到的結(jié)果钟哥。

5、通過(guò)跨多個(gè)磁盤(pán)來(lái)分散數(shù)據(jù)查詢鄙信，來(lái)獲得更大的查詢吞吐量瞪醋。

分區(qū)表的原理

數(shù)據(jù)庫(kù)表分區(qū)把一個(gè)大的物理表分成若干個(gè)小的物理表忿晕，并使得這些小物理表在邏輯上可以被當(dāng)成一張表來(lái)使用装诡。

Select操作：想要充分的返回分區(qū)的優(yōu)勢(shì)，查詢的條件一定要有分區(qū)的字段践盼，這邊優(yōu)化器才可以過(guò)濾不需要的部分分區(qū)鸦采；

Insert操作和delete操作：分區(qū)層先打開(kāi)所有的底層表，確認(rèn)那個(gè)分區(qū)接收咕幻，在將記錄寫(xiě)入底層表渔伯；

Update操作：分區(qū)層先打開(kāi)所有的底層表，確認(rèn)那個(gè)分區(qū)接收肄程，將數(shù)據(jù)拿出更新锣吼，然后確認(rèn)是那個(gè)分區(qū)，將數(shù)據(jù)寫(xiě)入蓝厌，原數(shù)據(jù)刪除操作玄叠；

雖然每一個(gè)操作很多回先打開(kāi)鎖住所有的底層表，但這并不代表在處理過(guò)程中是鎖住全表的拓提。如果存儲(chǔ)引擎能夠自己實(shí)現(xiàn)行級(jí)鎖（innoDB）读恃，則會(huì)在分區(qū)層釋放對(duì)應(yīng)的表鎖，所以建議使用類似innodb的引擎代态；

分區(qū)表的類型

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

2LIST分區(qū)：類似于按RANGE分區(qū)蹦疑，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇西雀。

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

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

RANGE分區(qū)

基于屬于一個(gè)給定連續(xù)區(qū)間的列值帮哈，把多行分配給分區(qū)膛檀；使用values less than操作符來(lái)定義；

Sql如下：

CREATE TABLE employees (

???id INT NOT NULL,

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

);

這邊以員工表中的門店id為分區(qū)依據(jù)：p0為store_id小于6的數(shù)據(jù)娘侍，那store_id大于20會(huì)如何咖刃？

直接報(bào)錯(cuò)，為了避免上圖的問(wèn)題憾筏，可以在建表的時(shí)候加上 values less

than maxvalue,將最大的值放入另外一張表嚎杨；

Sql如下：

CREATE TABLE employees (

???id INT NOT NULL,

???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)，

partition p4values less than maxvalue;

);

這邊的話氧腰，當(dāng)store_id大于21的時(shí)候枫浙，數(shù)據(jù)就會(huì)加入到p4中；這樣來(lái)看古拴，range有點(diǎn)類似與java當(dāng)中的SWTICH case箩帚；

List分區(qū)

類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇黄痪。

LIST分區(qū)通過(guò)使用“PARTITION

BY LIST(expr)”來(lái)實(shí)現(xiàn)紧帕，其中“expr”是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式桅打，然后通過(guò)“VALUES IN (value_list)”的方式來(lái)定義每個(gè)分區(qū)是嗜，其中“value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(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 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)

)

假定有20個(gè)音像店挺尾，分布在4個(gè)有經(jīng)銷權(quán)的地區(qū)鹅搪，如下表所示：

地區(qū)?????商店ID 號(hào)

北區(qū)3, 5, 6, 9, 17

東區(qū)1, 2, 10, 11, 19, 20

西區(qū)4, 12, 13, 14, 18

中心區(qū)7, 8,15, 16

這樣的話，會(huì)根據(jù)插入的store_id潦嘶，插入到對(duì)應(yīng)的分區(qū)涩嚣，指得注意的是如果插入了store_id=21的，將會(huì)報(bào)錯(cuò)掂僵，也沒(méi)有類似range的 less than maxvalue的功能航厚；

HASH分區(qū)

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

要使用HASH分區(qū)來(lái)分割一個(gè)表芹扭，要在CREATE TABLE 語(yǔ)句上添加一個(gè)“PARTITION BY HASH (expr)”子句麻顶，其中“expr”是一個(gè)返回一個(gè)整數(shù)的表達(dá)式赦抖。它可以僅僅是字段類型為MySQL整型的一列的名字。此外辅肾，你很可能需要在后面再添加一個(gè)“PARTITIONS

num”子句队萤，其中num是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量矫钓。

Sql代碼：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULLDEFAULT '1970-01-01',

??? separated DATE NOT NULLDEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

)

PARTITION BY HASH(store_id)

PARTITIONS 4;

如果沒(méi)有包括一個(gè)PARTITIONS子句要尔，那么分區(qū)的數(shù)量將默認(rèn)為1。例外：對(duì)于NDB Cluster（簇）表新娜，默認(rèn)的分區(qū)數(shù)量將與簇?cái)?shù)據(jù)節(jié)點(diǎn)的數(shù)量相同赵辕，這種修正可能是考慮任何MAX_ROWS設(shè)置，以便確保所有的行都能合適地插入到分區(qū)中概龄。

LINER HASH

MySQL還支持線性哈希功能还惠，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個(gè)線性的2的冪（powers-of-two）運(yùn)算法則私杜，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)蚕键。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語(yǔ)法上的唯一區(qū)別在于，在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字歪今。

Sql代碼：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULLDEFAULT '1970-01-01',

??? separated DATE NOT NULLDEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

)

PARTITION BY LINEAR HASH(YEAR(hired))

PARTITIONS 4;

1. 它的優(yōu)點(diǎn)是在數(shù)據(jù)量大的場(chǎng)景嚎幸，譬如TB級(jí)颜矿，增加寄猩、刪除、合并和拆分分區(qū)會(huì)更快骑疆，缺點(diǎn)是田篇，相對(duì)于HASH分區(qū)，它數(shù)據(jù)分布不均勻的概率更大箍铭。

KSY分區(qū)

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

總結(jié)：

1.? ? MySQL分區(qū)中如果存在主鍵或唯一鍵，則分區(qū)列必須包含在其中冷守。優(yōu)化器會(huì)根據(jù)分區(qū)函數(shù)來(lái)過(guò)濾一些分區(qū)刀崖，通過(guò)過(guò)濾分區(qū)讓查詢掃描的數(shù)據(jù)變少；如果沒(méi)有的話就有訪問(wèn)全表拍摇；

可以使用explain partitions select *from ** 來(lái)知道sql語(yǔ)句是否用了分區(qū)亮钦，如下圖：

2. 對(duì)于原生的RANGE分區(qū)，LIST分區(qū)充活，HASH分區(qū)蜂莉，分區(qū)對(duì)象返回的只能是整數(shù)值蜡娶。

3. RANGE COLUMNS，LIST COLUMNS映穗，KEY窖张，LINEAR KEY分區(qū)對(duì)象只能是列，不能是基于列的表達(dá)式蚁滋。

4.分區(qū)的值為null值會(huì)是分區(qū)過(guò)濾無(wú)效荤堪，記錄會(huì)放在第一個(gè)分區(qū)；建議創(chuàng)建第一個(gè)分區(qū)為無(wú)效分區(qū)枢赔，例如創(chuàng)建一個(gè)partition by range（col）(partition p0 less than 0,**),達(dá)到第一個(gè)無(wú)法過(guò)濾的數(shù)據(jù)很小;

查詢優(yōu)化

1澄阳、 查詢的時(shí)候，where一定要帶上分區(qū)列踏拜，且只能是列本身（和獨(dú)立索引相似）碎赢，如果是列的函數(shù)，優(yōu)化器將過(guò)濾不出分區(qū)：如下圖

2速梗、分區(qū)表關(guān)聯(lián)操作中的第二張表肮塞，關(guān)聯(lián)條件需要是分區(qū)鍵，這樣的話mysql會(huì)在對(duì)應(yīng)的分區(qū)里面匹配行姻锁；

問(wèn)題

1枕赵、實(shí)際生產(chǎn)環(huán)境下，分區(qū)需要實(shí)時(shí)的增加位隶，如使用range類型的拷窜，按時(shí)間分區(qū)；這樣的話必然導(dǎo)致分區(qū)越來(lái)越多（按官方上說(shuō)mysql支持1024個(gè)分區(qū)）涧黄，或者不增加分區(qū)的話（maxvalue的數(shù)據(jù)越來(lái)越多）篮昧，所以個(gè)人的想法是：

每一段時(shí)間通過(guò)語(yǔ)句：-- 刪除分區(qū)

alter table employees REMOVE PARTITIONING ;刪除分區(qū)，數(shù)據(jù)不會(huì)刪除

然后在去創(chuàng)建分區(qū)笋妥，保證熱點(diǎn)（最近）數(shù)據(jù)在分區(qū)里面懊昨；語(yǔ)句為：

#新增分區(qū)

ALTER TABLE 表名 add PARTITION (PARTITION 分區(qū)名 VALUES LESS THAN (規(guī)則));

各位有什么好的想法？

?

最后數(shù)據(jù)大于千萬(wàn)級(jí)春宣，單表優(yōu)化之后還是慢酵颁？可以適當(dāng)考慮分表分庫(kù)操作，隨之帶來(lái)的是邏輯的直線上升月帝；