MySQL的分区表

2321212dd

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数十个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。
1、查看使用的MySQL是否支持分区表。

    5.6.1版本以后使用show plugins;命令查看

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name                       | Status   | Type               | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     | | mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     | | sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     | ...... | INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     | | INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     | | ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | | partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     | +----------------------------+----------+--------------------+---------+---------+

    5.6.1以前的版本使用show variables like '%partition%';命令查看

1 2 3 4 5 6

mysql> show variables like '%partition%'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | have_partitioning | YES   | +-------------------+-------+

2、分区类型

  MySQL中可用的分区类型主要有以下4种：

• RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。
  
• LIST分区：类似RANGE分区，区别在于LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区。
  
• HASH分区：基于给定的分区个数，把数据分配到不同的分区。
  
• KEY分区：类似于HASH分区。
  
  

RANGE分区、LIST分区、HASH分区都要求分区必须是INT类型，或者通过表达式返回INT类型，唯一的例外就是分区类型为KEY分区时，可以使用其他类型的列(BLOB或TEXT列类型除外)作为分区键。在5.5或以上版本中，已经支持非整数的RANGE和LIST分区了，使用RANGE COLUMNS和LIST COLUMNS分区实现(后面介绍)。无论是哪种MySQL分区类型,要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须都包含分区键。
  2.1、RANGE分区
