欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL表分區(qū)的幾種實現(xiàn)

 更新時間:2022年08月18日 16:03:57   作者:atwdy  
本文主要介紹了MySQL表分區(qū)的幾種實現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

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

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

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

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

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

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

之所以特別說明一下是水平分區(qū),是因為還有一種垂直分區(qū)的分區(qū)方式,二者一個橫向切割一個縱向切割,(對比之下感覺水平分區(qū)類似于HBase中的segment,垂直分區(qū)類似于HBase中的region~),關(guān)于垂直分區(qū)先跳過,一是沒找到多少相關(guān)的資料,二是感覺業(yè)務(wù)中用到的也不多,大多用的都是水平分區(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時會自動去對應(yīng)分區(qū)中查找,否則還是全表掃描。

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

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

range分區(qū)字段只支持整型,如果需要對時間日期這樣的字段進行range分區(qū),可以通過相關(guān)函數(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ū)所對應(yīng)著的列表里面都沒找到,則會報錯。

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

3.hash分區(qū)

hash分區(qū)分為常規(guī)hash和線性hash,常規(guī)hash是在分區(qū)字段上基于分區(qū)個數(shù)的取模運算,根據(jù)余數(shù)分區(qū)。線性hash是對分區(qū)字段進行二次方運算,根據(jù)運算結(jié)果分區(qū),所以hash分區(qū)同樣要求分區(qū)字段為整型或者是可以返回整型結(jié)果的表達(dá)式。二者在建表時候的區(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ū)名,會默認(rèn)創(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求余對應(yīng)的結(jié)果 1 1 0 2,也就是上面4條數(shù)據(jù)應(yīng)該分別被保存到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;

這樣當(dāng)在查詢的時候會采用相同的取模運算到對應(yīng)分區(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;

關(guān)于線性分區(qū)的具體計算規(guī)則可以參考官方文檔:https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html,這里假設(shè)num是分區(qū)個數(shù),value是某條記錄的分區(qū)字段對應(yīng)的值,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,驗證一下計算結(jié)果:

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;

結(jié)果計算正確。

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ī)則為:

  • 當(dāng)表中只有主鍵primary key或只有唯一鍵unique key時,分區(qū)列必須包含主鍵或唯一鍵中的部分或全部字段,不允許出現(xiàn)主鍵或唯一鍵中字段以外的其它字段
  • 當(dāng)表中主鍵和唯一鍵同時存在時,分區(qū)列為主鍵和唯一鍵公共字段的部分或全部
  • 當(dāng)表中主鍵唯一鍵都沒有時:任意指定除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ū)(復(fù)合分區(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)默認(rèn)。

不指定子分區(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ū)名不能重復(fù):

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ū)字段可以不為整型,可以有多個,感覺用得不多,附上文檔地址,用到的時候再來學(xué)習(xí)吧,https://dev.mysql.com/doc/refman/8.0/en/partitioning-columns.html

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

相關(guān)文章

  • mysql語句實現(xiàn)簡單的增、刪、改、查操作示例

    mysql語句實現(xiàn)簡單的增、刪、改、查操作示例

    這篇文章主要介紹了mysql語句實現(xiàn)簡單的增、刪、改、查操作,結(jié)合實例形式分析總結(jié)了mysql語句實現(xiàn)數(shù)據(jù)庫與表的創(chuàng)建、刪除以及增刪改查等常見操作技巧,需要的朋友可以參考下
    2019-05-05
  • mysql 索引的基礎(chǔ)操作匯總(四)

    mysql 索引的基礎(chǔ)操作匯總(四)

    這篇文章主要為大家詳細(xì)介紹了mysql 索引的基礎(chǔ)操作匯總,涵蓋了創(chuàng)建和查看索引、刪除索引等操作,感興趣的小伙伴們可以參考一下
    2016-08-08
  • mysql替換表中的字符串的sql語句

    mysql替換表中的字符串的sql語句

    替換字段里數(shù)據(jù)內(nèi)容部分字符串,這個在替換網(wǎng)址呀什么的最經(jīng)常用了,發(fā)布一下做個筆記
    2008-06-06
  • 詳解MySQL中的緩沖池(buffer pool)

    詳解MySQL中的緩沖池(buffer pool)

    這篇文章主要介紹了MySQL中的緩沖池(buffer pool)的相關(guān)資料,幫助大家更好的理解和使用MySQL數(shù)據(jù)庫,感興趣的朋友可以了解下
    2021-01-01
  • MySQL中DATE_FORMAT()函數(shù)將Date轉(zhuǎn)為字符串

    MySQL中DATE_FORMAT()函數(shù)將Date轉(zhuǎn)為字符串

    時間、字符串、時間戳之間的互相轉(zhuǎn)換很常用,下面這篇文章主要給大家介紹了關(guān)于MySQL中DATE_FORMAT()函數(shù)將Date轉(zhuǎn)為字符串的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下
    2022-09-09
  • 在MySQL中為何不建議使用utf8

    在MySQL中為何不建議使用utf8

    這篇文章主要介紹了在MySQL中為何不建議使用utf8,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-11-11
  • 詳細(xì)聊聊MySQL中慢SQL優(yōu)化的方向

    詳細(xì)聊聊MySQL中慢SQL優(yōu)化的方向

    由于在MySQL日常查詢中,查詢類型的語句占慢sql的大部分,所以下面這篇文章主要給大家介紹了關(guān)于MySQL中慢SQL優(yōu)化方向的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2021-08-08
  • mysql臨時表插入數(shù)據(jù)方式

    mysql臨時表插入數(shù)據(jù)方式

    這篇文章主要介紹了mysql臨時表插入數(shù)據(jù)方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-09-09
  • mysql 5.7.14 安裝配置代碼分享

    mysql 5.7.14 安裝配置代碼分享

    這篇文章主要為大家分享了CentOS 6.6下mysql 5.7.13winx64安裝配置方法圖文教程,感興趣的朋友可以參考一下
    2016-09-09
  • mysql 安全管理詳情

    mysql 安全管理詳情

    這篇文章主要介紹了mysql 安全管理,MySQL服務(wù)器的安全基礎(chǔ)是用戶應(yīng)該對他們需要的數(shù)據(jù)具有適當(dāng)?shù)脑L問權(quán),既不能多也不能少,換句話說,用戶不能對過多的數(shù)據(jù)具有過多的訪問權(quán),下面?zhèn)z看看文章詳細(xì)內(nèi)容吧
    2021-10-10

最新評論