什么是分區(qū)表
MySQL從5.1版本開(kāi)始支持分區(qū)功能，分區(qū)是將一個(gè)表的數(shù)據(jù)按照某種方式，比如按照時(shí)間上的月份，分成多個(gè)較小的，更容易管理的部分，但是邏輯上仍是一個(gè)表。
還沒(méi)出現(xiàn)分區(qū)表的時(shí)候，所有的數(shù)據(jù)都是存放在一個(gè)文件里面的，如果數(shù)據(jù)量太大，查詢數(shù)據(jù)時(shí)總是避免不了需要大量io操作；使用分區(qū)表后，每個(gè)分區(qū)存放不同的數(shù)據(jù)。這樣不但可以減少io。還可以加快數(shù)據(jù)的訪問(wèn)；
為了保證MySQL的性能，我們都建議mysql單表不要太大，建議是：?jiǎn)伪硇∮?G，記錄數(shù)小于1千萬(wàn)，十庫(kù)百表。如果但行記錄數(shù)非常小，那么記錄數(shù)可以再偏大些，反之，可能記錄數(shù)到百萬(wàn)級(jí)別就開(kāi)始變慢了。
那么，業(yè)務(wù)量在增長(zhǎng)，數(shù)據(jù)到瓶頸了怎么辦呢，除了使用分布式數(shù)據(jù)庫(kù)，我們也可以自行分庫(kù)分表，或者利用mysql的分區(qū)功能實(shí)現(xiàn)。
分區(qū)表的出現(xiàn)是為了分而治之的概念，分區(qū)表的用處非常大，只是現(xiàn)在還有很多人都不知道；
將一個(gè)表設(shè)置為分區(qū)表后，會(huì)在數(shù)據(jù)文件.idb的文件名加上#號(hào)，代表這是一個(gè)分區(qū)表；
分區(qū)表應(yīng)用場(chǎng)景
- 表非常大以至于無(wú)法全部放在內(nèi)存中，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)，其他都是歷史數(shù)據(jù)
- 分區(qū)表的數(shù)據(jù)更容易維護(hù)，，能批量刪除大量數(shù)據(jù)
- 對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作
- 分區(qū)表的數(shù)據(jù)可以分布在不同的設(shè)備上，從未高效的利用多個(gè)硬件設(shè)備
- 可以備份和恢復(fù)獨(dú)立的分區(qū)；
分區(qū)表的限制
- 一個(gè)表最多能有1024個(gè)分區(qū)，在5.7版本及以上可以有8196個(gè)分區(qū)
- 在早期mysql中，分區(qū)表達(dá)式必須是整數(shù)或者整返回整數(shù)的表達(dá)式，在mysql5.5中，某些場(chǎng)景可以直接使用列來(lái)進(jìn)行分區(qū)
- 分區(qū)表無(wú)法使用外檢約束
- 最好不要去修改分區(qū)列
- 如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)；就像這樣：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
-- 創(chuàng)建分區(qū)必須包含所有主鍵create table user_11( id bigint(20) not null , name varchar(20) , age int(3), PRIMARY KEY (`id`,`age`))-- 創(chuàng)建分區(qū)partition by range columns(id,age)( partition p00 values less than(6,30), -- 小于6的值在P0分區(qū) partition p11 values less than(11,40), -- 小于11的值在p1分區(qū) partition p22 values less than(16,50), -- 小于16的值在p2分區(qū) partition p33 values less than (9999,9999) -- 大于21的值在p3分區(qū)，或者用一個(gè)更大的值);-- 創(chuàng)建分區(qū)必須包含所有唯一鍵create table user_22( id bigint(20) not null, name varchar(20) , age int(3) not null , unique key only_one_1(age,id ))-- 創(chuàng)建分區(qū)partition by range columns(id,age)( partition p000 values less than(6,30), -- 小于6的值在P0分區(qū) partition p111 values less than(11,40), -- 小于11的值在p1分區(qū) partition p222 values less than(16,50), -- 小于16的值在p2分區(qū) partition p333 values less than (9999,9999) -- 大于21的值在p3分區(qū)，或者用一個(gè)更大的值); |
分區(qū)類(lèi)型
- 范圍分區(qū)
- 列表分區(qū)
- 列分區(qū)
- hash分區(qū)
- 秘鑰分區(qū)
- 子分區(qū)
分區(qū)表的使用
1、范圍分區(qū)
下面示例中將年齡進(jìn)行分區(qū)，
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create table employees(id bigint(20) not null,age int(3) not null,name varchar(20))-- 創(chuàng)建分區(qū)partition by range (age)( partition p0 values less than(6), -- 小于6的值在P0分區(qū) partition p1 values less than(11), -- 小于11的值在p1分區(qū) partition p2 values less than(16), -- 小于16的值在p2分區(qū) partition p3 values less than(21) -- 小于21的值在p3分區(qū)); |
創(chuàng)建好之后，就可以看到在數(shù)據(jù)文件夾中的分區(qū)文件了
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@VM_0_5_centos test]# pwd/var/lib/mysql/test[root@VM_0_5_centos test]# ll總用量 8741504-rw-rw---- 1 mysql mysql 61 10月 31 2018 db.opt-rw-rw---- 1 mysql mysql 8614 8月 1 21:30 employees.frm-rw-rw---- 1 mysql mysql 32 8月 1 21:30 employees.par-rw-rw---- 1 mysql mysql 98304 8月 1 21:30 employees#P#p0.ibd-rw-rw---- 1 mysql mysql 98304 8月 1 21:30 employees#P#p1.ibd-rw-rw---- 1 mysql mysql 98304 8月 1 21:30 employees#P#p2.ibd-rw-rw---- 1 mysql mysql 98304 8月 1 21:30 employees#P#p3.ibd |
因?yàn)閍ge字段最大只能插入21以下的數(shù)字，如果插入21的數(shù)字則會(huì)報(bào)錯(cuò)，
|
1
2
|
mysql> insert employees (id,name,age) values(1,'yexindong',21);ERROR 1526 (HY000): Table has no partition for value 21 |
所以，為了解決這個(gè)問(wèn)題，在建表的時(shí)候可以這么干，將最大的值使用maxvalue，據(jù)說(shuō)maxvalue的值為28個(gè)9，也就是9999999999999999999999999999
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create table employees(id bigint(20) not null,age int(3) not null,name varchar(20))-- 創(chuàng)建分區(qū)partition by range (age)( partition p0 values less than(6), -- 小于6的值在P0分區(qū) partition p1 values less than(11), -- 小于11的值在p1分區(qū) partition p2 values less than(16), -- 小于16的值在p2分區(qū) partition p3 values less than maxvalue -- 大于16的值在p3分區(qū)，或者用一個(gè)更大的值); |
時(shí)間范圍分區(qū)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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), -- 1991年之前的數(shù)據(jù)在P0分區(qū) PARTITION p1 VALUES LESS THAN (1996),-- 1996年之前的數(shù)據(jù)在P1分區(qū) PARTITION p2 VALUES LESS THAN (2001),-- 2001年之前的數(shù)據(jù)在P2分區(qū) PARTITION p3 VALUES LESS THAN MAXVALUE -- 2001年制后的數(shù)據(jù)在P3分區(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); |
2、列表分區(qū)（list分區(qū)）
列表分區(qū)和范圍分區(qū)最大的區(qū)別就是列表是等值的，而范圍分區(qū)是在某個(gè)范圍內(nèi)的；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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), -- 3,5,6,9,17的值放在pNorth分區(qū) PARTITION pEast VALUES IN (1,2,10,11,19,20),-- 1,2,10,11,19,20的值放在pEast分區(qū) PARTITION pWest VALUES IN (4,12,13,14,18),-- 4,12,13,14,18的值放在pWest分區(qū) PARTITION pCentral VALUES IN (7,8,15,16)-- 7,8,15,16的值放在pCentral分區(qū)); |
3、列分區(qū)
列分區(qū)是范圍分區(qū)和列表分區(qū)的變體，也就是說(shuō)列分區(qū)就是由范圍分區(qū)和列表分區(qū)封裝得來(lái)的，唯一的不同的是，列分區(qū)沒(méi)有數(shù)據(jù)類(lèi)型的限制，換句話說(shuō)，范圍分區(qū)和列表分區(qū)就是列分區(qū)；
4、hash分區(qū)
hash分區(qū)不需要指定范圍或者列表，而是根據(jù)插入的值動(dòng)態(tài)分配來(lái)決定插入到哪個(gè)分區(qū)，和hashMap的原理很像，不同的是hashMap會(huì)通過(guò)擾動(dòng)函數(shù)來(lái)解決hash碰撞問(wèn)題，但是mysql的hash分區(qū)是直接取模運(yùn)算得出結(jié)果；然后插入指定位置的分區(qū)；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
-- 普通字段的分區(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 HASH(store_id)PARTITIONS 5;--創(chuàng)建5個(gè)分區(qū)，分別是0，1，2，3，4-- 創(chuàng)建時(shí)間類(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 HASH( YEAR(hired) )PARTITIONS 4; -- 創(chuàng)建四個(gè)分區(qū)，分別為0，1，2，3 |
5、秘鑰分區(qū)（key分區(qū)）
key分區(qū)用的比較少
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
-- 以主鍵進(jìn)行分區(qū)CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20))PARTITION BY KEY()PARTITIONS 2; -- 創(chuàng)建2個(gè)分區(qū)分別為P0和P1，這里是hash分區(qū)的變種，存儲(chǔ)方式和hash分區(qū)一樣-- 以唯一鍵進(jìn)行分區(qū)CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id))PARTITION BY KEY()PARTITIONS 3;-- 創(chuàng)建三個(gè)分區(qū)，分別是p0，p1，p2-- 指定主鍵字段進(jìn)行分區(qū)CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY)PARTITION BY KEY(s1)PARTITIONS 10; -- 創(chuàng)建10個(gè)分區(qū) |
6、子分區(qū)
子分區(qū)這么理解就行了：在分區(qū)的基礎(chǔ)上在分區(qū)；舉個(gè)例子吧，如果一張表分成三個(gè)分區(qū)，而每個(gè)分區(qū)又有三個(gè)子分區(qū)，所以一共有3 * 3 = 9個(gè)分區(qū)；
|
1
2
3
4
5
6
7
8
9
|
-- 表中有3個(gè)分區(qū)，每個(gè)分區(qū)上有2個(gè)子分區(qū)，所以加起來(lái)一共有6個(gè)分區(qū)CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ); |
進(jìn)入mysql的數(shù)據(jù)文件中就可以看到有6個(gè)文件,顧名思義，生成了6個(gè)分區(qū)
|
1
2
3
4
5
6
|
-rw-rw---- 1 mysql mysql 98304 8月 2 22:37 ts#P#p0#SP#p0sp0.ibd-rw-rw---- 1 mysql mysql 98304 8月 2 22:37 ts#P#p0#SP#p0sp1.ibd-rw-rw---- 1 mysql mysql 98304 8月 2 22:37 ts#P#p1#SP#p1sp0.ibd-rw-rw---- 1 mysql mysql 98304 8月 2 22:37 ts#P#p1#SP#p1sp1.ibd-rw-rw---- 1 mysql mysql 98304 8月 2 22:37 ts#P#p2#SP#p2sp0.ibd-rw-rw---- 1 mysql mysql 98304 8月 2 22:37 ts#P#p2#SP#p2sp1.ibd |
添加分區(qū)
|
1
2
|
-- 添加列表分區(qū)alter table titles add partition(partition p7 values in('CEO')); |
分區(qū)表原理
分區(qū)表由多個(gè)相關(guān)的底層表實(shí)現(xiàn)，這個(gè)底層表也是由句柄對(duì)象標(biāo)識(shí)，我們可以直接訪問(wèn)各個(gè)分區(qū)。存儲(chǔ)引擎管理分區(qū)的各個(gè)底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲(chǔ)引擎），分區(qū)表的索引知識(shí)在各個(gè)底層表上各自加上一個(gè)完全相同的索引。從存儲(chǔ)引擎的角度來(lái)看，底層表和普通表沒(méi)有任何不同，存儲(chǔ)引擎也無(wú)須知道這是一個(gè)普通表還是一個(gè)分區(qū)表的一部分。
分區(qū)表的操作按照以下的操作邏輯進(jìn)行：
select查詢
當(dāng)查詢一個(gè)分區(qū)表的時(shí)候，分區(qū)層先打開(kāi)并鎖住所有的底層表，優(yōu)化器先判斷是否可以過(guò)濾部分分區(qū)，然后再調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎接口訪問(wèn)各個(gè)分區(qū)的數(shù)據(jù)
insert操作
當(dāng)寫(xiě)入一條記錄的時(shí)候，分區(qū)層先打開(kāi)并鎖住所有的底層表，然后確定哪個(gè)分區(qū)接受這條記錄，再將記錄寫(xiě)入對(duì)應(yīng)底層表
delete操作
當(dāng)刪除一條記錄時(shí)，分區(qū)層先打開(kāi)并鎖住所有的底層表，然后確定數(shù)據(jù)對(duì)應(yīng)的分區(qū)，最后對(duì)相應(yīng)底層表進(jìn)行刪除操作
update操作
當(dāng)更新一條記錄時(shí)，分區(qū)層先打開(kāi)并鎖住所有的底層表，mysql先確定需要更新的記錄再哪個(gè)分區(qū)，然后取出數(shù)據(jù)并更新，再判斷更新后的數(shù)據(jù)應(yīng)該再哪個(gè)分區(qū)，最后對(duì)底層表進(jìn)行寫(xiě)入操作，并對(duì)源數(shù)據(jù)所在的底層表進(jìn)行刪除操作
有些操作時(shí)支持過(guò)濾的，例如，當(dāng)刪除一條記錄時(shí)，MySQL需要先找到這條記錄，如果where條件恰好和分區(qū)表達(dá)式匹配，就可以將所有不包含這條記錄的分區(qū)都過(guò)濾掉，這對(duì)update同樣有效。如果是insert操作，則本身就是只命中一個(gè)分區(qū)，其他分區(qū)都會(huì)被過(guò)濾掉。mysql先確定這條記錄屬于哪個(gè)分區(qū)，再將記錄寫(xiě)入對(duì)應(yīng)得曾分區(qū)表，無(wú)須對(duì)任何其他分區(qū)進(jìn)行操作
雖然每個(gè)操作都會(huì)“先打開(kāi)并鎖住所有的底層表”，但這并不是說(shuō)分區(qū)表在處理過(guò)程中是鎖住全表的，如果存儲(chǔ)引擎能夠自己實(shí)現(xiàn)行級(jí)鎖，例如innodb，則會(huì)在分區(qū)層釋放對(duì)應(yīng)表鎖。
如何使用分區(qū)表
- 日志系統(tǒng)可以用分區(qū)，一般日志數(shù)量都是比較多的，按年或者月份來(lái)分區(qū)，一般來(lái)說(shuō)都需要在日志系統(tǒng)中查詢出某一段時(shí)間的歷史記錄，因?yàn)閿?shù)據(jù)量巨大，肯定不能走全表掃描，全表掃描會(huì)引發(fā)大量的隨機(jī)io，當(dāng)數(shù)據(jù)量超大的時(shí)候，索引也無(wú)法起作用；此時(shí)應(yīng)該考慮用分區(qū)進(jìn)行解決；
- 并不是數(shù)據(jù)量大才需要用分區(qū)，數(shù)據(jù)量小的時(shí)候也可以用分區(qū)，怎樣的場(chǎng)景下數(shù)據(jù)量小呢？答案是你每次查詢的數(shù)據(jù)都是某一個(gè)批次的時(shí)候就可以用分區(qū)，比如說(shuō)字典，業(yè)務(wù)的字典和用戶類(lèi)型的字典一般都是存放在同一張表里面的，且你每次查詢的時(shí)候不是差一個(gè)業(yè)務(wù)或者一個(gè)用戶類(lèi)型，而是查詢整個(gè)業(yè)務(wù)或者用戶類(lèi)型，這就是一個(gè)批次，此時(shí)也可以用分區(qū)來(lái)實(shí)現(xiàn)；
- 使用分區(qū)后，就可以不用索引了，因?yàn)橐话闶褂梅謪^(qū)的話都是范圍查詢，范圍查詢也就沒(méi)必要使用索引了；已經(jīng)將數(shù)據(jù)分布在不同的分區(qū)中了；
- 要使用索引的話，也可以，但是要分離熱數(shù)據(jù)和冷數(shù)據(jù)，熱數(shù)據(jù)就是經(jīng)常要查詢的數(shù)據(jù)，在熱數(shù)據(jù)的表上加索引來(lái)加快訪問(wèn)速度；
注意事項(xiàng)
- null值會(huì)使分區(qū)過(guò)濾無(wú)效；分區(qū)是需要制定列名的，需要確保這個(gè)列名不會(huì)出現(xiàn)null值；
- 如果分區(qū)列和索引列不是同一列的話，會(huì)導(dǎo)致查詢無(wú)法進(jìn)行分區(qū)過(guò)濾，比如說(shuō)你的id和age字段都加了索引，那么分區(qū)的時(shí)候最好把這2個(gè)列設(shè)為分區(qū)列：干PARTITION BY RANGE COLUMNS(id,age)
- 對(duì)分區(qū)表增刪改的成本很高，每次對(duì)表進(jìn)行次增刪改的時(shí)候會(huì)打開(kāi)并鎖住所有的底層表，只要有一個(gè)鎖住了，其他的操作就無(wú)法進(jìn)行；
- 維護(hù)分區(qū)的時(shí)候，成本可能很高，特別是需要修改分區(qū)的時(shí)候，成本是最高的，
總結(jié)
到此這篇關(guān)于mysql表分區(qū)的使用與底層原理的文章就介紹到這了,更多相關(guān)mysql表分區(qū)底層原理內(nèi)容請(qǐng)搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文鏈接：https://blog.csdn.net/qq_27184497/article/details/119336142
