MySQL中怎么實現(xiàn)分區(qū)表

MySQL中怎么實現(xiàn)分區(qū)表，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

 
MySQL分區(qū)的建立
MySQL可以建立四種分區(qū)類型的分區(qū)： 
          RANGE 分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。詳情參見18.2.1節(jié)，“RANGE分區(qū)”。
·         LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇。詳情參見18.2.2節(jié)，“LIST分區(qū)”。
·         HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。詳情參見18.2.3節(jié)，“HASH分區(qū)”。
·         KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。詳情參照：18.2.4. KEY分區(qū)。
        子分區(qū)：子分區(qū)是分區(qū)表中每個分區(qū)的再次分割。書寫格式參照：18.2.5. 子分區(qū)
        （1）關(guān)于子分區(qū)應(yīng)注意的地方： 每個分區(qū)必須有相同數(shù)量的子分區(qū)。
·       （2）如果在一個分區(qū)表上的任何分區(qū)上使用SUBPARTITION 來明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)。
在建立分區(qū)的時候可以指定分區(qū)的數(shù)據(jù)存儲位置和索引位置，這樣可以跨磁盤或者文件系統(tǒng)保存不同的數(shù)據(jù)。數(shù)據(jù)分磁盤存儲可以一定程度上增加數(shù)據(jù)讀取速度，因為采用多磁盤后，每個磁盤的I/O操作會降低。而且采用指定分區(qū)存儲位置能夠增大存儲量。
 
無論使用何種類型的分區(qū)，分區(qū)總是在創(chuàng)建時就自動的順序編號，且從0開始記錄，記住這一點非常重要。當(dāng)有一新行插入到一個分區(qū)表中時，就是使用這些分區(qū)編號來識別正確的分區(qū)。例如，如果你的表使用4個分區(qū)，那么這些分區(qū)就編號為0, 1, 2, 和3。對于RANGE和LIST分區(qū)類型，確認(rèn)每個分區(qū)編號都定義了一個分區(qū)，很有必要。對HASH分區(qū)，使用的用戶函數(shù)必須返回一個大于0的整數(shù)值。對于KEY分區(qū)，這個問題通過MySQL服務(wù)器內(nèi)部使用的 哈希函數(shù)自動進(jìn)行處理。注意：分區(qū)的名字是不區(qū)分大小寫的，且對于RANGE分區(qū)和LIST分區(qū)，分區(qū)的名稱是不能重復(fù)的。這幾種可根據(jù)不同的需求來選擇，比較常用的是RANGE分區(qū)。
 
常用的MySQL的分區(qū)管理：
 RANGE 和LIST分區(qū)管理
         分區(qū)對于程序來說是透明的，而且只有刪除能在分區(qū)層面上操作，其他如查詢、修改、增加都不能指定分區(qū)。
 
ALTER TABLE …DROPPARTITION ….(刪除分區(qū))
ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分區(qū)
ALTER TABLE ... REORGANIZE PARTITION …,… INTO (
   PARTITION p0 VALUES LESS THAN (…)
)；[z4] 合并拆分分區(qū)。
 
  HASH和KEY分區(qū)管理
         添加分區(qū)和RANGE、LIST分區(qū)方式相同，對于修改分區(qū)，不能使用與從按照RANGE或LIST分區(qū)的表中刪除分區(qū)相同的方式，來從HASH或KEY分區(qū)的表中刪除分區(qū)。但是，可以使用“ALTERTABLE ... COALESCE PARTITION”命令來合并HASH或KEY分區(qū)。
 
如果要查看分區(qū)的信息，可以通過sql語句來查詢
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’
 
分區(qū)表效率比較
 
 
MySQL分區(qū)表實驗
分區(qū)采用紅色，不分區(qū)采用藍(lán)色
 
測試環(huán)境：CentOS ，1G內(nèi)存，20G硬盤
實驗：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ū)表的物理存儲如下，當(dāng)前用的是innodB的存儲引擎，采用分表結(jié)構(gòu)
分析如下
 (條件查詢查詢?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)
 
(查詢部分?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)
 
