MySQL分區表

標籤：

一、概述

當 MySQL的總記錄數超過了100萬後，會出現效能的大幅度下降嗎？答案是肯定的，但是，效能下降>的比率不一而同，要看系統的架構、應用程式、還有>包括索引、伺服器硬體等多種因素而定。當有網友問我這個問題的時候，我最常見的回答>就是：分表，可以根據id區間或者時間先後順序等多種規則來分表。分表很容易，然而由此所帶來的應用程式甚至是架構方面的改動工作卻不>容小覷，還包括將來的擴充性等。

在以前，一種解決方案就是使用 MERGE
類型，這是一個非常方便的做飯。架構和程式基本上不用做改動，不過，它的缺點是顯見的：

• 只能在相同結構的 MyISAM 表上使用
• 無法享受到 MyISAM 的全部功能，例如無法在 MERGE 類型上執行 FULLTEXT 搜尋
• 它需要使用更多的檔案描述符
• 讀取索引更慢

這個時候，MySQL 5.1 中新增的分區(Partition)功能的優勢也就很明顯了：

• 與單個磁碟或檔案系統分區相比，可以儲存更多的資料
• 很容易就能刪除不用或者過時的資料
• 一些查詢可以得到極大的最佳化
• 涉及到 SUM()/COUNT() 等彙總函式時，可以並行進行
• IO輸送量更大

分區允許可以設定為任意大小的規則，跨檔案系統分配單個表的多個部分。實際上，表的不同部分在不同的位置被儲存為單獨的表。

 

分區應該注意的事項：

1、 做分區時，要麼不定義主鍵，要麼把分區欄位加入到主鍵中。

2、 分區欄位不能為NULL，要不然怎麼確定分區範圍呢，所以盡量NOT NULL

 

二、分區的類型

• RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。
• LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。
• HASH分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。這個函數可以包>含MySQL中有效、產生非負整數值的任何錶達式。
• KEY分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含>整數值。

可以通過使用SHOW VARIABLES命令來確定MySQL是否支援分區，例如：

mysql> SHOW VARIABLES LIKE ‘%partition%‘;

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_partition_engine | YES   |

+-----------------------+-------+

1 row in set (0.00 sec)

1 。range分區

　create table t_range( 
　　id int(11), 
　　money int(11) unsigned not null, 
　　date datetime 
　　)partition by range(year(date))( 
　　partition p2007 values less than (2008), 
　　partition p2008 values less than (2009), 
　　partition p2009 values less than (2010) 
　　partition p2010 values less than maxvalue 
　　)；

2.list分區

 

create table t_list(  　　a int(11),  　　b int(11)  　　)(partition by list (b)  　　partition p0 values in (1,3,5,7,9),  　　partition p1 values in (2,4,6,8,0)  　　); 對於innodb和myisam引擎，一條語句插入多條記錄的時候，如果中間有值不能插入，innodb會全部復原，myisam在錯誤值之前的資料可以插入到表中。

對於innodb和myisam引擎，一條語句插入多條記錄的時候，如果中間有值不能插入，innodb會全部復原，myisam在錯誤值之前的資料可以插入到表中。 3.hash分區

hash分區的目的是將資料均勻的分布到預先定義的各個分區中，保證各分區的資料量大致一致。

 

create table t_hash(  　　a int(11),  　　b datetime  　　)partition by hash (YEAR(b)  　　partitions 4;

 

　　hash的分區函數頁需要返回一個整數值。partitions子句中的值是一個非負整數，不加的partitions子句的話，預設為分區數為1。

4.key分區

key分區和hash分區相似，不同在於hash分區是使用者自訂函數進行分區，key分區使用mysql資料庫提供的函數進行分區，NDB cluster使用MD5函數來分區，對於其他儲存引擎mysql使用內部的hash函數，這些函數基於password()一樣的演算法。

 

create table t_key(  　　a int(11),  　　b datetime)  　　partition by key (b)  　　partitions 4;

5。columns分區

上面的RANGE、LIST、HASH、KEY四種分區中，分區的條件必須是整形，如果不是整形需要通過函數將其轉換為整形。

　　mysql-5.5開始支援COLUMNS分區，可視為RANGE和LIST分區的進化，COLUMNS分區可以直接使用非整形資料進行分區。COLUMNS分區支援以下資料類型：

　　所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支援。

　　日期類型，如DATE和DATETIME。其餘日期類型不支援。

　　字串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支援。

　　COLUMNS可以使用多個列進行分區。

三、分區操作

新增分區

mysql> ALTER TABLE sale_data

    ->   ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

刪除分區

--當刪除了一個分區，也同時刪除了該分區中所有的資料。

mysql> ALTER TABLE sale_data DROP PARTITION p201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

 

分區的合并

下面的SQL，將p201001 - p201009 合并為3個分區p2010Q1 - p2010Q3

mysql> ALTER TABLE sale_data

    ->   REORGANIZE PARTITION p201001,p201002,p201003,

    ->                        p201004,p201005,p201006,

    ->                        p201007,p201008,p201009 INTO

    -> (

    ->   PARTITION p2010Q1 VALUES LESS THAN (201004),

    ->   PARTITION p2010Q2 VALUES LESS THAN (201007),

    ->   PARTITION p2010Q3 VALUES LESS THAN (201010)

    -> );

Query OK, 0 rows affected (1.14 sec)

Records: 0  Duplicates: 0  Warnings: 0

分區表的管理操作

刪除分區:

alter table emp drop partition p1;

不可以刪除hash或者key分區。

一次性刪除多個分區，alter table emp drop partition p1,p2;

 

分解分區:

Reorganizepartition關鍵字可以對錶的部分分區或全部分區進行修改，並且不會遺失資料。分解前後分區的整體範圍應該一致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); ----不會遺失資料

 

