前言
了解分區(qū)表之前需要知道渔彰，我們先來(lái)看一看Mysql對(duì)分區(qū)的解釋?zhuān)旅嬉欢卧?huà)摘自官網(wǎng)嵌屎。
SQL標(biāo)準(zhǔn)不會(huì)特別關(guān)注數(shù)據(jù)的物理存儲(chǔ)，SQL語(yǔ)言本身則傾向獨(dú)立于它所使用的模式恍涂、表宝惰、行或列底層的
任何數(shù)據(jù)結(jié)構(gòu)或存儲(chǔ)媒介。盡管如此再沧，大多數(shù)高級(jí)數(shù)據(jù)庫(kù)管理系統(tǒng)已經(jīng)可以根據(jù)文件系統(tǒng)尼夺、硬件或者同時(shí)
根據(jù)文件系統(tǒng)和硬件來(lái)確定特定數(shù)據(jù)塊的物理位置。MySQL中,InnoDB存儲(chǔ)引擎一直支持表空間(table
space)的概念,甚至引入分區(qū)前MySQL服務(wù)器就支持配置不同的物理目錄用于存儲(chǔ)不同的數(shù)據(jù)庫(kù).
分區(qū)(Partitioning)使這個(gè)概念更進(jìn)了一步炒瘸，它允許根據(jù)實(shí)際需求設(shè)置的規(guī)則將表的各個(gè)部分跨文件
系統(tǒng)打散(實(shí)際上指的是表的水平拆分)淤堵。實(shí)際上，表的不同部分作為單獨(dú)的分區(qū)表存儲(chǔ)在不同的位置顷扩。
用戶(hù)選擇的數(shù)據(jù)劃分規(guī)則被稱(chēng)為分區(qū)函數(shù)拐邪，在MySQL中，它可以是模數(shù)隘截、對(duì)一組范圍或值列表的簡(jiǎn)單匹
配扎阶、內(nèi)部哈希函數(shù)或線(xiàn)性哈希函數(shù)。根據(jù)用戶(hù)指定的分區(qū)類(lèi)型選擇函數(shù)婶芭，并將用戶(hù)提供的表達(dá)式的值作為
其參數(shù)乘陪。這個(gè)表達(dá)式可以是一個(gè)列值、一個(gè)作用于一個(gè)或多個(gè)列值的函數(shù)雕擂，或者一個(gè)或多個(gè)列值的集合啡邑，
這取決于所使用的分區(qū)類(lèi)型。
這里的分區(qū)被稱(chēng)為水平分區(qū)——也就是說(shuō)井赌，一個(gè)表的不同行可能被分配到不同的物理分區(qū)谤逼。MySQL 5.7不
支持垂直分區(qū)(表的不同列被分配到不同的物理分區(qū))。
重要：分區(qū)適用于表的所有數(shù)據(jù)和索引;不能只分區(qū)數(shù)據(jù)而不分區(qū)索引仇穗，反之亦然流部，也不能僅對(duì)表的一部分
進(jìn)行分區(qū).
官網(wǎng)的解釋可以看出，Mysql的分區(qū)是在mysql表空間概念基礎(chǔ)上的升級(jí)纹坐，支持用戶(hù)自定義規(guī)則對(duì)單表進(jìn)行分區(qū)枝冀，分散存儲(chǔ)各分區(qū)表；除此之外耘子，需要注意果漾，Mysql分區(qū)要求同一張表的所有分區(qū)必須采用相同的存儲(chǔ)引擎，這一點(diǎn)上來(lái)看谷誓，分區(qū)表與普通表對(duì)存儲(chǔ)引擎來(lái)說(shuō)沒(méi)有區(qū)別绒障；比如說(shuō)對(duì)表test來(lái)說(shuō)，它的所有分區(qū)表只能采用InnoDB或者M(jìn)yISAM捍歪，不能test分區(qū)表1采用InnoDB户辱，test分區(qū)表2采用MyISAM鸵钝。但是，不同Server或者DB中的不同表的分區(qū)表可以采用不同的存儲(chǔ)引擎庐镐。Mysql僅支持水平分區(qū)(5.7版本)恩商，這是另一個(gè)需要注意的點(diǎn)。圖示如下：
一必逆、分區(qū)優(yōu)點(diǎn)及使用場(chǎng)景
通常情況下痕届，對(duì)于非常大的表，無(wú)法全部放在內(nèi)存中末患，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)研叫，適合進(jìn)行分區(qū)。分區(qū)表有以下幾個(gè)優(yōu)點(diǎn)：
1璧针、數(shù)據(jù)更容易維護(hù)
便于批量處理符合條件的單個(gè)分區(qū)數(shù)據(jù)嚷炉，比如批量刪除、備份探橱、恢復(fù)等申屹；便于對(duì)獨(dú)立單個(gè)分區(qū)進(jìn)行優(yōu)化、檢查隧膏、哗讥、修復(fù)等操作。
2胞枕、數(shù)據(jù)物理隔離杆煞，高效利用物理設(shè)備
分區(qū)表數(shù)據(jù)可以分布在不同的物理設(shè)備上，從而高效利用多個(gè)物理設(shè)備腐泻。
3决乎、有效避免性能瓶頸
可以有效避免性能瓶頸，比如innodb單個(gè)索引的互斥訪(fǎng)問(wèn)派桩、ext3文件系統(tǒng)的inode（linux可以通過(guò)ls -li查看文件的inodeid）鎖競(jìng)爭(zhēng)等构诚。
二、分區(qū)表類(lèi)型
mysql 支持的分區(qū)表類(lèi)型包括：Range Partitioning（范圍分區(qū)）铆惑、List Partitioning（列表分區(qū)）范嘱、Column Partitioning（列分區(qū)、Hash Partitioning（hash分區(qū)）员魏、KEY Partitioning（鍵值分區(qū)）丑蛤、subPartitioning（子分區(qū)），幾種逆趋，下面我們按照這個(gè)順序盏阶，根據(jù)官網(wǎng)的介紹一一進(jìn)行介紹
2.1 Range Partitioning（范圍分區(qū)）
范圍分區(qū)根據(jù)用戶(hù)定義的范圍規(guī)則，將指定列值在范圍內(nèi)的行記錄存儲(chǔ)至同一張分區(qū)表闻书。
例如名斟，原始單表建表語(yǔ)句：
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
);
假設(shè)這里按照列store_id進(jìn)行范圍分區(qū)，sql語(yǔ)句如下：
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)
);
建表完成之后魄眉，查看表：
+----------------+
| Tables_in_test |
+----------------+
| employees |
+----------------+
1 row in set (0.00 sec)
再來(lái)看mysql的數(shù)據(jù)文件變化砰盐，可以在mysql中執(zhí)行下列語(yǔ)句，查看當(dāng)前mysql的數(shù)據(jù)文件存儲(chǔ)位置：
SHOW VARIABLES LIKE 'datadir';
-- mac下Mysql數(shù)據(jù)的默認(rèn)存儲(chǔ)路徑是/usr/local/mysql/data/
-- 查看路徑下數(shù)據(jù)文件坑律，其中test是DB名稱(chēng) sudo ls /usr/local/mysql/data/test
進(jìn)入該目錄岩梳，查看數(shù)據(jù)文件,其中，.ibd文件存儲(chǔ)的是索引與數(shù)據(jù)晃择，.frm文件則存儲(chǔ)表結(jié)構(gòu)及元數(shù)據(jù)冀值。可以看到宫屠，分區(qū)后的employees數(shù)據(jù)文件被一分為四列疗，拆分為4個(gè)ibd文件
回國(guó)過(guò)頭我們?cè)賮?lái)看，如果此時(shí)執(zhí)行insert語(yǔ)句浪蹂，插入一條store_id = 14的數(shù)據(jù)抵栈，那么會(huì)落在p2分區(qū)；那么坤次，如果插入一條sotre_id = 30的數(shù)據(jù)古劲，此時(shí)Mysql會(huì)直接報(bào)錯(cuò)，因?yàn)樵摋l數(shù)據(jù)不滿(mǎn)足當(dāng)前的分區(qū)規(guī)則缰猴，Mysql也不知道這條記錄應(yīng)該放在哪個(gè)分區(qū)产艾。當(dāng)然，Mysql也提供了解決方案滑绒，比如可以新增一個(gè)分區(qū)胰舆，用于存放分區(qū)值小于Integer.MaxValue的store_id對(duì)應(yīng)的記錄
PARTITION p4 VALUES LESS THAN MAXVALUE*
分區(qū)列除了支持int類(lèi)型外，還支持date蹬挤、timestamp
-- 按照int類(lèi)型列分區(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
);
-- 按照date分區(qū)
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN ('1960-01-01'),
PARTITION p1 VALUES LESS THAN ('1970-01-01'),
PARTITION p2 VALUES LESS THAN ('1980-01-01'),
PARTITION p3 VALUES LESS THAN ('1990-01-01'),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-- 按照timestamp分區(qū)
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
2.3 List Partitioning（列表分區(qū)）
列表分區(qū)與范圍分區(qū)非常相似缚窿，不同之處在于，列表分區(qū)是根據(jù)分區(qū)字段值所在的散列值作為分區(qū)的規(guī)則條件焰扳。我們?nèi)砸?.2中的employees表為例：
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
);
假設(shè)現(xiàn)在有20家音像店倦零，分部在東西南北，4個(gè)區(qū)域：
RegionStore ID Numbers：
North3, 5, 6, 9, 17
East1, 2, 10, 11, 19, 20
West4, 12, 13, 14, 18
Central7, 8, 15, 16
對(duì)應(yīng)的分區(qū)建表語(yǔ)句如下：
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)
);
此次吨悍，如果老板要賣(mài)掉東部地區(qū)的音像店扫茅，那么此時(shí)可以直接刪除DB中東部的分區(qū)：
-- 這要比從 DELETE FROM employees WHERE store_id IN (4,12,13,14,18)高效的多
ALTER TABLE employees TRUNCATE PARTITION pWest;
另外，與范圍分區(qū)不同之處在于育瓜，列表分區(qū)沒(méi)有兜底的方案葫隙，若當(dāng)前分區(qū)字段值不在任一個(gè)分區(qū)表達(dá)式中，那么Mysql會(huì)直接報(bào)錯(cuò)：
ERROR 1525 (HY000): Table has no partition for value 3
還有一種情況需要注意躏仇，單條insert語(yǔ)句插入多條記錄時(shí)恋脚，若其中有若干條記錄的分區(qū)字段值與分區(qū)表達(dá)式不匹配腺办，那么此時(shí)Mysql是否會(huì)報(bào)錯(cuò)取決于當(dāng)前的存儲(chǔ)引擎是否支持事務(wù)。若使用Innodb糟描，那么insert會(huì)被當(dāng)做一個(gè)事務(wù)怀喉，此時(shí)失敗事務(wù)回滾，所有記錄都不會(huì)插入成功船响；若使用的是MyISAM躬拢，那么在失敗的記錄之前的記錄可以插入成功，之后的記錄不會(huì)成功见间。當(dāng)然聊闯，Mysql提供了這種問(wèn)題的規(guī)避機(jī)制，即IGNORE關(guān)鍵字米诉，此時(shí)與分區(qū)表達(dá)式不匹配的記錄不會(huì)插入菱蔬，而其他記錄會(huì)成功插入，且不會(huì)報(bào)錯(cuò)荒辕。
INSERT IGNORE INTO employees VALUES (2,'1970-01-01','1970-01-01',1, 35), (2,'1970-01-01','1970-01-01',1, 15);
2.3 Column Partitioning（列分區(qū)）
2.4 Hash Partitioning（hash分區(qū)）
2.5 KEY Partitioning（鍵值分區(qū)）
2.6 subPartitioning（子分區(qū)）
三汗销、優(yōu)點(diǎn)
四、分區(qū)表原理
五抵窒、分區(qū)表維護(hù)
六弛针、注意事項(xiàng)
1、單張表最多只能有1024個(gè)分區(qū)李皇，5.7版本以后可以支持8192個(gè)分區(qū)削茁，這與系統(tǒng)文件；
2掉房、如果分區(qū)字段中有主鍵或者唯一索引的列茧跋，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)，比如
參考文檔：https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
