mysql分區的數據類型?一、分區概念　　分區是将一個表分成多個區塊進行操作和保存，從而降低每次操作的數據，提高性能而對于應用來說則是透明的，從邏輯上看隻有一張表，但在物理上這個表可能是由多個物理分區組成的，每個分區都是獨立的對象，可以進行獨立處理，下面我們就來聊聊關于mysql分區的數據類型?接下來我們就一起去了解一下吧!

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

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!