简述
分区是指根据一定的规则，数据库将表分解为多个更小的，更容易管理的部分，就访问数据库而言，逻辑上只有一张表或一个索引，但实际上这张表可能又多个物理分区共同构成，每一个分区都是一个独立的对象，可以独自处理，也可以作为表的一部分进行处理，分区对应用来说是完全透明的，不影响应用的业务逻辑。
MySQL采用分区的优点：
1.和单个磁盘或单个文件系统比较，可以存储更多的数据。
2.优化查询，采用‘分而治之’的思想，例如在where子句中包含分区条件时，可以只扫描必要的一个或几个分区，避免全表扫描带来的性能缺失。此外，在使用SUM和COUNT等聚合函数时，可以采用归并的思想，只需汇总每一个分区的查询结果来提高效率。
3.对于过期或垃圾数据的清除，可以通过删除特定分区来减少查询。
特别注意:
1.大部分存储引擎(InnoDB, MyISAM, Memory等)均支持分区，MERGE和CSV不支持分区；
2.可以使用 SHOW VARIABLES LIKE '%partition%'(版本在5.6以下，5.6以上使用SHOW PLUGINS)来查看当前版本的MySQL是否支持分区；
3.同一个分区表必须使用同一个存储引擎，但是对同一个MySQL服务器中，同一个数据库中对不同分区表使用不同的存储引擎；
4.MySQL分区适用于一个表的所有数据和索引，不能只对其数据(或索引)进行分区。分区表上创建的所有一定是LOCAL索引。
分区类型
| 分区类型 | 介绍 |
|---|---|
| RANGE | 对分区字段限定区间范围来分配分区, 例如LESS THAN (100) |
| LIST | 对分区字段使用枚举集合来分配分区，例如 IN (1, 2, 3) |
| HASH | 对分区字段执行哈希算法，得到其在分区表列表的索引，实现分散热点数据 |
| KEY | 类似HASH分区，不支持自定义表达式作为分区键。 |
RANGE分区
RANGE分区的表是利用取值范围将表分成分区，区间要连续并且不能重叠，使用
VALUES LESS THAN操作符进行分区定义
格式：
# 建表
CREATE TABLE emp(
# 字段集
) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 默认配置
PARTITION BY RANGE (分区字段) (
PARTITION 分区名称 VALUES LESS THAN (值1),
PARTITION 分区名称 VALUES LESS THAN (值2),
PARTITION 分区名称 VALUES LESS THAN (max_value)
);
例子，对emp表进行分区，分区参照字段为stored_id。
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(20),
job VARCHAR(30),
stored_id INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (stored_id) (
PARTITION p01 VALUES LESS THAN (10),
PARTITION P02 VALUES LESS THAN (20),
PARTITION p03 VALUES LESS THAN (30)
);
emp表中分区字段为stored_id，其最高分区为p03，接收的最大值为30，所以当进行INSERT操作，若stored_id>30则会因为找不到分区而出错。
mysql> insert into emp values(3, "123", "worker", 33);
ERROR 1526 (HY000): Table has no partition for value 33
查看表分区信息：
查看当前表分区的信息是在information_schema的partition表中得到的，schema()函数获取当前数据库库实例
SELECT
partition_name,
partition_expression,
partition_description,
table_rows
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = '表名';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p01 | stored_id | 10 | 0 |
| P02 | stored_id | 20 | 0 |
| p03 | stored_id | 30 | 0 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.00 sec)
注意：
1.在RANGE分区中，分区键如果是NULL值则会被当成最小值来处理
2.RANGE分区支持整数列分区，如果想要对日期列或字符串列进行分区的话，就需要使用函数来讲其他类型转换为整数类型，例如YEAR()、TO_DAYS()、TO_SECONDS()函数能够将时间转换为整数，CONVERT('12345', SIGNED)、CAST('12345', SIGNED)实现字符串向数字的转换
3.若要删除过期数据可以通过ALTER TABLE [表名] DROP PARTITION [分区名]来实现。
LIST分区
LIST分区和RANGE分区类似，不同之处在于LIST分区是一个枚举列表的集合，RANGE则是一个区间
LIST分区通过使用PARTITION BY LIST(expr)子句实现，expr是某列或某一个基于某列的函数表达式，该表达式总是返回一个整型数据，最后通过 VALUES IN (values_list)的方式来定义分区，其中value_list是一个整型列表，与RANGE分区不同之处在于不必按照特定的顺序，
格式：
# 建表
CREATE TABLE xxx(
# 字段
) ENGINE=InnoDB DEFAULT xxxx
PARTITION BY LIST(expr)(
PARTITION p1 VALUES IN (1, 2, 4),
PARTITION P2 VALUES IN (3, 5, 7),
PARTITION p3 VALUES IN (6)
);
如果视图插入的列值不包含在values_list中会报错。
COLUMNS分区
前面所说的RANGE、LIST都是不支持
expr为非整型值的，后面MySQL就出现了COLUMNS分区来对RANGE和LIST分区进行包装，也被称为RANGE COLUMNS()和LIST COLUMNS()，这样就解决了原来的RANGE和LIST需要通过函数来对非整形数据进行转换的问题
此外，COLUMNS分区支持多列字段作为复合分区键。大致和order by后跟两个字段的意思相似，但是不支持表达式作为分区键
CREATE TABLE t1(
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p_t1_1 VALUES LESS THAN (0, 10),
PARTITION P_t1_2 VALUES LESS THAN (10, 10),
PARTITION P_t1_3 VALUES LESS THAN (20, 20),
PARTITION P_t1_4 VALUES LESS THAN (25, 30)
);
例如，此时如果插入a、b分别为1,15, 就会被插入到P_t1_3分区表中，(1,10)会被插入到P_t1_2中。
HASH分区
HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布，在一个表中执行HASH分区时，MySQL会对分区键应用一个散列函数，以此来确定数据应该放在N个分区的哪一个分区中。
MySQL支持两种HASH分区，一种常规HASH分区，另一种是线性HASH分区(linear hash)；其中常规分区采用取模算法，线性分区使用线性的2的幂的运算法则来确定分区表。
使用 PARTITION BY [linear] HASH (expr) PARTITIONS p_num, 其中p_num为分区个数，linear为可选字，加上linear则视为线性分区，就不在采用取模算法来确定数据的分区位置，而是采用线性的2的幂的算法。
CREATE TABLE T2(
ID INT NOT NULL
)
PARTITION BY HASH (ID) PARTITIONS 4;
例如插入一条id=234的值到T2表中，他将会被存储到p2表中。这里采用的是
mysql> select partition_name, partition_expression, table_rows from information_schema.partitions where table_schema=schema() and table_name='t2';
+----------------+----------------------+------------+
| partition_name | partition_expression | table_rows |
+----------------+----------------------+------------+
| p0 | ID | 0 |
| p1 | ID | 0 |
| p2 | ID | 1 |
| p3 | ID | 0 |
+----------------+----------------------+------------+
4 rows in set (0.00 sec)
取模：
假设分区总数量为num, 分区编号为N，则N=MOD(expr, num)，其中expr代表分区键。
线性的2的幂的运算法则：
假设分区总数量为num=4, 分区编号是N, 插入的分区键是234
其次要用到几个数学函数：POWER(x, y)代表X的y次方, CEILING(x)代表x向上取整
第一步，获取下一个大于或等于num的2的幂m，再获得2^m，这个值设置为V：即V=Power(2, Ceiling(Log(2, num)))
2^x >= num -> x=2
2的幂的值就是 x^2 = 4
第二步，设置N=F(columns_list)&(V-1) , F(columns_list)是分区键的键值234。
N = 234&3=11101010&11 = 2
Key分区
key分区与hash分区类似，不同之处在于，既然是Key分区，那必然分区键与索引挂钩，且这个索引还必须是主键或唯一性索引
格式
CREATE TABLE xx(
# 字段
)
PARTITION BY [linear] KEY (expr) PARTITIONS P_NUM
p_num依然是分区个数，是一个非负整数。linear为可选项，改变数据分配算法
注意
1.如果在一个缺少主键和唯一键的表中建立key分区，会出现错误。
2.如果在建立分区时，指定分区键，会默认使用该表的主键(默认)或唯一键作为分区键。
3.不能在Key分区模式下的表中执行ALTER TABLE DROP PRIMARY KEY来删除主键
子分区
子分区就是对已分区的表中的某一分区进行再分区，即分区的分区，也称为复合分区
例子：
CREATE TABLE subt(
ID INT,
D DATE
)
PARTITION BY RANGE(YEAR(D))
SUBPARTITION BY LINEAR HASH(TO_DAYS(D)) SUBPARTITIONS 2
(
PARTITION P0 VALUES LESS THAN (1999),
PARTITION P1 VALUES LESS THAN (2009),
PARTITION P2 VALUES LESS THAN (2019)
);
mysql> select partition_name, partition_expression, table_rows from information_schema.partitions where table_schema=schema() and table_name='SUBT';
+----------------+----------------------+------------+
| partition_name | partition_expression | table_rows |
+----------------+----------------------+------------+
| P0 | YEAR(D) | 0 |
| P0 | YEAR(D) | 0 |
| P1 | YEAR(D) | 0 |
| P1 | YEAR(D) | 0 |
| P2 | YEAR(D) | 0 |
| P2 | YEAR(D) | 0 |
+----------------+----------------------+------------+
6 rows in set (0.01 sec)
P0、P1、P2又分别分成了两个子分区。
不同分区对NULL值的处理
1.RANGE分区将NULL定位为(零值)最小值；
2.LIST分区规定，NULL必须包含在分区列表下，否则插入出错；
3.HASH分区将NULL值存储在第一分区中；
分区管理
删除分区:
RANGE或LIST:
ALTER TABLE [表名] DROP PARTITION [分区名];
HASH或KEY：
alter table coalesce partition [p_num]
新增分区
ALTER TABLE [表名] ADD PARTITION (...)
对RANGE分区而言，只能添加新的分区到分区列表的最大一端
# range分区
ALTER TABLE [表名] ADD PARTITION ( PARTITION [分区名] VALUES LESS THAN (值) )
对LIST分区而言，不能添加一个包含现有分区键的分区值列表，比如原来分区中有这样一句PARTIION P1 VALUES IN (1, 2, 3)，那么新增分区的分区制列表就不能包含1,2,3中的任意一个或多个。
# list分区
alter table [表名] add partition (partition [分区名] values in (分区值列表));
对HASH或KEY分区的增加分区和RANGE或LIST不相同，
# 如果原来有2个分区，p_num为2，则现在的总分区表个数为4
alter table add partition partitions [p_num]
分区重定义(拆分或合并分区)
RANGE分区例子：
mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(a,b)
(PARTITION p_t1_1 VALUES LESS THAN (0,10) ENGINE = InnoDB,
PARTITION P_t1_2 VALUES LESS THAN (10,10) ENGINE = InnoDB,
PARTITION P_t1_3 VALUES LESS THAN (20,20) ENGINE = InnoDB,
PARTITION P_t1_4 VALUES LESS THAN (25,30) ENGINE = InnoDB) */
1 row in set (0.00 sec)
将(20,20) 拆分为（20,15）和 （20， 20）；
mysql> alter table t1 reorganize partition p_t1_3 into
-> (
-> partition p_t1_3_1 values less than (20, 15),
-> partition p_t1_3_2 values less than (20, 20)
-> );
Query OK, 0 rows affected (1.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
LIST和RANGE分区重定义方式相同。
注意：range分区和list分区重定义时，应该避免各自的分区键冲突情况的发生。