mysql 分區說明

標籤：支援   char   定義   集合   最佳化   ash   string   多行   彙總函式   

當 MySQL的總記錄數超過了100萬後，效能會大幅下降，可以採用資料分割配置

分區允許根據指定的規則，跨檔案系統分配單個表的多個部分。表的不同部分在不同的位置被儲存為單獨的表。

1.先看下innodb的資料結構

（1）共用資料表空間的資料結構

1	create table t3(id int)engine innodb;

查看儲存的檔案t3.frm，ibdata1的檔案，存放著所有的表額資料和索引

（2）獨立資料表空間的資料結構

1	create table t2(id int)engine innodb;

查看儲存的檔案　　

要對innodb進行分區操作，需要要設定innodb的資料表空間為獨立資料表空間

如果以前不是獨立資料表空間，在設定了innodb_file_per_table=1重啟mysql後，使用alter table table_name engine=innodb;修改為獨立資料表空間

2.分區的優勢：

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

分區的限制

• 最大分區數目不能超過1024
• 如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內
• 不支援外鍵
• 不支援全文索引（fulltext）

3.查看mysql是否支援分區

1	SHOW VARIABLES LIKE ‘%partition%‘

　　

或者用

1	SHOW PLUGINS;

　

4.分區類型

　　（1）RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區。

　　（2）LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。

　　（3）HASH分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。這個函數可以包>含MySQL中有效、產生非負整數值的任何錶達式。

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

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

　　（4）KEY分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含>整數值。 

　　（5）columns分區

 　　mysql-5.5開始支援COLUMNS分區，COLUMNS分區可以直接使用非整形資料進行分區。COLUMNS分區支援以下資料類型：
　　　　所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支援。
　　　　日期類型，如DATE和DATETIME。其餘日期類型不支援。
　　　　字串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支援。
　　COLUMNS可以使用多個列進行分區。

eg:

12345

create table t(     id int )engine innodb partition by hash(id) partitions 4;

查看一個該表的檔案結構

t表實現的分區，分成了4個分區

註：

　　HASH分區不能刪除分區，所以不能使用DROP PARTITION操作進行分區刪除操作

　　ALTER TABLE ... COALESCE PARTITION num來合并分區，num是減去的分區數量；

　　ALTER TABLE ... ADD PARTITION PARTITIONS num來增加分區，num是在原先基礎上再增加的分區數量

　　減去兩個分區後，資料根據現有的分區進行了重新的分布

1	ALTER TABLE t ADD PARTITION PARTITIONS 3;

1	ALTER TABLE t COALESCE PARTITION 3;

　

 

　

　　

　　

mysql 分區說明