mysql表分區創建使用學習筆記

表分區是最近才知道的哦 ，以前自己做都是分表來實現上億級別的數據了，下面我來給大家介紹一下mysql表分區創建與使用吧，希望對各位同學會有所幫助。

表分區的測試使用，主要內容來自於其他博客文章以及mysql5.1的參考手冊

mysql測試版本：mysql5.5.28

mysql物理存儲文件（有mysql配置的datadir決定存儲路徑）格式簡介

數據庫engine為MYISAM

 

frm表結構文件，myd表數據文件，myi表索引文件。

INNODB engine對應的表物理存儲文件

“

innodb的數據庫的物理文件結構為：

.frm文件

.ibd文件和.ibdata文件：

這兩種文件都是存放innodb數據的文件，之所以用兩種文件來存放innodb的數據，是因為innodb的數據存儲方式能夠通過配置來決定是使用共享表空間存放存儲數據，還是用獨享表空間存放存儲數據。

獨享表空間存儲方式使用.ibd文件，並且每個表一個ibd文件

共享表空間存儲方式使用.ibdata文件，所有表共同使用一個ibdata文件

”

創建分區
分區的一些優點包括：

·         與單個磁盤或文件系統分區相比，可以存儲更多的數據。

·         對於那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區，來很方便地實現。

通常和分區有關的其他優點包括下面列出的這些。MySQL 分區中的這些功能目前還沒有實現，但是在我們的優先級列表中，具有高的優先級；我們希望在5.1的生產版本中，能包括這些功能。

·         一些查詢可以得到極大的優化，這主要是借助於滿足一個給定WHERE 語句的數據可以只保存在一個或多個分區內，這樣在查找時就不用查找其他剩余的分區。因為分區可以在創建了分區表後進行修改，所以在第一次配置分區方案時還不曾這麼做時，可以重新組織數據，來提高那些常用查詢的效率。

·         涉及到例如SUM() 和 COUNT()這樣聚合函數的查詢，可以很容易地進行並行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“並行”， 這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。

·         通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量。

簡而言之就是 數據管理優化，查詢更快，數據查詢並行

檢測mysql是否支持分區

 代碼如下 復制代碼

mysql> show variables like
"%partition%";
+-------------------+-------+
| Variable_name&nbsp&nbsp | Value |
+-------------------+-------+
| have_partitioning | YES&nbsp |
+-------------------+-------+
1 row in set

RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。

 代碼如下 復制代碼

DROP TABLE IF EXISTS `p_range`;

CREATE TABLE `p_range` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

range分區就是 partition by range(id) 表示按id 1-7的數據存儲在p0分區；如果id大於7了則數據不能寫入了，因為沒有對應的數據分區來存儲；
所以這時在創建分區時需要使用maxvalues關鍵字了

 代碼如下 復制代碼

PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (8),
PARTITION p1 VALUES LESS THAN MAXVALUE)

這樣就表示，所有id大於7的數據記錄存在在p1分區裡。

RANGE分區在如下場合特別有用：

·         當需要刪除“舊的”數據時。如果你使用上面最近的那個例子給出的分區方案，你只需簡單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經停止工作的雇員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個DELETE查詢要有效得多。

·         想要使用一個包含有日期或時間值，或包含有從一些其他級數開始增長的值的列。

·         經常運行直接依賴於用於分割表的列的查詢。例如，當執行一個如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時，MySQL可以很迅速地確定只有分區p2需要掃描，這是因為余下的分區不可能包含有符合該WHERE子句的任何記錄。

LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。

list分區可以理解為按一個鍵的id區間進行數據存儲，比如類型表 1,2,3,4的所有記錄存儲在p0裡面，5,6,7,8存在在p1分區裡面

這裡與range分區一樣，如果現在有條記錄typeid是9，那麼這條記錄是不能存入的；

需要注意的是：LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內的定義。將要匹配的任何值都必須在值列表中找到。

 代碼如下 復制代碼

DROP TABLE IF EXISTS `p_list`;

