MySQL 分区
分区是指根据一定的规则，把一个表分解成多个更小更易管理的部分，逻辑上只有一个表或一个索引，
但是实际上该表可能由数个物理分区对象组成，每个分区都是一个独立的对象，每个分区可以独自处理，也可以作为表的一部分处理。分区对应用是完全透明的。
分区的优点体现在以下方面：
- 和单个磁盘或者文件系统相比，可以存储更多数据
- 优化查询，在Where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；像sum和count这类聚集函数的查询时，可以很容易地在每个分区上并行地处理，最终汇总所有分区的结果
- 对于已经过期的数据，可以通过删除与这些数据有关的分区来快速删除数据
- 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
MySQL中分区有如下几种类型：
- Range分区：指定区间的范围，把数据分配到不同的分区
- List分区：类似于Range分区，但是List是基于枚举出的值列表分区
- Hash分区：基于给定的分区个数，把数据分配到不同的分区
- Key分区：类似于Hash分区
在MySQL5.1版本中，Range、List、Hash分区都要求分区键必须是INT类型，或者通过表达式返回一个INT类型的数值，也就是说5.1只支持整数分区，唯一的例外就是分区类型为Key分区时，可以使用其它类型的列（BLOB和TEXT除外）作为分区键。
如果一个表有主键/非空唯一键，那么分区键必须使用该主键/非空唯一键；如果没有主键/非空唯一键，那只要该列类型被分区类型允许，就可以使用。
Range分区
Range分区利用取值范围将数据分区，区间连续且区间之间不得有重叠，使用VALUES LESS THAN操作符进行定义：
- create table emp (
- -> id int not null primary key,
- -> name varchar(100) ,
- -> hired date )
- -> partition by range (id) (
- -> partition p0 values less than (5),
- -> partition p1 values less than (10),
- -> partition p2 values less than MAXVALUE);
emp表中id在0-4之间的记录将保存在 p0分区，5-9的在p1分区，10 - int的最大值之间的在p2分区。
注意，按上面的语法paitition by range (id)中，括号内的字段必须为整数类型，但是如果是时间类型的怎么办？
1.可以用函数YEAR()和TO_DAYS()来转换，例如：
- create table emp (
- -> id int(11),
- -> name varchar(100),
- -> hired date)
- -> partition by range (YEAR(hired)) (
- -> partition p0 values less than (2017),
- -> partition p1 values less than (2018),
- -> partition p2 values less than MAXVALUE);
2.如果MySQL版本是5.5及之后的版本，那么可以用partition by range columns ：
- create table emp (
- -> id int,
- -> name varchar(100),
- -> hired date
- -> )
- -> partition by range columns (hired) (
- -> partition p0 values less than ('2017-01-01'),
- -> partition p1 values less than ('2018-01-01'),
- -> partition p2 values less than MAXVALUE
- -> )
- -> ;
当你要删除一个分区的数据时，只需
- ALTER TABLE table_name DROP PARTITION partition_name
即可删除该分区的数据，比delete高效
查询时，MySQL会根据条件判断需要扫描的分区，而不是全表扫描：
List分区
List分区时根据离散的值列表告诉数据库特定的值属于哪个分区，SQL定义语法：
- create table good (
- -> id int,
- -> name varchar(255),
- -> category int
- -> )
- -> partition by list (category) (
- -> partition p0 values in (1,3,5),
- -> partition p1 values in (2,4,6)
- -> );
按上述定义后，若插入category=7的记录，那么是会报错的，你必须把所有可能值都枚举完，不像range分区有MAXVALUE可以用。
MySQL5.5及其之后的版本中List分区也支持非整数列
Columns分区
Columns分区是MySQL5.5引入的新的分区类型，解决了Range分区和List分区只支持整数列的问题，Columns分区支持的分区列类型为：
- 整数：tinyint、smallint、mediumint、int和bigint
- 日期时间：date和datetime
- 字符串类型：char、varchar、binary和varbinary
Columns分区仅支持一个或多个字段名作为分区键，不再支持表达式作为分区键。
Columns分区支持多列分区：
- create table aa (
- -> a int,
- -> b int
- -> )
- -> partition by range columns(a,b) (
- -> partition p0 values less than (0,10),
- -> partition p1 values less than (10,10),
- -> partition p2 values less than (10,20),
- -> partition p3 values less than (10,35),
- -> partition p4 values less than (10,MAXVALUE),
- -> partition p5 values less than (MAXVALUE,MAXVALUE)
- -> );
分区键包含多列时，其比较是基于元组的比较，也就是基于字段组的比较，假设有两个元组(A1, B1)和(A2, B2)，那么其比较规则为：
- (A1 < A2) OR ((A1 == A2) && (B1 < B2))
例如：
Hash分区
Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行Hash分区时，MySQL会对分区键应用一个散列函数，依=以此确定数据应当放在分区中的哪个分区中。
MySQL支持两种Hash分区：
- 常规Hash分区：使用取模算法
- 线性Hash分区(Linear Hash)：使用一个线性的2 的幂的运算规则
常规Hash分区语法：
- create table emp (
- -> id int,
- -> name varchar(100),
- -> hired date
- -> ) engine = innodb
- -> partition by hash(id) partitions 3;
id为0的将在p0分区，id为1的将在p1分区....以此类推：
常规Hash不适合需要灵活变动分区的需求，因为分区数增加或减少后都得重新计算分区，否则数据就错位了。
为此我们可以用线性Hash分区：
- create table emp (
- -> id int,
- -> name varchar(100),
- -> hired date
- -> ) engine = innodb
- -> partition by <strong>linear</strong> hash(id) partitions 3;
看一下数据分布：
可以看到，数据往哪个分区的计算方法稍有区别：假如有N个分区，要插入的数据的id为Q，那么找到下一个大于等于N的2的幂，记为M，那么分区下标X = Q & (M - 1)。其实说简单点，原来常规Hash是直接对分区数取模，但是线性Hash就不是这样，而是找出最小的大于等于分区数的2的幂，对该数进行取模，比如分区数为6，那么模数为8，要插入的记录的d为10的话，那么分区下标就为 10 % 8 = 2。这样的好处就是，分区数增加或减少不一定要重新计算分区，因为改变的分区数可能并没有使模数发生变化，因此一条记录原来往哪个分区存，还是往哪个分区存，但是如果分区数变化使得模数变化（比如分区数从7变成9，模数从8变为16），那么是依然需要重新计算分区的。
线性hash分区的优点是，在分区维护（增加、删除、合并和拆分分区）时，MySQL能够处理得更加迅速；缺点是，对比常规Hash分区，线性Hash分区各个分区数据分布并不是很均匀。
Key分区
Key分区类似于Hash分区，只不过Hash分区允许使用用户自定义得表达式，而Key分区不允许，需要使用MySQL服务器提供的Hash函数；同时Hash分区只支持整数分区，而Key分区支持除了Blob和Text类型外的所有类型作为分区键。在有主键或者非空唯一键的情况下，创建Key分区时可以不指定分区键，MySQL会默认使用主键作为分区键，若没有主键则使用非空唯一键作为分区键。
- create table emp (
- -> id int,
- -> name varchar(100),
- -> hired date
- -> ) engine = innodb
- -> partition by key (id) partitions 4;
对于所有分区，MySQL都允许使用Null值，再Range分区中，Null值被当作最小值来处理；再List分区中，Null值必须被枚举出来，否则就不允许使用插入Null值；再Hash/Key分区中，Null值被当作零值来处理。
分区管理命令
MySQL提供了添加、删除、重定义、合并和拆分分区的命令，这些操作都可以用ALTER TABLE命令来实现
Range分区
- create table emp (
- -> id int,
- -> name varchar(100),
- -> hired date
- -> )
- -> partition by range columns (hired) (
- -> partition p0 values less than ('2017-01-01'),
- -> partition p1 values less than ('2018-01-01'),
- -> partition p2 values less than ('2019-01-01')
- -> );
添加分区：
- mysql> alter table emp add partition (partition p3 values less than ('2020-01-01'));
添加分区时不能从中间添加，只能从最大的那个分区后面添加：
- mysql> alter table emp add partition (partition p4 values less than ('2019-07-01'));
- ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
拆分p0为p00和p01：
- mysql> alter table emp reorganize partition p0 into (
- -> partition p00 values less than ('2016-07-01'),
- -> partition p01 values less than ('2017-01-01')
- -> );
合并p00和p01为p0：
- mysql> alter table emp reorganize partition p00,p01 into (
- -> partition p0 values less than ('2017-01-01')
- -> );
删除p2分区：
- mysql> alter table emp drop partition p2;
List分区
- create table good (
- id int,
- name varchar(255),
- category int
- )
- partition by list (category) (
- partition p0 values in (1,2),
- partition p1 values in (3,4)
- );
添加分区：
- mysql> alter table good add partition (partition p2 values in (5,6));
拆分分区p0为p00和p01：
- mysql> alter table good reorganize partition p0 into (
- -> partition p00 values in (1),
- -> partition p01 values in (2)
- -> );
合并分区：
- mysql> alter table good reorganize partition p00,p01 into (
- -> partition p0 values in (1,2)
- -> );
删除分区：
- mysql> alter table good drop partition p2;
Hash分区
- mysql> create table emp (
- -> id int,
- -> name varchar(100),
- -> hired date
- -> ) engine = innodb
- -> partition by hash (id) partitions 3;
减少分区的数量：
- mysql> alter table emp coalesce partition 2;
增加分区的数量
- mysql> alter table emp add partition partitions 4; # 该数字为增加的数量而不是增加后的数量
Key分区
key分区和Hash一致.
无论是什么分区，都可以通过information_schema.partitions表来查看其详细信息：
- mysql> desc partitions;
- +-------------------------------+---------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------------------------------+---------------------+------+-----+---------+-------+
- | TABLE_CATALOG | varchar(512) | NO | | | |
- | TABLE_SCHEMA | varchar(64) | NO | | | |
- | TABLE_NAME | varchar(64) | NO | | | |
- | PARTITION_NAME | varchar(64) | YES | | NULL | |
- | SUBPARTITION_NAME | varchar(64) | YES | | NULL | |
- | PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | |
- | SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | |
- | PARTITION_METHOD | varchar(18) | YES | | NULL | |
- | SUBPARTITION_METHOD | varchar(12) | YES | | NULL | |
- | PARTITION_EXPRESSION | longtext | YES | | NULL | |
- | SUBPARTITION_EXPRESSION | longtext | YES | | NULL | |
- | PARTITION_DESCRIPTION | longtext | YES | | NULL | |
- | TABLE_ROWS | bigint(21) unsigned | NO | | 0 | |
- | AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 | |
- | DATA_LENGTH | bigint(21) unsigned | NO | | 0 | |
- | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
- | INDEX_LENGTH | bigint(21) unsigned | NO | | 0 | |
- | DATA_FREE | bigint(21) unsigned | NO | | 0 | |
- | CREATE_TIME | datetime | YES | | NULL | |
- | UPDATE_TIME | datetime | YES | | NULL | |
- | CHECK_TIME | datetime | YES | | NULL | |
- | CHECKSUM | bigint(21) unsigned | YES | | NULL | |
- | PARTITION_COMMENT | varchar(80) | NO | | | |
- | NODEGROUP | varchar(12) | NO | | | |
- | TABLESPACE_NAME | varchar(64) | YES | | NULL | |
- +-------------------------------+---------------------+------+-----+---------+-------+
- 25 rows in set (0.03 sec)
取消分区
1065
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
