程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中表分區技巧具體解析

MySQL中表分區技巧具體解析

編輯：MySQL綜合教程

MySQL中表分區技巧具體解析。本站提示廣大學習愛好者：（MySQL中表分區技巧具體解析）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中表分區技巧具體解析正文

MySQL 分區技巧（是mysql 5.1以版本後開端用->是甲骨文mysql技巧團隊保護人員以插件情勢拔出到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)

總結：

分區表是在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