CREATE TABLE `p_list` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`typeid` mediumint(10) NOT NULL DEFAULT '0',
`typename` char(20) DEFAULT NULL,
PRIMARY KEY (`id`,`typeid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (typeid)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM,
PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

HASH分區：基於用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。

HASH分區主要用來確保數據在預先確定數目的分區中平均分布。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。

要使用HASH分區來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數的表達式。它可以僅僅是字段類型為MySQL 整型的一列的名字。此外，你很可能需要在後面再添加一個“PARTITIONS num”子句，其中num 是一個非負的整數，它表示表將要被分割成分區的數量。如果沒有包括一個PARTITIONS子句，那麼分區的數量將默認為1。

 代碼如下 復制代碼

DROP TABLE IF EXISTS `p_hash`;

CREATE TABLE `p_hash` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`storeid` mediumint(10) NOT NULL DEFAULT '0',
`storename` char(255) DEFAULT NULL,
PRIMARY KEY (`id`,`storeid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (storeid)
PARTITIONS 4 */;

InnoDB引擎

簡單點說就是數據的存入可以按 partition by hash(expr); 這裡的expr可以是鍵名也可以是表達式比如YEAR(time)，如果是表達式的情況下

“但是應當記住，每當插入或更新（或者可能刪除）一行，這個表達式都要計算一次；這意味著非常復雜的表達式可能會引起性能問題，尤其是在執行同時影響大量行的運算（例如批量插入）的時候。 ”

在執行刪除、寫入、更新時這個表達式都會計算一次。

數據的分布采用基於用戶函數結果的模數來確定使用哪個編號的分區。換句話，對於一個表達式“expr”，將要保存記錄的分區編號為N ，其中“N = MOD(expr, num)”。

比如上面的storeid 為10；那麼 N=MOD(10,4) ;N是等於2的，那麼這條記錄就存儲在p2的分區裡面。

如果插入一個表達式列值為’2005-09-15′的記錄到表中，那麼保存該條記錄的分區確定如下：MOD(YEAR(’2005-09-01′),4)  =  MOD(2005,4)  =  1 ;就存儲在p1分區裡面了。

“MySQL 5.1 還支持一個被稱為“linear hashing（線性哈希功能）”的變量，它使用一個更加復雜的算法來確定新行插入到已經分區了的表中的位置。

線性哈希分區和常規哈希分區在語法上的唯一區別在於，在“PARTITION BY” 子句中添加“LINEAR”關鍵字；線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則

按照線性哈希分區的優點在於增加、刪除、合並和拆分分區將變得更加快捷，有利於處理含有極其大量（1000GB）數據的表。

它的缺點在於，與使用常規HASH分區得到的數據分布相比，各個分區間數據的分布不大可能均衡。”

KEY 分區：類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

 代碼如下 復制代碼

DROP TABLE IF EXISTS `p_key`;

CREATE TABLE `p_key` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`keyname` char(20) DEFAULT NULL,
`keyval` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (id)
PARTITIONS 4 */;

按照KEY進行分區類似於按照HASH分區，除了HASH分區使用的用戶定義的表達式，而KEY分區的 哈希函數是由MySQL 服務器提供。MySQL 簇（Cluster）使用函數MD5()來實現KEY分區；對於使用其他存儲引擎的表，服務器使用其自己內部的 哈希函數，這些函數是基於與PASSWORD()一樣的運算法則。

“CREATE TABLE … PARTITION BY KEY”的語法規則類似於創建一個通過HASH分區的表的規則。它們唯一的區別在於使用的關鍵字是KEY而不是HASH，並且KEY分區只采用一個或多個列名的一個列表。

與hash的區別就是，hash使用用戶定義的表達式如YEAR(time) ；而key分區則是由mysql服務器提供的。同樣KEY也是可以使用linear線性key的，與hash linear是相同的算法。

子分區：是分區表中每個分區的再次分割。

 代碼如下 復制代碼

DROP TABLE IF EXISTS `p_subpartition`;

CREATE TABLE `p_subpartition` (
`id` int(10) DEFAULT NULL,
`title` char(255) NOT NULL,
`createtime` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100

PARTITION BY RANGE (YEAR(createtime))
SUBPARTITION BY HASH (MONTH(createtime))
(PARTITION p0 VALUES LESS THAN (2012)
(SUBPARTITION s1 ENGINE = MyISAM,
SUBPARTITION s2 ENGINE = MyISAM),
PARTITION p1 VALUES LESS THAN (2013)
(SUBPARTITION s3 ENGINE = MyISAM,
SUBPARTITION s4 ENGINE = MyISAM),
PARTITION p2 VALUES LESS THAN MAXVALUE
(SUBPARTITION s5 ENGINE = MyISAM,
SUBPARTITION s6 ENGINE = MyISAM)) */;

可以看到p_subpartition有三個分區p0,p1,p2；而這三個分區每一個又進一步分為2個分區。那麼整個表都就分為6個小分區；

 

可以看到代表p_sobpartitionp0.myd的文件消失了，取代的是p_subpartition#p#p0#sp#s1.myd

在MySQL 5.1中，對於已經通過RANGE或LIST分區了的表再進行子分區是可能的。

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

1，如果一個分區中創建了子分區，其他分區也要有子分區

2，如果創建了了分區，每個分區中的子分區數必有相同

3，同一分區內的子分區，名字不相同，不同分區內的子分區名子可以相同（5.1.50不適用）

分區注意點
1、重新分區時，如果原分區裡面存在maxvalue則新的分區裡面也必須包含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、分區刪除時，數據也同樣會被刪除

alter table p_range drop partition p0;

3、如果range分區列表裡面沒有maxvalue則如有新數據大於現在分區range數據值那麼這個數據是無法寫入到數據庫表的。

4、修改表名不需要 刪除分區後在進行更改，修改表名後分區存儲myd myi對應也會自動更改。

如果希望從所有分區刪除所有的數據，但是又保留表的定義和表的分區模式，使用TRUNCATE TABLE命令。（請參見13.2.9節，“TRUNCATE語法”）。

如果希望改變表的分區而又不丟失數據，使用“ALTER TABLE … REORGANIZE PARTITION”語句。參見下面的內容，或者在13.1.2節，“ALTER TABLE語法” 中參考關於REORGANIZE PARTITION的信息。

5、對表進行分區時，不論采用哪種分區方式如果表中存在主鍵那麼主鍵必須在分區列中。表分區的局限性。

6、list方式分區沒有類似於range那種 less than maxvalue的寫法，也就是說list分區表的所有數據都必須在分區字段的值列表集合中。

7、在MySQL 5.1版中，同一個分區表的所有分區必須使用同一個存儲引擎；例如，不能對一個分區使用MyISAM，而對另一個使用InnoDB。

8、分區的名字是不區分大小寫的，myp1與MYp1是相同的。

分區的管理
range與list分區的改變動作不能適用於hash與key方式的分區。刪除與添加動作是都能使用的。

以下面的例子

 代碼如下 復制代碼

DROP TABLE IF EXISTS `p_list`;

CREATE TABLE `p_list` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`typeid` mediumint(10) NOT NULL DEFAULT '0',
`typename` char(20) DEFAULT NULL,
PRIMARY KEY (`id`,`typeid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (typeid)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM,
PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

range與list分區的管理
刪除分區
ALTER TABLE tr DROP PARTITION p1;
需要注意的是刪除分區後，該分區的所有數據都沒有了。同時刪除後存在一個重大影響也就是typeid為5,6,7,8的記錄是不能寫入到該表了的！

清空數據

如果想要保留表結構與分區結構可以使用 TRUNCATE TABLE 清空表

更改分區保留數據

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；如果想保留數據進行分區的更改

ALTER TABLE p_list REORGANIZE PARTITION p0 INTO (
 PARTITION s0 VALUES IN(1,2),
 PARTITION s1 VALUES IN(3,4),
);這樣就能進行分區的合並了，那怎麼進行拆分呢

ALTER TABLE p_list REORGANIZE PARTITION s0,s1 INTO (
 PARTITION p0 VALUES IN(1,2,3,4),
); 使用 REORGANIZE PARTITION進行數據的合並與拆分，數據是沒有丟失的。
在使用REORGANIZE進行重新分區時，需要注意幾點：
1、用來確定新分區模式的PARTITION子句使用與用在CREATE TABLE中確定分區模式的PARTITION子句相同的規則。(partition 分區子句必須與創建原分區時的規則相同)
2、partition_definitions 列表中分區的合集應該與在partition_list 中命名分區的合集占有相同的區間或值集合。 (不管是合並還是拆分，s0,s1到p0；p0到s0,s1 裡面的區間或者值都必須相同)
3、對於按照RANGE分區的表，只能重新組織相鄰的分區；不能跳過RANGE分區。(比如按range年份 p0 1990,p1 2000 ,p2 2013三個分區；在合並時partition p0,p2 into()
   這樣是不行的，因為這兩個分區不是相鄰的分區；)
4、不能使用REORGANIZE PARTITION來改變表的分區類型；也就是說，例如，不能把RANGE分區變為HASH分區，反之亦然。也不能使用該命令來改變分區表達式或列。

增加分區
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 會產生錯誤1465 (HY000): 在LIST分區中，同一個常數的多次定義
hash與key分區的管理在改變分區設置方面，按照HASH分區或KEY分區的表彼此非常相似，但是它們又與按照RANGE或LIST分區的表在很多方面有差別。
關於添加和刪除按照RANGE或LIST進行分區的表的分區
不能使用與從按照RANGE或LIST分區的表中刪除分區相同的方式，來從HASH或KEY分區的表中刪除分區。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令來合並HASH或KEY分區。
查看源代碼打印幫助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的分區數為4個；
要減少分區數為2個
ALTER TABLE p_hash COALESCE PARTITION 2；
對於按照HASH，KEY，LINEAR HASH，或LINEAR KEY分區的表， COALESCE能起到同樣的作用。COALESCE不能用來增加分區的數量，如果你嘗試這麼做，結果會出現類似於下面的錯誤：

mysql> ALTER TABLE clients COALESCE PARTITION 18;
錯誤1478 (HY000): 不能移動所有分區，使用DROP TABLE代替要增加顧客表的分區數量從12到18，使用“ALTER TABLE … ADD PARTITION”,具體如下：

ALTER TABLE clients ADD PARTITION PARTITIONS 18；注釋：“ALTER TABLE … REORGANIZE PARTITION”不能用於按照HASH或HASH分區的表。

分區維護
重建分區

這和先刪除保存在分區中的所有記錄，然後重新插入它們，具有同樣的效果。它可用於整理分區碎片。

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；

優化分區如果從分區中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，

可以使用“ALTER TABLE … OPTIMIZE PARTITION”來收回沒有使用的空間，並整理分區數據文件的碎片。

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；

分析分區
讀取並保存分區的鍵分布
ALTER TABLE t1 ANALYZE PARTITION (p3)；

修補分區： 修補被破壞的分區。
ALTER TABLE t1 REPAIR PARTITION (p0,p1);

檢查分區
可以使用幾乎與對非分區表使用CHECK TABLE 相同的方式檢查分區。
ALTER TABLE trb3 CHECK PARTITION (p1)；
這個命令可以告訴你表t1的分區p1中的數據或索引是否已經被破壞。如果發生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補該分區。獲取分區信息
在mysql服務器信息數據庫裡面的partitions存放著服務器所有表的分區信息。

--

 代碼如下 復制代碼 explain partitions命令
explain partitions select * from p_hash
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table&nbsp; | partitions&nbsp; | type | possible_keys | key&nbsp; | key_len | ref&nbsp; | rows | Extra |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+
|&nbsp; 1 | SIMPLE&nbsp&nbsp&nbsp; | p_hash | p0,p1,p2,p3 | ALL&nbsp; | NULL&nbsp&nbsp&nbsp&nbsp&nbsp; | NULL | NULL&nbsp&nbsp; | NULL |&nbsp 10 |&nbsp&nbsp&nbsp |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+

-- 獲取到p_list表的分區詳細信息。

select * from information_schema.`PARTITIONS` where TABLE_NAME = 'p_list';
-- 分區的創建信息
show create table p_list;