MySQL分區概述：
允許根據可以設置為任意大小的規則，跨文件系統分配單個表的多個部分。實際上，表的不同部分在不同的位置被存儲為單獨的表。用戶所選擇的、實現數據分割的規則被稱為分區函數，這在MySQL中它可以是模數，或者是簡單的匹配一個連續的數值區間或數值列表，或者是一個內部HASH函數，或一個線性HASH函數。函數根據用戶指定的分區類型來選擇，把用戶提供的表達式的值作為參數。該表達式可以是一個整數列值，或一個作用在一個或多個列值上並返回一個整數的函數。[z1] 。這個表達式的值傳遞給分區函數，分區函數返回一個表示那個特定記錄應該保存在哪個分區的序號。這個函數不能是常數，也不能是任意數。它不能包含任何查詢，但是實際上可以使用MySQL 中任何可用的SQL表達式，只要該表達式返回一個小於MAXVALUE（最大可能的正整數）的正數值。
由於MySQL無全局索引的概念，只有本地分區索引，基於此種原因，一個表中如果有2個或2個以上的唯一索引，此表無法分區。分區函數應用的數據庫的列必須是MySQL的主鍵，否則不能分區。[z2]
對於創建了分區的表，可以使用你的MySQL 服務器所支持的任何存儲引擎。在MySQL 5.1版中，同一個分區表的所有分區必須使用同一個存儲引擎；例如，不能對一個分區使用MyISAM，而對另一個使用InnoDB。但是，這並不妨礙在同一個 MySQL 服務器中，甚至在同一個數據庫中，對於不同的分區表使用不同的存儲引擎。
MySQL分區的建立
MySQL可以建立四種分區類型的分區：
RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。詳情參見18.2.1節，“RANGE分區”。
· LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。詳情參見18.2.2節，“LIST分區”。
· HASH分區：基於用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。詳情參見18.2.3節，“HASH分區”。
· KEY 分區：類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。詳情參照：18.2.4. KEY分區。
子分區：子分區是分區表中每個分區的再次分割。書寫格式參照：18.2.5. 子分區
（1）關於子分區應注意的地方： 每個分區必須有相同數量的子分區。
· （2）如果在一個分區表上的任何分區上使用SUBPARTITION 來明確定義任何子分區，那麼就必須定義所有的子分區。
在建立分區的時候可以指定分區的數據存儲位置和索引位置，這樣可以跨磁盤或者文件系統保存不同的數據。數據分磁盤存儲可以一定程度上增加數據讀取速度，因為采用多磁盤後，每個磁盤的I/O操作會降低。而且采用指定分區存儲位置能夠增大存儲量。
無論使用何種類型的分區，分區總是在創建時就自動的順序編號，且從0開始記錄，記住這一點非常重要。當有一新行插入到一個分區表中時，就是使用這些分區編號來識別正確的分區。例如，如果你的表使用4個分區，那麼這些分區就編號為0, 1, 2, 和3。對於RANGE和LIST分區類型，確認每個分區編號都定義了一個分區，很有必要。對HASH分區，使用的用戶函數必須返回一個大於0的整數值。對於KEY分區，這個問題通過MySQL服務器內部使用的 哈希函數自動進行處理。注意：分區的名字是不區分大小寫的，且對於RANGE分區和LIST分區，分區的名稱是不能重復的。這幾種可根據不同的需求來選擇，比較常用的是RANGE分區。
常用的MySQL的分區管理：
RANGE 和LIST分區管理
分區對於程序來說是透明的，而且只有刪除能在分區層面上操作，其他如查詢、修改、增加都不能指定分區。
ALTER TABLE …DROPPARTITION ….(刪除分區)
ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分區
ALTER TABLE ... REORGANIZE PARTITION …,… INTO (
PARTITION p0 VALUES LESS THAN (…)
)；[z4] 合並拆分分區。
HASH和KEY分區管理
添加分區和RANGE、LIST分區方式相同，對於修改分區，不能使用與從按照RANGE或LIST分區的表中刪除分區相同的方式，來從HASH或KEY分區的表中刪除分區。但是，可以使用“ALTERTABLE ... COALESCE PARTITION”命令來合並HASH或KEY分區。
如果要查看分區的信息，可以通過sql語句來查詢
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’
分區表效率比較
MySQL分區表實驗
分區采用紅色，不分區采用藍色
測試環境：CentOS 虛擬機，1G內存，20G硬盤
實驗數據庫：test 不分區（內有1張表RPT_MALEVENTS）、test2（與test一樣）
背景數據：
mysql> SELECT COUNT(*)FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (10.84 sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (14.63sec)
數據分布：2011/8/4~2011/8/17
分區表結構：
CREATETABLE `RPT_MALEVENTS` (
`RECORD_DATE` date NOT NULL,
`RECORD_HOUR` tinyint(2) NOT NULL,
`RECORD_MINUTE` tinyint(2) NOT NULL,
`RECORD_DATETIME` datetime NOT NULL,
`MC_IP` int(10) unsigned NOT NULL,
`PC_IP` int(10) unsigned NOT NULL,
`NETOBJECT_GROUP_ID` smallint(5) DEFAULTNULL,
`ALERT_TYPE` tinyint(3) NOT NULL,
`SUB_TYPE` smallint(5) NOT NULL,
`SHOW_TYPE` smallint(5) NOT NULL,
`ALERT_ID` tinyint(3) NOT NULL,
`EVENT_COUNT` int(10) unsigned DEFAULT NULL,
PRIMARY KEY(`RECORD_DATE`,`RECORD_HOUR`,`RECORD_MINUTE`,`MC_IP`,`PC_IP`,`ALERT_TYPE`,`SUB_TYPE`,`ALERT_ID`),
KEY `RECORD_DATETIME` (`RECORD_DATETIME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITIONBY RANGE (TO_DAYS(RECORD_DATE)[z1] ) (PARTITION p2011 VALUES LESS THAN (734503)ENGINE = InnoDB, PARTITION p20110809 VALUES LESS THAN (734724) ENGINE = InnoDB,PARTITION p20110810 VALUES LESS THAN (734725) ENGINE = InnoDB, PARTITIONp20110811 VALUES LESS THAN (734726) ENGINE = InnoDB, PARTITION p20110812 VALUESLESS THAN (734727) ENGINE = InnoDB, PARTITION p20110813 VALUES LESS THAN(734728) ENGINE = InnoDB, PARTITION p20110814 VALUES LESS THAN (734729) ENGINE= InnoDB, PARTITION p20110815 VALUES LESS THAN (734730) ENGINE = InnoDB,PARTITION p20110816 VALUES LESS THAN (734731) ENGINE = InnoDB, PARTITIONp20110817 VALUES LESS THAN (734732) ENGINE = InnoDB, PARTITION p20110818 VALUESLESS THAN (734733) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE[z2] ENGINE = InnoDB)
分區表的物理存儲如下，當前用的是innodB的存儲引擎，采用分表結構
分析如下
(條件查詢查詢全部數據)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE< '2011-08-19';
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (21.62sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE< '2011-08-19';
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (29.20sec)
(查詢部分數據，不使用分區函數使用的列)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' ANDRECORD_DATETIME < '2011-08-11';
+----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (2.83sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' AND RECORD_DATETIME< '2011-08-11';
+----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (5.60sec)
(使用其他條件查詢部分數據)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (8.49sec)
SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (12.88sec)
(小范圍查詢,在一個分區內查詢)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE< '2011-08-15';
+----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (1.85sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE< '2011-08-15';
+----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (3.10sec)
分析SQL語句的執行過程
rows表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數。
mysql>EXPLAIN PARTITIONS SELECT * FROMRPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME <'2011-08-13' LIMIT 1\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p2011[z3] 0816,p20110817,p20110818,pMax
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 355911[z4]
Extra: Using where
1 row in set (0.00sec)
mysql> EXPLAIN SELECT * FROM RPT_MALEVENTS WHERERECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME < '2011-08-13' LIMIT1\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 1002288[z5]
Extra: Using where
1 row in set (0.00sec)
與分區函數使用列無關的查詢條件
mysql>EXPLAIN PARTITIONS SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p20110816,p20110817,p20110818,pMax[z6]
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17084274[z7]
Extra: Using where; Using index
1 row in set (0.00sec)
mysql> EXPLAINSELECT COUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17082459
Extra: Using where; Using index
1 row in set (0.00sec)
采用分區函數使用的列
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE < '2011-08-15'\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p20110810,p20110811,p20110812,p20110813,p20110814,p20110815[z8]
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 3767081[z9]
Extra: Using where; Using index
1 row in set (0.08sec)
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE < '2011-08-15'\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: NULL
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 8541229[z10]
Extra: Using where; Using index
1 row in set (0.00sec)
刪除數據，如果刪除1整天的數據，由於我們采用按天分區，
mysql> ALTER TABLERPT_MALEVENTS DROP PARTITION p20110809;[z11]
Query OK, 0 rowsaffected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
刪除後包含索引的和數據的RPT_MALEVENTS#P#p20110809.ibd被刪除了
如果采用傳統的不分區的方式刪除。
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE < '2011-08-10';
Query OK, 3929328rows affected (1 min 29.68 sec)
由此可見，刪除整個分區內的數據還是很快的，
如果分區表采用傳統的方式刪除：
mysql> DELETEFROM RPT_MALEVENTS WHERE RECORD_DATE< '2011-08-11';
Query OK, 1153866rows affected (19.72 sec)
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE < '2011-08-11';
Query OK, 1153866rows affected (18.75 sec)
采用傳統的方式刪除一天的數據，用的時間都差不多。
只刪除數據後，數據分區配p20110810還在，而且大小不變。可以用ALTER TABLE t1 OPTIMIZE PARTITION來進行回收，但是MySQL5.1.22還沒有實現。
跨分區刪除。
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (55.20 sec)
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (50.26 sec)
分區表刪除比不分區的略慢
[z1]分區函數
[z2]分區信息，從2011-08-09開始
[z3]沒有用分區函數使用的列會掃描所有分區
[z4]數據量為681311，分區後掃描行數為355911，雖然查詢條件沒有分區函數的列，但是mysql的查詢優化器會將其對應於時間分區，這樣可以減少掃描行數
[z5]數據量為681311，分區後掃描行數為1002288
[z6]查找所有分區
[z7]無關分區函數的字段，會遍歷幾乎所有行。
[z8]掃描部分分區
[z9]掃描行數隨之減少
[z10]估計掃描的行數
[z11]這個分區的數據是所有2011-8-10之前的所有數據，共3929328。
總結：
分區表是在MySQL5.1中新增的的功能，截止到MySQL5.1.22-rc,分區技術並不很成熟，很多分區的維護和管理功能未實現。如，分區內數據存儲空間的回收、分區的修復、分區的優化等，MySQL的分區可以用在可以按分區刪除的表中，且對數據庫的修改操作不大，且頻繁按照分區字段進行查詢的表中（如惡意代碼中的統計表按天分區，經常按照時間進行查詢、分組等，且可以按天刪除分區）。此外，由於MySQL無全局索引只有分區索引，當一張有2個唯一索引[z5] 的時候，不能將此表分區，分區列中必須包含主鍵。否則MySQL會報錯。
總之，MySQL對於分區的限制很多，且個人認為hash和key的分區實際意義不是太大。
分區引入了一種新的優化查詢的方式（當然，也有相應的缺點）。優化器可以使用分區函數修整分區，或者把分區從查詢中完全移除掉。它通過推斷是否可以在特定的分區上找到數據來達成這種優化。因此在最好的情況下，修整可以讓查詢訪問更少的數據。重要的是要在WHERE子句中定義分區鍵，即使它看上去像是多余的。通過分區鍵，優化器就可以去掉不用的分區，否則的話，執行引擎就會像合並表那樣訪問表的所有分區，這在大表上會非常慢。分區數據比非分區數據更好維護，並且可以通過刪除分區來移除老的數據。分區數據可以被分布到不同的物理位置，這樣服務器可以更有效地使用多個硬盤驅動器。
[z1]分區函數的返回值必須是整數，新增分區的分區函數返回值應大於任何一個現有分區的分區函數的返回值。
[z2]對於有主鍵的表錯誤提示：#1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION，沒有主鍵的則無此約束
[z3]注意：對於通過RANGE分區的表，只可以使用ADD PARTITION添加新的分區到分區列表的高端。即不能添加比這個分區的范圍小的分區。
[z4] 對於按照RANGE分區的表，只能重新組織相鄰的分區；不能跳過RANGE分區。不能使用REORGANIZEPARTITION來改變表的分區類型；也就是說，例如，不能把RANGE分區變為HASH分區，反之亦然。也不能使用該命令來改變分區表達式或列。
[z5]注意主鍵和唯一索引的區別
作者“深巷明朝賣杏花”
