什么是數(shù)據(jù)庫分區(qū)

時間：2018-11-23 11:38:01

關(guān)鍵字： mysql 數(shù)據(jù)庫分區(qū)

手機(jī)看文章

掃描二維碼
隨時隨地手機(jī)看文章

[導(dǎo)讀]一，什么是數(shù)據(jù)庫分區(qū)前段時間寫過一篇關(guān)于mysql分表的的文章，下面來說一下什么是數(shù)據(jù)庫分區(qū)，以mysql為例。mysql數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認(rèn)放在/mysql/data下面（

一，什么是數(shù)據(jù)庫分區(qū)

前段時間寫過一篇關(guān)于mysql分表的的文章，下面來說一下什么是數(shù)據(jù)庫分區(qū)，以mysql為例。mysql數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認(rèn)放在/mysql/data下面（可以通過my.cnf中的datadir來查看），一張表主要對應(yīng)著三個文件，一個是frm存放表結(jié)構(gòu)的，一個是myd存放表數(shù)據(jù)的，一個是myi存表索引的。如果一張表的數(shù)據(jù)量太大的話，那么myd,myi就會變的很大，查找數(shù)據(jù)就會變的很慢，這個時候我們可以利用mysql的分區(qū)功能，在物理上將這一張表對應(yīng)的三個文件，分割成許多個小塊，這樣呢，我們查找一條數(shù)據(jù)時，就不用全部查找了，只要知道這條數(shù)據(jù)在哪一塊，然后在那一塊找就行了。如果表的數(shù)據(jù)太大，可能一個磁盤放不下，這個時候，我們可以把數(shù)據(jù)分配到不同的磁盤里面去。
分區(qū)的二種方式

1，橫向分區(qū)

什么是橫向分區(qū)呢？就是橫著來分區(qū)了，舉例來說明一下，假如有100W條數(shù)據(jù)，分成十份，前10W條數(shù)據(jù)放到第一個分區(qū)，第二個10W條數(shù)據(jù)放到第二個分區(qū)，依此類推。也就是把表分成了十分，根用merge來分表，有點像哦。取出一條數(shù)據(jù)的時候，這條數(shù)據(jù)包含了表結(jié)構(gòu)中的所有字段，也就是說橫向分區(qū)，并沒有改變表的結(jié)構(gòu)。

2，縱向分區(qū)

什么是縱向分區(qū)呢？就是豎來分區(qū)了，舉例來說明，在設(shè)計用戶表的時候，開始的時候沒有考慮好，而把個人的所有信息都放到了一張表里面去，這樣這個表里面就會有比較大的字段，如個人簡介，而這些簡介呢，也許不會有好多人去看，所以等到有人要看的時候，在去查找，分表的時候，可以把這樣的大字段，分開來。

感覺數(shù)據(jù)庫的分區(qū)好像是切蘋果，到底是橫著切呢，還是豎著切，根據(jù)個人喜好了，mysql提供的分區(qū)屬于第一種，橫向分區(qū)，并且細(xì)分成很多種方式。下面將舉例說明一下。

二，mysql的分區(qū)

我覺著吧，mysql的分區(qū)只有一種方式，只不過運(yùn)用不同的算法，規(guī)則將數(shù)據(jù)分配到不同的區(qū)塊中而已。

1，mysql5.1及以上支持分區(qū)功能

安裝安裝的時候，我們就可以查看一下

查看復(fù)制打印? [root@BlackGhostmysql-5.1.50]#./configure--help|grep-A3Partition ===PartitionSupport=== PluginName:partition Description:MySQLPartitioningSupport Supportsbuild:static Configurations:max,max-no-ndb

查看一下，如果發(fā)現(xiàn)有上面這個東西，說明他是支持分區(qū)的，默認(rèn)是打開的。如果你已經(jīng)安裝過了mysql的話

查看一下變量，如果支持的話，會有上面的提示的。

2，range分區(qū)

按照RANGE分區(qū)的表是通過如下一種方式進(jìn)行分區(qū)的，每個分區(qū)包含那些分區(qū)表達(dá)式的值位于一個給定的連續(xù)區(qū)間內(nèi)的行

