MySQL学习笔记十三：表分区

reference：http://www.cnblogs.com/zmxmumu/p/4450857.html

1.分区一般用于非常大的表，采用“分而治之”的策略，将一个很大的对象分成多个小对象进行管理，每个分区都是一个独立的对象。

分区使用分区键将数据根据范围值，特定列值或HASH值等规则分布在不同的分区中。查看当前MySQL是否支持分区，如下所示。

mysql> show variables like '%partition%'; --或者使用select @@have_partitioning; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)

2.分区类型，主要分为以下四种：

RANGE：基于一个连续的区间范围，将数据分配到不同的分区。

LIST：基于枚举出的值列表分区

HASH：基于给定的分区数，将数据分配到不同的分区

KEY：类似于HASH分区，但不允许用户自定义表达式

3.设置表的分区，类似指定表的存储引擎，分区不支持CSV和merge引擎对象，如下所示

create table student( sno int not null, sname varchar(30) not null, sclass varchar(10) not null, sage int not null, sgender varchar(6) )engine=InnoDB default charset=utf8 partition by list columns(sgender)( partition p0 values in ('男'), partition p1 values in ('女') );

不管哪种分区类型，分区键必须是主键或唯一键，除非两者都没有，否者将会报如下错误。

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

4.RANGE分区

RANGE分区利用取值范围来进行分区，区间必须连续且不重叠，使用values less than进行分区定义，分区键必须是INT，或者表达式返回INT。

create table users_par( id int not null, usrName varchar(50) not null, usrEmail varchar(50) not null, age int not null, regDate date not null )engine=InnoDB default charset=utf8 partition by range(age)( partition p0 values less than(20), partition p1 values less than(30), partition p2 values less than maxvalue --防止插入大于30岁的用户，出现错误 );

如果是将注册日期作为分区键，则须要使用日期处理函数转换为整型，例如year(regDate),to_days(regDate),to_seconds(regDate),且只支持这三个函数。

或者使用RANGE COLUMNS分区，则不需要转换日期，如下所示

create table users_par( id int not null, usrName varchar(50) not null, usrEmail varchar(50) not null, age int not null, regDate date not null )engine=InnoDB default charset=utf8 partition by range columns(regDate)( partition p0 values less than('2005-05-05'), partition p1 values less than('2009-09-09'), partition p2 values less than('2015-05-05'), );

RANGE分区特别适用于删除过期数据或者某范围数据，只需要alter table tbl_name truncate partition partition_name即可，比delete语句效率要高很多，还有就是经常使用分区键的查询，可以提高查询性能，因为只需扫描某些分区就OK，如下所示。

mysql> explain partitions select * from users_par where age=30; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | users_par | p2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+

5.LIST分区

LIST分区是建立离散的值列表来实现特定的值属于哪个分区，使用partition by list来实现，values in 来定义。

CREATE TABLE `student` ( `sno` int(11) NOT NULL, `sname` varchar(30) NOT NULL, `sclass` varchar(10) NOT NULL, `sage` int(11) NOT NULL, `sgender` varchar(6) DEFAULT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY LIST COLUMNS(sgender)( PARTITION p0 VALUES IN ('男'), PARTITION p1 VALUES IN ('女') );

如果试图插入的值不在分区值列表中，插入语句将会报错，将要匹配的值必须在分区值列表中找到。

6.COLUMNS分区

columns分区是对range，list分区的补充，弥补了后两者只支持整型数分区（或者通过转换为整型数），使得支持数据类型增加很多（所有整数类型，日期时间类型，字符类型），还支持多列分区。

columns分区可细分为range columns分区和list columns分区，多列分区示例：

create table range_columns( a int not null, b int not null ) partition by range columns(a,b)( partition p0 values less than(0,10), partition p1 values less than(10,20), partition p2 values less than(10,30), partition p3 values less than(maxvalue,maxvalue) );

在多列分区表上插入数据时，采用元组的比较，即多列排序，先根据field1排序，再根据field2排序，根据排序结果来来分区存储数据。

mysql> insert into range_columns values (0,9); Query OK, 1 row affected (0.00 sec) mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name='range_columns'; +----------------+----------------------+------------+ | PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS | +----------------+----------------------+------------+ | p0 | `a`,`b` | 1 | | p1 | `a`,`b` | 0 | | p2 | `a`,`b` | 1 | | p3 | `a`,`b` | 0 | +----------------+----------------------+------------+ 4 rows in set (0.01 sec)

7.HASH分区

HASH主要是为了让数据在设定个数的分区中尽可能分布平均，执行哈希分区时，mysql会对分区键执行哈希函数，以确定数据放在哪个分区中。HASH分区分为常规HASH分区和线性HASH分区，前者使用取模算法，后者使用线性2的幂的运算规则。HASH分区示例如下，

create table hash_par( id int not null, name varchar(50) not null ) partition by hash(id) partitions 4; ----如果要指定为线性hash，可以使用partition by linear hash

插入一个id为31的数据，如下所示

mysql> insert into hash_par values (31,'zhumuxian'); Query OK, 1 row affected (0.00 sec) mysql> explain partitions select * from hash_par where id=31; +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | hash_par | p3 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)

