實踐背景
項目中有的表空間太大，且行數太多，故決定對一些表進行分庫分表。再研究選型方案的時候發現常用的一些分庫分表的解決方案對業務代碼修改較多，故決定採用MySQL的分區方案。
其實在我個人看來，分區表就是MySQL幫我們實現了底層的分庫分表，不需要涉及業務代碼的修改，不需要關注分佈式事務。因為就訪問數據庫而言，邏輯上還是隻有一個表，但是實際上確有多個物理分區對象組成，會根據具體的分區規則查詢具體的分區。
介紹一下這次實踐的表，表空間大小172G，1億2千萬條記錄。
數據庫版本：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=COMPACT
PARTITION 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';
實踐背景
項目中有的表空間太大，且行數太多，故決定對一些表進行分庫分表。再研究選型方案的時候發現常用的一些分庫分表的解決方案對業務代碼修改較多，故決定採用MySQL的分區方案。
其實在我個人看來，分區表就是MySQL幫我們實現了底層的分庫分表，不需要涉及業務代碼的修改，不需要關注分佈式事務。因為就訪問數據庫而言，邏輯上還是隻有一個表，但是實際上確有多個物理分區對象組成，會根據具體的分區規則查詢具體的分區。
介紹一下這次實踐的表，表空間大小172G，1億2千萬條記錄。
數據庫版本：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=COMPACT
PARTITION 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行數少的走不同的範圍索引。至於它底層是怎麼實現的我就沒去研究了
三、分區管理
簡單介紹了下範圍分區，接下來說一下對分區常用的一下操作。
分區管理包括對於分區的增加，刪除，以及查詢。更多詳見官方文檔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';
實踐背景
項目中有的表空間太大，且行數太多，故決定對一些表進行分庫分表。再研究選型方案的時候發現常用的一些分庫分表的解決方案對業務代碼修改較多，故決定採用MySQL的分區方案。
其實在我個人看來，分區表就是MySQL幫我們實現了底層的分庫分表，不需要涉及業務代碼的修改，不需要關注分佈式事務。因為就訪問數據庫而言，邏輯上還是隻有一個表，但是實際上確有多個物理分區對象組成，會根據具體的分區規則查詢具體的分區。
介紹一下這次實踐的表，表空間大小172G，1億2千萬條記錄。
數據庫版本：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=COMPACT
PARTITION 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行數少的走不同的範圍索引。至於它底層是怎麼實現的我就沒去研究了
三、分區管理
簡單介紹了下範圍分區，接下來說一下對分區常用的一下操作。
分區管理包括對於分區的增加，刪除，以及查詢。更多詳見官方文檔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呢？因為它可以不停機遷移數據，支持全量遷移和增量遷移，對原表影響不大。
遷移過程如下：
- 首先在RDS的同一個實例裡面新建了一張同結構的分區表
- 使用DTS創建遷移任務，遷移時候不要選擇結構錢謙益，只選擇全量+增量遷移
- 然後還需要編輯下目標庫表名，也就是做下映射從A->B的遷移
- 停掉寫入數據的任務，當任務隊列為空時，等待幾分鐘暫停並結束遷移任務
- 最後修改表名，完成數據遷移和切換（我在測試環境修改分區表名要一些時間，但RDS修改表名是秒改）
參考官方文檔：MySQL 5.6 分區
大家有什麼疑惑可以私信我“學習”領取視頻學習資料。
實踐背景
項目中有的表空間太大，且行數太多，故決定對一些表進行分庫分表。再研究選型方案的時候發現常用的一些分庫分表的解決方案對業務代碼修改較多，故決定採用MySQL的分區方案。
其實在我個人看來，分區表就是MySQL幫我們實現了底層的分庫分表，不需要涉及業務代碼的修改，不需要關注分佈式事務。因為就訪問數據庫而言，邏輯上還是隻有一個表，但是實際上確有多個物理分區對象組成，會根據具體的分區規則查詢具體的分區。
介紹一下這次實踐的表，表空間大小172G，1億2千萬條記錄。
數據庫版本：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=COMPACT
PARTITION 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行數少的走不同的範圍索引。至於它底層是怎麼實現的我就沒去研究了
三、分區管理
簡單介紹了下範圍分區，接下來說一下對分區常用的一下操作。
分區管理包括對於分區的增加，刪除，以及查詢。更多詳見官方文檔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呢？因為它可以不停機遷移數據，支持全量遷移和增量遷移，對原表影響不大。
遷移過程如下：
- 首先在RDS的同一個實例裡面新建了一張同結構的分區表
- 使用DTS創建遷移任務，遷移時候不要選擇結構錢謙益，只選擇全量+增量遷移
- 然後還需要編輯下目標庫表名，也就是做下映射從A->B的遷移
- 停掉寫入數據的任務，當任務隊列為空時，等待幾分鐘暫停並結束遷移任務
- 最後修改表名，完成數據遷移和切換（我在測試環境修改分區表名要一些時間，但RDS修改表名是秒改）
參考官方文檔：MySQL 5.6 分區
大家有什麼疑惑可以私信我“學習”領取視頻學習資料。