查看復(fù)制打印? //創(chuàng)建range分區(qū)表 mysql>CREATETABLEIFNOTEXISTS`user`( ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'用戶ID', ->`name`varchar(50)NOTNULLDEFAULT''COMMENT'名稱', ->`sex`int(1)NOTNULLDEFAULT'0'COMMENT'0為男，1為女', ->PRIMARYKEY(`id`) ->)ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1 ->PARTITIONBYRANGE(id)( ->PARTITIONp0VALUESLESSTHAN(3), ->PARTITIONp1VALUESLESSTHAN(6), ->PARTITIONp2VALUESLESSTHAN(9), ->PARTITIONp3VALUESLESSTHAN(12), ->PARTITIONp4VALUESLESSTHANMAXVALUE ->); QueryOK,0rowsaffected(0.13sec) //插入一些數(shù)據(jù) mysql>INSERTINTO`test`.`user`(`name`,`sex`)VALUES('tank','0') ->,('zhang',1),('ying',1),('張',1),('映',0),('test1',1),('tank2',1) ->,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1) ->,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1) ->,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1); QueryOK,25rowsaffected(0.05sec) Records:25Duplicates:0Warnings:0 //到存放數(shù)據(jù)庫表文件的地方看一下，my.cnf里面有配置，datadir后面就是 [root@BlackGhosttest]#ls|grepuser|xargsdu-sh 4.0Kuser#P#p0.MYD 4.0Kuser#P#p0.MYI 4.0Kuser#P#p1.MYD 4.0Kuser#P#p1.MYI 4.0Kuser#P#p2.MYD 4.0Kuser#P#p2.MYI 4.0Kuser#P#p3.MYD 4.0Kuser#P#p3.MYI 4.0Kuser#P#p4.MYD 4.0Kuser#P#p4.MYI 12Kuser.frm 4.0Kuser.par //取出數(shù)據(jù) mysql>selectcount(id)ascountfromuser; +-------+ |count| +-------+ |25| +-------+ 1rowinset(0.00sec) //刪除第四個分區(qū) mysql>altertableuserdroppartitionp4; QueryOK,0rowsaffected(0.11sec) Records:0Duplicates:0Warnings:0 /**存放在分區(qū)里面的數(shù)據(jù)丟失了，第四個分區(qū)里面有14條數(shù)據(jù)，剩下的3個分區(qū) 只有11條數(shù)據(jù)，但是統(tǒng)計出來的文件大小都是4.0K，從這兒我們可以看出分區(qū)的最小區(qū)塊是4K */ mysql>selectcount(id)ascountfromuser; +-------+ |count| +-------+ |11| +-------+ 1rowinset(0.00sec) //第四個區(qū)塊已刪除 [root@BlackGhosttest]#ls|grepuser|xargsdu-sh 4.0Kuser#P#p0.MYD 4.0Kuser#P#p0.MYI 4.0Kuser#P#p1.MYD 4.0Kuser#P#p1.MYI 4.0Kuser#P#p2.MYD 4.0Kuser#P#p2.MYI 4.0Kuser#P#p3.MYD 4.0Kuser#P#p3.MYI 12Kuser.frm 4.0Kuser.par /*可以對現(xiàn)有表進(jìn)行分區(qū),并且會按規(guī)則自動的將表中的數(shù)據(jù)分配相應(yīng)的分區(qū) 中，這樣就比較好了，可以省去很多事情，看下面的操作*/ mysql>altertableaapartitionbyRANGE(id) ->(PARTITIONp1VALUESlessthan(1), ->PARTITIONp2VALUESlessthan(5), ->PARTITIONp3VALUESlessthanMAXVALUE); QueryOK,15rowsaffected(0.21sec)//對15數(shù)據(jù)進(jìn)行分區(qū) Records:15Duplicates:0Warnings:0 //總共有15條 mysql>selectcount(*)fromaa; +----------+ |count(*)| +----------+ |15| +----------+ 1rowinset(0.00sec) //刪除一個分區(qū) mysql>altertableaadroppartitionp2; QueryOK,0rowsaffected(0.30sec) Records:0Duplicates:0Warnings:0 //只有11條了，說明對現(xiàn)有的表分區(qū)成功了 mysql>selectcount(*)fromaa; +----------+ |count(*)| +----------+ |11| +----------+ 1rowinset(0.00sec)

3，list分區(qū)

LIST分區(qū)中每個分區(qū)的定義和選擇是基于某列的值從屬于一個值列表集中的一個值，而RANGE分區(qū)是從屬于一個連續(xù)區(qū)間值的集合。

