MySQL 分區(qū)技術(shù)（是mysql 5.1以版本后開(kāi)始用->是甲骨文mysql技術(shù)團(tuán)隊(duì)維護(hù)人員以插件形式插入到mysql里面的技術(shù)）

1、概述

數(shù)據(jù)庫(kù)單表到達(dá)一定量后，性能會(huì)有衰減，像mysql\sql server等猶為明顯，所以需要把這些數(shù)據(jù)進(jìn)行分區(qū)處理。同時(shí)有時(shí)候可能出現(xiàn)數(shù)據(jù)剝離什么的，分區(qū)表就更有用處了！

MySQL 5.1 中新增的分區(qū)(Partition)功能就開(kāi)始增加，優(yōu)勢(shì)也越來(lái)越明顯了：

1. 與單個(gè)磁盤(pán)或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)
2. 很容易就能刪除不用或者過(guò)時(shí)的數(shù)據(jù)
3. 一些查詢(xún)可以得到極大的優(yōu)化
4. 涉及到 SUM()/COUNT() 等聚合函數(shù)時(shí)，可以并行進(jìn)行
5. IO吞吐量更大
6. 分區(qū)允許可以設(shè)置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分。實(shí)際上，表的不同部分在不同的位置被存儲(chǔ)為單獨(dú)的表。
   

2、分區(qū)技術(shù)支持

在5.6之前，使用這個(gè)參數(shù)查看當(dāng)將配置是否支持分區(qū)：

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

如果是yes表示你當(dāng)前的配置支持分區(qū)。 在5.6及以采用后，則采用如下方式進(jìn)行查看：

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的，表示支持分區(qū)。

3、分區(qū)類(lèi)型及舉例

3.1范圍分區(qū)

RANGE 分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。如時(shí)間，連續(xù)的常量值等 --按年分區(qū)

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列舉分區(qū)

LIST 分區(qū)：類(lèi)似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。比如說(shuō)類(lèi)似性別(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只能是數(shù)字，使用字符會(huì)報(bào)錯(cuò)ERROR 1697 (HY000): VALUES value for partition 'psex1' must have type INT。

3.3離散分區(qū)

HASH分區(qū)：基于用戶(hù)定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包>含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

--以int字段hash分區(qū)

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;

--以時(shí)間函數(shù)hash分區(qū)

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鍵值分區(qū)

KEY分區(qū)：類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含>整數(shù)值。 其分區(qū)方法與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其它說(shuō)明

mysql-5.5開(kāi)始支持COLUMNS分區(qū)，可視為RANGE和LIST分區(qū)的進(jìn)化，COLUMNS分區(qū)可以直接使用非整形數(shù)據(jù)進(jìn)行分區(qū)。COLUMNS分區(qū)支持以下數(shù)據(jù)類(lèi)型： 所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。 日期類(lèi)型，如DATE和DATETIME。其余日期類(lèi)型不支持。 字符串類(lèi)型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類(lèi)型不支持。 COLUMNS可以使用多個(gè)列進(jìn)行分區(qū)。

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)

總結(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ū)，否則的話(huà)，執(zhí)行引擎就會(huì)像合并表那樣訪(fǎng)問(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)镠ASH分區(qū)，反之亦然。也不能使用該命令來(lái)改變分區(qū)表達(dá)式或列。
 [z5]注意主鍵和唯一索引的區(qū)別

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

最新評(píng)論