MySQL——RDS下的分區表實踐

實踐背景

? ? ? 項目中有的表空間太大，且行數太多，故決定對一些表進行分庫分表。再研究選型方案的時候發現常用的一些分庫分表的解決方案對業務代碼修改較多，故決定采用MySQL的分區方案。

? ? ? 其實在我個人看來，分區表就是MySQL幫我們實現了底層的分庫分表，不需要涉及業務代碼的修改，不需要關注分布式事務。因為就訪問數據庫而言，邏輯上還是只有一個表，但是實際上確有多個物理分區對象組成，會根據具體的分區規則查詢具體的分區。

? ? ? 介紹一下這次實踐的表，表空間大小172G，1億2千萬條記錄。鄭州不孕不育專科醫院：http://jbk.39.net/yiyuanzaixian/zztjyy/

? ? ? 數據庫版本：RDS MySQL 5.6

? ? ? 工具：阿里云DTS

一、為什么分區？

? ?優點：

• ? ? ?對已過期或者不需要保存的數據，可以通過刪除與這些數據有關的分區來快速刪除數據，它的效率遠比DELETE高

• ? ? ?在where子句中包含分區條件時，可以只掃描必要的一個或者多個分區來提高查詢效率

? ? ? ?例如下面語句：

? ? ? ? SELECT * FROM t PARTITION（p0，p1）WHERE c <5 僅選擇與WHERE條件匹配的分區p0和p1中的記錄

• ? ? ?涉及聚合函數SUM()、COUNT()的查詢時，會在每個分區上并行處理

• ? ? ?分區把原本一個表的數據存儲在多個物理磁盤上，實現了更高的IOPS

? ?缺點：

• ? ? ?無法使用外鍵，不支持全文索引（現在應該也沒什么公司還在用外鍵吧）

• ? ? ?分區鍵設計不太靈活，如果不走分區鍵，很容易出現全表鎖

• ? ? 開發寫一個SQL，不清楚mysql是怎么玩的

二、RANGE分區

? ??目前MySQL支持范圍分區（RANGE），列表分區（LIST），哈希分區（HASH）以及KEY分區四種。

? ? 本文是以范圍分區（RANGE）對時間進行的分區的，故我就簡單介紹一下RANGE分區。更多分區類型詳見官方文檔MySQL 5.6 分區類型

?

? ? 基于一個給定連續區間的列值，根據區間分配分區。最常見的是基于時間字段。其實基于分區的列最好是整型，如果日期型的可以使用函數轉換為整型。MySQL 5.6支持的分區函數

? ? 本例中使用TO_DAYS函數

? ??

CREATE?TABLE?members?(????id?VARCHAR(25)?NOT?NULL,
????firstname?VARCHAR(25)?NOT?NULL,
????lastname?VARCHAR(25)?NOT?NULL,
????username?VARCHAR(16)?NOT?NULL,
????email?VARCHAR(35),
????joindate?DATETIME?NOT?NULL?DEFAULT?'0000-00-00?00:00:00',
????PRIMARY?KEY?(id,joindate)?USING?BTREE,????KEY?idx_joindate?(joindate)?USING?BTREE
)ENGINE=InnoDB?DEFAULT?CHARSET=utf8?ROW_FORMAT=COMPACTPARTITION?BY?RANGE?(TO_DAYS(joindate))?(????PARTITION?p0?VALUES?LESS?THAN?(TO_DAYS('1960-01-01')),????PARTITION?p1?VALUES?LESS?THAN?(TO_DAYS('1970-01-01')),????PARTITION?p2?VALUES?LESS?THAN?(TO_DAYS('1980-01-01')),????PARTITION?p3?VALUES?LESS?THAN?(TO_DAYS('1990-01-01')),????PARTITION?p4?VALUES?LESS?THAN?MAXVALUE
);

?PS:像例子中的如果你有主鍵或唯一索引，你必須把你的分區鍵也加上，其中joindate就是分區鍵，要不創建會失敗！

?PS:像上面加了LESS THAN MAXVALUE，后面就不能新加分區了！！！

示例：

如下查詢就會落在定義的p2分區內的索引上。故在查詢的時候帶上你的分區鍵就會走對應分區查詢數據，如果你的條件跨越多個分區進行聚合函數SUM()、COUNT()的查詢時，它會在每個分區上并行處理。如果沒有帶分區鍵查詢就會全表查詢。

explain?partitions?select?*?from?members?WHERE?joindate?BETWEEN?'1970-02-03'?AND?'1970-02-04';

?

?

我在遷移完數據進行查詢的時候發現一個特別有意思的現象，同一條SQL如果分區鍵的時間區間不一樣，它會根據rows行數少的走不同的范圍索引。至于它底層是怎么實現的我就沒去研究了

?

三、分區管理

? ? ? 簡單介紹了下范圍分區，接下來說一下對分區常用的一下操作。焦作國醫胃腸專科醫院：http://jz.lieju.com/zhuankeyiyuan/37756433.htm

? ? ? 分區管理包括對于分區的增加，刪除，以及查詢。更多詳見官方文檔MySQL 分區管理

? ? 1.增加分區

? ? ??對于RANGE和LIST分區：

alter?table?table_name?add?partition?(partition?p0?values?...(exp))

#例ALTER?TABLE?members?ADD?PARTITION?(TO_DAYS('2021-03-01'));

? ??2.刪除分區

? ? ??刪除了分區，同時也將刪除該分區中的所有數據。如果刪除了分區導致分區不能覆蓋所有值，那么插入數據的時候會報錯。

alter?table?table_name?drop?partition?p0;

? ?3.查詢有多少個分區

SELECT?*?FROM?INFORMATION_SCHEMA.PARTITIONS?WHERE?TABLE_NAME?=?'members';

四、數據遷移

? 前面說了那么多概念，我說一下本次把大表數據遷移到分區表的過程。

? 為什么會選擇DTS呢？因為它可以不停機遷移數據，支持全量遷移和增量遷移，對原表影響不大。

?

? 遷移過程如下：

1. ? 首先在RDS的同一個實例里面新建了一張同結構的分區表

2. ? 使用DTS創建遷移任務，遷移時候不要選擇結構錢謙益，只選擇全量+增量遷移

3. ? 然后還需要編輯下目標庫表名，也就是做下映射從A->B的遷移

4. ? 停掉寫入數據的任務，當任務隊列為空時，等待幾分鐘暫停并結束遷移任務

5. ? 最后修改表名，完成數據遷移和切換（我在測試環境修改分區表名要一些時間，但RDS修改表名是秒改）