查看復(fù)制打印? //這種方式失敗 mysql>CREATETABLEIFNOTEXISTS`list_part`( ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'用戶ID', ->`province_id`int(2)NOTNULLDEFAULT0COMMENT'省', ->`name`varchar(50)NOTNULLDEFAULT''COMMENT'名稱', ->`sex`int(1)NOTNULLDEFAULT'0'COMMENT'0為男，1為女', ->PRIMARYKEY(`id`) ->)ENGINE=INNODBDEFAULTCHARSET=utf8AUTO_INCREMENT=1 ->PARTITIONBYLIST(province_id)( ->PARTITIONp0VALUESIN(1,2,3,4,5,6,7,8), ->PARTITIONp1VALUESIN(9,10,11,12,16,21), ->PARTITIONp2VALUESIN(13,14,15,19), ->PARTITIONp3VALUESIN(17,18,20,22,23,24) ->); ERROR1503(HY000):APRIMARYKEYmustincludeallcolumnsinthetable'spartitioningfunction //這種方式成功 mysql>CREATETABLEIFNOTEXISTS`list_part`( ->`id`int(11)NOTNULLCOMMENT'用戶ID', ->`province_id`int(2)NOTNULLDEFAULT0COMMENT'省', ->`name`varchar(50)NOTNULLDEFAULT''COMMENT'名稱', ->`sex`int(1)NOTNULLDEFAULT'0'COMMENT'0為男，1為女' ->)ENGINE=INNODBDEFAULTCHARSET=utf8 ->PARTITIONBYLIST(province_id)( ->PARTITIONp0VALUESIN(1,2,3,4,5,6,7,8), ->PARTITIONp1VALUESIN(9,10,11,12,16,21), ->PARTITIONp2VALUESIN(13,14,15,19), ->PARTITIONp3VALUESIN(17,18,20,22,23,24) ->); QueryOK,0rowsaffected(0.33sec)

上面的這個創(chuàng)建list分區(qū)時，如果有主銉的話，分區(qū)時主鍵必須在其中，不然就會報錯。如果我不用主鍵，分區(qū)就創(chuàng)建成功了，一般情況下，一個張表肯定會有一個主鍵，這算是一個分區(qū)的局限性吧。

如果對數(shù)據(jù)進(jìn)行測試，請參考range分區(qū)的測試來操作

4，hash分區(qū)

HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，你所要做的只是基于將要被哈希的列值指定一個列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。

查看復(fù)制打印? mysql>CREATETABLEIFNOTEXISTS`hash_part`( ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'評論ID', ->`comment`varchar(1000)NOTNULLDEFAULT''COMMENT'評論', ->`ip`varchar(25)NOTNULLDEFAULT''COMMENT'來源IP', ->PRIMARYKEY(`id`) ->)ENGINE=INNODBDEFAULTCHARSET=utf8AUTO_INCREMENT=1 ->PARTITIONBYHASH(id) ->PARTITIONS3; QueryOK,0rowsaffected(0.06sec)

測試請參考range分區(qū)的操作

5，key分區(qū)

按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)，除了HASH分區(qū)使用的用戶定義的表達(dá)式，而KEY分區(qū)的哈希函數(shù)是由MySQL 服務(wù)器提供。

查看復(fù)制打印? mysql>CREATETABLEIFNOTEXISTS`key_part`( ->`news_id`int(11)NOTNULLCOMMENT'新聞ID', ->`content`varchar(1000)NOTNULLDEFAULT''COMMENT'新聞內(nèi)容', ->`u_id`varchar(25)NOTNULLDEFAULT''COMMENT'來源IP', ->`create_time`DATENOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'時間' ->)ENGINE=INNODBDEFAULTCHARSET=utf8 ->PARTITIONBYLINEARHASH(YEAR(create_time)) ->PARTITIONS3; QueryOK,0rowsaffected(0.07sec)

測試請參考range分區(qū)的操作

6，子分區(qū)

子分區(qū)是分區(qū)表中每個分區(qū)的再次分割，子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)。這也被稱為復(fù)合分區(qū)（composite partitioning）。

1，如果一個分區(qū)中創(chuàng)建了子分區(qū)，其他分區(qū)也要有子分區(qū)

2，如果創(chuàng)建了了分區(qū)，每個分區(qū)中的子分區(qū)數(shù)必有相同

3，同一分區(qū)內(nèi)的子分區(qū)，名字不相同，不同分區(qū)內(nèi)的子分區(qū)名子可以相同（5.1.50不適用）

查看復(fù)制打印? mysql>CREATETABLEIFNOTEXISTS`sub_part`( ->`news_id`int(11)NOTNULLCOMMENT'新聞ID', ->`content`varchar(1000)NOTNULLDEFAULT''COMMENT'新聞內(nèi)容', ->`u_id`int(11)NOTNULLDEFAULT0sCOMMENT'來源IP', ->`create_time`DATENOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'時間' ->)ENGINE=INNODBDEFAULTCHARSET=utf8 ->PARTITIONBYRANGE(YEAR(create_time)) ->SUBPARTITIONBYHASH(TO_DAYS(create_time))( ->PARTITIONp0VALUESLESSTHAN(1990)(SUBPARTITIONs0,SUBPARTITIONs1,SUBPARTITIONs2), ->PARTITIONp1VALUESLESSTHAN(2000)(SUBPARTITIONs3,SUBPARTITIONs4,SUBPARTITIONgood), ->PARTITIONp2VALUESLESSTHANMAXVALUE(SUBPARTITIONtank0,SUBPARTITIONtank1,SUBPARTITIONtank3) ->); QueryOK,0rowsaffected(0.07sec)