按照RANGE分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用VALUES LESS THAN 操作符进行分区定义。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> create table emp (     -> id int not null,     -> ename varchar(30),     -> hired date not null default '2016-01-01',     -> separated date not null default '9999-12-31',     -> job varchar(30) not null,     -> store_id int not null)     -> partition by range (store_id)     -> (     -> partition p0 values less than (10),     -> partition p1 values less than (20),     -> partition p2 values less than (30)     -> ); Query OK, 0 rows affected (0.08 sec) mysql> show create table emp \G; *************************** 1. row ***************************        Table: emp Create Table: CREATE TABLE `emp` (   `id` int(11) NOT NULL,   `ename` varchar(30) DEFAULT NULL,   `hired` date NOT NULL DEFAULT '2016-01-01',   `separated` date NOT NULL DEFAULT '9999-12-31',   `job` varchar(30) NOT NULL,   `store_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB) */ 1 row in set (0.00 sec)

emp表可以存储store_id<30的数据，并且根据store_id值选择不同的分区，如store_id=9则存储在p0分区，如果store_id=10则存储在p1分区。如果插入store_id大于30的行会报错：

1 2	mysql> insert into emp (id,ename,hired,job,store_id) values('1','zx','2016-01-01','zx',50); ERROR 1526 (HY000): Table has no partition for value 50

可以在设置分区的时候使用使用VALUES LESS THAN MAXVALUE 子名，该子名提供给所有大于明确指定的最高值的值，MAXVALUES表示最大的可能的整数值。

1 2 3 4 5 6

mysql> alter table emp add partition (partition p3 values less than maxvalue); Query OK, 0 rows affected (0.14 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> insert into emp (id,ename,hired,job,store_id) values('1','zx','2016-01-01','zx',50); Query OK, 1 row affected (0.02 sec)

MySQL支持在VALUES LESS THAN 子句中使用表达式，比如，以日期作为RANGE分区的分区列：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> create table emp_date (     -> id int not null,     -> ename varchar(30),     -> hired date not null default '2016-01-01',     -> separated date not null default '9999-12-31',     -> job varchar(30) not null,     -> store_id int not null)     -> partition by range (YEAR(separated))     -> (     -> partition p0 values less than (2014),     -> partition p1 values less than (2015),     -> partition p2 values less than (2016)     -> ); Query OK, 0 rows affected (0.48 sec)

查看语句的执行计划

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> explain partitions select * from emp where store_id=50\G; *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: emp    partitions: p3          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 2         Extra: Using where 1 row in set (0.00 sec) ERROR: No query specified

  2.2 LIST分区

  LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST分区在很多方面类似于RANGE分区，区别在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合。
  LIST分区通过使用PARTITION BY LIST(expr)子名来实现，expr是某列值或一个基于某列值返回一个整数值的表达式，然后通过VALUES IN(value_list)的方式来定义分区，其中value_list是一个逗号分隔的整数列表。与RANGE分区不同，LIST分区不必声明任何特定的顺序。

1 2 3 4 5 6 7 8 9 10

mysql> create table expenses(     -> expense_date date not null,     -> category int,     -> amount decimal(10,3))     -> partition by list (category)     -> (     -> partition p0 values in (3,5),     -> partition p1 values in (1,10)     -> ); Query OK, 0 rows affected (0.73 sec)

如果试图插入的列值(或者分区表达式的反回值)不包含分区值列表中时，那么INSERT操作会失败并报错。

1 2	mysql> insert into expenses(expense_date,category) values('2016-11-25',2); ERROR 1526 (HY000): Table has no partition for value 2

  2.3 Columns分区
  Columns分区是在MySQL5.5引入的分区类型，引入Columns分区解决了MySQL5.5版本之前RANGE分区和LIST分区只支持整数分区，从而导致需要额外的函数计算等到整数或通过额外的转换表来转换为整数再分区的问题。Columns分区可以细分为RANGE Columns和LIST Columns分区，它们都支持整数、日期时间、字符串三大数据类型。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> drop table emp_date; Query OK, 0 rows affected (0.06 sec) mysql> create table emp_date (     -> id int not null,     -> ename varchar(30),     -> hired date not null default '2016-01-01',     -> separated date not null default '9999-12-31',     -> job varchar(30) not null,     -> store_id int not null)     -> partition by range columns (separated)     -> (     -> partition p0 values less than ('2014-01-01'),     -> partition p1 values less than ('2015-01-01'),     -> partition p2 values less than ('2016-01-01')     -> ); Query OK, 0 rows affected (0.10 sec) mysql> drop table expenses; Query OK, 0 rows affected (0.08 sec) mysql> create table expenses(     -> expense_date date not null,     -> category varchar(30),     -> amount decimal(10,3))     -> partition by list columns (category)     -> (     -> partition p0 values in ('food','lodging'),     -> partition p1 values in ('flights')     -> ); Query OK, 0 rows affected (0.13 sec)

对比RANGE分区和LIST分区，Columns分区的亮点除了支持数据类型增加之外，另外一大亮点是Columns分区还支持多列分区。

1 2 3 4 5 6 7 8 9 10 11 12

mysql> create table rc3(     -> a int,     -> b int)     -> partition by range columns (a,b)     -> (     -> partition p01 values less than (1,10),     -> partition p02 values less than (10,10),     -> partition p03 values less than (10,20),     -> partition p04 values less than (10,maxvalue),     -> partition p05 values less than (maxvalue,maxvalue)     -> ); Query OK, 0 rows affected (0.23 sec)

RANGE Columns 分区键的比较是基于元组的比较，也就是基于字段组的比较，这和之前RANGE分区键的比较有些差异，比较逻辑为(a<10) or ((a=10)and(b<10))。下面插入几条测试数据验证一下。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

--插入(0,11) mysql> insert into rc3(a,b) values(0,11); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows     -> from information_schema.partitions     -> where table_schema=schema() and table_name='rc3'; +------+---------+-------------------+------------+ | part | expr    | descr             | table_rows | +------+---------+-------------------+------------+ | p01  | `a`,`b` | 1,10              |          1 | | p02  | `a`,`b` | 10,10             |          0 | | p03  | `a`,`b` | 10,20             |          0 | | p04  | `a`,`b` | 10,MAXVALUE       |          0 | | p05  | `a`,`b` | MAXVALUE,MAXVALUE |          0 | +------+---------+-------------------+------------+ 5 rows in set (0.01 sec) --插入(1,10) mysql> insert into rc3(a,b) values(1,10); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows     -> from information_schema.partitions     -> where table_schema=schema() and table_name='rc3'; +------+---------+-------------------+------------+ | part | expr    | descr             | table_rows | +------+---------+-------------------+------------+ | p01  | `a`,`b` | 1,10              |          1 | | p02  | `a`,`b` | 10,10             |          1 | | p03  | `a`,`b` | 10,20             |          0 | | p04  | `a`,`b` | 10,MAXVALUE       |          0 | | p05  | `a`,`b` | MAXVALUE,MAXVALUE |          0 | +------+---------+-------------------+------------+ --插入(12,15) mysql> insert into rc3(a,b) values(12,15); Query OK, 1 row affected (0.08 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows     -> from information_schema.partitions     -> where table_schema=schema() and table_name='rc3'; +------+---------+-------------------+------------+ | part | expr    | descr             | table_rows | +------+---------+-------------------+------------+ | p01  | `a`,`b` | 1,10              |          1 | | p02  | `a`,`b` | 10,10             |          1 | | p03  | `a`,`b` | 10,20             |          0 | | p04  | `a`,`b` | 10,MAXVALUE       |          0 | | p05  | `a`,`b` | MAXVALUE,MAXVALUE |          1 | +------+---------+-------------------+------------+ 5 rows in set (0.01 sec)

  2.4 HASH分区
  HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行HASH分区时，Mysql会对分区键应用一个英散列函数，以此确定数据应当放在N个分区中的哪个。MySQL支持两种HASH分区：常规HASH分区和线性HASH分区(LINEAR HASH分区)。常规HAS使用的是取模算法，线性HASH分区使用的是一个线性的2的幂的运算法则。
  常规HASH分区使用PARTITION BY HASH(expr) PARTITIONS num子句来实现。exp是某列值或一个基于某列值返回一个整数值的表达式，num是一个非负整数，表示分割成分区的数量，默认num=1。数据保存在哪个分区N=MOD(expr,num)。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

mysql> create table emp (     -> id int not null,     -> ename varchar(30),     -> hired date not null default '2016-01-01',     -> separated date not null default '9999-12-31',     -> job varchar(30) not null,     -> store_id int not null)     -> partition by hash (store_id) partitions 4; Query OK, 0 rows affected (0.11 sec) mysql> insert into emp (id,ename,hired,job,store_id) values('1','zx','2016-01-01','zx',50); Query OK, 1 row affected (0.04 sec) mysql> select mod(50,4); +-----------+ | mod(50,4) | +-----------+ |         2 | +-----------+ 1 row in set (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp'; +------+----------+-------+------------+ | part | expr     | descr | table_rows | +------+----------+-------+------------+ | p0   | store_id | NULL  |          0 | | p1   | store_id | NULL  |          0 | | p2   | store_id | NULL  |          1 | | p3   | store_id | NULL  |          0 | +------+----------+-------+------------+

  常规HASH分区存在的问题，如果新增一个常规HASH分区，原来的取模算法是MOD(exp,4)就需要修改为MOD(exp,5)，原来4个分区中的数据大部分都需要通过重新计算新分区，代价太大。为了降低分区处理上的代价，MySQL提供了线性HASH分区，分区函数是一个线性的2的幂的运算法则。
  线性HASH分区和常规HASH分区在语法上的唯一区别是在"PARTITION BY"子句中添加"LINEAR"关键字

1 2 3 4 5 6 7 8 9

mysql> create table emp (     -> id int not null,     -> ename varchar(30),     -> hired date not null default '2016-01-01',     -> separated date not null default '9999-12-31',     -> job varchar(30) not null,     -> store_id int not null)     -> partition by linear hash (store_id) partitions 4; Query OK, 0 rows affected (0.89 sec)

  2.5 KEY分区
  按照Key进行分区非常类似于按照HASH分区，只不过HASH分区允许使用用户自定义的表达式，而Key分区不允许，需要使用Mysql服务器提供的HASH函数。Key分区支持使用除BLOB或TEXT类型外的其他类型列作为分区键。使用PARTITION BY KEY(expr)子句来创建一个Key分区表，exp是0个或多个字段名我的列表。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

mysql> create table emp (     -> id int not null,     -> ename varchar(30),     -> hired date not null default '2016-01-01',     -> separated date not null default '9999-12-31',     -> job varchar(30) not null,     -> store_id int not null)     -> partition by key (job) partitions 4; Query OK, 0 rows affected (0.17 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp'; +------+-------+-------+------------+ | part | expr  | descr | table_rows | +------+-------+-------+------------+ | p0   | `job` | NULL  |          0 | | p1   | `job` | NULL  |          0 | | p2   | `job` | NULL  |          0 | | p3   | `job` | NULL  |          0 | +------+-------+-------+------------+ 4 rows in set (0.01 sec) mysql> insert into emp (id,ename,hired,job,store_id) values('1','zx','2016-01-01','zx',50); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp';                            +------+-------+-------+------------+ | part | expr  | descr | table_rows | +------+-------+-------+------------+ | p0   | `job` | NULL  |          0 | | p1   | `job` | NULL  |          0 | | p2   | `job` | NULL  |          0 | | p3   | `job` | NULL  |          1 | +------+-------+-------+------------+ 4 rows in set (0.00 sec) mysql> insert into emp (id,ename,hired,job,store_id) values('1','zx','2016-01-01','lx',50); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='emp'; +------+-------+-------+------------+ | part | expr  | descr | table_rows | +------+-------+-------+------------+ | p0   | `job` | NULL  |          0 | | p1   | `job` | NULL  |          1 | | p2   | `job` | NULL  |          0 | | p3   | `job` | NULL  |          1 | +------+-------+-------+------------+ 4 rows in set (0.00 sec)

可以不指定分区键，默认会首先选择使用主键作为分区键，没有主键的情况会选择非空唯一键作为分区键，没有主键也没有唯一键的情况就不能不指定分区键了。Key分区也可以像HASH分区一样使用LINEAR KEY分区。
  2.6子分区

  子分区(subpartitioning)是分区表中对每个分区的再次分割对被称为复合分区(composite partitioning)。从5.1版本开始支持通过RANGE或LIST分区了的表再进行子分区，子分区可以使用HASH分区或KEY分区。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> CREATE TABLE ts (id INT, purchased DATE)     -> PARTITION BY RANGE( YEAR(purchased) )     -> SUBPARTITION BY HASH( TO_DAYS(purchased) )     -> SUBPARTITIONS 2 (     -> PARTITION p0 VALUES LESS THAN (1990),     -> PARTITION p1 VALUES LESS THAN (2000),     -> PARTITION p2 VALUES LESS THAN MAXVALUE     -> ); Query OK, 0 rows affected (0.22 sec) mysql> select partition_name part,subpartition_name subpart,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='ts'; +------+---------+------------------+----------+------------+ | part | subpart | expr             | descr    | table_rows | +------+---------+------------------+----------+------------+ | p0   | p0sp0   |  YEAR(purchased) | 1990     |          0 | | p0   | p0sp1   |  YEAR(purchased) | 1990     |          0 | | p1   | p1sp0   |  YEAR(purchased) | 2000     |          0 | | p1   | p1sp1   |  YEAR(purchased) | 2000     |          0 | | p2   | p2sp0   |  YEAR(purchased) | MAXVALUE |          0 | | p2   | p2sp1   |  YEAR(purchased) | MAXVALUE |          0 | +------+---------+------------------+----------+------------+ 6 rows in set (0.00 sec)

  2.7 MySQL分区处理NULL值的方式
  MySQL不禁止在分区键值上使用NULL，分区键可能是一个字段或都一个用户定义的表达式。一般情况下，MySQL的分区把NULL当作0值或一个最小值进行处理。RANGE分区中，NULL值 会被当作最小值来处理；LIST分区中，NULL值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，NULL值被当作0值来处理。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> create table tb_range(id int ,name varchar(2))     -> partition by range(id)     -> (     -> partition p0 values less than (0),     -> partition p1 values less than (100),     -> partition p2 values less than maxvalue     -> ); Query OK, 0 rows affected (0.11 sec) mysql> insert into tb_range values(null,'a'); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_range'; +------+------+----------+------------+ | part | expr | descr    | table_rows | +------+------+----------+------------+ | p0   | id   | 0        |          1 | | p1   | id   | 100      |          0 | | p2   | id   | MAXVALUE |          0 | +------+------+----------+------------+ 3 rows in set (0.00 sec)

LIST分区处理NULL值示例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

mysql> create table tb_list(id int ,name varchar(2))     -> partition by list(id)     -> (     -> partition p0 values in (0),     -> partition p1 values in (1)     -> ); Query OK, 0 rows affected (0.09 sec) mysql> insert into tb_list values(null,'a'); ERROR 1526 (HY000): Table has no partition for value NULL mysql> alter table tb_list add partition(partition p2 values in (null)); Query OK, 0 rows affected (0.09 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> insert into tb_list values(null,'a'); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_list'; +------+------+-------+------------+ | part | expr | descr | table_rows | +------+------+-------+------------+ | p0   | id   | 0     |          0 | | p1   | id   | 1     |          0 | | p2   | id   | NULL  |          1 | +------+------+-------+------------+ 3 rows in set (0.00 sec)

HASH分区处理NULL值示例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> create table tb_hash(id int ,name varchar(2))     -> partition by hash(id) partitions 2; Query OK, 0 rows affected (0.12 sec) mysql> insert into tb_hash values(null,'a'); Query OK, 1 row affected (0.01 sec) mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_hash'; +------+------+-------+------------+ | part | expr | descr | table_rows | +------+------+-------+------------+ | p0   | id   | NULL  |          1 | | p1   | id   | NULL  |          0 | +------+------+-------+------------+ 2 rows in set (0.00 sec)