mysql分區及實例演示

一、為什麼要分區？

需求：大數據。解決方案：分而治之，更細一點即為。將大表和大索引分為一個更小的操作單元
在mysql中，分區允許將表、索引和索引編排表細分為更小的單元。分區後，每個分區有自己單獨的名稱。對於DBA來講，這些分區可以統一管理，也可以分開管理。
但是對於應用程序來講，分區的表和沒有分區的表示一樣的。換句話來講，分區對於應用是透明的，只是數據庫對於數據的重新整理。
Mysql分區，這個是mysql對於分區的官方解釋。

1.分區的作用

a)提升性能


應該說，幾乎所有分區的目的都是用來提升性能的。不是誰哪天早上吃著雞蛋灌餅，說咱們分個區玩玩吧！不是的。那麼分區是如何來提升檢索數據的性能的呢？沒有分區之前，數據每次在查詢和檢索總是面向的是整個數據庫和表。分區之後，mysql針對每個分區生成特定的數據文件與索引文件。每次只會檢索特定的部分數據，因此可以更好的來執行和維護數據庫。究其原因還是因為在分區之後表指派到不同的物理驅動器上，這樣便可在同時訪問多個分區時減少物理 I/O 爭用。
b)易於管理
對於一些無用的冗余的歷史數據來講，可以根據分區之後直接刪除相應的分區。操作起來更加簡便，因為執行數以萬計的數據遠比直接刪除文件來的困難的多、時間也更加的長。
c)容錯
分區之後相比分區之前，一張表從之前一個文件分為現在的三個文件。分區之後即使一個文件出現破壞，也不會影響其他數據。
2..常用分區方法

RANGE分區
LIST分區
HASH分區
KEY分區

二、如何分區？

1.查看數據庫是否支持分區

SHOW VARIABLES LIKE '%partition%';

 

對於mysql來講，現階段支持分區操作的版本有5.1和5.5.如圖顯示為YES則表明該數據庫支持分區操作。

2.四種常見分區

Range分區

俗稱：范圍分區。根據表的字段的值，依據給定某段連續的區間來分區。

 

直接創建表時分區

 

 

create table teacher
(id varchar(20) not null ,
name varchar(20),
age varchar(20),
birthdate date not null,
salary int
)
partition by range(year(birthdate))
(
partition p1 values less than (1970),
partition p2 values less than (1990),
partition p3 values less than maxvalue
);

 

 

Ps：創建teacher表，並在創建teacher表同時根據birthdate字段將表劃分為p1、p2、p3三個分區。

 

 

在創建表後分區

 

 

ALTER TABLE teacher 
partition by range(year(birthdate))
(
partition p1 values less than (1970),
partition p2 values less than (1990),
partition p3 values less than maxvalue
);

 

Ps：給已經創建了的表分區，分為p1、p2、p3.

 

LIST分區

俗名：列表分區。其實list分區和range分區應該說都是一樣的，不同的是range分區在分區是的依據是一段連續的區間；而list分區針對的分區依據是一組分布的散列值。

 

create table student
 (id varchar(20) not null ,
 studentno int(20) not null,
 name varchar(20),
 age varchar(20)
 )
 partition by list(studentno)
 (
 partition p1 values in (1,2,3,4),
 partition p2 values in  (5,6,7,8),
 partition p3 values in (9,10,11)
 );

 

Ps：如上創建表student，並將student表分為p1、p2、p3三個分區。需要注意的是一般情況下，針對表的分區字段為int等數值類型。

 

HASH分區

 

小名：哈希分區。哈希分區主要是依據表的某個字段以及指定分區的數量。

 

create table user (
  id int(20) not null,
  role varchar(20) not null,
  description varchar(50) 
)
partition by hash(id) 
partitions 10;

 

 

Ps:如上創建user表，並將user表平均分為十個分區。比較有限制的就是需要知道表的數據有多少才能更好平均分配分區。

 

 

key分區

類似於按HASH分區，區別在於KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

 

 

create table role( id int(20) not null,name varchar(20) not null)
partition by linear key(id)
partitions 10;

 

 

 

3.分區表管理

對指定表添加分區

 

alter table user add partition(partition p4 values less than MAXVALUE);  

 

刪除指定表指定分區

alter table student drop partition p1;

 

創建子分區

 

create table role_subp(id int(20) not null,name int(20) not null)
partition by list(id)
subpartition by hash(name)
subpartitions 3
(
  partition p1 values in(10),
  partition p2 values in(20)
)

 

 

復合分區

 

 

alter table user
reorganize partition p1,p3 into
(partition p1 values less than (1000));