MySQL表分割區的幾種實現

2022-08-18 22:02:44

關於分割區以及為什麼要分割區

我們首先找到mysql的資料儲存目錄，可以通過語句show variables like '%datadir%';檢視，我本機的是"C:ProgramDataMySQLMySQL Server 8.0Data"，在該目錄下，可以看到每個資料庫對應著一個資料夾，對於沒有分割區的表，庫中的每個表就對應著資料夾下的一個ibd檔案

當一個表中的資料量太大時，會面臨兩個問題，一是對資料的操作會變慢，比如select、join、update、delete時，會對全表操作；二是不便於儲存，可能會出現剩餘磁碟空間儲存不下這張表的情況。而分割區就可以在一定程度上解決這兩個問題。

簡要的說，分割區就是將表物理截斷，但在邏輯上依然是一個整體，開發人員在資料操作時仍然是對這個整體大表進行操作，之後由資料庫底層自己去尋找對應的分割區進行操作，資料庫底層尋找分割區這個過程對開發人員來說是透明的，這樣在資料操作時可以只對特定分割區操作以提高效率，儲存時也可以將不同分割區的物理檔案分開存放，下面是一個有3個分割區(p1、p2、p3)的表(p_table)的實際儲存

注：當過濾條件為分割區的欄位時才會自動尋找分割區，否則還是全表掃描

水平分割區的幾種型別及demo

之所以特別說明一下是水平分割區，是因為還有一種垂直分割區的分割區方式，二者一個橫向切割一個縱向切割，(對比之下感覺水平分割區類似於HBase中的segment，垂直分割區類似於HBase中的region~)，關於垂直分割區先跳過，一是沒找到多少相關的資料，二是感覺業務中用到的也不多，大多用的都是水平分割區，有時間日後再補。

mysql中的水平分割區包含下面幾種：

1.range分割區

range分割區，顧名思義，就是按照範圍進行分割區，下面是建立一個range分割區表：

drop table if exists `range_table`;
create table `range_table`(
	`id` int,
	`name` varchar(10)
) 
partition by range(id)(
	partition p1 values less than (10),
	partition p2 values less than (20),
	partition p3 values less than maxvalue
);

上面以id為分割區欄位，根據id大小劃分為[-∞, 10)，[10, 20)，[20, +∞]三個區間，注意包前不包後，在資料插入時會自動根據id插入到各自分割區

# 插入資料
insert into range_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");
# 檢視各個分割區資料條數
select partition_name,table_rows from information_schema.partitions where table_name = 'range_table';

分別指定分割區檢視各個分割區裡面的資料，可以看到id為1的儲存到了p1，id為10和15的儲存到了p2，id為50的儲存到了p3

select * from range_table partition (p1);
select * from range_table partition (p2);
select * from range_table partition (p3);

在進行select/update/delete時如果where後面的限制條件包含分割區欄位id時會自動去對應分割區中查詢，否則還是全表掃描。

explain select * from range_table where id = "1" and name = '梁靜茹';

explain select * from range_table where name = '梁靜茹';

range分割區欄位只支援整型，如果需要對時間日期這樣的欄位進行range分割區，可以通過相關函數將型別轉為整型再分割區。

2.list分割區

list就是列舉的意思，list分割區就是在建立各分割區時具體指定哪些值屬於這些分割區，下面是建立list分割區表的程式碼：

drop table if exists `list_table`;
create table `list_table`(
	`id` int,
	`name` varchar(10)
) 
partition by list(id)(
	partition p1 values in (1),
	partition p2 values in (10,15,50)
);

重新執行插入語句insert into list_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");，id為1的儲存到了p1分割區，id為10，15，20的儲存到了p2分割區，需要注意如果插入資料的 id 在各個分割區所對應著的列表裡面都沒找到，則會報錯。

list分割區分割區欄位同樣只能是int型。

3.hash分割區

hash分割區分為常規hash和線性hash，常規hash是在分割區欄位上基於分割區個數的取模運算，根據餘數分割區。線性hash是對分割區欄位進行二次方運算，根據運算結果分割區，所以hash分割區同樣要求分割區欄位為整型或者是可以返回整型結果的表示式。二者在建表時候的區別只是線性hash比常規hash多了個linear(線性的)限定。

3.1.常規hash

常規hash分割區建表：

drop table if exists `hash_table`;
create table `hash_table`(
	`id` int,
	`name` varchar(10)
) 
partition by hash(id)
partitions 3;

hash分割區不能指定分割區名，會預設建立名為pn的分割區，n從0開始自增。上面這段程式碼會建立p0，p1，p2三個分割區，分割區名可以通過下面的sql檢視，

select partition_name from information_schema.PARTITIONS where table_schema = schema() and table_name = "hash_table";

上面說的常規hash就是基於分割區數對分割區欄位進行取模求餘操作，按照這種計算，插入下面的資料，

insert into hash_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");

1 10 15 50 分別對3求餘對應的結果 1 1 0 2，也就是上面4條資料應該分別被儲存到p1, p1, p0, p2分割區，對此進行驗證：

select 'p0' as part, t.* from hash_table partition (p0) t
union
select 'p1' as part, t.* from hash_table partition (p1) t
union
select 'p2' as part, t.* from hash_table partition (p2) t;

