摘要：本文主要向大家介紹了MySQL數據庫之MySQL的分區和分表詳解 ，通過具體的內容向大家展現，希望對大家學習MySQL數據庫有所幫助。

本文主要向大家介紹了MySQL數據庫之MySQL的分區和分表詳解 ，通過具體的內容向大家展現，希望對大家學習MySQL數據庫有所幫助。

1. 分區

MySQL中的分區是指將一個數據表按照某種規則（如時間範圍、哈希等），劃分爲多個區塊，各個區塊所屬的數據文件是相互獨立的。分區對於SQL層來說，是一個完全封裝的、底層實現的黑盒子；但對於底層的文件系統來說，每一個區塊都對應 一個使用 # 分割命名的表文件。

分區的一個主要目的是將數據按照一個較粗的粒度劃分到不同的分區表中，這樣，可以將相關的數據存放在一起，另外，如果想一次刪除整個分區的數據也會變得非常方便。

分區的應用場景：

表的數據量非常大或者只在表的最後部分有熱點數據，其他都是歷史數據或冷數據。

批量刪除大量數據，可以使用刪除整個分區的方式。

分區表的數據可以分佈到不同的磁盤上，從而高效地利用多個硬件設備。

可以對獨立的分區進行優化、檢查、修復、備份和恢復等操作，易於維護。

分區的侷限性：

一個表最多隻能有1024個分區。

表中如果有主鍵或唯一索引，則必須包含到分區字段中。

分區表中無法使用外鍵。

所有分區都必須使用相同的存儲引擎。

某些存儲引擎不支持分區。

在創建表時，使用?PARTITION BY?子句，來定義每個分區中存放的數據。

在執行查詢操作時，優化器會根據分區的定義過濾掉那些沒有我們所要數據的分區，這樣，查詢就不會掃描所有分區，只查找包含我們所要數據的分區就可以了。

分區的類型：

範圍分區（PARTITION BY RANGE 或 PARTITION BY RANGE COLUMNS）

列表分區（PARTITION BY LIST）

哈希分區（PARTITION BY HASH)

索引分區（PARTITION BY KEY）

對於 RANGE 或 LIST 類型的分區，還支持子分區 SUBPARTITION，不過，生產環境中很少見。

1.1 範圍分區

MySQL支持多種分區表，最常見的就是根據範圍進行分區。

根據範圍分區時，每個分區存儲指定範圍的數據，分區表達式可以是列（字段），也可以是包含列的表達式。

根據範圍分區時，範圍應該連續但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關鍵字。不使用COLUMNS關鍵字時RANGE括號內必須爲整數字段名或返回確定整數的函數。

下面就是一個將日誌記錄表 log_1 根據時間範圍來分區的例子。

根據具體的年份：

1

2

3

4

5

6

7

8

9

10

create table if not exists log_1 (

content varchar(200) not null default '' COMMENT '日誌內容',

create_time datetime not null default CURRENT_TIMESTAMP COMMENT '創建時間',

KEY create_time (create_time)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日誌記錄表' PARTITION BY RANGE(YEAR(create_time))(

PARTITION p_null VALUES LESS THAN (2017),

PARTITION p_2017 VALUES LESS THAN (2018),

PARTITION p_2018 VALUES LESS THAN (2019),

PARTITION p_2019 VALUES LESS THAN (2020),

PARTITION p_final VALUES LESS THAN MAXVALUE);

根據時間戳：

1

2

3

4

5

6

PARTITION BY RANGE ( UNIX_TIMESTAMP(create_time) ) (

PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),

PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00') ),

PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00') ),

PARTITION p3 VALUES LESS THAN ( MAXVALUE )

);

根據COLUMNS關鍵字：

添加COLUMNS關鍵字可定義非integer範圍及多列範圍，不過需要注意COLUMNS括號內只能是列名，不支持函數；多列範圍時，多列範圍必須呈遞增趨勢。

1

2

3

4

5

6

7

PARTITION BY RANGE COLUMNS( create_time ) (

PARTITION p0 VALUES LESS THAN ('2017-01-01 00:00:00'),

PARTITION p1 VALUES LESS THAN ('2018-01-01 00:00:00'),

PARTITION p2 VALUES LESS THAN ('2019-01-01 00:00:00'),

PARTITION p3 VALUES LESS THAN ('2020-01-01 00:00:00'),

PARTITION p4 VALUES LESS THAN MAXVALUE

);
1.2 列表分區

