本文是对MySQL的分区基本知识的总结，包括一些MySQL常见分区管理命令的示例

MySQL的分区

分区的使用场景

当一个表的数据量非常大不能放到一个磁盘时，通过数据库分区为一个表创建多个分区，分区可以在不同的磁盘中，表中的数据分布在不同的分区中，分区的优点包括：

一个硬盘放不下的一个表中的数据可以分别放到不同的硬盘分区中
对不同分区的数据进行聚合，插入等操作可以并行执行
通过直接删除分区来快速删除数据

为一个表创建分区的示例

-- engine子句需要在 分区声明之前
mysql> create table emp (empid int, salary decimal(7, 2), birth_date date) engine=innodb partition by hash(month(birth_date)) partitions 6;
Query OK, 0 rows affected (0.03 sec)

表分区需要一个分区键，插入的数据行就是按照其分区键的值来映射到某个分区，分区表的要么不存在主键/唯一键，否则主键/唯一键要包含分区键

分区的4种类型

不同的映射规则表示不同的分区类型，有4种分区类型：

Range分区：分区键的值连续的，按照不同的区间映射到不同的分区
List分区：与Range分区类似，但分区键的值是枚举的值列表里的值
Hash分区：分区键经过哈希函数来确定数据所属的分区，映射规则使用哈希
Key分区：类似Hash分区，但不能对分区键使用函数转换

注：分区键的值可以是Null，但Range分区会把Null当做最小值，List分区的值列表里需要有Null值，Hash分区和Key分区则会当成0

创建Range分区表的示例

mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-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.02 sec)

-- 插入超出分区键范围的值不成功
mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'miller', '1982-01-23', 'cleak', 50);
ERROR 1526 (HY000): Table has no partition for value 50

-- 可以使用MAXVALUE可表示分区值的最大值
mysql> alter table emp add partition (partition p3 values less than MAXVALUE);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'miller', '1982-01-23', 'cleak', 50);
Query OK, 1 row affected (0.00 sec)

-- 可以在分区中使用函数
mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-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 (1995),
    -> partition p1 values less than (2000),
    -> partition p2 values less than (2005)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 直接删除分区能快速删除数据
mysql> alter table emp drop partition p0;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询时使用分区键作为条件时只有某些分区会被扫描
mysql> explain select * from emp where separated='1996-07-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建List分区表的示例

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),
    -> partition p2 values in (4, 9),
    -> partition p3 values in (2),
    -> partition p4 values in (6)
    -> );
Query OK, 0 rows affected (0.02 sec)

Range分区和List分区可以使用columns来创建非整数类型的分区，并且columns分区支持多列分区键

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

-- 分区键的比较是基于元组的比较
mysql> insert into rc3 (a, b) values(1, 10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into rc3 (a, b) values(10, 9);
Query OK, 1 row affected (0.00 sec)

mysql> insert into rc3 (a, b) values(10, 10);
Query OK, 1 row affected (0.00 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` | 0,10              |          0 |
| p02  | `a`,`b` | 10,10             |          2 |
| p03  | `a`,`b` | 10,20             |          1 |
| p04  | `a`,`b` | 10,35             |          0 |
| p05  | `a`,`b` | 10,MAXVALUE       |          0 |
| p06  | `a`,`b` | MAXVALUE,MAXVALUE |          0 |
+------+---------+-------------------+------------+
6 rows in set (0.00 sec)

Hash分区是通过对分区键运用哈希规则得出分区，有两种哈希规则，第一种是取模，第二种线性2的幂；使用取模能简单而平均的分散数据，但当需要改变分区的数目则需要重新计算所有分区，而线性2的幂更好的支持改变分区的数目，但数据分布不均匀

-- 对store_id模4确定分区号
mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-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.02 sec)

-- 线性模2分区
mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-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.02 sec)

Key分区与Hash类型也是有常规和线性模2两种

-- 对store_id模4确定分区号
mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by key(store_id) partitions 4;
Query OK, 0 rows affected (0.02 sec)

-- 线性模2分区
mysql> create table emp(
    -> id int not null,
    -> ename varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job varchar(30) not null,
    -> store_id int not null
    -> )
    -> partition by linear key(store_id) partitions 4;
Query OK, 0 rows affected (0.02 sec)

子分区

可以在Range和List分区中再进一步创建Hash和Key子分区

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.02 sec)

分区的管理

分区的管理操作包括添加，删除，重定义，合并，拆分分区等操作，这些操作都通过alter table来进行；删除分区会删除分区中的数据，而重定义分区不会删除原来数据，重定义分区只能是相邻的分区

Range分区管理操作示例

-- 删除分区，分区中原有的数据会同时被删除，并且新插入的数据会分配到比原来上限更大(如果有的话)的分区中；如删除p0分区后，若有p1分区，则新数据会插入到p1中
alter table emp drop partition p0;

-- 增加的分区只能在已有分区的最大端；如原有p3，则增加只能p4到p3之后
alter table emp_date add partition (partition p4 values less than (2030));

-- 重定义已有分区来拆分分区，新的分区要覆盖原有的分区的范围
mysql> show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-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=gbk
/*!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,
 PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table emp_date reorganize partition p4 into (
    -> partition p4 values less than (40),
    -> partition p5 values less than (2030)
    -> );
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 重定义分区来合并相邻分区为一个或多个新的分区，新的分区要覆盖原有的分区的范围
mysql> show create table emp_date \G
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-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=gbk
/*!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,
 PARTITION p4 VALUES LESS THAN (40) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2030) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table emp_date reorganize partition p4,p5 into (
    -> partition p3 values less than (2030)
    -> );
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

List分区管理操作示例

-- 删除分区，分区中原有的数据会同时被删除，无法再插入原来值列表里的值
alter table emp drop partition p0;

-- 增加的分区的值列表不能包含已有分区值列表中的值
alter table emp_date add partition (partition p4 values in (7, 8));

-- 重定义相邻分区，值列表覆盖原来的值列表
mysql> alter table emp_date reorganize partition p4,p5,p6 into (
    -> partition p4 values in (6, 11),
    -> partition p5 values in (7, 8)
    -> );
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Hash和Key分区管理操作示例

-- 合并分区，原来的数据不会被删除
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 '1970-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=gbk
/*!50100 PARTITION BY LINEAR KEY (store_id)
PARTITIONS 4 */
1 row in set (0.01 sec)

mysql> alter table emp coalesce partition 2;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 增加2个分区，原来的数据不会被删除
mysql> alter table emp add partition partitions 2;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

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 '1970-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=gbk
/*!50100 PARTITION BY LINEAR KEY (store_id)
PARTITIONS 4 */
1 row in set (0.01 sec)

参考文献

《深入浅出MySQL》
MySQL 5.7 Reference Manual

Stay Hungry Stay Foolish

MySQL分区简介

MySQL的分区

分区的使用场景

分区的4种类型

子分区

分区的管理

参考文献

Stay Hungry Stay Foolish

目录

MySQL的分区

分区的使用场景

分区的4种类型

子分区

分区的管理

参考文献