如何使用MySQL分區表

這篇文章給大家介紹如何使用分區表，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

分區是一種表的設計模式，通俗地講表分區是將一大表，根據條件分割成若干個(gè)小表。但是對于應用程序來(lái)講，分區的表和沒(méi)有分區的表是一樣的。換句話(huà)來(lái)講，分區對于應用是透明的，只是數據庫對于數據的重新整理。

MySQL在創(chuàng )建表的時(shí)候可以通過(guò)使用PARTITION BY子句定義每個(gè)分區存放的數據。在執行查詢(xún)的時(shí)候，優(yōu)化器根據分區定義過(guò)濾那些沒(méi)有我們需要的數據的分區，這樣查詢(xún)就可以無(wú)需掃描所有分區，只需要查找包含需要數據的分區即可。

分區的另一個(gè)目的是將數據按照一個(gè)較粗的粒度分別存放在不同的表中。這樣做可以將相關(guān)的數據存放在一起，另外，當我們想要一次批量刪除整個(gè)分區的數據也會(huì )變得很方便。

下面簡(jiǎn)單介紹下四種常見(jiàn)的分區類(lèi)型：

• RANGE分區：最為常用，基于屬于一個(gè)給定連續區間的列值，把多行分配給分區。最常見(jiàn)的是基于時(shí)間字段。

• LIST分區：LIST分區和RANGE分區類(lèi)似，區別在于LIST是枚舉值列表的集合，RANGE是連續的區間值的集合。

• HASH分區：基于用戶(hù)定義的表達式的返回值來(lái)進(jìn)行選擇的分區，該表達式使用將要插入到表中的這些行的列值進(jìn)行計算。這個(gè)函數可以包含MySQL中有效的、產(chǎn)生非負整數值的任何表達式。

• KEY分區：類(lèi)似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且MySQL提供其自身的哈希函數。必須有一列或多列包含整數值。

上述四種分區類(lèi)型中，RANGE分區即范圍分區是最常用的。RANGE分區的特點(diǎn)是多個(gè)分區的范圍要連續，但是不能重疊，默認情況下使用VALUES LESS THAN屬性，即每個(gè)分區不包括指定的那個(gè)值。

2.分區操作示例

本節內容以RANGE分區為例，介紹下分區表相關(guān)的操作。

# 創(chuàng  )建分區表
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

創(chuàng )建后可以看到，每個(gè)分區都會(huì )對應1個(gè)ibd文件。上面創(chuàng )建語(yǔ)句還是很好理解的，在此分區表中，通過(guò)YEAR函數取出DATE日期中的年份并轉化為整型，年份小于1990的存儲在分區p0中，小于1995的存儲在分區p1中，以此類(lèi)推。請注意，每個(gè)分區的定義順序是從最低到最高。為了防止插入的數據因找不到相應分區而報錯，我們應該及時(shí)創(chuàng )建新的分區。下面繼續展示關(guān)于分區維護的其他操作。

# 查看某個(gè)分區的數據
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
# 交換分區
# 先創(chuàng  )建與分區表同樣結構的交換表
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.分區注意事項及適用場(chǎng)景

其實(shí)分區表的使用有很多限制和需要注意的事項，參考官方文檔，簡(jiǎn)要總結幾點(diǎn)如下：

• 分區字段必須是整數類(lèi)型或解析為整數的表達式。

• 分區字段建議設置為NOT NULL，若某行數據分區字段為null，在RANGE分區中，該行數據會(huì )劃分到最小的分區里。

• MySQL分區中如果存在主鍵或唯一鍵，則分區列必須包含在其中。

• Innodb分區表不支持外鍵。

• 更改sql_mode模式可能影響分區表的表現。

• 分區表不影響自增列。

從上面的介紹中可以看出，分區表適用于一些日志記錄表。這類(lèi)表的特點(diǎn)是數據量大、并且有冷熱數據區分，可以按照時(shí)間維度來(lái)進(jìn)行數據歸檔。這類(lèi)表是比較適合使用分區表的，因為分區表可以對單獨的分區進(jìn)行維護，對于數據歸檔更方便。

4.分區表為什么不常用

在我們項目開(kāi)發(fā)中，分區表其實(shí)是很少用的，下面簡(jiǎn)單說(shuō)明下幾點(diǎn)原因：

• 分區字段的選擇有限制。

• 若查詢(xún)不走分區鍵，則可能會(huì )掃描所有分區，效率不會(huì )提升。

• 若數據分布不均，分區大小差別較大，可能性能提升也有限。

• 普通表改造成分區表比較繁瑣。

• 需要持續對分區進(jìn)行維護，比如到了6月份前就要新增6月份的分區。

• 增加學(xué)習成本，存在未知風(fēng)險。

如果想使用分區表的話(huà)，建議提早做好規劃，在初始化的時(shí)候即創(chuàng )建分區表并制定維護計劃，使用得當還是比較方便的，特別是有歷史數據歸檔需求的表，使用分區表會(huì )使歸檔更方便。