共計 3761 個字符，預計需要花費 10 分鐘才能閱讀完成。
這篇文章給大家介紹如何使用 MySQL 分區表，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
分區是一種表的設計模式，通俗地講表分區是將一大表，根據條件分割成若干個小表。但是對于應用程序來講，分區的表和沒有分區的表是一樣的。換句話來講，分區對于應用是透明的，只是數據庫對于數據的重新整理。
MySQL 在創建表的時候可以通過使用 PARTITION BY 子句定義每個分區存放的數據。在執行查詢的時候，優化器根據分區定義過濾那些沒有我們需要的數據的分區，這樣查詢就可以無需掃描所有分區，只需要查找包含需要數據的分區即可。
分區的另一個目的是將數據按照一個較粗的粒度分別存放在不同的表中。這樣做可以將相關的數據存放在一起，另外，當我們想要一次批量刪除整個分區的數據也會變得很方便。
下面簡單介紹下四種常見的分區類型：
RANGE 分區：最為常用，基于屬于一個給定連續區間的列值，把多行分配給分區。最常見的是基于時間字段。
LIST 分區：LIST 分區和 RANGE 分區類似，區別在于 LIST 是枚舉值列表的集合，RANGE 是連續的區間值的集合。
HASH 分區：基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含 MySQL 中有效的、產生非負整數值的任何表達式。
KEY 分區：類似于按 HASH 分區，區別在于 KEY 分區只支持計算一列或多列，且 MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。
上述四種分區類型中，RANGE 分區即范圍分區是最常用的。RANGE 分區的特點是多個分區的范圍要連續，但是不能重疊，默認情況下使用 VALUES LESS THAN 屬性，即每個分區不包括指定的那個值。
2. 分區操作示例
本節內容以 RANGE 分區為例，介紹下分區表相關的操作。
# 創建分區表
mysql 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)
- );
Query OK, 0 rows affected (0.28 sec)
# 插入數據
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
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0創建后可以看到，每個分區都會對應 1 個 ibd 文件。上面創建語句還是很好理解的，在此分區表中，通過 YEAR 函數取出 DATE 日期中的年份并轉化為整型，年份小于 1990 的存儲在分區 p0 中，小于 1995 的存儲在分區 p1 中，以此類推。請注意，每個分區的定義順序是從最低到最高。為了防止插入的數據因找不到相應分區而報錯，我們應該及時創建新的分區。下面繼續展示關于分區維護的其他操作。
# 查看某個分區的數據
mysql SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
# 增加分區
mysql alter table tr add partition( - PARTITION p6 VALUES LESS THAN (2020)
- );
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 拆分分區
mysql alter table tr reorganize partition p5 into( - partition s0 values less than(2012),
- partition s1 values less than(2015)
- );
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 合并分區
mysql alter table tr reorganize partition s0,s1 into (
- partition p5 values less than (2015)
- );
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 清空某分區的數據
mysql alter table tr truncate partition p0;
Query OK, 0 rows affected (0.11 sec)
# 刪除分區
mysql alter table tr drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 交換分區
# 先創建與分區表同樣結構的交換表
mysql CREATE TABLE `tr_archive` (
- `id` INT,
- `name` VARCHAR(50),
- `purchased` DATE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.28 sec)
# 執行 exchange 交換分區
mysql alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)3. 分區注意事項及適用場景
其實分區表的使用有很多限制和需要注意的事項，參考官方文檔，簡要總結幾點如下：
分區字段必須是整數類型或解析為整數的表達式。
分區字段建議設置為 NOT NULL，若某行數據分區字段為 null，在 RANGE 分區中，該行數據會劃分到最小的分區里。
MySQL 分區中如果存在主鍵或唯一鍵，則分區列必須包含在其中。
Innodb 分區表不支持外鍵。
更改 sql_mode 模式可能影響分區表的表現。
分區表不影響自增列。
從上面的介紹中可以看出，分區表適用于一些日志記錄表。這類表的特點是數據量大、并且有冷熱數據區分，可以按照時間維度來進行數據歸檔。這類表是比較適合使用分區表的，因為分區表可以對單獨的分區進行維護，對于數據歸檔更方便。
4. 分區表為什么不常用
在我們項目開發中，分區表其實是很少用的，下面簡單說明下幾點原因：
分區字段的選擇有限制。
若查詢不走分區鍵，則可能會掃描所有分區，效率不會提升。
若數據分布不均，分區大小差別較大，可能性能提升也有限。
普通表改造成分區表比較繁瑣。
需要持續對分區進行維護，比如到了 6 月份前就要新增 6 月份的分區。
增加學習成本，存在未知風險。
如果想使用分區表的話，建議提早做好規劃，在初始化的時候即創建分區表并制定維護計劃，使用得當還是比較方便的，特別是有歷史數據歸檔需求的表，使用分區表會使歸檔更方便。
關于如何使用 MySQL 分區表就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
