mysql表分區使用及詳細介紹

一、分區概念

創新互聯專業為企業提供雙流網站建設、雙流做網站、雙流網站設計、雙流網站制作等企業網站建設、網頁設計與制作、雙流企業網站模板建站服務，十載雙流做網站經驗，不只是建網站，更提供有價值的思路和整體網絡服務。

分區是將一個表分成多個區塊進行操作和保存，從而降低每次操作的數據，提高性能。而對于應用來說則是透明的，從邏輯上看只有一張表，但在物理上這個表可能是由多個物理分區組成的，每個分區都是獨立的對象，可以進行獨立處理。

二、分區作用

1.可以邏輯數據分割，分割數據能夠有多個不同的物理文件路徑。

2.可以存儲更多的數據，突破系統單個文件最大限制。

3.提升性能，提高每個分區的讀寫速度，提高分區范圍查詢的速度。

4.可以通過刪除相關分區來快速刪除數據

5.通過跨多個磁盤來分散數據查詢，從而提高磁盤I/O的性能。

6.涉及到例如SUM()、COUNT()這樣聚合函數的查詢，可以很容易的進行并行處理。

7.可以備份和恢復獨立的分區，這對大數據量很有好處。

三、分區能支持的引擎

MySQL支持大部分引擎創建分區，入MyISAM、InnoDB等；不支持MERGE和CSV等來創建分區。同一個分區表中的所有分區必須是同一個存儲引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分區。

四、確認MySQL支持分區

從MySQL5.1開始引入分區功能，可以如下方式查看是否支持：

老版本用：SHOW VARIABLES LIKE '%partition%';

新版本用：show plugins;

五、分區類型

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

例如，可以將一個表通過年份劃分成兩個分區，2001 -2010年、2011-2020。

2. LIST分區：類似于RANGE分區，LIST是列值匹配一個離散值集合中的某個值來進行選擇。

比如 根據字段 把值為1、3、5的放到一起，2、4、6的另外放到一起 等等...

3. HASH分區：基于用戶定義的表達式的返回值來進行選擇分區，該表達式使用將要插入到表中的這些行的列值來進行計算，這個函數必須產生非負整數值。

通過HASH運算來進行分區，分布的比較均勻

4. KEY分區：類似于按HASH分區，由MySQL服務器提供其自身的哈希函數。

按照KEY進行分區類似于按照HASH分區

六、分區創建注意事項

1. 如果表中存在primary key 或者 unique key 時，分區的列必須是paimary key或者unique key的一個組成部分，也就是說，分區函數的列只能從pk或者uk這些key中取子集

2. 如果表中不存在任何的paimary key或者unique key，則可以指定任何一個列作為分區列

3. 5.5版本前的RANGE、LIST、HASH分區要求分區鍵必須是int；MySQL5.5及以上，支持非整形的RANGE和LIST分區，即：range columns 和 list columns (可以用字符串來進行分區)。

七、分區命名

1. 分區的名字基本上遵循其他MySQL 標識符應當遵循的原則，例如用于表和數據庫名字的標識符。應當注意的是， 分區的名字是不區分大小寫的 。

2. 無論使用何種類型的分區，分區總是在創建時就自動的順序編號，且從0開始記錄。

八、 創建分區

1. RANGE分區：

CREATE TABLE `test01` (

`dayid` int(11) DEFAULT NULL,

`mac` varchar(32) NOT NULL DEFAULT '',

`dtype` varchar(50) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (dayid)

(PARTITION p20171205 VALUES IN (20171205) ENGINE = InnoDB,

PARTITION p20171204 VALUES IN (20171204) ENGINE = InnoDB,

PARTITION p20171206 VALUES IN (20171206) ENGINE = InnoDB,

PARTITION p20171207 VALUES IN (20171207) ENGINE = InnoDB) */

解讀：以上為 uuid小于5時放到p0分區下，uuid大于5且小于10放到p1分區下，uuid大于10且小于15放到p2分區下，uuid大于15 一直到最大值的存在p3分區下

2. LIST分區：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

)

)