根據具體數值分區，每個分區數值不重疊，使用PARTITION BY LIST、VALUES IN關鍵字。跟Range分區類似，不使用COLUMNS關鍵字時List括號內必須爲整數字段名或返回確定整數的函數。

1

2

3

4

5

6

PARTITION BY LIST(cate_id) (

PARTITION p1 VALUES IN (1,2,3),

PARTITION p2 VALUES IN (4,5,6),

PARTITION p3 VALUES IN (7,8,9),

PARTITION p4 VALUES IN (10,11,12)

);

注意：?所有的數值都必須被分區，否則插入一個不屬於任何一個分區的數值會報錯。

1.3 哈希分區

Hash分區主要用來確保數據在預先確定數目的分區中平均分佈，Hash括號內只能是整數列或返回確定整數的函數，實際上就是使用返回的整數對分區數取模。

1

2

PARTITION BY HASH( YEAR(creat_time) )

PARTITIONS 4;

Hash分區也存在與傳統Hash分表一樣的問題，可擴展性差。MySQL提供了一個類似於一致Hash的分區方法--線性Hash分區，只需要在定義分區時添加LINEAR關鍵字。

1

2

PARTITION BY LINEAR HASH( YEAR(creat_time) )

PARTITIONS 4;
1.4 索引分區

Key分區與Hash分區很相似，只是函數不同，定義時把Hash關鍵字替換成Key即可，同樣Key分區也有對應與線性Hash的線性Key分區方法。

1

2

PARTITION BY LINEAR KEY (id)

PARTITIONS 4;

分區查詢優化：

分區給查詢優化帶來了新的思路。分區的最大優點就是優化器可以根據分區的定義過濾掉一些分區。可以把分區當作是一種粗粒度的索引策略。

查詢分區表時，必須在WHERE條件中加入分區列（即使看似多餘的也要帶上），這樣就可以讓優化器過濾掉無須訪問的分區。

使用?EXPLAIN PARTITIONS?可以檢查優化器是否進行了分區過濾。

1

EXPLAIN PARTITIONS SELECT * FROM log_1

上面這個查詢語句將訪問所有的分區，效率很低。下面，我們加入一個where條件。

1

EXPLAIN PARTITIONS SELECT * FROM log_1 WHERE create_time>'2017-01-01' AND create_time<='2017-12-31'

這時，查詢就只會訪問 p_2017 分區，而過濾掉其他分區。

注意：?MySQL只能在使用分區函數的列本身進行WHERE比較時才能過濾分區，而不能根據表達式的值來來過濾分區。

下面這條查詢語句就無法過濾分區：

1

EXPLAIN PARTITIONS SELECT * FROM log_1 WHERE YEAR(create_time)=2017

這和查詢語句中使用獨立的列才能用到索引的道理是一樣的。

2. 分表 2.1 合併表（Merge table）

合併表屬於分表的一種方式，和分區表類似，在MyISAM中，各個子表被一個結構完全相同的邏輯表所封裝，但合併表允許用戶直接訪問各個子表。而分區表的子表（分區）都是被MySQL隱藏的，只能通過分區表去訪問子表。

合併表相當於一個容器，裏面包含了多個真實的子表。

創建合併表時，使用?UNION?關鍵字來指定合併表中包含哪些子表。合併表必須採用?MERGE?引擎。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

# 子表t1

create table t1(

id int not null primary key,

name char(10) not null,

)ENGINE=MyISAM;

# 子表t2

create table t2(

id int not null primary key,

name char(10) not null,

)ENGINE=MyISAM;

# 合併表m

create table mrg(

id int not null primary key,

name char(10) not null,

)ENGINE=MERGE UINON(t1,t2) INSERT_METHOD=LAST;

INSERT_METHOD=LAST語法的作用是將所有的INSERT語句都發送給最後一個子表。FIRST或LAST關鍵字，可以控制將數據插入到合併表中的哪一個子表。當然，也可以直接在SQL中操作子表。

2.2 切分表

切分表是指將表中的數據按照水平分割或垂直分割的方式分配到多張表中。

通常，我們所說的分表是指水平分割，也就是傳統的分表技術。

常用的分表方法有：

範圍分表

哈希分表

分表後，如果想對總數據進行統計（count、sum等），只能對所有子表統計後，再在應用層再次計算得出最後的統計數據。而分區則不受影響，直接統計分區表即可。

也就是說，分表後，原來的應用層代碼需要做較大的改動。

職

相關文章