全MySQL分區(qū)表實(shí)踐

MySQL分區(qū)表實(shí)踐1.分區(qū)的目的及分區(qū)類型MySQL在創(chuàng)建表的時(shí)候可以通過使用PARTITIONBY子句定義每個(gè)分區(qū)存放的數(shù)據(jù)。在執(zhí)行查詢的時(shí)候，優(yōu)化器根據(jù)分區(qū)定義過濾那些沒有我們需要的數(shù)據(jù)的分區(qū)，這樣查詢就可以無需掃描所有分區(qū)，只需要查找包含需要數(shù)據(jù)的分區(qū)即可。分區(qū)的另一個(gè)目的是將數(shù)據(jù)按照一個(gè)較粗的粒度分別存放在不同的表中。這樣做可以將相關(guān)的數(shù)據(jù)存放在一起，另外，當(dāng)我們想要一次批量刪除整個(gè)分區(qū)的數(shù)據(jù)也會(huì)變得很方便。下面簡(jiǎn)單介紹下四種常見的分區(qū)類型：RANGE分區(qū)：最為常用，基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。最常見的是基于時(shí)間字段。LIST分區(qū)：LIST分區(qū)和RANGE分區(qū)類似，區(qū)別在于LIST是枚舉值列表的集合，RANGE是連續(xù)的區(qū)間值的集合。HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。上述四種分區(qū)類型中，RANGE分區(qū)即范圍分區(qū)是最常用的。RANGE分區(qū)的特點(diǎn)是多個(gè)分區(qū)的范圍要連續(xù)，但是不能重疊，默認(rèn)情況下使用VALUESLESSTHAN屬性，即每個(gè)分區(qū)不包括指定的那個(gè)值。2.分區(qū)操作示例本節(jié)內(nèi)容以RANGE分區(qū)為例，介紹下分區(qū)表相關(guān)的操作。#創(chuàng)建分區(qū)表

mysql>CREATETABLE`tr`(->`id`INT,->`name`VARCHAR(50),

->`purchased`DATE->)ENGINE=InnoDBDEFAULTCHARSET=utf8->PARTITIONBYRANGE(YEAR(purchased))(->PARTITIONp0VALUESLESSTHAN(1990),

->PARTITIONp1VALUESLESSTHAN(1995),

->PARTITIONp2VALUESLESSTHAN(2000),

->PARTITIONp3VALUESLESSTHAN(2005),

->PARTITIONp4VALUESLESSTHAN(2010),

->PARTITIONp5VALUESLESSTHAN(2015)

->);QueryOK,0rowsaffected(0.28sec)

#插入數(shù)據(jù)mysql>INSERTINTO`tr`VALUES->(1,'deskorganiser','2003-10-15'),

->(2,'alarmclock','1997-11-05'),

->(3,'chair','2009-03-10'),

->(4,'bookcase','1989-01-10'),

->(5,'exercisebike','2014-05-09'),

->(6,'sofa','1987-06-05'),

->(7,'espressomaker','2011-11-22'),

->(8,'aquarium','1992-08-04'),

->(9,'studydesk','2006-09-16'),

->(10,'lavalamp','1998-12-25');

QueryOK,10rowsaffected(0.03sec)

Records:10Duplicates:0Warnings:0創(chuàng)建后可以看到，每個(gè)分區(qū)都會(huì)對(duì)應(yīng)1個(gè)ibd文件。上面創(chuàng)建語句還是很好理解的，在此分區(qū)表中，通過YEAR函數(shù)取出DATE日期中的年份并轉(zhuǎn)化為整型，年份小于1990的存儲(chǔ)在分區(qū)p0中，小于1995的存儲(chǔ)在分區(qū)p1中，以此類推。請(qǐng)注意，每個(gè)分區(qū)的定義順序是從最低到最高。為了防止插入的數(shù)據(jù)因找不到相應(yīng)分區(qū)而報(bào)錯(cuò)，我們應(yīng)該及時(shí)創(chuàng)建新的分區(qū)。下面繼續(xù)展示關(guān)于分區(qū)維護(hù)的其他操作。#查看某個(gè)分區(qū)的數(shù)據(jù)

mysql>SELECT*FROMtrPARTITION(p2);+------+-------------+------------+|id|name|purchased|+------+-------------+------------+|2|alarmclock|1997-11-05|

|10|lavalamp|1998-12-25|

+------+-------------+------------+2rowsinset(0.00sec)

#增加分區(qū)mysql>altertabletraddpartition(->PARTITIONp6VALUESLESSTHAN(2020)

->);QueryOK,0rowsaffected(0.06sec)

Records:0Duplicates:0Warnings:0

#拆分分區(qū)mysql>altertabletrreorganizepartitionp5into(->partitions0valueslessthan(2012),

->partitions1valueslessthan(2015)

->);QueryOK,0rowsaffected(0.26sec)

Records:0Duplicates:0Warnings:0

#合并分區(qū)mysql>altertabletrreorganizepartitions0,s1into(->partitionp5valueslessthan(2015)

->);QueryOK,0rowsaffected(0.12sec)

Records:0Duplicates:0Warnings:0

#清空某分區(qū)的數(shù)據(jù)mysql>altertabletrtruncatepartitionp0;QueryOK,0rowsaffected(0.11sec)

#刪除分區(qū)mysql>altertabletrdroppartitionp1;

QueryOK,0rowsaffected(0.06sec)

Records:0Duplicates:0Warnings:0

#交換分區(qū)#先創(chuàng)建與分區(qū)表同樣結(jié)構(gòu)的交換表mysql>CREATETABLE`tr_archive`(->`id`INT,->`name`VARCHAR(50),

->`purchased`DATE->)ENGINE=InnoDBDEFAULTCHARSET=utf8;QueryOK,0rowsaffected(0.28sec)

#執(zhí)行exchange交換分區(qū)mysql>altertabletrexchangePARTITIONp2withtabletr_archive;QueryOK,0rowsaffected(0.13sec)3.分區(qū)注意事項(xiàng)及適用場(chǎng)景其實(shí)分區(qū)表的使用有很多限制和需要注意的事項(xiàng)，參考官方文檔，簡(jiǎn)要總結(jié)幾點(diǎn)如下：分區(qū)字段必須是整數(shù)類型或解析為整數(shù)的表達(dá)式。分區(qū)字段建議設(shè)置為NOTNULL，若某行數(shù)據(jù)分區(qū)字段為null，在RANGE分區(qū)中，該行數(shù)據(jù)會(huì)劃分到最小的分區(qū)里。MySQL分區(qū)中如果存在主鍵或唯一鍵，則分區(qū)列必須包含在其中。Innodb分區(qū)表不支持外鍵。更改sql_mode模式可能影響分區(qū)表的表現(xiàn)。分區(qū)表不影響自增列。從上面的介紹中可以看出，分區(qū)表適用于一些日志記錄表。這類表的特點(diǎn)是數(shù)據(jù)量大、并且有冷熱數(shù)據(jù)區(qū)分，可以按照時(shí)間維度來進(jìn)行數(shù)據(jù)歸檔。這類表是比較適合使用分區(qū)表的，因?yàn)榉謪^(qū)表可