PARTITION BY List (uuid) (

PARTITION p0 VALUES in (1,2,3,5),

PARTITION p1 VALUES in (7,9,10),

PARTITION p2 VALUES in (11,15)

)

);

解讀：以上為uuid 等于1/2/3/5時放到p0分區，7/9/10放到p1分區，11/15放到p2分區。當時用insert into時 如果uuid的值不存在p0/p1/p2分區時，則會插入失敗而報錯。

3. HASH分區:

HASH分區主要用來確保數據在預先確定數目的分區中平均分布。在RANGE分區和LIST分區中必須明確指定一個指定的列值或列值集合以指定應該保存在哪個分區中。而在HASH分區中，MySQL會自動完成這些工作，要做的只是基于將要被哈希的列值指定一個表達式，以及指定被分區的表將要被分割成的分區數量，如：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY HASH (uuid) (

PARTITIONS 3

));

解讀：MySQL自動創建3個分區，在執行insert into時，根據插入的uuid通過算法來自動分配區間。

注意：

(1) 由于每次插入、更新、刪除一行，這個表達式都要計算一次，這意味著非常復雜的表達式可能會引起性能問題，尤其是在執行同時影響大量行的運算（例如批量插入）的時候。

(2) 最有效率的哈希函數是只對單個表列進行計算，并且它的值隨列值進行一致的增大或減小，因為這考慮了在分區范圍上的“修剪”。也就是說，表達式值和它所基于的列的值變化越接近，就越能有效地使用該表達式來進行HASH分區。

3.1：線性HASH分區

線性HASH分區在“PARTITION BY”子句中添加“LINEAR”關鍵字。

線性HASH分區的有點在于增加、刪除、合并和拆分分區將變得更加快捷，有利于處理含有及其大量數據的表。它的缺點在于各個分區間數據的分布不大可能均衡。

4. KEY分區

類似于HASH分區，HASH分區允許用戶自定義的表達式，而KEY分區則不允許使用用戶自定義的表達式；HASH分區只支持整數分區，KEY分區支持除了blob和text類型之外的其他數據類型分區。

與HASH分區不同，創建KEY分區表的時候，可以不指定分區鍵，默認會選擇使用主鍵或唯一鍵作為分區鍵，沒有主鍵或唯一鍵，就必須指定分區鍵。

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY LINEAR Key (uuid)

PARTITIONS 3;

解讀：根據分區鍵來進行分區

5. 子分區

子分區是分區表中，每個分區的再次分割，適合保存非常大量的數據。

CREATE TABLE tbl_test (

registerTime Date

))

PARTITION BY GANGE(YEAR(registerTime))

SUBPARTITION BY HASH (TO_DAYS(registerTime))

SUBPARTITIONS 2

(

PARTITION p0 VALUES LESS THAN (2017),

PARTITION p1 VALUES LESS THAN (2020),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

解讀：主分區使用RANGE按照年來進行分區，有3個RANGE分區。這3個分區中又被進一步分成了2個子分區，實際上，整個表被分成了3 * 2 = 6個分區。每個子分區按照天進行HASH分區。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

(1) 在MySQL5.1中，對于已經通過RANGE或LIST分區了的表在進行子分區是可能的。子分區既可以使用HASH分區，也可以使用KEY分區。這也被稱為復合分區。

(2) 每個分區必須有相同數量的子分區。

(3) 如果在一個分區表上的任何分區上使用SUBPARTITION來明確定義任何子分區，那么就必須定義所有的子分區。

(4) 每個SUBPARTITION子句必須包含（至少）子分區的一個名字。

(5) 在每個子分區內，子分區的名字必須是惟一的，目前在整個表中，也要保持唯一。例如：

PARTITION BY RANGE(YEAR(registerTime))

SUBPARTITION BY HASH(TO_DAYS(registerTime))

(

PARTITION p0 VALUES LESS THAN (2017) (

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2020) (

SUBPARTITION s2,

SUBPARTITION s3

),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4,

SUBPARTITION s5

)

)

