表分區的測試使用,主要內容來自于其他博客文章以及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是否支持分區,代碼如下:

1. mysql> show variables like 
2. "%partition%"; 
3. +-------------------+-------+ 
4. | Variable_name&nbsp&nbsp | Value | 
5. +-------------------+-------+ 
6. | have_partitioning | YES&nbsp | 
7. +-------------------+-------+ 
8. 1 row in set 

RANGE 分區,基于屬于一個給定連續區間的列值,把多行分配給分區,代碼如下:

1. DROP TABLE IF EXISTS `p_range`; 
2.  
3. CREATE TABLE `p_range` ( 
4. `id` int(10) NOT NULL AUTO_INCREMENT, 
5. `name` char(20) NOT NULL, 
6. PRIMARY KEY (`id`) 
7. ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 
8. /*!50100 PARTITION BY RANGE (id) 
9. (PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */; 

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

1. PARTITION BY RANGE (id) 
2. ( 
3. PARTITION p0 VALUES LESS THAN (8), 
4. 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”這樣的包含其他值在內的定義,將要匹配的任何值都必須在值列表中找到,代碼如下:

1. DROP TABLE IF EXISTS `p_list`; 
2.  
3. CREATE TABLE `p_list` ( 
4. `id` int(10) NOT NULL AUTO_INCREMENT, 
5. `typeid` mediumint(10) NOT NULL DEFAULT '0', 
6. `typename` char(20) DEFAULT NULL, 
7. PRIMARY KEY (`id`,`typeid`) 
8. ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 
9. /*!50100 PARTITION BY LIST (typeid) 
10. (PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM, 
11. 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,代碼如下:

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 */; 

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 服務器提供其自身的哈希函數,必須有一列或多列包含整數值,代碼如下:

1. DROP TABLE IF EXISTS `p_key`; 
2.  
3. CREATE TABLE `p_key` ( 
4. `id` int(10) NOT NULL AUTO_INCREMENT, 
5. `keyname` char(20) DEFAULT NULL, 
6. `keyval` varchar(1000) DEFAULT NULL, 
7. PRIMARY KEY (`id`) 
8. ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 
9. /*!50100 PARTITION BY KEY (id) 
10. 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是相同的算法。

子分區:是分區表中每個分區的再次分割,代碼如下:

1. DROP TABLE IF EXISTS `p_subpartition`; 
2.  
3. CREATE TABLE `p_subpartition` ( 
4. `id` int(10) DEFAULT NULL, 
5. `title` char(255) NOT NULL, 
6. `createtime` date NOT NULL 
7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 
8. /*!50100 
9.  
10. PARTITION BY RANGE (YEAR(createtime)) 
11. SUBPARTITION BY HASH (MONTH(createtime)) 
12. (PARTITION p0 VALUES LESS THAN (2012) 
13. (SUBPARTITION s1 ENGINE = MyISAM, //Vevb.com
14. SUBPARTITION s2 ENGINE = MyISAM), 
15. PARTITION p1 VALUES LESS THAN (2013) 
16. (SUBPARTITION s3 ENGINE = MyISAM, 
17. SUBPARTITION s4 ENGINE = MyISAM), 
18. PARTITION p2 VALUES LESS THAN MAXVALUE 
19. (SUBPARTITION s5 ENGINE = MyISAM, 
20. SUBPARTITION s6 ENGINE = MyISAM)) */;