表分區(qū)是最近才知道的哦 ，以前自己做都是分表來(lái)實(shí)現(xiàn)上億級(jí)別的數(shù)據(jù)了，下面我來(lái)給大家介紹一下mysql表分區(qū)創(chuàng)建與使用吧，希望對(duì)各位同學(xué)會(huì)有所幫助。
表分區(qū)的測(cè)試使用，主要內(nèi)容來(lái)自于其他博客文章以及mysql5.1的參考手冊(cè)
mysql測(cè)試版本：mysql5.5.28
mysql物理存儲(chǔ)文件（有mysql配置的datadir決定存儲(chǔ)路徑）格式簡(jiǎn)介
數(shù)據(jù)庫(kù)engine為MYISAM

frm表結(jié)構(gòu)文件，myd表數(shù)據(jù)文件，myi表索引文件。
INNODB engine對(duì)應(yīng)的表物理存儲(chǔ)文件
innodb的數(shù)據(jù)庫(kù)的物理文件結(jié)構(gòu)為：
.frm文件
.ibd文件和.ibdata文件：
這兩種文件都是存放innodb數(shù)據(jù)的文件，之所以用兩種文件來(lái)存放innodb的數(shù)據(jù)，是因?yàn)閕nnodb的數(shù)據(jù)存儲(chǔ)方式能夠通過(guò)配置來(lái)決定是使用共享表空間存放存儲(chǔ)數(shù)據(jù)，還是用獨(dú)享表空間存放存儲(chǔ)數(shù)據(jù)。
獨(dú)享表空間存儲(chǔ)方式使用.ibd文件，并且每個(gè)表一個(gè)ibd文件
共享表空間存儲(chǔ)方式使用.ibdata文件，所有表共同使用一個(gè)ibdata文件
創(chuàng)建分區(qū)
分區(qū)的一些優(yōu)點(diǎn)包括：
· 與單個(gè)磁盤或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)。
· 對(duì)于那些已經(jīng)失去保存意義的數(shù)據(jù)，通?？梢酝ㄟ^(guò)刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過(guò)程又可以通過(guò)為那些新數(shù)據(jù)專門增加一個(gè)新的分區(qū)，來(lái)很方便地實(shí)現(xiàn)。
通常和分區(qū)有關(guān)的其他優(yōu)點(diǎn)包括下面列出的這些。MySQL 分區(qū)中的這些功能目前還沒(méi)有實(shí)現(xiàn)，但是在我們的優(yōu)先級(jí)列表中，具有高的優(yōu)先級(jí)；我們希望在5.1的生產(chǎn)版本中，能包括這些功能。
· 一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個(gè)給定WHERE 語(yǔ)句的數(shù)據(jù)可以只保存在一個(gè)或多個(gè)分區(qū)內(nèi)，這樣在查找時(shí)就不用查找其他剩余的分區(qū)。因?yàn)榉謪^(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時(shí)還不曾這么做時(shí)，可以重新組織數(shù)據(jù)，來(lái)提高那些常用查詢的效率。
·  涉及到例如SUM() 和 COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。這種查詢的一個(gè)簡(jiǎn)單例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通過(guò)“并行”， 這意味著該查詢可以在每個(gè)分區(qū)上同時(shí)進(jìn)行，最終結(jié)果只需通過(guò)總計(jì)所有分區(qū)得到的結(jié)果。
·   通過(guò)跨多個(gè)磁盤來(lái)分散數(shù)據(jù)查詢，來(lái)獲得更大的查詢吞吐量。
簡(jiǎn)而言之就是 數(shù)據(jù)管理優(yōu)化，查詢更快，數(shù)據(jù)查詢并行
檢測(cè)mysql是否支持分區(qū)

RANGE 分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

range分區(qū)就是 partition by range(id) 表示按id 1-7的數(shù)據(jù)存儲(chǔ)在p0分區(qū)；如果id大于7了則數(shù)據(jù)不能寫入了，因?yàn)闆](méi)有對(duì)應(yīng)的數(shù)據(jù)分區(qū)來(lái)存儲(chǔ)；
所以這時(shí)在創(chuàng)建分區(qū)時(shí)需要使用maxvalues關(guān)鍵字了

