前言：
分區(qū)是一種表的設(shè)計(jì)模式俭茧，通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個(gè)小表漓帚。但是對(duì)于應(yīng)用程序來(lái)講母债，分區(qū)的表和沒(méi)有分區(qū)的表是一樣的。換句話來(lái)講胰默，分區(qū)對(duì)于應(yīng)用是透明的场斑，只是數(shù)據(jù)庫(kù)對(duì)于數(shù)據(jù)的重新整理。本篇文章給大家?guī)?lái)的內(nèi)容是關(guān)于MySQL中分區(qū)表的介紹及使用場(chǎng)景牵署，有需要的朋友可以參考一下漏隐，希望對(duì)你有所幫助。
1.分區(qū)的目的及分區(qū)類型
MySQL在創(chuàng)建表的時(shí)候可以通過(guò)使用PARTITION BY子句定義每個(gè)分區(qū)存放的數(shù)據(jù)奴迅。在執(zhí)行查詢的時(shí)候青责，優(yōu)化器根據(jù)分區(qū)定義過(guò)濾那些沒(méi)有我們需要的數(shù)據(jù)的分區(qū)挺据，這樣查詢就可以無(wú)需掃描所有分區(qū)，只需要查找包含需要數(shù)據(jù)的分區(qū)即可脖隶。
分區(qū)的另一個(gè)目的是將數(shù)據(jù)按照一個(gè)較粗的粒度分別存放在不同的表中扁耐。這樣做可以將相關(guān)的數(shù)據(jù)存放在一起，另外产阱，當(dāng)我們想要一次批量刪除整個(gè)分區(qū)的數(shù)據(jù)也會(huì)變得很方便婉称。
下面簡(jiǎn)單介紹下四種常見(jiàn)的分區(qū)類型：
- RANGE分區(qū)：最為常用，基于屬于一個(gè)給定連續(xù)區(qū)間的列值构蹬，把多行分配給分區(qū)王暗。最常見(jiàn)的是基于時(shí)間字段。
- LIST分區(qū)：LIST分區(qū)和RANGE分區(qū)類似庄敛，區(qū)別在于LIST是枚舉值列表的集合俗壹，RANGE是連續(xù)的區(qū)間值的集合。
- HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)藻烤，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算绷雏。這個(gè)函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式怖亭。
- KEY分區(qū)：類似于按HASH分區(qū)涎显，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)依许。必須有一列或多列包含整數(shù)值棺禾。
上述四種分區(qū)類型中缀蹄，RANGE分區(qū)即范圍分區(qū)是最常用的峭跳。RANGE分區(qū)的特點(diǎn)是多個(gè)分區(qū)的范圍要連續(xù)，但是不能重疊缺前，默認(rèn)情況下使用VALUES LESS THAN屬性蛀醉，即每個(gè)分區(qū)不包括指定的那個(gè)值。
2.分區(qū)操作示例
本節(jié)內(nèi)容以RANGE分區(qū)為例衅码，介紹下分區(qū)表相關(guān)的操作拯刁。
# 創(chuàng)建分區(qū)表
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)
# 插入數(shù)據(jù)
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è)分區(qū)都會(huì)對(duì)應(yīng)1個(gè)ibd文件逝段。上面創(chuàng)建語(yǔ)句還是很好理解的垛玻，在此分區(qū)表中，通過(guò)YEAR函數(shù)取出DATE日期中的年份并轉(zhuǎn)化為整型奶躯，年份小于1990的存儲(chǔ)在分區(qū)p0中帚桩，小于1995的存儲(chǔ)在分區(qū)p1中，以此類推嘹黔。請(qǐng)注意账嚎，每個(gè)分區(qū)的定義順序是從最低到最高。為了防止插入的數(shù)據(jù)因找不到相應(yīng)分區(qū)而報(bào)錯(cuò)，我們應(yīng)該及時(shí)創(chuàng)建新的分區(qū)郭蕉。下面繼續(xù)展示關(guān)于分區(qū)維護(hù)的其他操作疼邀。
# 查看某個(gè)分區(qū)的數(shù)據(jù)
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)
# 增加分區(qū)
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
# 拆分分區(qū)
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
# 合并分區(qū)
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
# 清空某分區(qū)的數(shù)據(jù)
mysql> alter table tr truncate partition p0;
Query OK, 0 rows affected (0.11 sec)
# 刪除分區(qū)
mysql> alter table tr drop partition p1;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 交換分區(qū)
# 先創(chuàng)建與分區(qū)表同樣結(jié)構(gòu)的交換表
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)
# 執(zhí)行exchange交換分區(qū)
mysql> alter table tr exchange PARTITION p2 with table tr_archive;
Query OK, 0 rows affected (0.13 sec)
3.分區(qū)注意事項(xiàng)及適用場(chǎng)景
其實(shí)分區(qū)表的使用有很多限制和需要注意的事項(xiàng)，參考官方文檔召锈，簡(jiǎn)要總結(jié)幾點(diǎn)如下：
- 分區(qū)字段必須是整數(shù)類型或解析為整數(shù)的表達(dá)式旁振。
- 分區(qū)字段建議設(shè)置為NOT NULL，若某行數(shù)據(jù)分區(qū)字段為null涨岁，在RANGE分區(qū)中乏冀，該行數(shù)據(jù)會(huì)劃分到最小的分區(qū)里。
- MySQL分區(qū)中如果存在主鍵或唯一鍵奥秆，則分區(qū)列必須包含在其中谈竿。
- Innodb分區(qū)表不支持外鍵。
- 更改sql_mode模式可能影響分區(qū)表的表現(xiàn)沮尿。
- 分區(qū)表不影響自增列丛塌。
從上面的介紹中可以看出，分區(qū)表適用于一些日志記錄表畜疾。這類表的特點(diǎn)是數(shù)據(jù)量大赴邻、并且有冷熱數(shù)據(jù)區(qū)分，可以按照時(shí)間維度來(lái)進(jìn)行數(shù)據(jù)歸檔啡捶。這類表是比較適合使用分區(qū)表的姥敛，因?yàn)榉謪^(qū)表可以對(duì)單獨(dú)的分區(qū)進(jìn)行維護(hù)，對(duì)于數(shù)據(jù)歸檔更方便瞎暑。
4.分區(qū)表為什么不常用
在我們項(xiàng)目開(kāi)發(fā)中彤敛，分區(qū)表其實(shí)是很少用的，下面簡(jiǎn)單說(shuō)明下幾點(diǎn)原因：
- 分區(qū)字段的選擇有限制了赌。
- 若查詢不走分區(qū)鍵墨榄，則可能會(huì)掃描所有分區(qū)，效率不會(huì)提升勿她。
- 若數(shù)據(jù)分布不均袄秩，分區(qū)大小差別較大，可能性能提升也有限逢并。
- 普通表改造成分區(qū)表比較繁瑣之剧。
- 需要持續(xù)對(duì)分區(qū)進(jìn)行維護(hù)，比如到了6月份前就要新增6月份的分區(qū)砍聊。
- 增加學(xué)習(xí)成本背稼，存在未知風(fēng)險(xiǎn)。
總結(jié)：
本文較為詳細(xì)的介紹了MySQL分區(qū)相關(guān)內(nèi)容辩恼，如果想使用分區(qū)表的話雇庙，建議提早做好規(guī)劃谓形，在初始化的時(shí)候即創(chuàng)建分區(qū)表并制定維護(hù)計(jì)劃，使用得當(dāng)還是比較方便的疆前，特別是有歷史數(shù)據(jù)歸檔需求的表寒跳，使用分區(qū)表會(huì)使歸檔更方便。當(dāng)然竹椒，關(guān)于分區(qū)表的內(nèi)容還有很多童太，有興趣的同學(xué)可以找找官方文檔，官方文檔中有大量示例胸完。
參考：
