以下是我對MySQL分區不分的了解，僅供參考。
MySQL從5.1版本後支援分區的功能，分區是根據一定規則，将滿足相同條件的資料存儲在一起，以便更友善的對資料經行管理。
在建立分區前，我們首先檢視使用的MySQL版本是否支援分區：
若是看到partition的值為yes，則支援分區。
分區類型：
- 1. range分區
- 2. list分區
- 3. hash分區
- 4. key分區
所有分區類型，不能使用主鍵/唯一鍵之外的字段經行分區！！！
1、range分區：依據給定的連續範圍，将資料按照規則存儲到對應分區上。
代碼實作：
- range分區的建立：
mysql> create table ranges(
-> id int ,
-> age int,
-> time timestamp default current_timestamp on update current_timestamp,
-> primary key (id)
-> )
-> partition by range(id)(
-> partition p0 values less than (5),
-> partition p1 values less than (10),
-> partition p2 values less than (15)
-> );
Query OK, 0 rows affected (0.07 sec)
插入資料：
mysql> insert into ranges (id ,age) values(1,20),(2,21),(3,22),(4,23),(5,24),(6,29),(7,32),(8,43),(9,23),(10,54),(11,23),(12,45),(13,90),(14,54);
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
查詢range分區相關資訊：
mysql> select partition_name name,
-> partition_expression expresstion,
-> partition_description description,
-> table_rows
-> from information_schema.partitions
-> where table_schema=schema() and table_name='ranges';
+------+-------------+-------------+------------+
| name | expresstion | description | table_rows |
+------+-------------+-------------+------------+
| p0 | id | 5 | 4 |
| p1 | id | 10 | 5 |
| p2 | id | 15 | 5 |
+------+-------------+-------------+------------+
3 rows in set (0.00 sec)
當我們要插入大于id大于等于15的數時，會發生什麼呢？
mysql> insert into ranges(id,age) values(15,30);
ERROR 1526 (HY000): Table has no partition for value 15
沒有包含15的分區，這時我們應該添加range分區，指令實作如下：
- 添加分區
mysql> alter table ranges add partition (partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
這一句是添加名為p3的分區，範圍為[15，最大值)，即大于等于15的所有值。
再次檢視分區的情況：
mysql> select partition_name name,
-> partition_expression expresstion,
-> partition_description description,
-> table_rows
-> from information_schema.partitions
-> where table_schema=schema() and table_name='ranges';
+------+-------------+-------------+------------+
| name | expresstion | description | table_rows |
+------+-------------+-------------+------------+
| p0 | id | 5 | 4 |
| p1 | id | 10 | 5 |
| p2 | id | 15 | 5 |
| p3 | id | MAXVALUE | 0 | <----新加的分區
+------+-------------+-------------+------------+
4 rows in set (0.00 sec)
此時再次添加id大于15的值：
mysql> insert into ranges(id,age) values(19,20);
Query OK, 1 row affected (0.01 sec)
- 拆分分區
mysql> alter table ranges reorganize partition p3 into (
-> partition p4 values less than (20),
-> partition p5 values less than (25),
-> partition p6 values less than maxvalue
-> ) ;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查詢拆分後的分區資訊
mysql> select partition_name name,
-> partition_expression expresstion,
-> partition_description description,
-> table_rows
-> from information_schema.partitions
-> where table_schema=schema() and table_name='ranges';
+------+-------------+-------------+------------+
| name | expresstion | description | table_rows |
+------+-------------+-------------+------------+
| p0 | id | 5 | 4 |
| p1 | id | 10 | 5 |
| p2 | id | 15 | 5 |
| p4 | id | 20 | 0 |
| p5 | id | 25 | 0 |
| p6 | id | MAXVALUE | 0 |
+------+-------------+-------------+------------+
6 rows in set (0.00 sec)
這是将p3分區拆分為p4、p5、p6三個分區，這時就沒有p3分區了。
- 合并分區：
mysql> alter table ranges reorganize partition p4,p5,p6 into(partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
#檢視合并後的分區資訊
mysql> select partition_name name,
-> partition_expression expresstion,
-> partition_description description,
-> table_rows
-> from information_schema.partitions
-> where table_schema=schema() and table_name='ranges';
+------+-------------+-------------+------------+
| name | expresstion | description | table_rows |
+------+-------------+-------------+------------+
| p0 | id | 5 | 4 |
| p1 | id | 10 | 5 |
| p2 | id | 15 | 5 |
| p3 | id | MAXVALUE | 0 |
+------+-------------+-------------+------------+
4 rows in set (0.00 sec)
- 删除分區：
#删除前ranges表資料
mysql> select *from ranges;
+----+------+---------------------+
| id | age | time |
+----+------+---------------------+
| 1 | 20 | 2019-10-26 17:45:56 |
| 2 | 21 | 2019-10-26 17:45:56 |
| 3 | 22 | 2019-10-26 17:45:56 |
| 4 | 23 | 2019-10-26 17:45:56 |
| 5 | 24 | 2019-10-26 17:45:56 |
| 6 | 29 | 2019-10-26 17:45:56 |
| 7 | 32 | 2019-10-26 17:45:56 |
| 8 | 43 | 2019-10-26 17:45:56 |
| 9 | 23 | 2019-10-26 17:45:56 |
| 10 | 54 | 2019-10-26 17:45:56 |
| 11 | 23 | 2019-10-26 17:45:56 |
| 12 | 45 | 2019-10-26 17:45:56 |
| 13 | 90 | 2019-10-26 17:45:56 |
| 14 | 54 | 2019-10-26 17:45:56 |
| 15 | 20 | 2019-10-26 18:10:09 |
| 19 | 20 | 2019-10-26 18:10:18 |
+----+------+---------------------+
16 rows in set (0.01 sec)
#删除語句
mysql> alter table ranges drop partition p2 ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#删除後ranges表資料
mysql> select *from ranges;
+----+------+---------------------+
| id | age | time |
+----+------+---------------------+
| 1 | 20 | 2019-10-26 17:45:56 |
| 2 | 21 | 2019-10-26 17:45:56 |
| 3 | 22 | 2019-10-26 17:45:56 |
| 4 | 23 | 2019-10-26 17:45:56 |
| 5 | 24 | 2019-10-26 17:45:56 |
| 6 | 29 | 2019-10-26 17:45:56 |
| 7 | 32 | 2019-10-26 17:45:56 |
| 8 | 43 | 2019-10-26 17:45:56 |
| 9 | 23 | 2019-10-26 17:45:56 |
| 15 | 20 | 2019-10-26 18:10:09 |
| 19 | 20 | 2019-10-26 18:10:18 |
+----+------+---------------------+
11 rows in set (0.00 sec)
可見，删除某一分區後，該分區的資料也被删除。
range分區的大部分知識點已經完結，歡迎大家補充。謝謝
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)