子分區可以用于特別大的表，可以在多個磁盤間分配數據和索引。例如：

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx'

,

,

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

INDEX DIRECTORY = '/disk1/idx'

九、MySQL分區處理NULL值的方式

MySQL中的分區禁止空值NULL上沒有進行處理，無論它是一個列值還是一個用戶定義表達式的值，一般而言，在這種情況下MySQL把NULL視為0。如果你希望回避這種做法，你應該在設計表時聲明列“NOT NULL”。

十、分區管理概述

可以對分區進行添加、刪除、重新定義、合并或拆分等管理操作。

① RANGE和LIST分區的管理

1. 刪除分區語句如：alter table tbl_test drop partition p0;

注意：

(1) 當刪除了一個分區，也同時刪除了該分區中所有的數據。

(2) 可以通過show create table tbl_test;來查看新的創建表的語句。

(3) 如果是LIST分區的話，刪除的數據不能新增進來，因為這些行的列值包含在已經刪除了的分區的值列表中。

2. 添加分區語句如：alter table tbl_test add partition(partition p3 values less than(50));

注意：

(1) 對于RANGE分區的表，只可以添加新的分區到分區列表的最高端。

(2) 對于LIST分區的表，不能添加已經包含在現有分區值列表中的任意值。

3. 如果希望能不丟失數據的條件下重新定義分區，可以使用如下語句：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)

(1) 拆分分區如：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10));

或者如：

ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5));

(2) 合并分區如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

4. 刪除所有分區，但保留數據，形式：ALTER TABLE tbl_name remove partitioning;

② HASH和KEY分區的管理

1. 減少分區數量語句如：ALTER TABLE tbl_name COALESCE PARTITION 2;

2. 添加分區數量語句如：ALTER TABLE tbl_name add PARTITION partitions 2;

③ 其他分區管理語句

1. 重建分區 ：類似于先刪除保存在分區中的所有記錄，然后重新插入它們，可用于整理分區碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

2. 優化分區 ：如果從分區中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB或TEXT類型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來收回沒有使用的空間，并整理分區數據文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

3. 分析分區 ：讀取并保存分區的鍵分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

4. 檢查分區 ：檢查分區中的數據或索引是否已經被破壞，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

5. 修補分區 ：修補被破壞的分區，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;

十、查看分區信息

1. 查看分區信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G;

2. 查看分區上的數據：select * from tbl_test partition(p0);

3. 查看MySQL會操作的分區：explain partitions select * from tbl_test where uuid = 2;

十一、 局限性

1. 最大分區數目不能超過1024，一般建議對單表的分區數不要超過50個。

2. 如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內。

3. 不支持外鍵。

4. 不支持全文索引，對分區表的分區鍵創建索引，那么這個索引也將被分區。

5. 按日期進行分區很合適，因為很多日期函數可以用。但是對字符串來說合適的分區函數不太多。

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

7. 臨時表不能被分區。

8. 分區表對于單條記錄的查詢沒有優勢。

9. 要注意選擇分區的成本，沒插入一行數據都需要按照表達式篩選插入的分區。

10. 分區字段盡量不要可以為null

Mysql表分區狀態查詢

一、查詢mysql表是否為分區表：可以查看表具有哪幾個分區、分區的方法、分區中數據的記錄數等信息

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION

FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='xw_coobill_order';

二、查詢表有多少個分區

SELECT TABLE_NAME, COUNT(*) AS CNT

FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL

GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;

三、分析執行語句

explain partitions select * from range_datetime where hiredate = '20151207124503' and hiredate='20151210111230';

四、分區管理

常規HASH和線性HASH的增加收縮分區的原理是一樣的。增加和收縮分區后原來的數據會根據現有的分區數量重新分布。HASH分區不能刪除分區，所以不能使用DROP PARTITION操作進行分區刪除操作；

只能通過ALTER TABLE ... COALESCE PARTITION num來合并分區，這里的num是減去的分區數量；

可以通過ALTER TABLE ... ADD PARTITION PARTITIONS num來增加分區，這里是null是在原先基礎上再增加的分區數量。

