写在前面
在一些公司的规范中，就明确规定，不允许使用分区表，那么到底该不该使用分区表呢，本文就一起来看下。
1：分区表是什么
我们首先执行如下的语句，来创建一个分区表：
CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);创建完毕之后，我们看下在data下生成frm和ibd文件：
[root@localhost db3]# ll | egrep 'ibd|frm'
-rw-r----- 1 mysql mysql 8586 Sep 14 10:09 t.frm
-rw-r----- 1 mysql mysql 114688 Sep 14 10:09 t#P#p_2017.ibd
-rw-r----- 1 mysql mysql 114688 Sep 14 13:20 t#P#p_2018.ibd
-rw-r----- 1 mysql mysql 114688 Sep 14 13:19 t#P#p_2019.ibd
-rw-r----- 1 mysql mysql 114688 Sep 14 10:09 t#P#p_others.ibd可以看到只有frm文件，但是有4个idb文件，其名称格式是表名#P#分区名.ibd，和我们创建的分区一一对应，也就是说分区表对于server层来说是一张表，但是对于存储引擎层来说是4张表，关于此，我们可以通过如下的测试来证明，首先我们来证明对于存储引擎层是来说是4张表：
- 创建会话A，B
- 在会话A执行如下语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where ftime='2017-5-1' for update;
Empty set (0.01 sec)- 在会话B执行如下语句
mysql> insert into t values('2018-2-1', 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t values('2017-12-1', 1);
阻塞中show processlist如下：
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+--------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+--------------------------------------+
| 5 | root | localhost | db3 | Sleep | 62 | | NULL |
| 6 | root | localhost | db3 | Query | 17 | update | insert into t values('2017-12-1', 1) |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+--------------------------------------+
3 rows in set (0.00 sec)按道理会话A执行生成的间隙锁如下图：
而2018-2-1和2017-12-1都是在这间隙里的，理论上都应该被间隙锁阻塞，但是'2018-2-1'却没有，这是因为，'2018-2-1'是在分区p_2019对应的表上，所以不受影响，实际加锁如下图：
接下来我们再来看下对于server层这是一张表：
- 创建会话A，B
- 在会话A执行如下语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where ftime='2018-4-1';
+---------------------+------+
| ftime | c |
+---------------------+------+
| 2018-04-01 00:00:00 | 1 |
+---------------------+------+
1 row in set (0.00 sec)- 会话B执行操作
mysql> alter table t truncate partition p_2017;
阻塞show processlist结果如下：
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------+
| 5 | root | localhost | db3 | Sleep | 47 | | NULL |
| 6 | root | localhost | db3 | Query | 10 | Waiting for table metadata lock | alter table t truncate partition p_2017 |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------+
3 rows in set (0.00 sec)可以看到是阻塞在mdl 锁上了，这是因为会话A的查询操作会获取表的DML读锁，而不仅仅是分区p_2017的MDL读锁，而会话B的alter语句会获取表的MDL写锁，而MDL读锁和MDL写锁之间是互斥的，所以就会被阻塞，这样就证明了对于Server层来说分区表是一个张表。
2：分区策略
有两种，第一种是通用分区策略，第二种是本地分区策略，其中通用分区策略是myisam存储引擎使用的，在刚支持分区时就存在，代码实现的很粗糙，性能问题比较严重，生产环境不要使用。本地分区策略是InnoDB存储引擎在5.7.9版本引入的，如果在生产环境要使用分区的话，最低要使用该版本的MySQL，且存储引擎使用InnoDB，另外在该版本MyISAM的分区表已经被标记为即将弃用（deprecated）了，意思就是将会在后续的版本删除该功能，当前请使用其他的替代方案，果然在mysql的8.0版本就已经不允许创建MyISAM的分区表了，而只允许使用实现了本地分区策略的存储引擎，当前的存储引擎有InnoDB和NDB，而我们使用最多的是InnoDB。
3：分区表的使用场景
业务上需要根据某个业务指标来对数据进行分类，并基于这些数据来执行某些业务处理，比如按照年来查询数据的场景，就很适合使用分区表，加速查询，并且对于不使用的分区也可以很方便的使用alter table t drop partition …语法删除分区，速度快，对系统影响小（普通的delete产生数据空洞，undo等）。但是需要注意如下几个点：
1：选择InnoDB存储引擎
2：版本>=5.7.9
3：分区数不要过多4：分区类型
存储引擎如无特殊说明，均是InnoDB。
4.1：range
通过VALUES LESS THAN进行分区，可以直接指定某个字段如：
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 (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);也可以使用函数，如：
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,
store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);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)
);4.2：list
通过PARTITION BY LIST(expr)指定，仅支持整数，如：
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,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);这种方式如果是插入数据的数据没有目标分区则会报错，如：
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)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1526 (HY000): Table has no partition for value 3可以通过insert ignore避免报错：
mysql> INSERT ignore INTO h2 VALUES (3, 5);
Query OK, 0 rows affected, 1 warning (0.00 sec)values插入多行，如果是存在没有对应分区的也会报错，全部插入不成功，如：
mysql> TRUNCATE h2;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
ERROR 1526 (HY000): Table has no partition for value 6
mysql> select * from h2;
Empty set (0.00 sec)也可以通过insert ignore插入存在对应分区的，忽略不存在对应分区的，如：
mysql> TRUNCATE h2;
Query OK, 0 rows affected (0.03 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)4.3：hash
通过PARTITION BY HASH (expr)指定，然后通过partitions {partition_num}指定分区个数，如下：
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,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;具体行落在哪个分区，通过MOD(expr,{partition_num}),如下：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;然后插入数据'2005-09-15'则计算如下：
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1最终落在分区1。
4.4：key
类似于hash，但只需要指定key关键字即可，使用内部函数将主键或者唯一索引转换为整数，选择指定分区，定义方法可能如下：
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;基于主键生成2个分区。
CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;基于唯一键生成2个分区。
4.5：subpartition
对已有的分区继续分区。
如：
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
);这样在range的3个分区内部，再按照hash分区方式分成2个区，最终是3*2=6个分区。也可以按照如下方式定义：
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);5：分区对于NULL的处理
不同的分区方式对于NULL值的处理方式不尽相同，下面分别来看下。
5.1：range分区
range分区会将值为NULL的行存储到最小的分区，如下创建分区：
create database db4;use db4;
CREATE TABLE t1 (
c1 INT,
c2 VARCHAR(20)
)
PARTITION BY RANGE(c1) (
PARTITION p0 VALUES LESS THAN (0),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
CREATE TABLE t2 (
c1 INT,
c2 VARCHAR(20)
)
PARTITION BY RANGE(c1) (
PARTITION p0 VALUES LESS THAN (-5),
PARTITION p1 VALUES LESS THAN (0),
PARTITION p2 VALUES LESS THAN (10),
PARTITION p3 VALUES LESS THAN MAXVALUE
);查看分区信息：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'db4' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 16384 |
| t1 | p1 | 0 | 0 | 16384 |
| t1 | p2 | 0 | 0 | 16384 |
| t2 | p0 | 0 | 0 | 16384 |
| t2 | p1 | 0 | 0 | 16384 |
| t2 | p2 | 0 | 0 | 16384 |
| t2 | p3 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)可以看到每个分区的数据行数都是0，接下来我们在t1和t2插入数据：
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)然后查看分区数据信息：
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'db4' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 1 | 16384 | 16384 |
| t1 | p1 | 0 | 0 | 16384 |
| t1 | p2 | 0 | 0 | 16384 |
| t2 | p0 | 1 | 16384 | 16384 |
| t2 | p1 | 0 | 0 | 16384 |
| t2 | p2 | 0 | 0 | 16384 |
| t2 | p3 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)可以看到数据是存储到分区p0了。我们也可以通过删除分区的方式来证明这点，如下，先插入数据：
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> alter table t1 drop partition p0;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t2 drop partition p1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)基于函数的range分区如PARTITION BY RANGE( YEAR(dt) ),如果函数结果是NULL，行为同直接插入NULL。
5.2：list分区
如果是不存在通过PARTITION p2 VALUES IN显式设置NULL的则无法插入，如：
mysql> create database db5;
Query OK, 1 row affected (0.00 sec)
mysql> use db5;
Database changed
mysql> CREATE TABLE ts1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1526 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1526 (HY000): Table has no partition for value NULL如果是显式设置了NULL的分区值，则可以正常查询：
mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)查看数据插入的分区信息:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts1 | p0 | 0 | 0 | 16384 |
| ts1 | p1 | 0 | 0 | 16384 |
| ts1 | p2 | 0 | 0 | 16384 |
| ts2 | p0 | 0 | 0 | 16384 |
| ts2 | p1 | 0 | 0 | 16384 |
| ts2 | p2 | 0 | 0 | 16384 |
| ts2 | p3 | 1 | 16384 | 16384 |
| ts3 | p0 | 0 | 0 | 16384 |
| ts3 | p1 | 1 | 16384 | 16384 |
| ts3 | p2 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
10 rows in set (0.00 sec)可以看到插入了ts2表分区p3，定义为PARTITION p3 VALUES IN (NULL)，ts3表分区p1，定义为PARTITION p1 VALUES IN (1, 4, 7, NULL)。
5.3：key和hash分区
对于NULL的数据，会按照结果值为0处理，如下创建分区：
mysql> CREATE TABLE th (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY HASH(c1)
-> PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME = 'th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.01 sec)接下来插入值0和NULL来对比查看：
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from th;
+------+--------+
| c1 | c2 |
+------+--------+
| NULL | mothra |
| 0 | gigan |
+------+--------+
2 rows in set (0.00 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME = 'th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 2 | 8192 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)可以看到NULL和0插入到了相同的分区p0。
6：分区管理
- 修改分区
创建分区：
mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'trb3';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| trb3 | p0 | 0 | 0 | 16384 |
| trb3 | p1 | 0 | 0 | 16384 |
| trb3 | p2 | 0 | 0 | 16384 |
| trb3 | p3 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)修改分区：
mysql> ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'trb3';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| trb3 | p0 | 0 | 0 | 16384 |
| trb3 | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)- 删除分区
创建分区：
mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'trb3';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| trb3 | p0 | 0 | 0 | 16384 |
| trb3 | p1 | 0 | 0 | 16384 |
| trb3 | p2 | 0 | 0 | 16384 |
| trb3 | p3 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)删除分区：
mysql> alter table trb3 remove partitioning;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'trb3';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| trb3 | NULL | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
1 row in set (0.00 sec)- 删除分区数据
创建分区：
mysql> drop table trb3;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.01 sec)插入测试数据：
mysql> insert into trb3 values(1,'zhangsan','1988-09-09'),(2,'lisi','1999-09-09'),(3,'wangwu','2004-09-09');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from trb3;
+------+----------+------------+
| id | name | purchased |
+------+----------+------------+
| 1 | zhangsan | 1988-09-09 |
| 2 | lisi | 1999-09-09 |
| 3 | wangwu | 2004-09-09 |
+------+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'trb3';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| trb3 | p0 | 1 | 16384 | 16384 |
| trb3 | p1 | 0 | 0 | 16384 |
| trb3 | p2 | 1 | 16384 | 16384 |
| trb3 | p3 | 1 | 16384 | 16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)删除分区数据：
mysql> alter table trb3 truncate partition all;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'trb3';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| trb3 | p0 | 0 | 0 | 16384 |
| trb3 | p1 | 0 | 0 | 16384 |
| trb3 | p2 | 0 | 0 | 16384 |
| trb3 | p3 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
4 rows in set (0.00 sec)可以看到每个分区中的数据都变成0了。
6.1：RANGE and LIST Partitions
因为range和LIST二者类似，所以放在一起说明。
6.1.1：删除分区
如下准备数据：
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005),
-> PARTITION p4 VALUES LESS THAN (2010),
-> PARTITION p5 VALUES LESS THAN (2015)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO tr VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'alarm clock', '1997-11-05'),
-> (3, 'chair', '2009-03-10'),
-> (4, 'bookcase', '1989-01-10'),
-> (5, 'exercise bike', '2014-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'espresso maker', '2011-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '2006-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tr;
+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 4 | bookcase | 1989-01-10 |
| 6 | sofa | 1987-06-05 |
| 8 | aquarium | 1992-08-04 |
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
| 1 | desk organiser | 2003-10-15 |
| 3 | chair | 2009-03-10 |
| 9 | study desk | 2006-09-16 |
| 5 | exercise bike | 2014-05-09 |
| 7 | espresso maker | 2011-11-22 |
+------+----------------+------------+
10 rows in set (0.01 sec)通过如下方式查看分区p2的数据：
mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM tr partition (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)接下里我们删除分区p2：
mysql> alter table tr drop partition p2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0注意！！！：此时也会删除分区中的数据，如下：
mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
mysql> SELECT * FROM tr partition (p2);
ERROR 1735 (HY000): Unknown partition 'p2' in table 'tr'此时分区2就不存在了，如下：
mysql> show create table tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.01 sec)6.1.2：添加分区
准备如下分区表：
mysql> CREATE TABLE members (
-> id INT,
-> fname VARCHAR(25),
-> lname VARCHAR(25),
-> dob DATE
-> )
-> PARTITION BY RANGE( YEAR(dob) ) (
-> PARTITION p0 VALUES LESS THAN (1980),
-> PARTITION p1 VALUES LESS THAN (1990),
-> PARTITION p2 VALUES LESS THAN (2000)
-> );
Query OK, 0 rows affected (0.01 sec)增加分区p3：
mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION p0 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)注意，我们增加的分区p3是加在最后的，但是如果是加在最前面或者是中间将会报错，如下：
mysql> ALTER TABLE members
-> ADD PARTITION (
-> PARTITION n VALUES LESS THAN (1970));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition从错误提示中可以看到，要求分区必须是严格递增的，我们可以通过修改分区操作REORGANIZE PARTITION来绕过这个限制，实现我们的需求，如下：
mysql> ALTER TABLE members
-> REORGANIZE PARTITION p0 INTO (
-> PARTITION n0 VALUES LESS THAN (1970),
-> PARTITION n1 VALUES LESS THAN (1980)
-> );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` varchar(25) DEFAULT NULL,
`lname` varchar(25) DEFAULT NULL,
`dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)可以看到p0修改为了n0，n1。
对List增加分区：
mysql> CREATE TABLE tt (
-> id INT,
-> data INT
-> )
-> PARTITION BY LIST(data) (
-> PARTITION p0 VALUES IN (5, 10, 15),
-> PARTITION p1 VALUES IN (6, 12, 18)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0如果是新增的分区已经包含了已存在分区存在的值，则会报错，比如新增分区包含了分区p1中的12：
mysql> ALTER TABLE tt ADD PARTITION
-> (PARTITION np VALUES IN (4, 8, 12));
ERROR 1495 (HY000): Multiple definition of same constant in list partitioning我们可以通过先创建不包含12的分区，然后在通过REORGANIZE PARTITION合并分区的方式来实现需求，且这种方式不会丢失数据：
mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tt\G
*************************** 1. row ***************************
Table: tt
Create Table: CREATE TABLE `tt` (
`id` int(11) DEFAULT NULL,
`data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (data)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB,
PARTITION np VALUES IN (4,8) ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
-> PARTITION p1 VALUES IN (6, 18),
-> PARTITION np VALUES in (4, 8, 12)
-> );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0然后查看此时的分区情况：
mysql> show create table tt\G
*************************** 1. row ***************************
Table: tt
Create Table: CREATE TABLE `tt` (
`id` int(11) DEFAULT NULL,
`data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (data)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
PARTITION p1 VALUES IN (6,18) ENGINE = InnoDB,
PARTITION np VALUES IN (4,8,12) ENGINE = InnoDB) */
1 row in set (0.00 sec)可以看到已经修改成功了。
6.1.3：修改分区类型
创建表：
mysql> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(50) NOT NULL,
-> lname VARCHAR(50) NOT NULL,
-> hired DATE NOT NULL
-> )
-> PARTITION BY RANGE( YEAR(hired) ) (
-> PARTITION p1 VALUES LESS THAN (1991),
-> PARTITION p2 VALUES LESS THAN (1996),
-> PARTITION p3 VALUES LESS THAN (2001),
-> PARTITION p4 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.00 sec)修改分区类型：
mysql> ALTER TABLE employees
-> PARTITION BY HASH( YEAR(hired) )
-> PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`fname` varchar(50) NOT NULL,
`lname` varchar(50) NOT NULL,
`hired` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH ( YEAR(hired))
PARTITIONS 4 */6.2：Management of HASH and KEY Partitions
不支持删除分区，因为都是隐式创建的，我们并不知道具体的分区信息，但是可以使用ALTER TABLE ... COALESCE PARTITION来合并分区，比如有如下的分区表：
mysql> CREATE TABLE clients (
-> id INT,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> signed DATE
-> )
-> PARTITION BY HASH( MONTH(signed) )
-> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT count(*) "partition total num" FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'clients';
+---------------------+
| partition total num |
+---------------------+
| 12 |
+---------------------+
1 row in set (0.00 sec)可以看到此时分区数时12个，然后通过如下语句修改分区数:
mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) "partition total num" FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db5' AND TABLE_NAME LIKE 'clients';
+---------------------+
| partition total num |
+---------------------+
| 8 |
+---------------------+
1 row in set (0.00 sec)然而查看分区数的结果，并不是4，而是8，这是因为COALESCE PARTITION后面的数字代表的意思是将其指定的分区个数的数据合并到其他的分区中，即指定的是要减少的分区数，所以这里的结果就是8。
6.3：查看分区信息
6.3.1：show create table
mysql> CREATE TABLE `trb2` (
-> `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) */
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table trb2\G
*************************** 1. row ***************************
Table: trb2
Create Table: CREATE TABLE `trb2` (
`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)6.3.2：explain查看用到的分区
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');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> explain select * from trb1 where id in(2,6);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | trb1 | p0,p1 | ALL | NULL | NULL | NULL | NULL | 6 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)从partitions列中的p0,p1可以看出使用了p0分区和p1分区。
6.3.3：通过information_schema.partitions
drop tbale trb1;
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> SELECT table_schema,TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS where table_schema='db6' and table_name='trb1';
+--------------+------------+----------------+------------+----------------+-------------+
| table_schema | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+--------------+------------+----------------+------------+----------------+-------------+
| db6 | trb1 | p0 | 2 | 8192 | 16384 |
| db6 | trb1 | p1 | 4 | 4096 | 16384 |
| db6 | trb1 | p2 | 2 | 8192 | 16384 |
| db6 | trb1 | p3 | 2 | 8192 | 16384 |
+--------------+------------+----------------+------------+----------------+-------------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
