mysql表分割槽技術詳細介紹

1、概述
    資料庫單表到達一定量後，效能會有衰減，像mysql\sql server等猶為明顯，所以需要把這些資料進行分割槽處理。同時有時候可能出現資料剝離什麼的，分割槽表就更有用處了！
    MySQL 5.1 中新增的分割槽(Partition)功能就開始增加，優勢也越來越明顯了：
--與單個磁碟或檔案系統分割槽相比，可以儲存更多的資料
--很容易就能刪除不用或者過時的資料
--一些查詢可以得到極大的最佳化
--涉及到 SUM()/COUNT() 等聚合函式時，可以並行進行
--IO吞吐量更大
--分割槽允許可以設定為任意大小的規則，跨檔案系統分配單個表的多個部分。實際上，表的不同部分在不同的位置被儲存為單獨的表。

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 changed
mysql> 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)

4、綜述
    總體說來，mysql的分割槽還在進步，需要進一步的加強，與oracle相比，還有差距！比如說基於非資料型別的分割槽技術，目前還沒有！