Mysql分區表Partition

一、背景

話說風和日麗的一天，為提高隨著業務增長的大表（3510449行吧）的訪問效率，于是決定對表分區，記錄如下。

二、實操

結合業務，若干條記錄會集中在一個日期，查詢時也往往只查詢一個日期內的數據，于是選取分區字段為時間。

創建分區 比如

CREATE TABLE message_all (

id int(10) NOT NULL AUTO_INCREMENT,

......

createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間'

PRIMARY KEY ( id , createtime )

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(createtime))

(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,

PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

不過我們表已經有了當然不能這么建，除非你想導一次數據。

如下操作 ：

1、

ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

(

PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),

PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

);

或者

2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))

(

PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))

);

然后追加。

ALTER TABLE message_all ADD PARTITION

(

PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

);

這里會有幾種錯誤情況：

1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime)) ;

[Err] 1492 - For RANGE partitions each partition must be defined

解釋：必須指定至少一個分區。

2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function

解釋：分區字段必須是主鍵之一。

3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

解釋：分區字段為timestamp，換成datetime。

4、[Err] 1526 - Table has no partition for value xxxx

解釋：用追加方式第一次必須覆蓋目前所有數據。

總結：

1、創建時必須指定至少一個分區。

2、key必須為主鍵之一。

3、RANGE處必須為INT型，時間字段用函數轉——YEAR()、YEARWEEK()、TO_DAYS()。

4、THAN處必須為INT型，時間字段用函數轉——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。

5、它就是以兩個INT比大小劃分的文件。

6、所有ENGINE必須一樣。

7、范圍分區添加只能在最大值后面追加。

8、分區是有上限的貌似1024個。

用到的其他操作

1、刪除分區（直接扔掉分區文件，數據也沒了）

ALTER TABLE message_all DROP PARTITION p2016;

2、清空分區數據

ALTER TABLE message_all TRUNCATE PARTITION p2017;

3、重定義（可實現：分區拆分、合并、重命名）

ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO

(

PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),

PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))

);

檢查/查看你的分區

1、SHOW TABLE STATUS LIKE 'message_all';

2、SELECT * FROM information_schema.partitions WHERE table_name='message_all';

3、SHOW CREATE TABLE message_all;

4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime= '2016-01-01' AND createtime '2016-12-30';如果用到了分區partitions里會有顯示。

5、指定分區查

SELECT COUNT(1) FROM message_all PARTITION (p2016) 表別名 WHERE ......;

到這里就結束啦，土豆白。

一些概念

水平分區Partition有以下幾種模式

mysql 分區PARTITIONS之基本使用

當數據表中的數據量很大時，分區帶來的效率提升才會顯現出來。

只有檢索字段為分區字段時，分區帶來的效率提升才會比較明顯。因此， 分區字段的選擇很重要 ，并且 業務邏輯要盡可能地根據分區字段做相應調整 （盡量使用分區字段作為查詢條件）。

1、分區表對業務透明，只需要維護一個表的數據結構。

2、DML操作加鎖僅影響操作的分區，不會影響未訪問分區。

3、通過分區交換快速將數據換入和換出分區表。

4、通過TRUNCATE操作快速清理特定分區數據。

5、通過強制分區僅訪問特定分區數據，減少操作影響。

6、通過大數據量分區能有效降低索引層數，提高查詢性能。

1、創建表時指定分區

物理文件：

2、插入數據

3、查詢 information_schema.partitions 表得到該表的分區信息

4、查詢計劃分析

5、添加分區

若報錯：

6、修改/覆蓋/合并分區

報錯

所以需要添加pmax 分區，一同修改

7、刪除分區

8、查詢具體分區的下數據

MySQL分區的限制

網站欄目：mysql分區怎么刪除 mysql 刪除分區會釋放磁盤嗎
網站路徑：http://m.newbst.com/article16/ddojidg.html

成都網站建設公司_創新互聯，為您提供服務器托管、定制網站、品牌網站建設、網站收錄、品牌網站設計、外貿網站建設

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