這樣就表示，所有id大于7的數(shù)據(jù)記錄存在在p1分區(qū)里。
RANGE分區(qū)在如下場(chǎng)合特別有用：
·  當(dāng)需要?jiǎng)h除“舊的”數(shù)據(jù)時(shí)。如果你使用上面最近的那個(gè)例子給出的分區(qū)方案，你只需簡(jiǎn)單地使用 “ALTER TABLE employees DROP PARTITION p0；”來(lái)刪除所有在1991年前就已經(jīng)停止工作的雇員相對(duì)應(yīng)的所有行。對(duì)于有大量行的表，這比運(yùn)行一個(gè)如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個(gè)DELETE查詢要有效得多。
·  想要使用一個(gè)包含有日期或時(shí)間值，或包含有從一些其他級(jí)數(shù)開始增長(zhǎng)的值的列。
·  經(jīng)常運(yùn)行直接依賴于用于分割表的列的查詢。例如，當(dāng)執(zhí)行一個(gè)如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時(shí)，MySQL可以很迅速地確定只有分區(qū)p2需要掃描，這是因?yàn)橛嘞碌姆謪^(qū)不可能包含有符合該WHERE子句的任何記錄。
LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。
list分區(qū)可以理解為按一個(gè)鍵的id區(qū)間進(jìn)行數(shù)據(jù)存儲(chǔ)，比如類型表 1,2,3,4的所有記錄存儲(chǔ)在p0里面，5,6,7,8存在在p1分區(qū)里面
這里與range分區(qū)一樣，如果現(xiàn)在有條記錄typeid是9，那么這條記錄是不能存入的；
需要注意的是：LIST分區(qū)沒(méi)有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。

HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。
HASH分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中；而在HASH分區(qū)中，MySQL 自動(dòng)完成這些工作，你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。
要使用HASH分區(qū)來(lái)分割一個(gè)表，要在CREATE TABLE 語(yǔ)句上添加一個(gè)“PARTITION BY HASH (expr)”子句，其中“expr”是一個(gè)返回一個(gè)整數(shù)的表達(dá)式。它可以僅僅是字段類型為MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一個(gè)“PARTITIONS num”子句，其中num 是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。如果沒(méi)有包括一個(gè)PARTITIONS子句，那么分區(qū)的數(shù)量將默認(rèn)為1。

InnoDB引擎
簡(jiǎn)單點(diǎn)說(shuō)就是數(shù)據(jù)的存入可以按 partition by hash(expr); 這里的expr可以是鍵名也可以是表達(dá)式比如YEAR(time)，如果是表達(dá)式的情況下
“但是應(yīng)當(dāng)記住，每當(dāng)插入或更新（或者可能刪除）一行，這個(gè)表達(dá)式都要計(jì)算一次；這意味著非常復(fù)雜的表達(dá)式可能會(huì)引起性能問(wèn)題，尤其是在執(zhí)行同時(shí)影響大量行的運(yùn)算（例如批量插入）的時(shí)候。 ”
在執(zhí)行刪除、寫入、更新時(shí)這個(gè)表達(dá)式都會(huì)計(jì)算一次。
數(shù)據(jù)的分布采用基于用戶函數(shù)結(jié)果的模數(shù)來(lái)確定使用哪個(gè)編號(hào)的分區(qū)。換句話，對(duì)于一個(gè)表達(dá)式“expr”，將要保存記錄的分區(qū)編號(hào)為N ，其中“N = MOD(expr, num)”。
比如上面的storeid 為10；那么 N=MOD(10,4) ;N是等于2的，那么這條記錄就存儲(chǔ)在p2的分區(qū)里面。
如果插入一個(gè)表達(dá)式列值為'2005-09-15′的記錄到表中，那么保存該條記錄的分區(qū)確定如下：MOD(YEAR('2005-09-01′),4)  =  MOD(2005,4)  =  1 ;就存儲(chǔ)在p1分區(qū)里面了。
“MySQL 5.1 還支持一個(gè)被稱為“l(fā)inear hashing（線性哈希功能）”的變量，它使用一個(gè)更加復(fù)雜的算法來(lái)確定新行插入到已經(jīng)分區(qū)了的表中的位置。
線性哈希分區(qū)和常規(guī)哈希分區(qū)在語(yǔ)法上的唯一區(qū)別在于，在“PARTITION BY” 子句中添加“LINEAR”關(guān)鍵字；線性哈希功能使用的一個(gè)線性的2的冪（powers-of-two）運(yùn)算法則
按照線性哈希分區(qū)的優(yōu)點(diǎn)在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有極其大量（1000GB）數(shù)據(jù)的表。
它的缺點(diǎn)在于，與使用常規(guī)HASH分區(qū)得到的數(shù)據(jù)分布相比，各個(gè)分區(qū)間數(shù)據(jù)的分布不大可能均衡?！?BR>KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

