MySQL分區表

1、分區表的基本介紹

分區是一種表的設計模式，正確的分區可以極大地提升數據庫的查詢效率，完成更高質量的SQL編程。但是如果錯誤地使用分區，那么分區可能帶來毀滅性的的結果。

分區功能并不是在存儲引擎層完成的，常見的存儲引擎InnoDB、MyISAM、NDB等都支持分區。但是并不是所有的存儲引擎都支持，如CSV、FEDORATED、MERGE等就不支持分區。在使用此分區功能前，應該對選擇的存儲引擎對分區的支持有所了解。

MySQL數據庫在5.1版本及以上時添加了對分區的支持，分區的過程是將一個表或索引分解為多個更小、更可管理的部分。就訪問數據庫的應用而言，從邏輯上講，只有一個表或一個索引，但是在物理上這個表或索引可能由數十個物理分區組成。每個分區都是獨立的對象，可以獨自處理，也可以作為一個更大對象的一部分進行處理。

1.1、分區類型（水平分區、垂直分區）

MySQL數據庫支持的分區類型為水平分區（指將同一個表中不同行的記錄分配到不同的物理文件中），并不支持垂直分區（指將同一表中不同列的記錄分配到不同的物理文件中）。

此外，MySQL數據庫的分區是局部分區索引，一個分區中既存放了數據又存放了索引。而全局分區是指，數據存放在各個分區中，但是所有數據的索引放在一個對象中。目前，MySQL數據庫還不支持全局分區。

1.2、表分區的優缺點

分區的好處：

可以讓單表存儲更多的數據。
分區表的數據更容易維護，可以通過清除整個分區來批量刪除大量數據，也可以增加新的分區來支持新插入的數據。另外，還可以對一個獨立分區進行優化、檢查、修復等操作。
部分查詢能夠從查詢條件確定只落在少數分區上，速度會很快（查詢條件盡量掃描少的分區）。
分區表的數據還可以分布在不同的物理設備上，從而高效利用多個硬件設備。
可以使用分區表來避免某些特殊瓶頸，例如InnoDB單個索引的互斥訪問、ext3文件系統的inode鎖競爭。
可以備份和恢復單個分區。

分區的限制和缺點：

在mysql5.6.7之前的版本，一個表最多有1024個分區；從5.6.7開始，一個表最多可以有8192個分區。
MYSQL的分區字段，必須包含在主鍵字段內。如果一個表有主鍵，那么分區字段必須包含在主鍵內，也就是分區字段必須是主鍵的一部分或者全部，不能以非主鍵的字段作為分區字段。當然，也可以為沒有主鍵的表建立分區。
分區表無法使用外鍵約束。
NULL值會使分區過濾無效。
所有分區必須使用相同的存儲引擎。

1.3、分表和表分區的區別

分表：指的是通過一定規則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據時間成多個表。

分表與分區的區別在于：分區從邏輯上來講只有一張表（雖然在物理層面上是有多個表文件），而分表則是將一張表分解成多張表。

2、表分區的常見操作

2.1、判斷是否支持分區

mysql可以通過下面語句判斷是否支持分區：

SHOW VARIABLES LIKE '%partition%';

如果輸出：have_partitioning YES 則表示支持分區。

或者通過：

SHOW PLUGINS;

顯示所有插件，如果有partition - ACTIVE - STORAGE ENGINE - GPL 插件則表明支持分區

2.2、創建分區

# 創建分區表
CREATE TABLE `tr` (
     `id` INT, 
     `name` VARCHAR(50), 
     `purchased` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2015)
);

上面假設以 date 字段作為分區字段。

操作分區表和操作普通表沒什么差別，比如插數操作，mysql 會自動根據數據來將該數據插入對應分區中。

如下：

# 插入數據
INSERT INTO `tr` VALUES
 (1, 'desk organiser', '2003-10-15'),
 (2, 'alarm clock', '1997-11-05'),
 (3, 'chair', '2009-03-10'),
 (4, 'bookcase', '1989-01-10'),
 (5, 'exercise bike', '2014-05-09'),
 (6, 'sofa', '1987-06-05'),
 (7, 'espresso maker', '2011-11-22'),
 (8, 'aquarium', '1992-08-04'),
 (9, 'study desk', '2006-09-16'),
 (10, 'lava lamp', '1998-12-25');