合并分區:

Merge分區：把2個分區合并為一個。
alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

----不會遺失資料

 

重新定義hash分區表:

Alter table emp partition by hash(salary)partitions 7;

----不會遺失資料

重新定義range分區表:

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); ----不會遺失資料

 

刪除表的所有分區:

 

Alter table emp removepartitioning;--不會遺失資料

 

重建分區：

這和先刪除儲存在分區中的所有記錄，然後重新插入它們，具有同樣的效果。它可用於整理分區片段。

ALTER TABLE emp rebuild partitionp1,p2;

 

最佳化分區：

如果從分區中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，並整理分區資料檔案的片段。

ALTER TABLE emp optimize partition p1,p2;

 

分析分區：

讀取並儲存分區的鍵分布。

ALTER TABLE emp analyze partition p1,p2;

 

修補分區：

修補被破壞的分區。

ALTER TABLE emp repairpartition p1,p2;

 

檢查分區：

可以使用幾乎與對非分區表使用CHECK TABLE 相同的方式檢查分區。

ALTER TABLE emp CHECK partition p1,p2;

這個命令可以告訴你表emp的分區p1,p2中的資料或索引是否已經被破壞。如果發生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補該分區。 

 

四、mysql分區表的局限性

1.      在5.1版本中分區表對唯一約束有明確的規定，每一個唯一約束必須包含在分區表的分區鍵（也包括主鍵約束）。

 

CREATE TABLE emptt (

empno varchar(20) NOT NULL  ,

empname varchar(20)，

deptno int,

birthdate date NOT NULL,

salary int ,

primary key (empno)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

這樣的語句會報錯。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table‘s partitioning function；

CREATE TABLE emptt (

empno varchar(20) NOT NULL  ,

empname varchar(20) ,

deptno int(11),

birthdate date NOT NULL,

salary int(11) ,

primary key (empno，salary)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

在主鍵中加入salary列就正常。

 

2.      MySQL分區處理NULL值的方式

如果分區鍵所在列沒有notnull約束。

如果是range分區表，那麼null行將被儲存在範圍最小的分區。

如果是list分區表，那麼null行將被儲存到list為0的分區。

在按HASH和KEY分區的情況下，任何產生NULL值的運算式mysql都視同它的傳回值為0。

為了避免這種情況的產生，建議分區鍵設定成NOT NULL。

 

3.      分區鍵必須是INT類型，或者通過運算式返回INT類型，可以為NULL。唯一的例外是當分

區類型為KEY分區的時候，可以使用其他類型的列作為分區鍵（ BLOB or TEXT 列除外）。

 

4.      對分區表的分區鍵建立索引，那麼這個索引也將被分區,分區鍵沒有全域索引一說。

5.      只有RANG和LIST分區能進行子分區，HASH和KEY分區不能進行子分區。

6.      暫存資料表不能被分區。

 

五、 擷取mysql分區表資訊的幾種方法

1.     show create table 表名
可以查看建立分區表的create語句

2.     show table status
可以查看錶是不是分區表

3.     查看information_schema.partitions表
select
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
from information_schema.partitions  where
  table_schema = schema()  //此行可不要
  and table_name=‘test‘; 
可以查看錶具有哪幾個分區、分區的方法、分區中資料的記錄數等資訊

4.     explain partitions select * from sales語句
通過此語句來顯示掃描哪些分區，及他們是如何使用的.

MySQL分區表