按照KEY進(jìn)行分區(qū)類似于按照HASH分區(qū)，除了HASH分區(qū)使用的用戶定義的表達(dá)式，而KEY分區(qū)的 哈希函數(shù)是由MySQL 服務(wù)器提供。MySQL 簇（Cluster）使用函數(shù)MD5()來(lái)實(shí)現(xiàn)KEY分區(qū)；對(duì)于使用其他存儲(chǔ)引擎的表，服務(wù)器使用其自己內(nèi)部的 哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運(yùn)算法則。
“CREATE TABLE … PARTITION BY KEY”的語(yǔ)法規(guī)則類似于創(chuàng)建一個(gè)通過(guò)HASH分區(qū)的表的規(guī)則。它們唯一的區(qū)別在于使用的關(guān)鍵字是KEY而不是HASH，并且KEY分區(qū)只采用一個(gè)或多個(gè)列名的一個(gè)列表。
與hash的區(qū)別就是，hash使用用戶定義的表達(dá)式如YEAR(time) ；而key分區(qū)則是由mysql服務(wù)器提供的。同樣KEY也是可以使用linear線性key的，與hash linear是相同的算法。
子分區(qū)：是分區(qū)表中每個(gè)分區(qū)的再次分割。

可以看到p_subpartition有三個(gè)分區(qū)p0,p1,p2；而這三個(gè)分區(qū)每一個(gè)又進(jìn)一步分為2個(gè)分區(qū)。那么整個(gè)表都就分為6個(gè)小分區(qū)；

