mysql分區及執行個體示範，mysql分區執行個體示範

mysql分區及執行個體示範，mysql分區執行個體示範

一、為什麼要分區？


需求：大資料。解決方案：分而治之，更細一點即為。將大表和大索引分為一個更小的操作單元

在mysql中，分區允許將表、索引和索引編排表細分為更小的單元。分區後，每個分區有自己單獨的名稱。對於DBA來講，這些分區可以統一管理，也可以分開管理。

但是對於應用程式來講，分區的表和沒有分區的表示一樣的。換句話來講，分區對於應用是透明的，只是資料庫對於資料的重新整理。




Mysql分區，這個是mysql對於分區的官方解釋。

 

1. 分區的作用


a)提升效能

 

應該說，幾乎所有分區的目的都是用來提升效能的。不是誰哪天早上吃著雞蛋灌餅，說咱們分個區玩玩吧！不是的。那麼分區是如何來提升檢索資料的效能的呢？沒有分區之前，資料每次在查詢和檢索總是面向的是整個資料庫和表。分區之後，mysql針對每個分區產生特定的資料檔案與索引檔案。每次只會檢索特定的部分資料，因此可以更好的來執行和維護資料庫。究其原因還是因為在分區之後表指派到不同的物理磁碟機上，這樣便可在同時訪問多個分區時減少物理 I/O 爭用。




b)易於管理




 對於一些無用的冗餘的曆史資料來講，可以根據分區之後直接刪除相應的分區。操作起來更加簡便，因為執行數以萬計的資料遠比直接刪除檔案來的困難的多、時間也更加的長。




c)容錯




       分區之後相比分區之前，一張表從之前一個檔案分為現在的三個檔案。分區之後即使一個檔案出現破壞，也不會影響其他資料。

2..常用分區方法

1. RANGE分區
2. LIST分區
3. HASH分區
4. 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 userreorganize partition p1,p3 into(partition p1 values less than (1000));