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