MySQL 分割槽介紹總結

pursuer.chen發表於2016-06-29

介紹

分割槽是指根據一定的規則將一個大表分解成多個更小的部分，這裡的規則一般就是利用分割槽規則將表進行水平切分；邏輯上沒有發生變化但實際上表已經被拆分成了多個物理物件，每個分成被劃分成了一個獨立的物件。相對於沒有分割槽的當個表而言分割槽的表有很多的優勢包括：併發統計查詢、快速歸檔刪除分割槽資料、分散儲存、查詢效能更佳。

mysql5.7以後查詢語句支援指定分割槽例如：“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分割槽同樣適用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

資料庫版本：mysql5.7.12

是否支援分割槽

SHOW PLUGINS ;

查詢partition的的狀態是active就代表支援分割槽，如果是原始碼安裝的話在編譯的過程中要新增“-DWITH_PARTITION_STORAGE_ENGINE=1 \”。

注意： MERGE, CSV, or FEDERATED儲存引擎不支援分割槽，同一個表所有的分割槽必須使用相同的儲存引擎，不能分割槽1使用MYISAM分割槽2又使用INNODB；不同的分割槽表可以是不同的儲存引擎。

分割槽介紹

目前mysql可用的分割槽型別主要有以下幾種：

RANGE分割槽：基於一個給定的連續區間範圍，RANGE主要是基於整數的分割槽，對於非整形的欄位需要利用表示式將其轉換成整形。

LIST分割槽：是基於列出的列舉值列表進行分割槽。

COLUMNS分割槽：可以無需通過表示式進行轉換直接對非整形欄位進行分割槽，同時COLUMNS分割槽還支援多個欄位組合分割槽，只有RANGELIST存在COLUMNS分割槽，COLUMNS是RANGE和LIST分割槽的升級。

HASH分割槽：基於給定的分割槽個數，將資料分配到不同的分割槽，HASH分割槽只能針對整數進行HASH，對於非整形的欄位只能通過表示式將其轉換成整數。

KEY分割槽：支援除text和BLOB之外的所有資料型別的分割槽,key分割槽可以直接基於欄位做分割槽無需轉換成整數。

說明

1.注意分割槽名的大小寫敏感問題，和關鍵字問題。

2.無論哪種分割槽型別，要麼分割槽表中沒有主鍵或唯一鍵，要麼主鍵或唯一鍵包含在分割槽列裡面，對於存在主鍵或者唯一鍵的表不能使用主鍵或者唯一鍵之外的欄位作為分割槽欄位。

3.5.7以前的版本顯示分割槽的執行計劃使用：explain PARTITIONS；5.7以後直接執行：explain

4.沒有強制要求分割槽列非空，建議分割槽的列為NOT NULL的列；在RANGE 分割槽中如果往分割槽列中插入NULL值會被當作最小的值來處理，在LIST分割槽中NULL值必須在列舉列表中否則插入失敗，在HASH/KEY分割槽中NULL值會被當作0來處理。

5.基於時間型別的欄位的轉換函式mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()"

6.拆分合並分割槽後會導致修改的分割槽的統計資訊失效,沒有修改的分割槽的統計資訊還在,不影響新插入的值加入到統計資訊；這時需要對錶執行Analyze操作.

7.針對非整形欄位進行RANG\LIST分割槽建議使用COLUMNS分割槽。

刪除增加分割槽

在每個分割槽內容介紹中詳細介紹了每種分割槽的用法，但是都是介紹在建立表的時候建立分割槽和修改刪除分割槽單個，也可以在一張已經存在的表中加入分割槽，可以一次性刪除整個表的分割槽。

1.移除表的分割槽

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分割槽是僅僅移除分割槽的定義，並不會刪除資料和drop PARTITION不一樣，後者會連同資料一起刪除

2.對已經存在記錄的表建立分割槽，以增加range分割槽為例，和建立表建分割槽的語法一樣。

ALTER TABLE `tb_partition`.`tb_varchar` 
PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5000),  PARTITION part1 VALUES LESS THAN (10000),  PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;

注意：對已有的表建立分割槽之後，資料會按照分割槽的定義分佈到各個分割槽檔案當中

分割槽系列文章：

RANGE分割槽：http://www.cnblogs.com/chenmh/p/5627912.html

LIST分割槽：http://www.cnblogs.com/chenmh/p/5643174.html

COLUMN分割槽：http://www.cnblogs.com/chenmh/p/5630834.html

HASH分割槽：http://www.cnblogs.com/chenmh/p/5644496.html

KEY分割槽：http://www.cnblogs.com/chenmh/p/5647210.html

子分割槽：http://www.cnblogs.com/chenmh/p/5649447.html

指定各分割槽路徑：http://www.cnblogs.com/chenmh/p/5644713.html

分割槽建索引：http://www.cnblogs.com/chenmh/p/5761995.html

分割槽介紹總結：http://www.cnblogs.com/chenmh/p/5623474.html