官方網(wǎng)站說不同分區(qū)內(nèi)的子分區(qū)可以有相同的名字，但是mysql5.1.50卻不行會提示以下錯誤

ERROR 1517 (HY000): Duplicate partition name s1

三，分區(qū)管理

1，刪除分區(qū)

mysql>altertableuserdroppartitionp4;

2，新增分區(qū)

查看復(fù)制打印? //range添加新分區(qū) mysql>altertableuseraddpartition(partitionp4valueslessthanMAXVALUE); QueryOK,0rowsaffected(0.06sec) Records:0Duplicates:0Warnings:0 //list添加新分區(qū) mysql>altertablelist_partaddpartition(partitionp4valuesin(25,26,28)); QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0 //hash重新分區(qū) mysql>altertablehash_partaddpartitionpartitions4; QueryOK,0rowsaffected(0.12sec) Records:0Duplicates:0Warnings:0 //key重新分區(qū) mysql>altertablekey_partaddpartitionpartitions4; QueryOK,1rowaffected(0.06sec)//有數(shù)據(jù)也會被重新分配 Records:1Duplicates:0Warnings:0 //子分區(qū)添加新分區(qū)，雖然我沒有指定子分區(qū)，但是系統(tǒng)會給子分區(qū)命名的 mysql>altertablesub1_partaddpartition(partitionp3valueslessthanMAXVALUE); QueryOK,0rowsaffected(0.02sec) Records:0Duplicates:0Warnings:0 mysql>showcreatetablesub1_partG; ***************************1.row*************************** Table:sub1_part CreateTable:CREATETABLE`sub1_part`( `news_id`int(11)NOTNULLCOMMENT'新聞ID', `content`varchar(1000)NOTNULLDEFAULT''COMMENT'新聞內(nèi)容', `u_id`varchar(25)NOTNULLDEFAULT''COMMENT'來源IP', `create_time`dateNOTNULLDEFAULT'0000-00-00'COMMENT'時間' )ENGINE=InnoDBDEFAULTCHARSET=utf8 !50100PARTITIONBYRANGE(YEAR(create_time)) SUBPARTITIONBYHASH(TO_DAYS(create_time)) (PARTITIONp0VALUESLESSTHAN(1990) (SUBPARTITIONs0ENGINE=InnoDB, SUBPARTITIONs1ENGINE=InnoDB, SUBPARTITIONs2ENGINE=InnoDB), PARTITIONp1VALUESLESSTHAN(2000) (SUBPARTITIONs3ENGINE=InnoDB, SUBPARTITIONs4ENGINE=InnoDB, SUBPARTITIONgoodENGINE=InnoDB), PARTITIONp2VALUESLESSTHAN(3000) (SUBPARTITIONtank0ENGINE=InnoDB, SUBPARTITIONtank1ENGINE=InnoDB, SUBPARTITIONtank3ENGINE=InnoDB), PARTITIONp3VALUESLESSTHANMAXVALUE (SUBPARTITIONp3sp0ENGINE=InnoDB,//子分區(qū)的名子是自動生成的 SUBPARTITIONp3sp1ENGINE=InnoDB, SUBPARTITIONp3sp2ENGINE=InnoDB)) 1rowinset(0.00sec)

3，重新分區(qū)

查看復(fù)制打印? //range重新分區(qū) mysql>ALTERTABLEuserREORGANIZEPARTITIONp0,p1,p2,p3,p4INTO(PARTITIONp0VALUESLESSTHANMAXVALUE); QueryOK,11rowsaffected(0.08sec) Records:11Duplicates:0Warnings:0 //list重新分區(qū) mysql>ALTERTABLElist_partREORGANIZEPARTITIONp0,p1,p2,p3,p4INTO(PARTITIONp0VALUESin(1,2,3,4,5)); QueryOK,0rowsaffected(0.28sec) Records:0Duplicates:0Warnings:0 //hash和key分區(qū)不能用REORGANIZE，官方網(wǎng)站說的很清楚 mysql>ALTERTABLEkey_partREORGANIZEPARTITIONCOALESCEPARTITION9; ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'PARTITION9'atline1

四，分區(qū)優(yōu)點

1，分區(qū)可以分在多個磁盤，存儲更大一點

2，根據(jù)查找條件，也就是where后面的條件，查找只查找相應(yīng)的分區(qū)不用全部查找了

3，進(jìn)行大數(shù)據(jù)搜索時可以進(jìn)行并行處理。

4，跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量