2.3、增加分區

alter table tr add partition(PARTITION p6 VALUES LESS THAN (2020));

2.4、刪除分區

# 刪除分區
alter table tr drop partition p1;

2.5、查看分區

查詢分區，各區表數據量。

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tr';

查詢結果類似以下：

查看某個分區的數據：

# 查看某個分區的數據
SELECT * FROM tr PARTITION (p2);

注意，mysql5.5.41不支持對指定分區的查詢，在5.6增強了分區表的分區的相關操作，其中包括支持了對指定分區的查詢。

2.6、清空分區數據

# 清空某分區的數據
alter table tr truncate partition p0;

3、分區類型

3.1、RANGE分區（范圍分區）

RANGE分區是最常用的一種分區類型，基于屬于一個給定連續區間的列值，把多行分配給分區。這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。不使用COLUMNS關鍵字時RANGE括號內必須為整數字段名或返回確定整數的函數。

RANG分區特點：

根據分區鍵的值的范圍把不同數據存儲到表的不同分區中。
多個分區的分區鍵的值的范圍要連續，但是不能重疊。
默認情況下使用VALUES LESS THAN屬性，但每個分區并不包括指定的那個值。

3.1.1、創建RANGE分區

下面創建一個以id列為區間分區表，當id小于10時，數據插入p0分區；當id大于等于10小于20時，數據插入p1分區。代碼如下：

create  table  t(
  id int
)engine=innodb
partition by range(id)(
     partition  p0  values  less  than(10),
     partition  p1  values  less  than(20)
);

建立分區后，我們在插入數據時，mysql 會自動根據數據的值來將數據插插入到對應分區內。

insert into t values (9);
insert into t values (11);

插入上面數據后，查看分區：

# 查看p0分區的數據
SELECT * FROM t PARTITION (p0);

結果如下：

在對表建立分區后，插入的數據的值應該嚴格遵守分區的定義，當插入一個不屬于任何分區的值時，MySQL數據庫會直接報錯。如下：

為了避免這種情況發生，我們可以對分區添加一個MAXVALUE值的分區，MAXVALUE可以理解為正無窮，因此所有大于等于20且小于MAXVALUE的值被放入p2分區。

alter table t add partition(partition p2 values less than maxvalue);

此時，所有 >=20 的值都會被插入到 p2 分區中。

3.2、LIST分區

LIST分區和RANGE分區類似，區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇，而非連續的。

LIST分區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基于某個列值、并返回一個整數值的表達式，然后通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。

LIST分區的特點：

按分區鍵取值的列表進行分區
同范圍分區一樣，各分區的列表值不能重復
每一行數據必須能找到對應的分區列表，否則數據插入失敗

3.3、HASH分區

HASH分區的特點：

根據MOD(分區鍵，分區數)的值把數據行存儲到表的不同分區中
數據可以平均的分布在各個分區中
HASH分區的鍵值必須是一個INT類型的值，或是通過函數可以轉為INT類型

4、分區字段和主鍵

MYSQL的分區字段，必須包含在主鍵字段或者唯一索引列中。如果一個表有主鍵或者唯一索引列，那么分區字段必須包含在主鍵或者唯一索引列內，也就是分區的字段必須全部都屬于主鍵或者唯一索引列的一部分或者全部，不能以非主鍵且非唯一索引列的字段作為分區字段。當然，也可以為沒有主鍵的表建立分區。

在對表進行分區時，如果分區字段沒有包含在主鍵字段內那就會直接報錯。

假設某個表主鍵為 id，我們想用 date 日期字段作為分區字段，此時必須得把 date 日期字段作為主鍵一部分才行，可以創建 (id, date) 的組合主鍵，否則創建分區將直接報錯。

posted @ 2022-02-16 17:55 wenxuehai 閱讀(12995) 評論(0) 收藏舉報

刷新頁面返回頂部

//右下角添加目錄

提升自己，才有選擇的能力和勇氣