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

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

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

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

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

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

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

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

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

可以看到代表p_sobpartitionp0.myd的文件消失了，取代的是p_subpartition#p#p0#sp#s1.myd
在MySQL 5.1中，對于已經(jīng)通過RANGE或LIST分區(qū)了的表再進(jìn)行子分區(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不適用）
分區(qū)注意點
1、重新分區(qū)時，如果原分區(qū)里面存在maxvalue則新的分區(qū)里面也必須包含maxvalue否則就錯誤。
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ù)據(jù)也同樣會被刪除
alter table p_range drop partition p0;
3、如果range分區(qū)列表里面沒有maxvalue則如有新數(shù)據(jù)大于現(xiàn)在分區(qū)range數(shù)據(jù)值那么這個數(shù)據(jù)是無法寫入到數(shù)據(jù)庫表的。
4、修改表名不需要 刪除分區(qū)后在進(jìn)行更改，修改表名后分區(qū)存儲myd myi對應(yīng)也會自動更改。
如果希望從所有分區(qū)刪除所有的數(shù)據(jù)，但是又保留表的定義和表的分區(qū)模式，使用TRUNCATE TABLE命令。（請參見13.2.9節(jié)，“TRUNCATE語法”）。
如果希望改變表的分區(qū)而又不丟失數(shù)據(jù)，使用“ALTER TABLE … REORGANIZE PARTITION”語句。參見下面的內(nèi)容，或者在13.1.2節(jié)，“ALTER TABLE語法” 中參考關(guān)于REORGANIZE PARTITION的信息。
5、對表進(jìn)行分區(qū)時，不論采用哪種分區(qū)方式如果表中存在主鍵那么主鍵必須在分區(qū)列中。表分區(qū)的局限性。
6、list方式分區(qū)沒有類似于range那種 less than maxvalue的寫法，也就是說list分區(qū)表的所有數(shù)據(jù)都必須在分區(qū)字段的值列表集合中。
7、在MySQL 5.1版中，同一個分區(qū)表的所有分區(qū)必須使用同一個存儲引擎；例如，不能對一個分區(qū)使用MyISAM，而對另一個使用InnoDB。
8、分區(qū)的名字是不區(qū)分大小寫的，myp1與MYp1是相同的。
分區(qū)的管理
range與list分區(qū)的改變動作不能適用于hash與key方式的分區(qū)。刪除與添加動作是都能使用的。
以下面的例子

range與list分區(qū)的管理
刪除分區(qū)
ALTER TABLE tr DROP PARTITION p1;
需要注意的是刪除分區(qū)后，該分區(qū)的所有數(shù)據(jù)都沒有了。同時刪除后存在一個重大影響也就是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ù)是沒有丟失的。
在使用REORGANIZE進(jìn)行重新分區(qū)時，需要注意幾點：
1、用來確定新分區(qū)模式的PARTITION子句使用與用在CREATE TABLE中確定分區(qū)模式的PARTITION子句相同的規(guī)則。(partition 分區(qū)子句必須與創(chuàng)建原分區(qū)時的規(guī)則相同)
2、partition_definitions 列表中分區(qū)的合集應(yīng)該與在partition_list 中命名分區(qū)的合集占有相同的區(qū)間或值集合。 (不管是合并還是拆分，s0,s1到p0；p0到s0,s1 里面的區(qū)間或者值都必須相同)
3、對于按照RANGE分區(qū)的表，只能重新組織相鄰的分區(qū)；不能跳過RANGE分區(qū)。(比如按range年份 p0 1990,p1 2000 ,p2 2013三個分區(qū)；在合并時partition p0,p2 into()
   這樣是不行的，因為這兩個分區(qū)不是相鄰的分區(qū)；)
4、不能使用REORGANIZE PARTITION來改變表的分區(qū)類型；也就是說，例如，不能把RANGE分區(qū)變?yōu)镠ASH分區(qū)，反之亦然。也不能使用該命令來改變分區(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 會產(chǎn)生錯誤1465 (HY000): 在LIST分區(qū)中，同一個常數(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ū)相同的方式，來從HASH或KEY分區(qū)的表中刪除分區(qū)。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令來合并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個；
要減少分區(qū)數(shù)為2個
ALTER TABLE p_hash COALESCE PARTITION 2；
對于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分區(qū)的表， COALESCE能起到同樣的作用。COALESCE不能用來增加分區(qū)的數(shù)量，如果你嘗試這么做，結(jié)果會出現(xiàn)類似于下面的錯誤：
mysql> ALTER TABLE clients COALESCE PARTITION 18;
錯誤1478 (HY000): 不能移動所有分區(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ū)中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，
可以使用“ALTER TABLE … OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；
分析分區(qū)
讀取并保存分區(qū)的鍵分布
ALTER TABLE t1 ANALYZE PARTITION (p3)；
修補分區(qū)： 修補被破壞的分區(qū)。
ALTER TABLE t1 REPAIR PARTITION (p0,p1);
檢查分區(qū)
可以使用幾乎與對非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。
ALTER TABLE trb3 CHECK PARTITION (p1)；
這個命令可以告訴你表t1的分區(qū)p1中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補該分區(qū)。獲取分區(qū)信息
在mysql服務(wù)器信息數(shù)據(jù)庫里面的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;

最新評論