MySQL中表分區技術詳細解析_Mysql

MySQL 分區技術（是mysql 5.1以版本後開始用->是甲骨文mysql技術團隊維護人員以外掛程式形式插入到mysql裡面的技術）

1、概述

資料庫單表到達一定量後，效能會有衰減，像mysql\sql server等猶為明顯，所以需要把這些資料進行分區處理。同時有時候可能出現資料剝離什麼的，分區表就更有用處了！

MySQL 5.1 中新增的分區(Partition)功能就開始增加，優勢也越來越明顯了：

1. 與單個磁碟或檔案系統分區相比，可以儲存更多的資料
2. 很容易就能刪除不用或者過時的資料
3. 一些查詢可以得到極大的最佳化
4. 涉及到 SUM()/COUNT() 等彙總函式時，可以並行進行
5. IO輸送量更大
6. 分區允許可以設定為任意大小的規則，跨檔案系統分配單個表的多個部分。實際上，表的不同部分在不同的位置被儲存為單獨的表。
   

2、分區支援人員

在5.6之前，使用這個參數查看當將配置是否支援分區：

mysql> SHOW VARIABLES LIKE '%partition%';+-----------------------+-------+|Variable_name     | Value |+-----------------------+-------+| have_partition_engine | YES  |+-----------------------+-------+

如果是yes表示你當前的配置支援分區。 在5.6及以採用後，則採用如下方式進行查看：

mysql> SHOW PLUGINS;+----------------------------+----------+--------------------+---------+---------+| Name            | Status  | Type        | Library | License |+----------------------------+----------+--------------------+---------+---------+| binlog           | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   || mysql_native_password   | ACTIVE  | AUTHENTICATION   | NULL  | GPL   |..................................................................................| INNODB_LOCKS        | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   || INNODB_LOCK_WAITS     | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   || partition         | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |+----------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00 sec) 最後一行，可以看到partition是ACTIVE的，表示支援分區。

3、分區類型及舉例

3.1定界分割

RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。如時間，連續的常量值等 --按年分區

mysql> use mytest;Database changedmysql> create table range_p(   -> perid int(11),   -> pername char(12) not null,   -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) partition by range(year(credate))(   -> partition p2011 values less than (2011),   -> partition p2012 values less than (2012),   -> partition p2013 values less than (2013),   -> partition p2014 values less than (2014),  -> partition p2015 values less than maxvalue   -> );Query OK, 0 rows affected (0.12 sec)

3.2列舉分區

LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。比如說類似性別(1,2)等屬性值。

mysql> create table list_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by list(sex) (
  -> partition psex1 values in(1),
  -> partition psex2 values in(2));
Query OK, 0 rows affected (0.06 sec)

注意，list只能是數字，使用字元會報錯ERROR 1697 (HY000): VALUES value for partition 'psex1' must have type INT。

3.3離散分區

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

--以int欄位hash分區

create table hash_p( perid int(11), pername char(12) not null,sex int(1) not null,monsalary DECIMAL(10,2),credate datetime ) partition by hash (perid) partitions 8;

--以時間函數hash分區

mysql> create table hash_p(   -> perid int(11),   -> pername char(12) not null,  -> sex int(1) not null,  -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) partition by hash (year(credate))   -> partitions 8;Query OK, 0 rows affected (0.11 sec)

3.4索引值分區

KEY分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含>整數值。 其分區方法與hash很相似：

mysql> create table key_p(   -> perid int(11),   -> pername char(12) not null,  -> sex int(1) not null,  -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) partition by key (perid)   -> partitions 8;Query OK, 0 rows affected (0.12 sec)

3.5其它說明

mysql-5.5開始支援COLUMNS分區，可視為RANGE和LIST分區的進化，COLUMNS分區可以直接使用非整形資料進行分區。COLUMNS分區支援以下資料類型： 所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支援。 日期類型，如DATE和DATETIME。其餘日期類型不支援。 字串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支援。 COLUMNS可以使用多個列進行分區。

mysql> create table range_p(   -> perid int(11),   -> pername char(12) not null,   -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) PARTITION BY RANGE COLUMNS (credate)(   -> partition p20151 values less than ('2015-04-01'),   -> partition p20152 values less than ('2015-07-01'),   -> partition p20153 values less than ('2015-10-01'),   -> partition p20154 values less than ('2016-01-01'),  -> partition p20161 values less than ('2016-04-01'),  -> partition partlog values less than maxvalue   -> );Query OK, 0 rows affected (0.12 sec)

總結：

分區表是在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]注意主鍵和唯一索引的區別

官方資料：https://dev.mysql.com/doc/refman/5.5/en/partitioning.html