可以看到代表p_sobpartitionp0.myd的文件消失了，取代的是p_subpartition#p#p0#sp#s1.myd
在MySQL 5.1中，對(duì)于已經(jīng)通過(guò)RANGE或LIST分區(qū)了的表再進(jìn)行子分區(qū)是可能的。
子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割，子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)。這 也被稱為復(fù)合分區(qū)（composite partitioning）。
1，如果一個(gè)分區(qū)中創(chuàng)建了子分區(qū)，其他分區(qū)也要有子分區(qū)
2，如果創(chuàng)建了了分區(qū)，每個(gè)分區(qū)中的子分區(qū)數(shù)必有相同
3，同一分區(qū)內(nèi)的子分區(qū)，名字不相同，不同分區(qū)內(nèi)的子分區(qū)名子可以相同（5.1.50不適用）
分區(qū)注意點(diǎn)
1、重新分區(qū)時(shí)，如果原分區(qū)里面存在maxvalue則新的分區(qū)里面也必須包含maxvalue否則就錯(cuò)誤。
alter table p_range2x
reorganize partition p1,p2
into (partition p0 values less than (5), partition p1 values less than maxvalue);
[Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
2、分區(qū)刪除時(shí)，數(shù)據(jù)也同樣會(huì)被刪除
alter table p_range drop partition p0;
3、如果range分區(qū)列表里面沒(méi)有maxvalue則如有新數(shù)據(jù)大于現(xiàn)在分區(qū)range數(shù)據(jù)值那么這個(gè)數(shù)據(jù)是無(wú)法寫入到數(shù)據(jù)庫(kù)表的。
4、修改表名不需要 刪除分區(qū)后在進(jìn)行更改，修改表名后分區(qū)存儲(chǔ)myd myi對(duì)應(yīng)也會(huì)自動(dòng)更改。
如果希望從所有分區(qū)刪除所有的數(shù)據(jù)，但是又保留表的定義和表的分區(qū)模式，使用TRUNCATE TABLE命令。（請(qǐng)參見13.2.9節(jié)，“TRUNCATE語(yǔ)法”）。
如果希望改變表的分區(qū)而又不丟失數(shù)據(jù)，使用“ALTER TABLE … REORGANIZE PARTITION”語(yǔ)句。參見下面的內(nèi)容，或者在13.1.2節(jié)，“ALTER TABLE語(yǔ)法” 中參考關(guān)于REORGANIZE PARTITION的信息。
5、對(duì)表進(jìn)行分區(qū)時(shí)，不論采用哪種分區(qū)方式如果表中存在主鍵那么主鍵必須在分區(qū)列中。表分區(qū)的局限性。
6、list方式分區(qū)沒(méi)有類似于range那種 less than maxvalue的寫法，也就是說(shuō)list分區(qū)表的所有數(shù)據(jù)都必須在分區(qū)字段的值列表集合中。
7、在MySQL 5.1版中，同一個(gè)分區(qū)表的所有分區(qū)必須使用同一個(gè)存儲(chǔ)引擎；例如，不能對(duì)一個(gè)分區(qū)使用MyISAM，而對(duì)另一個(gè)使用InnoDB。
8、分區(qū)的名字是不區(qū)分大小寫的，myp1與MYp1是相同的。
分區(qū)的管理
range與list分區(qū)的改變動(dòng)作不能適用于hash與key方式的分區(qū)。刪除與添加動(dòng)作是都能使用的。
以下面的例子

range與list分區(qū)的管理
刪除分區(qū)
ALTER TABLE tr DROP PARTITION p1;
需要注意的是刪除分區(qū)后，該分區(qū)的所有數(shù)據(jù)都沒(méi)有了。同時(shí)刪除后存在一個(gè)重大影響也就是typeid為5,6,7,8的記錄是不能寫入到該表了的！
清空數(shù)據(jù)
如果想要保留表結(jié)構(gòu)與分區(qū)結(jié)構(gòu)可以使用 TRUNCATE TABLE 清空表
更改分區(qū)保留數(shù)據(jù)
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；如果想保留數(shù)據(jù)進(jìn)行分區(qū)的更改
ALTER TABLE p_list REORGANIZE PARTITION p0 INTO (
 PARTITION s0 VALUES IN(1,2),
 PARTITION s1 VALUES IN(3,4),
);這樣就能進(jìn)行分區(qū)的合并了，那怎么進(jìn)行拆分呢
ALTER TABLE p_list REORGANIZE PARTITION s0,s1 INTO (
 PARTITION p0 VALUES IN(1,2,3,4),
); 使用 REORGANIZE PARTITION進(jìn)行數(shù)據(jù)的合并與拆分，數(shù)據(jù)是沒(méi)有丟失的。
在使用REORGANIZE進(jìn)行重新分區(qū)時(shí)，需要注意幾點(diǎn)：
1、用來(lái)確定新分區(qū)模式的PARTITION子句使用與用在CREATE TABLE中確定分區(qū)模式的PARTITION子句相同的規(guī)則。(partition 分區(qū)子句必須與創(chuàng)建原分區(qū)時(shí)的規(guī)則相同)
2、partition_definitions 列表中分區(qū)的合集應(yīng)該與在partition_list 中命名分區(qū)的合集占有相同的區(qū)間或值集合。 (不管是合并還是拆分，s0,s1到p0；p0到s0,s1 里面的區(qū)間或者值都必須相同)
3、對(duì)于按照RANGE分區(qū)的表，只能重新組織相鄰的分區(qū)；不能跳過(guò)RANGE分區(qū)。(比如按range年份 p0 1990,p1 2000 ,p2 2013三個(gè)分區(qū)；在合并時(shí)partition p0,p2 into()
   這樣是不行的，因?yàn)檫@兩個(gè)分區(qū)不是相鄰的分區(qū)；)