(使用其他條件查詢部分?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)
 
 
(小范圍查詢,在一個分區(qū)內(nèi)查詢)
 
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語句的執(zhí)行過程
rows表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(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ù)使用列無關(guā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'\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)
 
 
刪除數(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)
 
由此可見，刪除整個分區(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ù)據(jù)后，數(shù)據(jù)分區(qū)配p20110810還在，而且大小不變?？梢杂肁LTER TABLE t1 OPTIMIZE PARTITION來進(jìn)行回收，但是MySQL5.1.22還沒有實現(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開始
 [z3]沒有用分區(qū)函數(shù)使用的列會掃描所有分區(qū)
 [z4]數(shù)據(jù)量為681311，分區(qū)后掃描行數(shù)為355911，雖然查詢條件沒有分區(qū)函數(shù)的列，但是mysql的查詢優(yōu)化器會將其對應(yīng)于時間分區(qū)，這樣可以減少掃描行數(shù)
 [z5]數(shù)據(jù)量為681311，分區(qū)后掃描行數(shù)為1002288
 [z6]查找所有分區(qū)
 [z7]無關(guān)分區(qū)函數(shù)的字段，會遍歷幾乎所有行。
 [z8]掃描部分分區(qū)
 [z9]掃描行數(shù)隨之減少
 [z10]估計掃描的行數(shù)
 [z11]這個分區(qū)的數(shù)據(jù)是所有2011-8-10之前的所有數(shù)據(jù)，共3929328。

 
總結(jié)：
分區(qū)表是在MySQL5.1中新增的的功能，截止到MySQL5.1.22-rc,分區(qū)技術(shù)并不很成熟，很多分區(qū)的維護(hù)和管理功能未實現(xiàn)。如，分區(qū)內(nèi)數(shù)據(jù)存儲空間的回收、分區(qū)的修復(fù)、分區(qū)的優(yōu)化等，MySQL的分區(qū)可以用在可以按分區(qū)刪除的表中，且對數(shù)據(jù)庫的修改操作不大，且頻繁按照分區(qū)字段進(jìn)行查詢的表中（如惡意代碼中的統(tǒng)計表按天分區(qū)，經(jīng)常按照時間進(jìn)行查詢、分組等，且可以按天刪除分區(qū)）。此外，由于MySQL無全局索引只有分區(qū)索引，當(dāng)一張有2個唯一索引[z5] 的時候，不能將此表分區(qū)，分區(qū)列中必須包含主鍵。否則MySQL會報錯。
總之，MySQL對于分區(qū)的限制很多，且個人認(rèn)為hash和key的分區(qū)實際意義不是太大。
 
分區(qū)引入了一種新的優(yōu)化查詢的方式（當(dāng)然，也有相應(yīng)的缺點）。優(yōu)化器可以使用分區(qū)函數(shù)修整分區(qū)，或者把分區(qū)從查詢中完全移除掉。它通過推斷是否可以在特定的分區(qū)上找到數(shù)據(jù)來達(dá)成這種優(yōu)化。因此在最好的情況下，修整可以讓查詢訪問更少的數(shù)據(jù)。重要的是要在WHERE子句中定義分區(qū)鍵，即使它看上去像是多余的。通過分區(qū)鍵，優(yōu)化器就可以去掉不用的分區(qū)，否則的話，執(zhí)行引擎就會像合并表那樣訪問表的所有分區(qū)，這在大表上會非常慢。分區(qū)數(shù)據(jù)比非分區(qū)數(shù)據(jù)更好維護(hù)，并且可以通過刪除分區(qū)來移除老的數(shù)據(jù)。分區(qū)數(shù)據(jù)可以被分布到不同的物理位置，這樣服務(wù)器可以更有效地使用多個硬盤驅(qū)動器。
 [z1]分區(qū)函數(shù)的返回值必須是整數(shù)，新增分區(qū)的分區(qū)函數(shù)返回值應(yīng)大于任何一個現(xiàn)有分區(qū)的分區(qū)函數(shù)的返回值。
 [z2]對于有主鍵的表錯誤提示：#1503
 A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION，沒有主鍵的則無此約束
 [z3]注意：對于通過RANGE分區(qū)的表，只可以使用ADD PARTITION添加新的分區(qū)到分區(qū)列表的高端。即不能添加比這個分區(qū)的范圍小的分區(qū)。
 
 [z4] 對于按照RANGE分區(qū)的表，只能重新組織相鄰的分區(qū)；不能跳過RANGE分區(qū)。不能使用REORGANIZEPARTITION來改變表的分區(qū)類型；也就是說，例如，不能把RANGE分區(qū)變?yōu)镠ASH分區(qū)，反之亦然。也不能使用該命令來改變分區(qū)表達(dá)式或列。
 [z5]注意主鍵和唯一索引的區(qū)別

看完上述內(nèi)容，你們掌握MySQL中怎么實現(xiàn)分區(qū)表的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！