這樣當在查詢的時候會採用相同的取模運算到對應分割區下查詢，比如查id為5的資料，就會去p2分割區查詢。

3.2.線性hash

線性hash在建表時只是比常規hash多了個linear欄位：

drop table if exists `hash_linear_table`;
create table `hash_linear_table`(
	`id` int,
	`name` varchar(10)
) 
partition by linear hash(id)
partitions 3;

關於線性分割區的具體計算規則可以參考官方檔案：https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html，這裡假設num是分割區個數，value是某條記錄的分割區欄位對應的值，N是最終經過計算得到的某個分割區編號，則N的計算過程如下：

step1：V = power(2, ceil(log(2, num)))
step2：N = value & (V-1)
step3：if N>=num: N=N & (ceil(V/2) - 1)

按照上面步驟，將id為50的這條資料代入計算：

step1：V = power(2, ceil(log(2, num))) = power(2, ceil(log(2, 3))) = power(2, 2) = 4
step2：N = value & (V-1) = 50 & 3 = 110010 & 000011 = 000010 = 2
step3：N>=num? <=> 2>=3? False：N=2

即id為50的這條資料儲存到p2分割區，同理可以計算出id為10時N=2，id為1時N=1，id為15是N=1，驗證一下計算結果：

select 'p0' as part, t.* from hash_linear_table partition (p0) t
union
select 'p1' as part, t.* from hash_linear_table partition (p1) t
union
select 'p2' as part, t.* from hash_linear_table partition (p2) t;

結果計算正確。

4.key分割區

主要還是參考官方檔案吧，https://dev.mysql.com/doc/refman/8.0/en/partitioning-key.html，裡面主要說的是，key分割區類似於hash分割區，只不過分割區列不再強制為整型，可以為除text和BLOB兩種型別外的其它型別。key分割區也有兩種，常規key和線性key，常規key對分割區欄位採用的是MD5演演算法，線性key對分割區欄位採用的是二次方演演算法，參考hash分割區中的線性hash，分割區列選取的具體規則為：

當表中只有主鍵primary key或只有唯一鍵unique key時，分割區列必須包含主鍵或唯一鍵中的部分或全部欄位，不允許出現主鍵或唯一鍵中欄位以外的其它欄位
當表中主鍵和唯一鍵同時存在時，分割區列為主鍵和唯一鍵公共欄位的部分或全部
當表中主鍵唯一鍵都沒有時：任意指定除text和BLOB型別外的其它欄位，可以為1個或多個

分割區列也可以預設不指定，但必須要求表中存在主鍵或唯一鍵，優先以主鍵作為分割區欄位，沒有主鍵時以唯一鍵作為分割區欄位，此時唯一鍵必須顯示指定not null。

下面是常規key分割區建表的一個demo，name為分割區欄位：

drop table if exists `key_table`;
create table `key_table`(
	`id` int,
	`name` varchar(10) not null,
	unique `uk_name` (name)
) 
partition by key()
partitions 3;

線性key分割區的建表也只是多了一個linear欄位：

drop table if exists `key_table`;
create table `key_table`(
	`id` int,
	`name` varchar(10) not null,
	unique `uk_name` (name)
) 
partition by linear key()
partitions 3;

5.子分割區(複合分割區)

檔案地址：https://dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html，裡面有這麼一段話，

說的是我們可以對採用range分割區或者list分割區的表，進行二次分割區，二次分割區只能為hash分割區或者key分割區。這種分割區方式有兩種建表寫法，一種是指定子分割區名，一種是不指定子分割區名由系統預設。

不指定子分割區名建立：

drop table if exists `subpart_table`;
create table `subpart_table`(
	dt date
) 
partition by range(year(dt))
subpartition by hash(month(dt))
subpartitions 2 (
	partition p1 values less than (1990),
	partition p2 values less than (2000),
	partition p3 values less than maxvalue
);

通過select partition_name, subpartition_name from information_schema.partitions where table_schema = schema() and table_name = 'subpart_table';

檢視下各個子分割區：

物理上也被分成了單獨的6個檔案：

指定分割區名建立，這種方式要求每個一級分割區下的子分割區數量必須一致，所有子分割區的分割區名不能重複：

drop table if exists `subpart_table`;
create table `subpart_table`(
	dt date
) 
partition by range(year(dt))
subpartition by hash(month(dt))
(
	partition p1 values less than (1990)(
		subpartition s1,
		subpartition s2
	),
	partition p2 values less than (2000)(
		subpartition s3,
		subpartition s4
	),
	partition p3 values less than maxvalue(
		subpartition s5,
		subpartition s6
	)
);

上表根據日期的年份進行一級分割區，根據日期的月份二級分割區，s1、s3、s5存偶數月，s2、s4、s6存奇數月，插入資料驗證一下：

insert into subpart_table values('1989-01-01'), ('1989-02-01'), 
								('1995-01-01'), ('1989-02-01'), 
								('2022-01-01'), ('2022-02-01');

select 's1' as part, t.* from subpart_table partition (s1) t
union
select 's2' as part, t.* from subpart_table partition (s2) t
union
select 's3' as part, t.* from subpart_table partition (s3) t
union
select 's4' as part, t.* from subpart_table partition (s4) t
union
select 's5' as part, t.* from subpart_table partition (s5) t
union
select 's6' as part, t.* from subpart_table partition (s6) t;