4、不能使用REORGANIZE PARTITION來(lái)改變表的分區(qū)類型；也就是說(shuō)，例如，不能把RANGE分區(qū)變?yōu)镠ASH分區(qū)，反之亦然。也不能使用該命令來(lái)改變分區(qū)表達(dá)式或列。
增加分區(qū)
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 10, 11))；
但是不能使用
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 14))；
這樣mysql 會(huì)產(chǎn)生錯(cuò)誤1465 (HY000): 在LIST分區(qū)中，同一個(gè)常數(shù)的多次定義
hash與key分區(qū)的管理在改變分區(qū)設(shè)置方面，按照HASH分區(qū)或KEY分區(qū)的表彼此非常相似，但是它們又與按照RANGE或LIST分區(qū)的表在很多方面有差別。
關(guān)于添加和刪除按照RANGE或LIST進(jìn)行分區(qū)的表的分區(qū)
不能使用與從按照RANGE或LIST分區(qū)的表中刪除分區(qū)相同的方式，來(lái)從HASH或KEY分區(qū)的表中刪除分區(qū)。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令來(lái)合并HASH或KEY分區(qū)。
查看源代碼打印幫助1 DROP TABLE IF EXISTS `p_hash`;  2    3 CREATE TABLE `p_hash` (  4 `id` int(10) NOT NULL AUTO_INCREMENT,  5 `storeid` mediumint(10) NOT NULL DEFAULT '0',  6 `storename` char(255) DEFAULT NULL,  7 PRIMARY KEY (`id`,`storeid`)  8 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8  9 /*!50100 PARTITION BY HASH (storeid)  10 PARTITIONS 4 */;
如p_hash的分區(qū)數(shù)為4個(gè)；
要減少分區(qū)數(shù)為2個(gè)
ALTER TABLE p_hash COALESCE PARTITION 2；
對(duì)于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分區(qū)的表， COALESCE能起到同樣的作用。COALESCE不能用來(lái)增加分區(qū)的數(shù)量，如果你嘗試這么做，結(jié)果會(huì)出現(xiàn)類似于下面的錯(cuò)誤：
mysql> ALTER TABLE clients COALESCE PARTITION 18;
錯(cuò)誤1478 (HY000): 不能移動(dòng)所有分區(qū)，使用DROP TABLE代替要增加顧客表的分區(qū)數(shù)量從12到18，使用“ALTER TABLE … ADD PARTITION”,具體如下：
ALTER TABLE clients ADD PARTITION PARTITIONS 18；注釋：“ALTER TABLE … REORGANIZE PARTITION”不能用于按照HASH或HASH分區(qū)的表。
分區(qū)維護(hù)
重建分區(qū)
這和先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，具有同樣的效果。它可用于整理分區(qū)碎片。
ALTER TABLE t1 REBUILD PARTITION (p0, p1)；
優(yōu)化分區(qū)如果從分區(qū)中刪除了大量的行，或者對(duì)一個(gè)帶有可變長(zhǎng)度的行（也就是說(shuō)，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，
可以使用“ALTER TABLE … OPTIMIZE PARTITION”來(lái)收回沒(méi)有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；
分析分區(qū)
讀取并保存分區(qū)的鍵分布
ALTER TABLE t1 ANALYZE PARTITION (p3)；
修補(bǔ)分區(qū)： 修補(bǔ)被破壞的分區(qū)。
ALTER TABLE t1 REPAIR PARTITION (p0,p1);
檢查分區(qū)
可以使用幾乎與對(duì)非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。
ALTER TABLE trb3 CHECK PARTITION (p1)；
這個(gè)命令可以告訴你表t1的分區(qū)p1中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來(lái)修補(bǔ)該分區(qū)。獲取分區(qū)信息
在mysql服務(wù)器信息數(shù)據(jù)庫(kù)里面的partitions存放著服務(wù)器所有表的分區(qū)信息。

-- 獲取到p_list表的分區(qū)詳細(xì)信息。
select * from information_schema.`PARTITIONS` where TABLE_NAME = 'p_list';
-- 分區(qū)的創(chuàng)建信息
show create table p_list;