什么是磁區表

MySQL從5.1版本開始支持磁區功能，
磁區是將一個表的資料按照某種方式，比如按照時間上的月份，分成多個較小的，更容易管理的部分，但是邏輯上仍是一個表，

還沒出現磁區表的時候，所有的資料都是存放在一個檔案里面的，如果資料量太大，查詢資料時總是避免不了需要大量io操作；使用磁區表后，每個磁區存放不同的資料，這樣不但可以減少io，還可以加快資料的訪問；

為了保證MySQL的性能，我們都建議mysql單表不要太大，建議是：單表小于2G，記錄數小于1千萬，十庫百表，如果但行記錄數非常小，那么記錄數可以再偏大些，反之，可能記錄數到百萬級別就開始變慢了，

那么，業務量在增長，資料到瓶頸了怎么辦呢，除了使用分布式資料庫，我們也可以自行分庫分表，或者利用mysql的磁區功能實作，
磁區表的出現是為了分而治之的概念，磁區表的用處非常大，只是現在還有很多人都不知道；
將一個表設定為磁區表后，會在資料檔案.idb的檔案名加上#號，代表這是一個磁區表；

磁區表應用場景

表非常大以至于無法全部放在記憶體中，或者只在表的最后部分有熱點資料，其他都是歷史資料
磁區表的資料更容易維護，，能批量洗掉大量資料
對一個獨立磁區進行優化、檢查、修復等操作
磁區表的資料可以分布在不同的設備上，從未高效的利用多個硬體設備
可以備份和恢復獨立的磁區；

磁區表的限制

一個表最多能有1024個磁區，在5.7版本及以上可以有8196個磁區
在早期mysql中，磁區運算式必須是整數或者整回傳整數的運算式，在mysql5.5中，某些場景可以直接使用列來進行磁區
磁區表無法使用外檢約束
最好不要去修改磁區列
如果磁區欄位中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來；就像這樣：

-- 創建磁區必須包含所有主鍵
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、范圍磁區

下面示例中將年齡進行磁區，

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磁區
);

創建好之后，就可以看到在資料檔案夾中的磁區檔案了

[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的數字則會報錯，

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

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磁區，或者用一個更大的值
);

時間范圍磁區

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磁區）

串列磁區和范圍磁區最大的區別就是串列是等值的，而范圍磁區是在某個范圍內的；

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磁區是直接取模運算得出結果；然后插入指定位置的磁區；

-- 普通欄位的磁區
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磁區用的比較少

-- 以主鍵進行磁區
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個磁區；

 -- 表中有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個磁區

-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

添加磁區

-- 添加串列磁區
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)
對磁區表增刪改的成本很高，每次對表進行次增刪改的時候會打開并鎖住所有的底層表，只要有一個鎖住了，其他的操作就無法進行；
維護磁區的時候，成本可能很高，特別是需要修改磁區的時候，成本是最高的，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/291935.html

標籤：其他

上一篇：Docker搭建redis主從+哨兵

下一篇：set命令保證java中的redis分布式鎖的原子性

mysql表磁區的使用和底層原理