HASH分区尽可能让数据平均地分布在每个分区上，提高了查询效率，但是增加了分区管理的代价，比如以前有5个分区，现在要加上一个分区，算法有mod(expr,5)变成(expr,6)，原来5个分区的数据大部分要重新结算重新分区。虽然使用线性HASH分区会降低分区管理的代价，但是数据却没有常规HASH分布得那么均匀。

8.KEY分区

KEY分区类似与HASH分区，但是不能自定义表达式，不过支持分区键的类型很多，除却Text,Blob等文本类型。KEY分区的设置如下：

create table hash_par( id int not null, name varchar(50) not null ) partition by key(id) partitions 4;

KEY分区的分区键可以不指定，默认会使用表的主键作为分区键，如果没有主键，就使用唯一键，两者都没有的话就必须指定分区键了。

9.子分区（关键字subparttition）

RANGE分区或者LIST分区可以再次分割形成子分区，子分区可以是HASH分区或者KEY分区，例如：

create table users_par( id int not null, usrName varchar(50) not null, usrEmail varchar(50) not null, age int not null, birth date not null )engine=InnoDB default charset=utf8 partition by range(age) subpartition by hash(age) subpartitions 2( partition p0 values less than(20), partition p1 values less than(30), partition p2 values less than maxvalue );

10.分区时NULL值的处理

一般情况下，MYSQL把NULL当成零值后者最小值来处理。

在RANGE分区中，NULL会被当成最小值处理；LIST分区中，NULL值必须出现在枚举值列表中，否则报错；HASH/KEY分区中，NULL值被当成零值处理。

mysql> insert into student values (4010404,'zhumuxian','A1114',20,'男'); Query OK, 1 row affected (0.04 sec) mysql> insert into student values (4010405,'zhongzhaoxi','A1114',20,NULL); ERROR 1526 (HY000): Table has no partition for value from column_list mysql> select PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name='student'; +----------------+----------------------+-----------------------+------------+ | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | +----------------+----------------------+-----------------------+------------+ | p0 | `sgender` | '男' | 1 | | p1 | `sgender` | '女' | 0 | +----------------+----------------------+-----------------------+------------+ 2 rows in set (0.01 sec)

11.分区管理

RANGE和LIST分区在删除，添加，重新定义等分区管理上非常类似，如下所示。

删除分区(alter table tbl_name drop partition partition_name)，分区被删除后，该分区的数据一起被删除。

mysql> alter table users_par drop partition p0; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0

添加分区(alter table tbl_name add partition)

mysql> alter table users_par add partition (partition p0 values less than (20)); ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition --这里报错是因为添加分区必须在原分区的最大端添加，在为LIST分区添加分区时，新分区的值列表的值不能包含任意一个现有分区中值列表中的值，否则报错 mysql> alter table student add partition (partition p2 values in ('男')); ERROR 1495 (HY000): Multiple definition of same constant in list partitioning

重新定义分区(alter table tbl_name reorganize partition partition_name into)，可以将一个分区拆开成多个，反之可以合并多个成一个或多个。

mysql> alter table users_par reorganize partition p1 into (partition p0 values less than (20),partition p1 values less t han(30)); Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0

需要注意的是：RANGE和LIST分区在重新定义时，只能重新定义相邻的分区，不可以跳过分区，并且重新定义的分区区间必须和原分区区间一致，也不可以改变分区的类型。

HASH和KEY分区的管理

减少分区数量，使用coaleace关键字

mysql> alter table hash_par coalesce partition 2; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0

增加分区数量

mysql> alter table hash_par add partition partitions 2; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0

MySQL分区有利于查询优化，快速删除过期数据，提高查询吞吐量等。