關于分區(qū)以及為什么要分區(qū)

我們首先找到mysql的數(shù)據(jù)存儲目錄，可以通過語句show variables like '%datadir%';查看，我本機的是"C:\ProgramData\MySQL\MySQL Server 8.0\Data"，在該目錄下，可以看到每個數(shù)據(jù)庫對應著一個文件夾，對于沒有分區(qū)的表，庫中的每個表就對應著文件夾下的一個ibd文件

當一個表中的數(shù)據(jù)量太大時，會面臨兩個問題，一是對數(shù)據(jù)的操作會變慢，比如select、join、update、delete時，會對全表操作；二是不便于存儲，可能會出現(xiàn)剩余磁盤空間存儲不下這張表的情況。而分區(qū)就可以在一定程度上解決這兩個問題。

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

注：當過濾條件為分區(qū)的字段時才會自動尋找分區(qū)，否則還是全表掃描

水平分區(qū)的幾種類型及demo

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

mysql中的水平分區(qū)包含下面幾種：

1.range分區(qū)

range分區(qū)，顧名思義，就是按照范圍進行分區(qū)，下面是創(chuàng)建一個range分區(qū)表：

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為分區(qū)字段，根據(jù)id大小劃分為[-∞, 10)，[10, 20)，[20, +∞]三個區(qū)間，注意包前不包后，在數(shù)據(jù)插入時會自動根據(jù)id插入到各自分區(qū)

# 插入數(shù)據(jù)
insert into range_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");
# 查看各個分區(qū)數(shù)據(jù)條數(shù)
select partition_name,table_rows from information_schema.partitions where table_name = 'range_table';

分別指定分區(qū)查看各個分區(qū)里面的數(shù)據(jù)，可以看到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后面的限制條件包含分區(qū)字段id時會自動去對應分區(qū)中查找，否則還是全表掃描。

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

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

range分區(qū)字段只支持整型，如果需要對時間日期這樣的字段進行range分區(qū)，可以通過相關函數(shù)將類型轉(zhuǎn)為整型再分區(qū)。

2.list分區(qū)

list就是枚舉的意思，list分區(qū)就是在創(chuàng)建各分區(qū)時具體指定哪些值屬于這些分區(qū)，下面是創(chuàng)建list分區(qū)表的代碼：

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

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

list分區(qū)分區(qū)字段同樣只能是int型。

3.hash分區(qū)

hash分區(qū)分為常規(guī)hash和線性hash，常規(guī)hash是在分區(qū)字段上基于分區(qū)個數(shù)的取模運算，根據(jù)余數(shù)分區(qū)。線性hash是對分區(qū)字段進行二次方運算，根據(jù)運算結果分區(qū)，所以hash分區(qū)同樣要求分區(qū)字段為整型或者是可以返回整型結果的表達式。二者在建表時候的區(qū)別只是線性hash比常規(guī)hash多了個linear(線性的)限定。

3.1.常規(guī)hash

常規(guī)hash分區(qū)建表：

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

hash分區(qū)不能指定分區(qū)名，會默認創(chuàng)建名為pn的分區(qū)，n從0開始自增。上面這段代碼會創(chuàng)建p0，p1，p2三個分區(qū)，分區(qū)名可以通過下面的sql查看，

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

上面說的常規(guī)hash就是基于分區(qū)數(shù)對分區(qū)字段進行取模求余操作，按照這種計算，插入下面的數(shù)據(jù)，

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

1 10 15 50 分別對3求余對應的結果 1 1 0 2，也就是上面4條數(shù)據(jù)應該分別被保存到p1, p1, p0, p2分區(qū)，對此進行驗證：

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;

這樣當在查詢的時候會采用相同的取模運算到對應分區(qū)下查找，比如查id為5的數(shù)據(jù)，就會去p2分區(qū)查找。

3.2.線性hash

線性hash在建表時只是比常規(guī)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;

關于線性分區(qū)的具體計算規(guī)則可以參考官方文檔：https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html，這里假設num是分區(qū)個數(shù)，value是某條記錄的分區(qū)字段對應的值，N是最終經(jīng)過計算得到的某個分區(qū)編號，則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的這條數(shù)據(jù)代入計算：

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的這條數(shù)據(jù)保存到p2分區(qū)，同理可以計算出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分區(qū)

主要還是參考官方文檔吧，https://dev.mysql.com/doc/refman/8.0/en/partitioning-key.html，里面主要說的是，key分區(qū)類似于hash分區(qū)，只不過分區(qū)列不再強制為整型，可以為除text和BLOB兩種類型外的其它類型。key分區(qū)也有兩種，常規(guī)key和線性key，常規(guī)key對分區(qū)字段采用的是MD5算法，線性key對分區(qū)字段采用的是二次方算法，參考hash分區(qū)中的線性hash，分區(qū)列選取的具體規(guī)則為：

• 當表中只有主鍵primary key或只有唯一鍵unique key時，分區(qū)列必須包含主鍵或唯一鍵中的部分或全部字段，不允許出現(xiàn)主鍵或唯一鍵中字段以外的其它字段
• 當表中主鍵和唯一鍵同時存在時，分區(qū)列為主鍵和唯一鍵公共字段的部分或全部
• 當表中主鍵唯一鍵都沒有時：任意指定除text和BLOB類型外的其它字段，可以為1個或多個

分區(qū)列也可以缺省不指定，但必須要求表中存在主鍵或唯一鍵，優(yōu)先以主鍵作為分區(qū)字段，沒有主鍵時以唯一鍵作為分區(qū)字段，此時唯一鍵必須顯示指定not null。

下面是常規(guī)key分區(qū)建表的一個demo，name為分區(qū)字段：

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分區(qū)的建表也只是多了一個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.子分區(qū)(復合分區(qū))

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

說的是我們可以對采用range分區(qū)或者list分區(qū)的表，進行二次分區(qū)，二次分區(qū)只能為hash分區(qū)或者key分區(qū)。這種分區(qū)方式有兩種建表寫法，一種是指定子分區(qū)名，一種是不指定子分區(qū)名由系統(tǒng)默認。

不指定子分區(qū)名創(chuàng)建：

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';

查看下各個子分區(qū)：

物理上也被分成了單獨的6個文件：

指定分區(qū)名創(chuàng)建，這種方式要求每個一級分區(qū)下的子分區(qū)數(shù)量必須一致，所有子分區(qū)的分區(qū)名不能重復：

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

上表根據(jù)日期的年份進行一級分區(qū)，根據(jù)日期的月份二級分區(qū)，s1、s3、s5存偶數(shù)月，s2、s4、s6存奇數(shù)月，插入數(shù)據(jù)驗證一下：

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;

6.columns分區(qū)

包含range columns和list columns兩種，分區(qū)字段可以不為整型，可以有多個，感覺用得不多，附上文檔地址，用到的時候再來學習吧，https://dev.mysql.com/doc/refman/8.0/en/partitioning-columns.html

到此這篇關于MySQL表分區(qū)的幾種實現(xiàn)的文章就介紹到這了,更多相關MySQL表分區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論