MySQL 从 5.1 版本开始支持分区的功能。分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。
就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数十个物理分区对象组成，每个分区都是独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。
MySQL 分区的优点主要包括以下四个方面：
- 和单个磁盘或者文件系统分区相比，可以存储更多数据
- 优化查询，在 WHERE 子句中包含分区条件时，可以只扫描一个或多个分区来提高查询效率（分区修剪）
- 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
- 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
分区概述
分区有利于管理非常大的表，它采用了 “分而治之” 的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值、范围值、特定值列表或者 HASH 函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象。
在 MySQL 5.7 及其以后的版本中，可以通过 show plugins 命令来确定当前 MySQL 版本是否支持分区，例如：
mysql> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 44 rows in set (0.00 sec)
这里会显示所有插件，如果有如下插件则表明支持分区。
partition ACTIVE STORAGE ENGINE GPL
在 MySQL5.7 中，同一个分区表的所有分区必须使用同一个存储引擎，即同一个分区表上，不能对一个分区使用 MyISAM 引擎，对另一个分区使用 InnoDB 引擎；但是，可以在同一个 MySQL 服务器中，甚至同一个数据库中，对于不同的分区表，可以使用不同的存储引擎。
和非分区表设置存储引擎一样，分区表设置存储引擎，只能用 [STORAGE] ENGINE 子句。[STORAGE] ENGINE 子句必须列在 CREATE TABLE 语句中的其他任何分区选项之前。例如，下面的例子创建了一个使用 InnoDB 引擎并有 6 个 HASH 分区的表：
mysql> create table emp ( empid int, salary decimal(7,2), birth_day date ) engine=innodb partition by hash(month(birth_day)) partitions 6;
注： MySQL 的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区；反过来也是一样的，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区。MySQL 的分区表上创建的索引一定是本地 LOCAL 索引。
分区类型
在 MySQL5.7 中，主要有如下四种可用的分区类型：
-
RANGE 分区：基于一个给定连续区间范围，把数据分配到不同的分区
-
LIST 分区：类似 RANGE 分区，区别在于 LIST 分区是基于枚举出的值列表分区，RANGE 分区是基于给定的连续区间范围分区
-
HASH 分区：基于给定的分区个数，把数据分配到不同的分区
-
KEY 分区：类似于 HASH 分区
数据库分区的一个非常常见的用法是按日期分隔数据，有些数据库系统支持显示日期分区（如 Oracle 数据库），而 MySQL 在 5.7 中并没有实现这种分区。但是，在 MySQL 中，基于 DATE、TIME 或 DATETIME 列，可使用这些列的表达式创建分区方案。
mysql> create table members ( firstname varchar(25) not null, lastname varchar(25) not null, username varchar(16) not null, email varchar(35), joined date not null ) partition by range(year(joined)) ( partition p0 values less than (1960), partition p1 values less than (1970), partition p2 values less than (1980), partition p3 values less than (1990), partition p4 values less than maxvalue );
当按 KEY 或 线性 KEY 分区时，可以使用 DATE、TIME 或 DATETIME 作为分区列，而无需对列值执行任何修改。
mysql> create table members ( firstname varchar(25) not null, lastname varchar(25) not null, username varchar(16) not null, email varchar(35), joined date not null ) partition by key(joined) partitions 6;
RANGE 分区
按照 RANGE 分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用 VALUES LESS THAN 操作符进行分区定义。
- 例 1：创建员工表 EMP，并按 STORE_ID 字段进行 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) );
按照这种分区方案，在商店 1~9 工作的员工相对应的所有行被保存在分区 p0 中，商店 10~19 的员工保存在分区 p1 中，依次类推。
这个时候，如果增加了 store_id 大于等于 30 的行，会出现错误，因为没有规则包含了 store_id 大于等于 30 的行，服务器不知道应该把记录保存在哪个分区中。
mysql> insert into emp(id, ename, job, store_id) values('7934', 'MILLER', 'CLERK', 30); ERROR 1526 (HY000): Table has no partition for value 30
为防止出现如上情况，可以在创建表及设置分区的时候使用 VALUES LESS THAN MAXVALUE 子句，该子句提供给所有大于明确指定值的最高值。
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), partition p3 values less than maxvalue );
- 例 2：MySQL 支持在 VALUES LESS THAN 子句中使用表达式，比如，以日期作为 RANGE 分区的分区列：
mysql> create table emp_date ( 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), partition p3 values less than maxvalue );
注： 在 RANGE 分区中，分区键如果是 NULL 值，则会被当作一个最小值来处理。
- 例 3：使用 UNIX_TIMESTAMP() 函数，根据 TIMESTAMP 的值，按 REPORT_UPDATED 字段进行 RANGE 分区：
mysql> create table quarterly_report_status ( report_id int not null, report_status varchar(20) not null, report_updated timestamp not null default current_timestamp on update current_timestamp ) partition by range (unix_timestamp(report_updated)) ( partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')), partition p1 values less than (unix_timestamp('2008-04-01 00:00:00')), partition p2 values less than (unix_timestamp('2008-07-01 00:00:00')), partition p3 values less than (unix_timestamp('2008-10-01 00:00:00')), partition p4 values less than (unix_timestamp('2009-01-01 00:00:00')), partition p5 values less than (unix_timestamp('2009-04-01 00:00:00')), partition p6 values less than (unix_timestamp('2009-07-01 00:00:00')), partition p7 values less than (unix_timestamp('2009-10-01 00:00:00')), partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')), partition p9 values less than (maxvalue) );
RANGE 分区功能特别适用于以下两种情况：
- 当需要删除过期的数据时，只需要简单的
ALTER TABLE emp DROP PARTITION p0来删除 p0 分区中的数据，对于具有上千万条记录的表来说，删除分区要比运行一个DELETE语句有效得多 - 经常包含分区键的查询，MySQL 可以很快地确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含有符合该
WHERE子句的任何记录。例如，检索store_id=25的记录数，MySQL 只需要扫描 p2 分区即可。
mysql> explain partitions select count(1) from emp where store_id = 25 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp partitions: p2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec)
LIST 分区
LIST 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST 分区在很多方面类似于 RANGE 分区，区别在于 LIST 分区是从属于一个枚举值列表的值的集合，RANGE 分区是从属于一个连续区间值的集合。
LIST 分区通过使用 PARTITION BY LIST(expr) 子句来实现，expr 是某列值或一个基于某列值返回一个整数值的表达式，然后通过 VALUES IN (value_list) 的方式来定义分区，其中 value(list) 是一个逗号分隔的整数列表。与 RANGE 分区不同，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) );
如果试图插入的列值（或者分区表达式的返回值）未包含在分区值列表中时，那么 INSERT 操作会失败并报错。要重点注意的是，LIST 分区不存在类似 VALUES LESS THAN MAXVALUE 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中找得到。
mysql> create table h2 ( c1 int, c2 int ) partition by list(c1) ( partition p0 values in (1, 4, 7), partition p1 values in (2, 5, 8) ); mysql> insert into h2 values(3, 5); ERROR 1526 (HY000): Table has no partition for value 3
可以使用关键字 IGNORE 忽略此类错误，如果执行此操作，则不会插入包含不匹配分区列值的行，但会插入具有匹配值的任何行，并且不会报告错误。
mysql> truncate table h2; Query OK, 0 rows affected (0.04 sec) mysql> select * from h2; Empty set (0.00 sec) mysql> insert ignore into h2 values (2,5), (6,10), (7,5), (3,1), (1,9); Query OK, 3 rows affected, 2 warnings (0.00 sec) Records: 5 Duplicates: 2 Warnings: 2 mysql> select * from h2; +------+------+ | c1 | c2 | +------+------+ | 7 | 5 | | 1 | 9 | | 2 | 5 | +------+------+ 3 rows in set (0.00 sec)
COLUMNS 分区
COLUMNS 分区是在 MySQL 5.5 引入的分区类型，引入 COLUMNS 分区解决了 MySQL 5.5 版本之前 RANGE 分区和 LIST 分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。COLUMNS 分区可以细分为 RANGE COLUMNS 分区和 LIST COLUMNS 分区，RANGE COLUMNS 分区和 LIST COLUMNS 分区都支持整数、日期时间、字符串三大数据类型。
- 所有整数类型：TINYINT、SMALLINT、MEDIUMINT、INT 和 BITINT；其它数值类型都不支持，例如不支持 DECIMAL 和 FLOAT
- 日期时间类型：DATE 和 DATETIME
- 字符类型：CHAR、VARCHAR、BINARY 和 VARBINARY；不支持 TEXT 和 BLOB 类型作为分区键
RANGE COLUMNS 分区
RANGE COLUMNS 分区与 RANGE 分区类似，但 RANGE COLUMNS 分区允许使用基于多个列值的范围定义分区。此外，还可以使用除整数类型以外的其他类型来定义 RANGE COLUMNS 分区。
RANGE COLUMNS 分区与 RANGE 分区主要有如下几点不同：
- RANGE COLUMNS 分区不接受表达式，仅接受列的名称
- RANGE COLUMNS 支持多列分区
- RANGE COLUMNS 分区不限于整数列，字符串、DATE、DATETIME 也可用作分区列
RANGE COLUMNS 分区中，分区列列表（partitioning column list）和定义每个分区的值列表（value list）中的元素必须以相同的顺序出现。此外，值列表中每个元素必须与分区列列表的相应元素具有相同的数据类型。
与按 RANGE 分区一样，可以使用 MAXVALUE 来表示一个值，以便插入到给定列中的任何合法值始终小于此值。
mysql> create table rcx ( a int, b int, c char(3), d int ) partition by range columns(a, d, c) ( partition p0 values less than (5 , 10, 'ggg'), partition p1 values less than (10, 20, 'mmm'), partition p2 values less than (15, 30, 'sss'), partition p3 values less than (maxvalue, maxvalue, maxvalue) );
表 rcx 包含 a, b, c, d 四列，提供给 RANGE COLUMNS 子句的分区列列表使用这些列中的三列，顺序为 a, d, c。那么，每个值列表元组都应具有格式 int, int, char(3) ，该格式对应于列 a, b, c 使用的数据类型。
注：需要注意的是，RANGE COLUMNS 分区键的比较是基于元组的比较，也就是基于字段组的比较。
- 创建表 rc3，使用 RANGE 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) );
- 写入
a=1, b=10的记录，从 information_schema.partitions 表发现数据实际写入了 p02 分区，也就是说元组(1, 10) < (10, 10)
mysql> insert into rc3 values(1, 10); Query OK, 1 row affected (0.01 sec) mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions where table_name = 'RC3'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p01 | 0,10 | 0 | | study | p02 | 10,10 | 1 | | study | p03 | 10,20 | 0 | | study | p04 | 10,35 | 0 | | study | p05 | 10,MAXVALUE | 0 | | study | p06 | MAXVALUE,MAXVALUE | 0 | +--------------+----------------+-----------------------+------------+ 6 rows in set (0.00 sec)
- 写入
a=10, b=9的记录，从 information_schema.partitions 表发现数据实际写入了 p02 分区，也就是说元组(10, 9) < (10, 10)
mysql> insert into rc3 values(10, 9); Query OK, 1 row affected (0.02 sec) mysql> select (10, 9) < (10, 10) from dual; +--------------------+ | (10, 9) < (10, 10) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions where table_name = 'RC3'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p01 | 0,10 | 0 | | study | p02 | 10,10 | 2 | | study | p03 | 10,20 | 0 | | study | p04 | 10,35 | 0 | | study | p05 | 10,MAXVALUE | 0 | | study | p06 | MAXVALUE,MAXVALUE | 0 | +--------------+----------------+-----------------------+------------+ 6 rows in set (0.01 sec)
- 写入
a=10, b=10的记录，从 information_schema.partitions 表发现数据实际写入了 p03 分区，也就是说元组(10, 0) < (10, 20)
mysql> insert into rc3 values(10, 10); Query OK, 1 row affected (0.00 sec) mysql> select (10, 10) < (10, 10), (10, 10) < (10, 20) from dual; +---------------------+---------------------+ | (10, 10) < (10, 10) | (10, 10) < (10, 20) | +---------------------+---------------------+ | 0 | 1 | +---------------------+---------------------+ 1 row in set (0.00 sec) mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions where table_name = 'RC3'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p01 | 0,10 | 0 | | study | p02 | 10,10 | 2 | | study | p03 | 10,20 | 1 | | study | p04 | 10,35 | 0 | | study | p05 | 10,MAXVALUE | 0 | | study | p06 | MAXVALUE,MAXVALUE | 0 | +--------------+----------------+-----------------------+------------+ 6 rows in set (0.01 sec)
从上述结果可知，RANGE COLUMNS 分区键的比较（元组的比较）其实就是多列排序，先根据 a 字段排序再根据 b 字段排序，根据排序结果来分区存放数据。
RANGE COLUMNS 分区也可直接使用 DATE、DATETIME 类型作为其分区列，例如：
mysql> create table employees ( id int not null, fname varchar(30), lname varchar(30), hired date not null default '1970-01-01', separated date not null default '9999-12-31', job_code int not null, store_id int not null ) partition by range columns(hired) ( partition p0 values less than ('1970-01-01'), partition p1 values less than ('1980-01-01'), partition p2 values less than ('1990-01-01'), partition p3 values less than ('2000-01-01'), partition p4 values less than ('2010-01-01'), partition p5 values less than (maxvalue) );
LIST COLUMNS 分区
LIST COLUMNS 分区是 LIST 分区的变体，它允许多个列作为分区键，并且允许使用除整数类型以外的其他数据类型的列作为分区列。比如 字符串、DATE、DATETIME 类型。
- 使用字符串列表分区
create table customers_1 ( first_name varchar(25), last_name varchar(25), street_1 varchar(30), street_2 varchar(30), city varchar(15), renewal date ) partition by list columns(city) ( partition p0 values in ('Oskarshamn', 'Högsby', 'Mönsterås'), partition p1 values in ('Vimmerby', 'Hultsfred', 'Västervik'), partition p2 values in ('Nässjö', 'Eksjö', 'Vetlanda'), partition p3 values in ('Uppvidinge', 'Alvesta', 'Växjo') );
- 使用日期列表分区
mysql> create table customers_2 ( first_name varchar(25), last_name varchar(25), street_1 varchar(30), street_2 varchar(30), city varchar(15), renewal date ) partition by list columns(city) ( partition p1 values in('2010-02-01', '2010-02-02', '2010-02-03', '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'), partition p2 values in('2010-02-08', '2010-02-09', '2010-02-10', '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'), partition p3 values in('2010-02-15', '2010-02-16', '2010-02-17', '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'), partition p4 values in('2010-02-22', '2010-02-23', '2010-02-24', '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28') );
HASH 分区
HASH 分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行 HASH 分区时，MySQL 会对分区键应用一个散列函数，以此确定数据应当放在 N 个分区的哪个分区中。
MySQL 支持两种 HASH 分区，常规 HASH 分区和线性 HASH 分区，常规 HASH 分区使用的是取模算法，线性 HASH 分区使用的是一个线性的 2 的幂的运算法则。
要使用 HASH 分区对表进行分区，必须在 CREATE TABLE 语句中附加一个 PARTITION BY HASH(expr) PARTITIONS num 子句，对分区类型、分区键和分区个数进行定义。其中 expr 是某列值或一个基于某列值返回一个整数值的表达式，num 是一个非负的整数，表示分割成分区的数量，默认 num 为 1。
使用列值作为分区键
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;
对于一个表达式 expr ，我们是可以计算出它会被保存在哪个分区中。假设将要保存记录的分区编号为 N，那么 “N = MOD(expr, num)”。例如，表 emp 有 4 个分区，插入一个 store_id 列值为 234 的记录到 emp 表中：
mysql> insert into emp values(1, 'Tom', '1970-01-01', '9999-12-31', 'Clerk', 234); Query OK, 1 row affected (0.00 sec) mysql> select * from emp; +----+-------+------------+------------+-------+----------+ | id | ename | hired | separated | job | store_id | +----+-------+------------+------------+-------+----------+ | 1 | Tom | 1970-01-01 | 9999-12-31 | Clerk | 234 | +----+-------+------------+------------+-------+----------+ 1 row in set (0.00 sec) mysql> select mod(234, 4) from dual; +-------------+ | mod(234, 4) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec)
从上述结果可知，store_id = 234 这条记录将会被保存到第二个分区。
通过执行计划也可以确定 store_id 这条记录存储在第二个分区内。
mysql> explain partitions select * from emp where store_id = 234 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp partitions: p2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec)
使用表达式作为分区键
表达式 expr 可以是 MySQL 中有效的任何函数或者其他表达式，只要它们返回一个即非常数也非随机数的整数。
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(year(hired)) partitions 4;
注： 常规 HASH 分区的优点是，通过取模的方式来让数据尽可能地平均分布在每个分区中，让每个分区管理的数据都少了，提高了查询效率；但缺点在于，当我们需要增加分区或者合并分区的时候，问题就出现了。假设原来是 5 个常规 HASH 分区，现在需要新增一个常规 HASH 分区，原来的取模算法是 MOD(expr, 5)，根据余数 0~4 分布在 5 个分区中，现在新增一个常规 HASH 分区，取模算法变成 MOD(expr, 6)，根据余数 0~5 分布在 6 个分区中，原来 5 个分区中的数据大部分都需要通过重新计算重新分区。
线性 HASH 分区
常规 HASH 分区在分区管理上带来的代价太大了，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，MySQL 提供了线性 HASH 分区，分区函数是一个线性的 2 的幂的运算法则。
线性 HASH 分区和常规 HASH 分区在语法上的唯一区别是在 PARTITION BY 子句中添加了 LINEAR 关键字，例如：
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;
当线性 HASH 分区个数是 2 的 N 次幂时，线性 HASH 分区的分区结果和常规 HASH 分区的分区结果是一致的。
注： 线性 HASH 分区的优点是，在分区维护（包含增加、删除、合并、拆分分区操作）时，MySQL 能够处理地更加迅速；缺点是，对比常规 HASH 分区各个分区之间数据的分布不太均衡。
KEY 分区
按照 KEY 进行分区非常类似于按照 HASH 进行分区，只不过 HASH 分区允许使用用户自定义的表达式，而 KEY 分区不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 HASH 函数；同时 HASH 分区只支持整数分区，而 KEY 分区支持使用除 BLOB or Text 类型外的其他类型列作为分区键。
可以使用 PARTITION BY KEY(expr) 子句来创建一个 KEY 分区表，expr 是零个或者多个字段名称的列表。下面的语句创建了一个基于 job 字段进行 KEY 分区的表，表被分成了 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(job) partitions 4;
与 HASH 分区不同，创建 KEY 分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键，例如：
mysql> create table emp ( id int not null primary key, 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() partitions 4;
在表没有主键的情况下，会选择非空唯一键作为 KEY 分区的分区键：
mysql> create table emp ( id int not null unique key, 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() partitions 4;
注： 作为分区键的唯一键必须是非空的，如果不是非空的，则会报错。
mysql> create table emp ( id int unique key, 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() partitions 4; > 1488 - Field in list of fields for partition function not found in table
在表没有主键、也没有唯一键的情况下，使用 KEY 分区，就不能不指定分区键了：
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() partitions 4; > 1488 - Field in list of fields for partition function not found in table
注： 在按照 KEY 分区的分区表上，不能够执行 ALTER TABLE DROP PRIMARY KEY; 语句来删除主键，MySQL 会返回错误 Field in list of fields for partition function not found in table。
和 HASH 分区类似，在 KEY 分区中使用关键字 LINEAR 具有同样的作用，也就是 LINEAR KEY 分区时，分区的编号是通过 2 的幂算法得到的，而不是通过取模得到的。
mysql> create table tk ( col1 int not null, col2 char(5), col3 date ) partition by linear key(col1) partitions 3;
子分区
子分区是分区表中对每个分区的再次分割，又被称为复合分区。MySQL 从 5.1 版本开始支持对已经通过 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 (1900), partition p1 values less than (2000), partition p2 values less than maxvalue );
表 ts 有 3 个 RANGE 分区，这 3 个分区中的每个分区（p0、p1、p2）又被进一步分成 2 个子分区，实际上，整个表被分成了 3*2=6 个分区。
分区处理 NULL 值的方式
MySQL 不禁止在分区键值上使用 NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL 的分区把 NULL 值当作零值，或者一个最小值进行处理。
注： 在 RANGE 分区中，NULL 值会被当作最小值来处理；LIST 分区中，NULL 值必须出现在枚举值列表中，否则不被接受；HASH/KEY 分区中，NULL 值会被当作零值来处理。
- 例 1：创建 TB_RANGE 表，按照 ID 列进行 RANGE 分区，并在 RANGE 分区中写入 NULL 值：
mysql> create table tb_range ( id int, name varchar(5) ) partition by range(id) ( partition p0 values less than (-6), partition p1 values less than (0), partition p2 values less than (1), partition p3 values less than maxvalue ); mysql> insert into tb_range values (null, 'NULL'); Query OK, 1 row affected (0.02 sec)
查询 INFORMATION_SCHEMA.PARTITIONS 表确认写入的 NULL 值被当作最小值处理，NULL 被分配在分区 p0 内：
mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions t where t.table_schema = 'STUDY' and t.table_name = 'TB_RANGE'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p0 | -6 | 1 | | study | p1 | 0 | 0 | | study | p2 | 1 | 0 | | study | p3 | MAXVALUE | 0 | +--------------+----------------+-----------------------+------------+ 4 rows in set (0.00 sec)
- 例 2：在 LIST 分区中写入 NULL 值，分区定义不包含 NULL 值的时候，会返回一个错误
create table tb_list ( id int, name varchar(5) ) partition by list(id) ( partition p0 values in (0), partition p1 values in (1) ); mysql> insert into tb_list values(null, 'NULL'); ERROR 1526 (HY000): Table has no partition for value NULL
- 例 3：在 LIST 分区中增加 NULL 值的定义之后，就能够成功写入 NULL 值
create table tb_list ( id int, name varchar(5) ) partition by list(id) ( partition p0 values in (0, null), partition p1 values in (1) ); mysql> insert into tb_list values(null, 'NULL'); Query OK, 1 row affected (0.02 sec) mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions t where t.table_schema = 'STUDY' and t.table_name = 'TB_LIST'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p0 | NULL,0 | 1 | | study | p1 | 1 | 0 | +--------------+----------------+-----------------------+------------+ 2 rows in set (0.00 sec)
- 例 4：创建 TB_HASH 表，按照 ID 列进行 HASH 分区，在 HASH 分区中写入 NULL 值：
mysql> create table tb_hash ( id int, name varchar(5) ) partition by hash(id) partitions 2; mysql> insert into tb_hash values (null, 'NULL'); Query OK, 1 row affected (0.01 sec) mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions t where t.table_schema = 'STUDY' and t.table_name = 'TB_HASH'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p0 | NULL | 1 | | study | p1 | NULL | 0 | +--------------+----------------+-----------------------+------------+ 2 rows in set (0.00 sec)
由于针对不同的分区类型，NULL 值时而被当作零值处理，时而被当作最小值处理，为了避免在处理 NULL 时出现误判，推荐通过设置分区字段非空和默认值来绕开 MySQL 默认对 NULL 值的处理。
获取分区信息
获取分区信息有如下几种方法：
- 使用 SHOW CREATE TABLE 语句查看创建分区表时使用的分区子句
- 使用 SHOW TABLE STATUS 语句判断表是否分区
- 查询 INFORMATION_SCHEMA.PARTITIONS 表
- 使用 EXPLAIN SELECT 查看给定 SELECT 语句使用了哪些分区
创建表 TRB1 并写入数据：
mysql> create table trb1 ( id int, name varchar(50), purchased date ) partition by range(id) ( partition p0 values less than (3), partition p1 values less than (7), partition p2 values less than (9), partition p3 values less than (11) ); mysql> insert into trb1 values (1, 'desk organiser', '2003-10-15'), (2, 'CD player' , '1993-11-05'), (3, 'TV set' , '1996-03-10'), (4, 'bookcase' , '1982-01-10'), (5, 'exercise bike' , '2004-05-09'), (6, 'sofa' , '1987-06-05'), (7, 'popcorn maker' , '2001-11-22'), (8, 'aquarium' , '1992-08-04'), (9, 'study desk' , '1984-09-16'), (10, 'lava lamp' , '1998-12-25'); mysql> select * from trb1; +------+----------------+------------+ | id | name | purchased | +------+----------------+------------+ | 1 | desk organiser | 2003-10-15 | | 2 | CD player | 1993-11-05 | | 3 | TV set | 1996-03-10 | | 4 | bookcase | 1982-01-10 | | 5 | exercise bike | 2004-05-09 | | 6 | sofa | 1987-06-05 | | 7 | popcorn maker | 2001-11-22 | | 8 | aquarium | 1992-08-04 | | 9 | study desk | 1984-09-16 | | 10 | lava lamp | 1998-12-25 | +------+----------------+------------+ 10 rows in set (0.00 sec)
- SHOW CREATE TABLE
mysql> show create table trb1 \G; *************************** 1. row *************************** Table: trb1 Create Table: CREATE TABLE `trb1` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (9) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (11) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
- SHOW TABLE STATUS
mysql> show table status like 'TRB1' \G; *************************** 1. row *************************** Name: trb1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 10 Avg_row_length: 6553 Data_length: 65536 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-11-27 22:24:55 Update_time: 2021-11-27 22:25:00 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.00 sec)
- INFORMATION_SCHEMA.PARTITIONS
mysql> select * from information_schema.partitions t where t.table_schema = 'STUDY' and t.table_name = 'TRB1' \G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: study TABLE_NAME: trb1 PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: id SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 3 TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA_LENGTH: NULL INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2021-11-27 22:24:55 UPDATE_TIME: 2021-11-27 22:25:00 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL
- EXPLAIN SELECT
mysql> explain select * from trb1 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1,p2,p3 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
分区管理
MySQL 5.7 提供了多种修改分区表的方法，可以添加、删除、重新定义、合并或拆分现有分区。所有这些操作都可以使用 ALTER TABLE 语句来实现。
RANGE 和 LIST 分区管理
在添加、删除、重新定义分区的处理上，RANGE 分区和 LIST 分区非常相似，所以合并一起来讨论。
| 序号 | 描述 | 语法 |
| 1 | 删除分区 | DROP PARTITION |
| 2 | 增加分区 | ADD PARTITION |
| 3 | 清空分区 | TRUNCATE PARTITION |
| 4 | 重定义分区之拆分分区 | REORGANIZE PARTITION |
| 5 | 重定义分区之合并分区 | REORGANIZE PARTITION |
创建分区表 EMP_DATE，按照 RANGE 分区，分区个数为 4 个：
mysql> create table emp_date ( 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), partition p3 values less than (2015) ); mysql> insert into emp_date(id, ename, hired, separated, job, store_id) values (7499, 'ALLEN' , '1981-02-20', '2003-08-03', 'SALESMAN' , 30), (7521, 'WARD' , '1981-02-22', '1993-09-01', 'SALESMAN' , 30), (7566, 'JONES' , '1981-04-02', '2000-08-01', 'MANAGER' , 20), (7654, 'MARTIN', '1981-09-28', '2012-12-31', 'SALESMAN' , 30), (7698, 'BLAKE' , '1981-05-01', '1998-09-08', 'MANAGER' , 30), (7782, 'CLARK' , '1981-06-09', '2007-08-01', 'MANAGER' , 10), (7788, 'SCOTT' , '1987-04-19', '2012-05-01', 'ANALYST' , 20), (7839, 'KING' , '1981-11-17', '2011-03-09', 'PRESIDENT', 10), (7844, 'TURNER', '1981-09-08', '2010-12-31', 'SALESMAN' , 30), (7876, 'ADAMS' , '1987-05-23', '2000-01-01', 'CLERK' , 20), (7900, 'JAMES' , '1981-12-03', '2004-09-02', 'CLERK' , 30), (7902, 'FORD' , '1981-12-03', '2010-12-31', 'ANALYST' , 20), (7934, 'MILLER', '1982-01-23', '2011-12-31', 'CLERK' , 10); mysql> select * from emp_date; +------+--------+------------+------------+-----------+----------+ | id | ename | hired | separated | job | store_id | +------+--------+------------+------------+-----------+----------+ | 7521 | WARD | 1981-02-22 | 1993-09-01 | SALESMAN | 30 | | 7698 | BLAKE | 1981-05-01 | 1998-09-08 | MANAGER | 30 | | 7499 | ALLEN | 1981-02-20 | 2003-08-03 | SALESMAN | 30 | | 7566 | JONES | 1981-04-02 | 2000-08-01 | MANAGER | 20 | | 7876 | ADAMS | 1987-05-23 | 2000-01-01 | CLERK | 20 | | 7900 | JAMES | 1981-12-03 | 2004-09-02 | CLERK | 30 | | 7654 | MARTIN | 1981-09-28 | 2012-12-31 | SALESMAN | 30 | | 7782 | CLARK | 1981-06-09 | 2007-08-01 | MANAGER | 10 | | 7788 | SCOTT | 1987-04-19 | 2012-05-01 | ANALYST | 20 | | 7839 | KING | 1981-11-17 | 2011-03-09 | PRESIDENT | 10 | | 7844 | TURNER | 1981-09-08 | 2010-12-31 | SALESMAN | 30 | | 7902 | FORD | 1981-12-03 | 2010-12-31 | ANALYST | 20 | | 7934 | MILLER | 1982-01-23 | 2011-12-31 | CLERK | 10 | +------+--------+------------+------------+-----------+----------+ 13 rows in set (0.00 sec)
删除分区
- 通过下面的查询语句查看哪些记录在分区 p2 中（LESS THAN 2005）
mysql> select * from emp_date partition(p2); +------+-------+------------+------------+----------+----------+ | id | ename | hired | separated | job | store_id | +------+-------+------------+------------+----------+----------+ | 7499 | ALLEN | 1981-02-20 | 2003-08-03 | SALESMAN | 30 | | 7566 | JONES | 1981-04-02 | 2000-08-01 | MANAGER | 20 | | 7876 | ADAMS | 1987-05-23 | 2000-01-01 | CLERK | 20 | | 7900 | JAMES | 1981-12-03 | 2004-09-02 | CLERK | 30 | +------+-------+------------+------------+----------+----------+ 4 rows in set (0.00 sec)
- 执行下面的语句，删除 p2 分区
mysql> alter table emp_date drop partition p2; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
注： 删除分区的命令执行之后，并不显示实际从表中删除的行数，但并不代表真的没有记录被删除。
- 从表结构定义上，可以观察到 p2 分区确实被删除了
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=utf8 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
- 删除了 p2 分区，那么同时也会删除该分区中的所有数据（根据建表分区条件，'2000-01-01'~'2004-12-31' 的数据将被写入 p2 分区）
mysql> select * from emp_date where separated between '2000-01-01' and '2004-12-31'; Empty set (0.00 sec)
- 再次写入 separated 日期在 '2000-01-01'~'2004-12-31' 之间的新记录到 EMP_DATE 表时，这些行记录将被保存至 p3 分区中，首先检查写入前 EMP_DATE 表的记录分布，此时 p3 分区中仅有 7 条记录：
mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions t where t.table_schema = 'STUDY' and t.table_name = 'EMP_DATE'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p0 | 1995 | 0 | | study | p1 | 2000 | 0 | | study | p3 | 2015 | 7 | +--------------+----------------+-----------------------+------------+ 3 rows in set (0.00 sec)
- 在 EMP_DATE 表中写入一条 separated 日期在 '2000-01-01'~'2004-12-31' 之间的新纪录：
mysql> insert into emp_date(id, ename, hired, separated, job, store_id) values (7566, 'JONES' , '1981-04-02', '2000-08-01', 'MANAGER' , 20);
- 再次检查 EMP_DATE 表的记录分布，发现 p3 分区的记录数增加为 8 条，确认新写入的记录写入 p3 分区：
mysql> select table_schema, partition_name, partition_description, table_rows from information_schema.partitions t where t.table_schema = 'STUDY' and t.table_name = 'EMP_DATE'; +--------------+----------------+-----------------------+------------+ | table_schema | partition_name | partition_description | table_rows | +--------------+----------------+-----------------------+------------+ | study | p0 | 1995 | 0 | | study | p1 | 2000 | 0 | | study | p3 | 2015 | 8 | +--------------+----------------+-----------------------+------------+ 3 rows in set (0.00 sec)
删除 LIST 分区和删除 RANGE 分区使用的语句完全相同，只不过删除 LIST 分区之后，由于在 LIST 分区的定义中不再包含已经被删除了的分区的值列表，所以后续无法写入包含有已经删除了的分区的值列表的数据。
增加分区
为一个 RANGE 分区或者 LIST 分区的表增加一个分区，可以使用 ALTER TABLE ADD PARTITION 语句来实现。对于 RANGE 分区来说，只能通过 ADD PARTITION 方式添加新分区到分区列表的最大一端，例如，给 EMP_DATE 表新增 p4 分区，存放 separated 日期在 '2015-01-01'~'2029-12-31' 之间的记录：
mysql> alter table emp_date add partition (partition p4 values less than (2030)); Query OK, 0 rows affected (0.03 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=utf8 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
注： 只能在 RANGE 分区列表最大端增加分区，否则会出现如下错误：
mysql> alter table emp_date add partition (partition p5 values less than (2025)); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
- 给 LIST 分区新增分区的方式也类似，以 EXPENSES 表为例，该表有 5 个分区：
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) );
- 为 EXPENSES 表新增 p5 分区，存储 category 分类为 7 和 8 的数据：
mysql> alter table expenses add partition (partition p5 values in (7, 8)); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table expenses \G; *************************** 1. row *************************** Table: expenses Create Table: CREATE TABLE `expenses` ( `expense_date` date NOT NULL, `category` int(11) DEFAULT NULL, `amount` decimal(10,3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (category) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB, PARTITION p3 VALUES IN (2) ENGINE = InnoDB, PARTITION p4 VALUES IN (6) ENGINE = InnoDB, PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */ 1 row in set (0.01 sec)
注： 增加 LIST 分区时，不能添加一个包含现有分区值列表中的任意值的分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区，否则会出现错误：
mysql> alter table expenses add partition (partition p6 values in (6, 11)); ERROR 1495 (HY000): Multiple definition of same constant in list partitioning
清空分区
清空一个 RANGE 分区或者 LIST 分区，可以使用 ALTER TABLE TRUNCATE PARTITION 语句来实现。
- 创建分区表 T_LIST 并写入记录：
mysql> create table t_list ( id int not null, name varchar(10) ) partition by list(id) ( partition p0 values in (1), partition p1 values in (2) ); mysql> insert into t_list values(1, 'SCOTT'), (2, 'JAMES'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
- 使用 TRUNCATE PARTITION 语句清空分区 p0 的数据：
mysql> alter table t_list truncate partition p0; Query OK, 0 rows affected (0.01 sec)
- 查看表 T_LIST 清空分区 p0 后的数据：
mysql> select * from t_list; +----+-------+ | id | name | +----+-------+ | 2 | JAMES | +----+-------+ 1 row in set (0.00 sec)
重定义分区之拆分分区
MySQL 提供了在不丢失数据的情况下，通过重新定义分区的语句 ALTER TABLE REORGANIZE PARTITION INTO 重定义分区。
- 以 RANGE 分区的 emp_date 表为例，当前 emp_date 的表结构如下：
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=utf8 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
- 将 p3 分区（2000~2015）拆分为两个分区 p2（2000~2005）和 p3（2005~2015）：
mysql> alter table emp_date reorganize partition p3 into ( partition p2 values less than (2005), partition p3 values less than (2015) ); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0
- 确认拆分之后 emp_date 的表结构：
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=utf8 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.00 sec)
重定义分区之合并分区
重定义分区也可以用来合并多个相邻 RANGE 分区为一个 RANGE 分区或者多个 RANGE 分区。
mysql> alter table emp_date reorganize partition p1, p2, p3 into (partition p1 values less than (2015)); Query OK, 0 rows affected (0.10 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=utf8 /*!50100 PARTITION BY RANGE (year(separated)) (PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */ 1 row in set (0.01 sec)
注： 重新定义（合并分区） RANGE 分区时，只能够重新定义相邻的分区，不能跳过某个 RANGE 分区进行重新定义。同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变分区的类型，例如，不能把 RANGE 分区变为 HASH 分区，也不能把 HASH 分区变成 RANGE 分区。
同样的，对 LIST 分区，也可以使用 ALTER TABLE ORGANIZE PARTITION 语句重定义分区，例如，当前 expenses 表的分区如下：
mysql> show create table expenses \G; *************************** 1. row *************************** Table: expenses Create Table: CREATE TABLE `expenses` ( `expense_date` date NOT NULL, `category` int(11) DEFAULT NULL, `amount` decimal(10,3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (category) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB, PARTITION p3 VALUES IN (2) ENGINE = InnoDB, PARTITION p4 VALUES IN (6) ENGINE = InnoDB, PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */ 1 row in set (0.01 sec)
现在需要调整 p4 分区，使得 p4 分区包含值为 6 和 11 的记录，即 p4 分区的定义为 PARTITION p4 VALUES in (6, 11)，由于 p4 分区已经包含单个值为 6 的记录，所以单纯通过 ADD PARTITION 的方式是不可以的：
mysql> alter table expenses add partition (partition p4 values in (6, 11)); ERROR 1517 (HY000): Duplicate partition name p4
可以变通的通过增加分区和重定义分区的方式来实现，首先增加不重复列值的 p6 分区，包含值 11：
mysql> alter table expenses add partition (partition p6 values in (11)); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table expenses \G; *************************** 1. row *************************** Table: expenses Create Table: CREATE TABLE `expenses` ( `expense_date` date NOT NULL, `category` int(11) DEFAULT NULL, `amount` decimal(10,3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (category) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB, PARTITION p3 VALUES IN (2) ENGINE = InnoDB, PARTITION p4 VALUES IN (6) ENGINE = InnoDB, PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB, PARTITION p6 VALUES IN (11) ENGINE = InnoDB) */ 1 row in set (0.01 sec)
之后再通过 REORGANIZE PARTITION 方式，重定义 p4、p5、p6 这三个分区，合并 p4 和 p6 两个分区为新的 p4 分区，包含值 6 和 11：
mysql> alter table expenses reorganize partition p4, p5, p6 into ( partition p4 values in (6, 11), partition p5 values in (7, 8) ); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table expenses \G; *************************** 1. row *************************** Table: expenses Create Table: CREATE TABLE `expenses` ( `expense_date` date NOT NULL, `category` int(11) DEFAULT NULL, `amount` decimal(10,3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (category) (PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB, PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB, PARTITION p3 VALUES IN (2) ENGINE = InnoDB, PARTITION p4 VALUES IN (6,11) ENGINE = InnoDB, PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */ 1 row in set (0.01 sec)
从上述结果可知，通过重定义分区之后，p4 分区的值包含了 6 和 11。
注： 类似重定义 RANGE 分区，重定义 LIST 分区时，同样只能够重新定义相邻的分区，不能跳过 LIST 分区进行重新定义。例如，将不相邻的两个分区 p4 和 p6 进行合并分区操作，就会提示以下信息：
mysql> alter table expenses reorganize partition p4, p6 into (partition p4 values in (6, 11)); ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order
HASH 和 KEY 分区管理
在改变分区设置方面，HASH 分区和 KEY 分区的表非常相似，所以这两种分区的管理合并一起讨论。
不能以 RANGE 分区或者 LIST 分区表中删除分区的相同方式，来从 HASH 分区或者 KEY 分区的表中删除分区，而可以通过 ALTER TABLE COALESCE PARTITION 语句来合并 HASH 分区或者 KEY 分区。
- 创建 HASH 分区表 emp，按照 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;
- 减少 HASH 分区的数量，从 4 个分区变为 2 个分区
mysql> alter table emp coalesce partition 2; Query OK, 0 rows affected (0.14 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=utf8 /*!50100 PARTITION BY HASH (store_id) PARTITIONS 2 */ 1 row in set (0.01 sec)
注： COALESCE 不能用来增加分区的数量，否则会出现以下错误：
mysql> alter table emp coalesce partition 8; ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead
要增加分区，可以通过 ALTER TABLE ADD PARTITION 语句来实现，例如，当前 emp 表有 2 个 HASH 分区，现在增加 8 个分区，最终 emp 表一共有 10 个 HASH 分区：
mysql> alter table emp add partition partitions 8; Query OK, 0 rows affected (0.27 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=utf8 /*!50100 PARTITION BY HASH (store_id) PARTITIONS 10 */ 1 row in set (0.01 sec)
注： 通过 ALTER TABLE ADD PARTITION PARTITIONS n 语句新增 HASH 分区或者 KEY 分区时，其实是对原表新增 n 个分区，而不是增加到 n 个分区。
使用表交换分区和子分区
在 MySQL 5.7 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 将表分区或子分区与表交换，其中 pt 是分区表，p 是分区表 pt 的分区或子分区。
将分区表中某个分区的数据写入非分区表
- 创建分区表 e 并写入数据：
mysql> create table e ( id int not null, fname varchar(30), lname varchar(30) ) partition by range(id) ( partition p0 values less than (50), partition p1 values less than (100), partition p2 values less than (150), partition p3 values less than maxvalue ); mysql> insert into e values (1669, 'Jim' , 'Smith'), (337 , 'Mary' , 'Jones'), (16 , 'Frank', 'White'), (2005, 'Linda', 'Black');
- 创建一个分区表 e 的非分区表副本 e2（删除其分区）：
mysql> create table e2 like e; Query OK, 0 rows affected (0.10 sec) mysql> select * from e2; Empty set (0.01 sec) mysql> alter table e2 remove partitioning; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table e2 \G; *************************** 1. row *************************** Table: e2 Create Table: CREATE TABLE `e2` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.03 sec)
- 使用 INFORMATION_SCHEMA.PARTITIONS 查看分区表 e 中的哪些分区包含行数据：
mysql> select partition_name, table_rows from information_schema.partitions where table_name = 'E'; +----------------+------------+ | partition_name | table_rows | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.03 sec)
- 使用 ALTER TABLE 语句将分区表 e 中的分区 p0 与非分区表 e2 交换：
mysql> alter table e exchange partition p0 with table e2; Query OK, 0 rows affected (0.05 sec)
- 通过分区交换，以前在分区表 e 的 p0 分区中的行记录不再存在：
mysql> select partition_name, table_rows from information_schema.partitions where table_name = 'E'; +----------------+------------+ | partition_name | table_rows | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.01 sec)
- 实际上，分区表 e 的 p0 分区中的行记录被交换至了非分区表 e2 中：
mysql> select * from e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 16 | Frank | White | +----+-------+-------+ 1 row in set (0.00 sec)
要与分区交换的非分区表不一定要求是非空的，从上述执行结果可知，表 e2 目前就存在一条记录。
- 首先在表 e 中插入一行新记录，通过选择小于 50 的 id 列值来确保此行存储在分区 p0 中：
mysql> insert into e values (41, 'Michael', 'Green'); Query OK, 1 row affected (0.00 sec) mysql> select partition_name, table_rows from information_schema.partitions where table_name = 'E'; +----------------+------------+ | partition_name | table_rows | +----------------+------------+ | p0 | 1 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.00 sec)
- 将分区表 e 中的分区 p0（该分区存在一条记录）与非分区表 e2（该表也存在一条记录）交换：
mysql> alter table e exchange partition p0 with table e2; Query OK, 0 rows affected (0.03 sec)
- 检查表 e 中分区 p0 的数据和表 e2 的数据，会发现行记录已发生交换：
mysql> select * from e; +------+-------+-------+ | id | fname | lname | +------+-------+-------+ | 16 | Frank | White | | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+-------+-------+ 4 rows in set (0.00 sec) mysql> select * from e2; +----+---------+-------+ | id | fname | lname | +----+---------+-------+ | 41 | Michael | Green | +----+---------+-------+ 1 row in set (0.00 sec)
注： 若非分区表为空，则会将分区表中对应分区的数据写入至非分区表中；若非分区表中存在数据且数据指向 ALTER TABLE 语句中对应的分区，则对应分区的数据和非分区表的数据会发生交换。
当非分区表中存在的数据并未指向 ALTER TABLE 语句中对应的分区，则会提示如下错误：
mysql> insert into e2 values (51, 'Ellen', 'McDonald'); Query OK, 1 row affected (0.00 sec) mysql> alter table e exchange partition p0 with table e2; ERROR 1737 (HY000): Found a row that does not match the partition
通过添加 WITHOUT VALIDATION 选项，上述语句才会执行成功（虽然会执行成功，但实际不会发生数据交换）：
mysql> alter table e exchange partition p0 with table e2 without validation; Query OK, 0 rows affected (0.03 sec) mysql> select * from e; +------+---------+----------+ | id | fname | lname | +------+---------+----------+ | 41 | Michael | Green | | 51 | Ellen | McDonald | | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+---------+----------+ 5 rows in set (0.00 sec) mysql> select * from e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 16 | Frank | White | +----+-------+-------+ 1 row in set (0.00 sec)
分区维护
在 MySQL 5.7 中，可以使用针对分区表的 SQL 语句执行分区维护任务。
- 重建分区
重建分区与删除存储在分区中的所有记录，然后重新插入它们具有相同的效果，这对于碎片整理非常有用。
mysql> create table t1 ( id int not null, fname varchar(30), lname varchar(30) ) partition by range(id) ( partition p0 values less than (50), partition p1 values less than (100) ); mysql> insert into e values (20, 'Jim' , 'Smith'), (30, 'Mary' , 'Jones'), (50, 'Frank', 'White'), (70, 'Linda', 'Black'); mysql> alter table t1 rebuild partition p0, p1; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t1 partition (p0) union all select * from t1 partition (p1); Empty set (0.00 sec)
注： 执行完重建分区的语句后，分区表中对应分区的数据将被清空。
- 优化分区
如果从分区表中删除了大量行记录，或者对具有可变长度行（即具有 VARCHAR、BLOB 或 TEXT 列）的分区表进行了许多更改，则可以使用 ALTER TABLE ... OPTIMIZE PARTITION 来回收任何未使用的空间并对分区数据文件进行碎片整理。
mysql> alter table t1 optimize partition p0, p1;
在给定分区上使用优化分区相当于在该分区上运行检查分区、分析分区和修复分区。
在执行 ALTER TABLE ... OPTIMIZE PARTITION 语句时，可能会提示如下信息：
mysql> alter table t1 optimize partition p0, p1; +----------+----------+----------+---------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+---------------------------------------------------------------------------------------------+ | study.t1 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | | study.t1 | optimize | status | OK | +----------+----------+----------+---------------------------------------------------------------------------------------------+ 2 rows in set (0.07 sec) mysql> select * from t1; Empty set (0.00 sec)
- 分区分析
读取并保存分区的键分布。
mysql> alter table t1 analyze partition p3;
- 分区修复
修补被破坏的分区。
mysql> alter table t1 repair partition p0, p1; +----------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+--------+----------+----------+ | study.t1 | repair | status | OK | +----------+--------+----------+----------+ 1 row in set (0.01 sec)
- 分区检查
可以使用几乎与对非分区表使用 CHECK TABLE 相同的方式检查分区。
mysql> alter table t1 check partition p0, p1; +----------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+----------+ | study.t1 | check | status | OK | +----------+-------+----------+----------+ 1 row in set (0.01 sec)
分区修剪
分区修剪背后的核心概念比较简单，该概念可以描述为 “不扫描没有匹配值的分区”。
- 创建表 T1，按 RANGE 分区
mysql> create table t1 ( fname varchar(50) not null, lname varchar(50) not null, region_code tinyint unsigned not null, dob date not null ) partition by range(region_code) ( partition p0 values less than (64), partition p1 values less than (128), partition p2 values less than (192), partition p3 values less than maxvalue );
- 假设希望从如下 SELECT 语句中获得结果：
mysql> select fname, lname, region_code, dob from t1 where region_code > 125 and region_code < 130;
从上述 SQL 语句 region_code > 125 and region_code < 130 可知，应该返回的行都不在分区 p0 或 p3 中；也就是说，我们只需要在分区 p1 和 p2 中搜索就可以找到匹配的行。通过这样做，查找匹配行的时间和精力可能要比扫描表中所有分区所需的时间和精力要少的多。
每当 WHERE 条件可以简化为以下两种情况之一时，优化器就可以执行分区修剪：
- partition_column = constant
- partition_column IN (constant1, constant2, ..., constantN)
在第一种情况下，优化器只需计算给定值的分区表达式，确定哪个分区包含该值，并仅扫描此分区。在许多情况下，等号可以替换为另一个算术比较，包括 < 、> 、<= 、>= 和 <>。一些在 WHERE 子句中使用 BETWEEN 的查询也可以利用分区修剪。
在第二种情况下，优化器计算列表中每个值的分区表达式，创建匹配分区的列表，然后仅扫描此分区列表中的分区。
- 创建表 T2，按 RANGE 分区
mysql> create table t2 ( fname varchar(50) not null, lname varchar(50) not null, region_code tinyint unsigned not null, dob date not null ) partition by range(year(dob)) ( partition d0 values less than (1970), partition d1 values less than (1975), partition d2 values less than (1980), partition d3 values less than (1985), partition d4 values less than (1990), partition d5 values less than (2000), partition d6 values less than (2005), partition d7 values less than maxvalue );
- 以下语句可以利用分区修剪
mysql> select * from t2 where dob = '1982-06-23'; mysql> update t2 set region_code = 8 where dob between '1991-02-05' and '1997-04-25'; mysql> delete from t2 where dob >= '1984-06-21' and dob <= '1999-06-21';
分区选择
MySQL 5.7 支持显示选择分区和子分区，在执行语句时，应检查这些分区和子分区是否匹配给定 WHERE 条件的行。分区选择类似于分区修剪，因为都只检查特定分区的匹配。但在如下两个关键方面不同：
1. 分区选择要检查的分区由语句发出者指定，与分区修剪不同，分区修剪是自动的
2. 分区修剪仅适用于查询，但分区选择支持查询和许多 DML 语句，下面是一些支持显示分区选择的 SQL 语句
- SELECT
- DELETE
- INSERT
- REPLACE
- UPDATE
- LOAD DATA.
- LOAD XML.
创建表 employees，使用 RANGE 分区：
mysql> set @@sql_mode = ''; mysql> create table employees ( id int not null auto_increment primary key, fname varchar(25) not null, lname varchar(25) not null, store_id int not null, department_id int not null ) partition by range(id) ( partition p0 values less than (5), partition p1 values less than (10), partition p2 values less than (15), partition p3 values less than maxvalue ); mysql> insert into employees values ('', 'Bob' , 'Taylor' , 3, 2), ('', 'Frank' , 'Williams', 1, 2), ('', 'Ellen' , 'Johnson' , 3, 4), ('', 'Jim' , 'Smith' , 2, 4), ('', 'Mary' , 'Jones' , 1, 1), ('', 'Linda' , 'Black' , 2, 3), ('', 'Ed' , 'Jones' , 2, 1), ('', 'June' , 'Wilson' , 3, 1), ('', 'Andy' , 'Smith' , 1, 3), ('', 'Lou' , 'Waters' , 2, 4), ('', 'Jill' , 'Stone' , 1, 4), ('', 'Roger' , 'White' , 3, 2), ('', 'Howard' , 'Andrews' , 1, 2), ('', 'Fred' , 'Goldberg', 3, 3), ('', 'Barbara', 'Brown' , 2, 3), ('', 'Alice' , 'Rogers' , 2, 2), ('', 'Mark' , 'Morgan' , 3, 3), ('', 'Karen' , 'Cole' , 3, 2); mysql> select * from employees; +----+---------+----------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+---------+----------+----------+---------------+ | 1 | Bob | Taylor | 3 | 2 | | 2 | Frank | Williams | 1 | 2 | | 3 | Ellen | Johnson | 3 | 4 | | 4 | Jim | Smith | 2 | 4 | | 5 | Mary | Jones | 1 | 1 | | 6 | Linda | Black | 2 | 3 | | 7 | Ed | Jones | 2 | 1 | | 8 | June | Wilson | 3 | 1 | | 9 | Andy | Smith | 1 | 3 | | 10 | Lou | Waters | 2 | 4 | | 11 | Jill | Stone | 1 | 4 | | 12 | Roger | White | 3 | 2 | | 13 | Howard | Andrews | 1 | 2 | | 14 | Fred | Goldberg | 3 | 3 | | 15 | Barbara | Brown | 2 | 3 | | 16 | Alice | Rogers | 2 | 2 | | 17 | Mark | Morgan | 3 | 3 | | 18 | Karen | Cole | 3 | 2 | +----+---------+----------+----------+---------------+ 18 rows in set (0.00 sec)
通过如下语句，可以查看分区 p1 中存储了哪些行：
mysql> select * from employees partition (p1); +----+-------+--------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 5 | Mary | Jones | 1 | 1 | | 6 | Linda | Black | 2 | 3 | | 7 | Ed | Jones | 2 | 1 | | 8 | June | Wilson | 3 | 1 | | 9 | Andy | Smith | 1 | 3 | +----+-------+--------+----------+---------------+ 5 rows in set (0.00 sec)
任何针对分区表的有效查询都可以使用 PARTITION 选项重写，以将结果限制为一个或多个所需分区。可以使用 WHERE 条件、ORDER BY 和 LIMIT 选项等。还可以将聚合函数与 HAVING 和 GROUP BY 选项一起使用。
mysql> select * from employees partition (p0, p2) where lname like 'S%'; +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 4 | Jim | Smith | 2 | 4 | | 11 | Jill | Stone | 1 | 4 | +----+-------+-------+----------+---------------+ 2 rows in set (0.00 sec) mysql> select id, concat(fname, ' ', lname) as name from employees partition (p0) order by lname; +----+----------------+ | id | name | +----+----------------+ | 3 | Ellen Johnson | | 4 | Jim Smith | | 1 | Bob Taylor | | 2 | Frank Williams | +----+----------------+ 4 rows in set (0.00 sec) mysql> select store_id, count(department_id) as c from employees partition (p1, p2, p3) group by store_id having c > 4; +----------+---+ | store_id | c | +----------+---+ | 2 | 5 | | 3 | 5 | +----------+---+ 2 rows in set (0.00 sec)
当 PARTITION 选项与 DELETE 语句一起使用时，只有使用该选项列出的分区（子分区）才会检查要删除的行。任何其他分区都将被忽略。
mysql> select * from employees where fname like 'j%'; +----+-------+--------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 4 | Jim | Smith | 2 | 4 | | 8 | June | Wilson | 3 | 1 | | 11 | Jill | Stone | 1 | 4 | +----+-------+--------+----------+---------------+ 3 rows in set (0.00 sec) mysql> delete from employees partition (p0, p1) where fname like 'j%'; Query OK, 2 rows affected (0.00 sec) mysql> select * from employees where fname like 'j%'; +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11 | Jill | Stone | 1 | 4 | +----+-------+-------+----------+---------------+ 1 row in set (0.00 sec)
从上述执行结果可知，只删除了分区 p0 和 p1 中匹配 WHERE 条件的两行。另一个分区 p2 中的数据虽然与 WHERE 条件匹配，但未被删除。
分区的限制与局限性
分区键、主键、唯一键
- 当表中存在主键或唯一键时，分区键却并未使用，则会提示错误：
mysql> create table t1 ( col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1, col2) ) partition by hash(col3) partitions 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function mysql> create table t2 ( col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1), unique key (col3) ) partition by hash(col1 + col3) partitions 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
- 当表中存在主键或唯一键时，只有分区键含有这些列，才会创建成功：
mysql> create table t1 ( col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1, col2, col3) ) partition by hash(col3) partitions 4; Query OK, 0 rows affected (0.06 sec) mysql> create table t2 ( col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1, col3) ) partition by hash(col1 + col3) partitions 4; Query OK, 0 rows affected (0.06 sec)
- 当表中存在多个唯一键时，分区键必须是多个唯一键的共有字段，否则会提示错误：
mysql> create table t3 ( col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1, col2), unique key (col3) ) partition by hash(col1 + col3) partitions 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function mysql> create table t3 ( col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1, col2, col3), unique key (col3) ) partition by hash(col3) partitions 4; Query OK, 0 rows affected (0.06 sec)
- 如果表没有主键也没有唯一键，则只要列类型与分区类型兼容，那么就可以使用该列作为分区键：
mysql> create table t_no_pk ( c1 int, c2 int ) partition by range(c1) ( partition p0 values less than (10), partition p1 values less than (20), partition p2 values less than (30), partition p3 values less than (40) ); Query OK, 0 rows affected (0.05 sec)
- 可以使用以下任一 ALTER TABLE 语句向表 t_no_pk 添加主键：
mysql> alter table t_no_pk add primary key(c1); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t_no_pk drop primary key; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t_no_pk add primary key(c1, c2); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t_no_pk drop primary key; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0
- 但是当使用分区键作为主键时，会提示如下错误：
mysql> alter table t_no_pk add primary key(c2); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
与函数相关的分区限制
分区表达式中只允许使用以下列表中所示的 MySQL 函数：
- ABS()
- CEILING()
- DATEDIFF()
- DAY()
- DAYOFMONTH()
- DAYOFWEEK()
- DAYOFYEAR()
- EXTRACT()
- FLOOR()
- HOUR()
- MICROSECOND()
- MINUTE()
- MOD()
- MONTH()
- QUARTER()
- SECOND()
- TIME_TO_SEC()
- TO_DAYS()
- TO_SECONDS()
- UNIX_TIMESTAMP()
- WEEKDAY()
- YEAR()
- YEARWEEK()
在 MySQL 5.7 中，TO_DAYS()，TO_SECONDS()，YEAR() 和 UNIX_TIMESTAMP() 函数支持分区修剪。
当分区表达式中使用 CEILING() 或 FLOOR() 函数时，若分区键类型为 FLOAT 或 DECIMAL ，会提示如下错误：
mysql> create table t ( c float ) partition by list(floor(c)) ( partition p0 values in (1, 3, 5), partition p1 values in (2, 4, 6) ); ERROR 1491 (HY000): The PARTITION function returns the wrong type
改为 INT 类型后，创建成功：
mysql> create table t ( c int ) partition by list(floor(c)) ( partition p0 values in (1, 3, 5), partition p1 values in (2, 4, 6) ); Query OK, 0 rows affected (0.03 sec)
分区和锁定
对于 MyISAM 等存储引擎，在执行 DML 或 DDL 语句时实际执行表级锁，在旧版本 MySQL（5.6 及更低版本）中，影响分区表的语句会对整个表施加锁；也就是说，所有分区都被锁定，直到语句完成。
在 MySQL 5.7 中，分区修剪在许多情况下消除了不需要的锁，大多数从分区 MyISAM 表读取或更新的语句只会导致受影响的分区被锁定。
对于使用 InnoDB 等存储引擎影响分区表的语句，这些语句使用行级锁定，并且在分区修剪之前实际上不执行锁。
对 DML 语句的影响
- SELECT 语句，仅锁定那些实际需要读取的分区，这也适用于
SEELCT PARTITION - UPDATE 语句，当直接指定分区进行更新时，不会引起锁
- REPLACE 和 INSERT 语句，仅锁定具有要插入或替换行的分区。但是，如果为任何分区列生成
AUTO_INCREMENT值，则所有分区都将被锁定
受影响的 DDL 语句
- CREATE VIEW 不会导致任何锁
- ALTER TABLE ... EXCHANGE PARTITION，仅锁定交换表和交换分区
- ALTER TABLE ... TRUNCATE PARTITION，仅锁定要清空的分区
此外，ALTER TABLE 语句在表级别上使用元数据锁。
参考资料
原创文章，转载请注明出处：http://www.opcoder.cn/article/54/