共計(jì) 9088 個(gè)字符，預(yù)計(jì)需要花費(fèi) 23 分鐘才能閱讀完成。
MySQL 中怎么實(shí)現(xiàn)分區(qū)表，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。
MySQL 分區(qū)的建立
MySQL 可以建立四種分區(qū)類(lèi)型的分區(qū)：
RANGE 分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。詳情參見(jiàn) 18.2.1 節(jié)，“RANGE 分區(qū)”。
middot; LIST 分區(qū)：類(lèi)似于按 RANGE 分區(qū)，區(qū)別在于 LIST 分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。詳情參見(jiàn) 18.2.2 節(jié)，“LIST 分區(qū)”。
middot; HASH 分區(qū)：基于用戶(hù)定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含 MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。詳情參見(jiàn) 18.2.3 節(jié)，“HASH 分區(qū)”。
middot; KEY 分區(qū)：類(lèi)似于按 HASH 分區(qū)，區(qū)別在于 KEY 分區(qū)只支持計(jì)算一列或多列，且 MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。詳情參照：18.2.4. KEY 分區(qū)。
子分區(qū)：子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割。書(shū)寫(xiě)格式參照：18.2.5. 子分區(qū)
（1）關(guān)于子分區(qū)應(yīng)注意的地方：每個(gè)分區(qū)必須有相同數(shù)量的子分區(qū)。
middot; （2）如果在一個(gè)分區(qū)表上的任何分區(qū)上使用 SUBPARTITION 來(lái)明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)。
在建立分區(qū)的時(shí)候可以指定分區(qū)的數(shù)據(jù)存儲(chǔ)位置和索引位置，這樣可以跨磁盤(pán)或者文件系統(tǒng)保存不同的數(shù)據(jù)。數(shù)據(jù)分磁盤(pán)存儲(chǔ)可以一定程度上增加數(shù)據(jù)讀取速度，因?yàn)椴捎枚啻疟P(pán)后，每個(gè)磁盤(pán)的 I / O 操作會(huì)降低。而且采用指定分區(qū)存儲(chǔ)位置能夠增大存儲(chǔ)量。
無(wú)論使用何種類(lèi)型的分區(qū)，分區(qū)總是在創(chuàng)建時(shí)就自動(dòng)的順序編號(hào)，且從 0 開(kāi)始記錄，記住這一點(diǎn)非常重要。當(dāng)有一新行插入到一個(gè)分區(qū)表中時(shí)，就是使用這些分區(qū)編號(hào)來(lái)識(shí)別正確的分區(qū)。例如，如果你的表使用 4 個(gè)分區(qū)，那么這些分區(qū)就編號(hào)為 0, 1, 2, 和 3。對(duì)于 RANGE 和 LIST 分區(qū)類(lèi)型，確認(rèn)每個(gè)分區(qū)編號(hào)都定義了一個(gè)分區(qū)，很有必要。對(duì) HASH 分區(qū)，使用的用戶(hù)函數(shù)必須返回一個(gè)大于 0 的整數(shù)值。對(duì)于 KEY 分區(qū)，這個(gè)問(wèn)題通過(guò) MySQL 服務(wù)器內(nèi)部使用的 哈希函數(shù)自動(dòng)進(jìn)行處理。注意：分區(qū)的名字是不區(qū)分大小寫(xiě)的，且對(duì)于 RANGE 分區(qū)和 LIST 分區(qū)，分區(qū)的名稱(chēng)是不能重復(fù)的。這幾種可根據(jù)不同的需求來(lái)選擇，比較常用的是 RANGE 分區(qū)。
常用的 MySQL 的分區(qū)管理：
RANGE 和 LIST 分區(qū)管理
分區(qū)對(duì)于程序來(lái)說(shuō)是透明的，而且只有刪除能在分區(qū)層面上操作，其他如查詢(xún)、修改、增加都不能指定分區(qū)。
ALTER TABLE hellip;DROPPARTITION hellip;.(刪除分區(qū))
ALTER TABLE hellip; ADD PARTITION (PARTITION p3 VALUESLESS THAN ( hellip;));[z3] 增加分區(qū)
ALTER TABLE … REORGANIZE PARTITION hellip;, hellip; INTO (
PARTITION p0 VALUES LESS THAN (hellip;)
)；[z4] 合并拆分分區(qū)。
HASH 和 KEY 分區(qū)管理
添加分區(qū)和 RANGE、LIST 分區(qū)方式相同，對(duì)于修改分區(qū)，不能使用與從按照 RANGE 或 LIST 分區(qū)的表中刪除分區(qū)相同的方式，來(lái)從 HASH 或 KEY 分區(qū)的表中刪除分區(qū)。但是，可以使用“ALTERTABLE … COALESCE PARTITION”命令來(lái)合并 HASH 或 KEY 分區(qū)。
如果要查看分區(qū)的信息，可以通過(guò) sql 語(yǔ)句來(lái)查詢(xún)
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME= xxx rsquo;
分區(qū)表效率比較
MySQL 分區(qū)表實(shí)驗(yàn)
分區(qū)采用紅色，不分區(qū)采用藍(lán)色
測(cè)試環(huán)境：CentOS，1G 內(nèi)存，20G 硬盤(pán)
實(shí)驗(yàn)：test 不分區(qū)（內(nèi)有 1 張表 RPT_MALEVENTS）、test2（與 test 一樣）
背景數(shù)據(jù)：
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)
數(shù)據(jù)分布：2011/8/4~2011/8/17
分區(qū)表結(jié)構(gòu)：
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)
分區(qū)表的物理存儲(chǔ)如下，當(dāng)前用的是 innodB 的存儲(chǔ)引擎，采用分表結(jié)構(gòu)
分析如下
(條件查詢(xún)查詢(xún)?nèi)繑?shù)據(jù))
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)
(查詢(xún)部分?jǐn)?shù)據(jù)，不使用分區(qū)函數(shù)使用的列)
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)
(使用其他條件查詢(xún)部分?jǐn)?shù)據(jù))
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)
(小范圍查詢(xún), 在一個(gè)分區(qū)內(nèi)查詢(xún))
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 語(yǔ)句的執(zhí)行過(guò)程
rows 表示 MySQL 根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)。
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)
與分區(qū)函數(shù)使用列無(wú)關(guān)的查詢(xún)條件
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)
采用分區(qū)函數(shù)使用的列
mysql EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE 2011-08-09 AND RECORD_DATE 2011-08-15
***************************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
***************************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)
刪除數(shù)據(jù)，如果刪除 1 整天的數(shù)據(jù)，由于我們采用按天分區(qū)，
mysql ALTER TABLERPT_MALEVENTS DROP PARTITION p20110809;[z11]
Query OK, 0 rowsaffected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
刪除后包含索引的和數(shù)據(jù)的 RPT_MALEVENTS#P#p20110809.ibd 被刪除了
如果采用傳統(tǒng)的不分區(qū)的方式刪除。
mysql DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE 2011-08-10
Query OK, 3929328rows affected (1 min 29.68 sec)
由此可見(jiàn)，刪除整個(gè)分區(qū)內(nèi)的數(shù)據(jù)還是很快的，
如果分區(qū)表采用傳統(tǒng)的方式刪除：
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)
采用傳統(tǒng)的方式刪除一天的數(shù)據(jù)，用的時(shí)間都差不多。
只刪除數(shù)據(jù)后，數(shù)據(jù)分區(qū)配 p20110810 還在，而且大小不變。可以用 ALTER TABLE t1 OPTIMIZE PARTITION 來(lái)進(jìn)行回收，但是 MySQL5.1.22 還沒(méi)有實(shí)現(xiàn)。
跨分區(qū)刪除。
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)
分區(qū)表刪除比不分區(qū)的略慢
[z1] 分區(qū)函數(shù)
[z2] 分區(qū)信息，從 2011-08-09 開(kāi)始
[z3] 沒(méi)有用分區(qū)函數(shù)使用的列會(huì)掃描所有分區(qū)
[z4] 數(shù)據(jù)量為 681311，分區(qū)后掃描行數(shù)為 355911，雖然查詢(xún)條件沒(méi)有分區(qū)函數(shù)的列，但是 mysql 的查詢(xún)優(yōu)化器會(huì)將其對(duì)應(yīng)于時(shí)間分區(qū)，這樣可以減少掃描行數(shù)
[z5] 數(shù)據(jù)量為 681311，分區(qū)后掃描行數(shù)為 1002288
[z6] 查找所有分區(qū)
[z7] 無(wú)關(guān)分區(qū)函數(shù)的字段，會(huì)遍歷幾乎所有行。
[z8] 掃描部分分區(qū)
[z9] 掃描行數(shù)隨之減少
[z10] 估計(jì)掃描的行數(shù)
[z11] 這個(gè)分區(qū)的數(shù)據(jù)是所有 2011-8-10 之前的所有數(shù)據(jù)，共 3929328。
總結(jié)：
分區(qū)表是在 MySQL5.1 中新增的的功能，截止到 MySQL5.1.22-rc, 分區(qū)技術(shù)并不很成熟，很多分區(qū)的維護(hù)和管理功能未實(shí)現(xiàn)。如，分區(qū)內(nèi)數(shù)據(jù)存儲(chǔ)空間的回收、分區(qū)的修復(fù)、分區(qū)的優(yōu)化等，MySQL 的分區(qū)可以用在可以按分區(qū)刪除的表中，且對(duì)數(shù)據(jù)庫(kù)的修改操作不大，且頻繁按照分區(qū)字段進(jìn)行查詢(xún)的表中（如惡意代碼中的統(tǒng)計(jì)表按天分區(qū)，經(jīng)常按照時(shí)間進(jìn)行查詢(xún)、分組等，且可以按天刪除分區(qū)）。此外，由于 MySQL 無(wú)全局索引只有分區(qū)索引，當(dāng)一張有 2 個(gè)唯一索引 [z5] 的時(shí)候，不能將此表分區(qū)，分區(qū)列中必須包含主鍵。否則 MySQL 會(huì)報(bào)錯(cuò)。
總之，MySQL 對(duì)于分區(qū)的限制很多，且個(gè)人認(rèn)為 hash 和 key 的分區(qū)實(shí)際意義不是太大。
分區(qū)引入了一種新的優(yōu)化查詢(xún)的方式（當(dāng)然，也有相應(yīng)的缺點(diǎn)）。優(yōu)化器可以使用分區(qū)函數(shù)修整分區(qū)，或者把分區(qū)從查詢(xún)中完全移除掉。它通過(guò)推斷是否可以在特定的分區(qū)上找到數(shù)據(jù)來(lái)達(dá)成這種優(yōu)化。因此在最好的情況下，修整可以讓查詢(xún)?cè)L問(wèn)更少的數(shù)據(jù)。重要的是要在 WHERE 子句中定義分區(qū)鍵，即使它看上去像是多余的。通過(guò)分區(qū)鍵，優(yōu)化器就可以去掉不用的分區(qū)，否則的話，執(zhí)行引擎就會(huì)像合并表那樣訪問(wèn)表的所有分區(qū)，這在大表上會(huì)非常慢。分區(qū)數(shù)據(jù)比非分區(qū)數(shù)據(jù)更好維護(hù)，并且可以通過(guò)刪除分區(qū)來(lái)移除老的數(shù)據(jù)。分區(qū)數(shù)據(jù)可以被分布到不同的物理位置，這樣服務(wù)器可以更有效地使用多個(gè)硬盤(pán)驅(qū)動(dòng)器。
[z1] 分區(qū)函數(shù)的返回值必須是整數(shù)，新增分區(qū)的分區(qū)函數(shù)返回值應(yīng)大于任何一個(gè)現(xiàn)有分區(qū)的分區(qū)函數(shù)的返回值。
[z2] 對(duì)于有主鍵的表錯(cuò)誤提示：#1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE S PARTITIONING FUNCTION，沒(méi)有主鍵的則無(wú)此約束
[z3] 注意：對(duì)于通過(guò) RANGE 分區(qū)的表，只可以使用 ADD PARTITION 添加新的分區(qū)到分區(qū)列表的高端。即不能添加比這個(gè)分區(qū)的范圍小的分區(qū)。
[z4] 對(duì)于按照 RANGE 分區(qū)的表，只能重新組織相鄰的分區(qū)；不能跳過(guò) RANGE 分區(qū)。不能使用 REORGANIZEPARTITION 來(lái)改變表的分區(qū)類(lèi)型；也就是說(shuō)，例如，不能把 RANGE 分區(qū)變?yōu)?HASH 分區(qū)，反之亦然。也不能使用該命令來(lái)改變分區(qū)表達(dá)式或列。
[z5] 注意主鍵和唯一索引的區(qū)別
看完上述內(nèi)容，你們掌握 MySQL 中怎么實(shí)現(xiàn)分區(qū)表的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！
