mysql 表分区
表分区是将一个表的数据按照一定的规则⽔水平划分为不不同的逻辑块，并分别进⾏行行物理理存储，
这个规则就叫做分区函数，可以有不不同的分区规则
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 (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 (21) ); Query OK, 0 rows affected (0.15 sec) mysql> insert into employees values(1,'a','a',now(),now(),1,1),(2,'b','b',now(),now(),1,6); Query OK, 2 rows affected, 4 warnings (0.03 sec) Records: 2 Duplicates: 0 Warnings: 4 mysql> select * from employees where store_id=1; +----+-------+-------+------------+------------+----------+----------+ | id | fname | lname | hired | separated | job_code | store_id | +----+-------+-------+------------+------------+----------+----------+ | 1 | a | a | 2019-04-01 | 2019-04-01 | 1 | 1 | +----+-------+-------+------------+------------+----------+----------+ 1 row in set (0.01 sec) mysql> explain select * from employees where store_id=1; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from employees; +----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | employees | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | +----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from employees where store_id in (6,15); +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | p1,p2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
[root@centos7 A2]# ll /usr/local/mysql/data/A2 总用量 1040 -rw-r-----. 1 mysql mysql 114688 3月 27 21:06 B2.ibd -rw-r----- 1 mysql mysql 114688 4月 1 05:20 employees#P#p0.ibd -rw-r----- 1 mysql mysql 114688 4月 1 05:20 employees#P#p1.ibd -rw-r----- 1 mysql mysql 114688 4月 1 05:18 employees#P#p2.ibd -rw-r----- 1 mysql mysql 114688 4月 1 05:18 employees#P#p3.ibd -rw-r----- 1 mysql mysql 114688 4月 1 01:03 temp112.ibd -rw-r----- 1 mysql mysql 114688 4月 1 01:07 temp113.ibd -rw-r----- 1 mysql mysql 114688 4月 1 05:00 temp1.ibd -rw-r----- 1 mysql mysql 114688 4月 1 00:15 temp2.ibd -rw-r----- 1 mysql mysql 114688 4月 1 02:50 temp3.ibd -rw-r----- 1 mysql mysql 114688 4月 1 03:18 temp4.ibd -rw-r----- 1 mysql mysql 114688 4月 1 03:19 temp5.ibd -rw-r----- 1 mysql mysql 114688 4月 1 05:05 temp.ibd mysql> select * from employees partition(p0); +----+-------+-------+------------+------------+----------+----------+ | id | fname | lname | hired | separated | job_code | store_id | +----+-------+-------+------------+------------+----------+----------+ | 1 | a | a | 2019-04-01 | 2019-04-01 | 1 | 1 | +----+-------+-------+------------+------------+----------+----------+ 1 row in set (0.00 sec) mysql> explain update employees set fname='a' where store_id=4; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | UPDATE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
但当表中含有主键或唯一键时，则每个被⽤用作分区函数的字段必须是表中唯一键和主键的全部或⼀部分
，否则就⽆无法创建分区表，但是同时有主键和唯一键则必须时主键和唯一键都有；
mysql> CREATE TABLE tnp (id INT NOT NULL AUTO_INCREMENT, -> ref BIGINT NOT NULL, -> name VARCHAR(255), -> PRIMARY KEY pk (id), -> UNIQUE KEY uk (ref) ) -> PARTITION BY RANGE (ref) -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11)); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function mysql> CREATE TABLE tnp ( -> id INT NOT NULL AUTO_INCREMENT, -> ref BIGINT NOT NULL, -> name VARCHAR(255), -> PRIMARY KEY pk (id), -> UNIQUE KEY uk (ref) ) -> PARTITION BY RANGE (id) -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11)); ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function mysql> CREATE TABLE tnp ( -> id INT NOT NULL AUTO_INCREMENT, -> ref BIGINT NOT NULL, -> name VARCHAR(255), -> PRIMARY KEY pk (id)) -> PARTITION BY RANGE (id) -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11)); Query OK, 0 rows affected (0.12 sec) mysql> drop table tnp; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE tnp (id INT NOT NULL , -> ref BIGINT NOT NULL, -> name VARCHAR(255), -> PRIMARY KEY pk (id,ref), -> UNIQUE KEY uk (ref) ) -> PARTITION BY RANGE (ref) -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11)); Query OK, 0 rows affected (0.03 sec)
表分区的主要优势在于：
可以允许在一个表里存储更多的数据，突破磁盘限制或者文件系统限制
对于从表里将过期或历史的数据移除在表分区很容易实现，只要将对应的分区移除即可
对某些查询和修改语句来说，可以自动将数据范围缩小到一个或几个表分区上，优化语句句执
行效率。而且可以通过显示指定表分区来执行语句，比如SELECT * FROM t PARTITION
(p0,p1) WHERE c < 5
表分区类型分为：
• RANGE表分区：范围表分区，按照一定的范围值来确定每个分区包含的数据
• LIST表分区：列列表表分区，按照一个一个确定的值来确定每个分区包含的数据
• HASH表分区：哈希表分区，按照一个⾃自定义的函数返回值来确定每个分区包含的数据,取模（取余数)
• KEY表分区 ：key表分区，与哈希表分区类似，只是⽤用MySQL⾃自⼰己的HASH函数来确定每个分区包含的数据
• 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 (21) ); 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 (store_id) -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (16), -> PARTITION p2 VALUES LESS THAN (11), PARTITION p3 VALUES LESS THAN (21) ); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition 范围必须是递增的 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 (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 (21) ); Query OK, 0 rows affected (0.06 sec)
mysql> insert into employees values(1,'a','a',now(),now(),1,21); ERROR 1526 (HY000): Table has no partition for value 21 mysql> CREATE TABLE employees2( 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 (name) -> ( PARTITION p0 VALUES LESS THAN ('a'), PARTITION p1 VALUES LESS THAN ('b'), -> PARTITION p2 VALUES LESS THAN ('c'), PARTITION p3 VALUES LESS THAN ('d') ); ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT 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 (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 -> ); Query OK, 0 rows affected (0.12 sec) mysql> show create table employees; +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | employees | CREATE TABLE `employees` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job_code` int(11) NOT NULL, `store_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`store_id`) (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ | +-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into employees values(1,'a','a',now(),now(),1,21); Query OK, 1 row affected, 2 warnings (0.03 sec) mysql> insert into employees values(1,'a','a',now(),now(),1,16); Query OK, 1 row affected, 2 warnings (0.02 sec) mysql> select * from employees partition(p3); +----+-------+-------+------------+------------+----------+----------+ | id | fname | lname | hired | separated | job_code | store_id | +----+-------+-------+------------+------------+----------+----------+ | 1 | a | a | 2019-04-01 | 2019-04-01 | 1 | 21 | | 1 | a | a | 2019-04-01 | 2019-04-01 | 1 | 16 | +----+-------+-------+------------+------------+----------+----------+ 2 rows in set (0.00 sec) MAXVALUE关键词的作⽤用是表示可能的最⼤大值，所以任何store_id>=16的数据都会被写⼊入到p3分区⾥里里
分区函数中也可以使⽤用表达式
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, 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 ); Query OK, 0 rows affected (0.11 sec)
对timestamp字段类型可以使⽤用的表达式⽬目前仅有unix_timestamp
mysql> create table temp(tstamp timestamp) partition by range(year(tstamp))(partition p0 values less than(2017)); • ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed mysql> create table temp(tstamp datetime) partition by range(year(tstamp))(partition p0 values less than(2017)); • Query OK, 0 rows affected (0.01 sec)
LIST表分区：列列表表分区，按照一个一个确定的值来确定每个分区包含的数据
通过PARTITION BY LIST(expr)分区函数表达式必须返回整数，取值范围通过VALUES IN (value_list)定义
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, 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)); Query OK, 0 rows affected (0.13 sec) mysql> insert into employees values(1,'a','a',now(),now(),1,5); Query OK, 1 row affected, 2 warnings (0.08 sec) mysql> insert into employees values(2,'a','a',now(),now(),1,13); Query OK, 1 row affected, 2 warnings (0.05 sec) mysql> select * from employees partition(pNorth); +----+-------+-------+------------+------------+----------+----------+ | id | fname | lname | hired | separated | job_code | store_id | +----+-------+-------+------------+------------+----------+----------+ | 1 | a | a | 2019-04-01 | 2019-04-01 | 1 | 5 | +----+-------+-------+------------+------------+----------+----------+ 1 row in set (0.00 sec) mysql> mysql> select * from employees partition(pWest); +----+-------+-------+------------+------------+----------+----------+ | id | fname | lname | hired | separated | job_code | store_id | +----+-------+-------+------------+------------+----------+----------+ | 2 | a | a | 2019-04-01 | 2019-04-01 | 1 | 13 | +----+-------+-------+------------+------------+----------+----------+ 1 row in set (0.00 sec)
对List表分区来说，没有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有定义的取值则会报错
同样，当有主键或者唯一键存在的情况下，分区函数字段需要包含在主键或唯⼀键中
对range和list表分区来说，分区函数可以包含多个字段
分区多字段函数(column partition)所涉及的字段类型可以包括：
TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
DATE and DATETIME.
CHAR, VARCHAR, BINARY, and VARBINARY.
其他的字段类型都不不⽀支持
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, store_name varchar(20) ) -> PARTITION BY LIST(store_name) -> ( PARTITION pNorth VALUES IN ('a','b'), -> PARTITION pEast VALUES IN ('c','d')); ERROR 1697 (HY000): VALUES value for partition 'pNorth' must have type INT mysql> create table customer -> (id int, -> province varchar(20)) -> partition by list COLUMNS(province) -> (partition pNorthwest values in ('xinjiang','xizang','qinhai','ningxia','shanxi'), -> partition pNorthest values in ('heilongjiang','jilin','liaoning'), -> partition pSouthwest values in ('sichuan','yunnan','guangxi') -> ); Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE rcx (a INT, b INT,
-> c CHAR(3), d INT)
-> PARTITION BY RANGE COLUMNS(a,b,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));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into rcx values(4,5,'abc',1),(5,9,'abc',1),(4,11,'ggg',1),(5,11,'abc',1),(6,2, 'abc',1);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from rcx partition (p0);
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 4 | 5 | abc | 1 |
| 5 | 9 | abc | 1 |
| 4 | 11 | ggg | 1 |
+------+------+------+------+
3 rows in set (0.00 sec)
mysql> select * from rcx partition (p1);
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 5 | 11 | abc | 1 |
| 6 | 2 | abc | 1 |
+------+------+------+------+
2 rows in set (0.01 sec)
mysql> select (5,9)<(5,10);
+--------------+
| (5,9)<(5,10) |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select (5,9,'abc')> (5,10,'ggg');
+---------------------------+
| (5,9,'abc')> (5,10,'ggg') |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
mysql> mysql> select * from information_schema.partitions where table_name='rcx';
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+----------------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+----------------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+
| def | A2 | rcx | p0 | NULL | 1 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | 5,10,'ggg' | 3 | 5461 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | 2019-04-01 06:58:17 | NULL | NULL | | default | NULL |
| def | A2 | rcx | p1 | NULL | 2 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | 10,20,'mmm' | 2 | 8192 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | 2019-04-01 06:58:17 | NULL | NULL | | default | NULL |
| def | A2 | rcx | p2 | NULL | 3 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | 15,30,'sss' | 0 | 0 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | NULL | NULL | NULL | | default | NULL |
| def | A2 | rcx | p3 | NULL | 4 | NULL | RANGE COLUMNS | NULL | `a`,`b`,`c` | NULL | MAXVALUE,MAXVALUE,MAXVALUE | 0 | 0 | 16384 | 0 | 0 | 0 | 2019-04-01 06:56:11 | NULL | NULL | NULL | | default | NULL |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+----------------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+
4 rows in set (0.01 sec)
mysql> select partition_name,table_rows from information_schema.partitions where table_name='rcx';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 3 |
| p1 | 2 |
| p2 | 0 |
| p3 | 0 |
+----------------+------------+
4 rows in set (0.00 sec)
mysql> CREATE TABLE rcf (a INT, b INT, c INT)
-> PARTITION BY RANGE COLUMNS(a,b,c) (
-> PARTITION p0 VALUES LESS THAN (0,25,50),
-> PARTITION p1 VALUES LESS THAN (20,20,100),
-> PARTITION p2 VALUES LESS THAN (10,30,50),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
哈希表分区，按照一个⾃自定义的函数返回值来确定每个分区包含的数据，这个⾃自定义函数也可以仅仅是一个字段名字
• 通过PARTITION BY HASH (expr)⼦子句句来表达哈希表分区，其中的expr表达式必须返回一个
整数，基于分区个数的取模（%）运算。根据余数插⼊入到指定的分区
• 对哈希表分区来说只需要定义分区的个数，其他的事情由内部完成
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, store_id INT ) -> PARTITION BY HASH(store_id) -> PARTITIONS 4; Query OK, 0 rows affected (0.06 sec) mysql> select mod(7,4); +----------+ | mod(7,4) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql> CREATE TABLE ts (id INT, purchased DATE) -> PARTITION BY RANGE( YEAR(purchased) ) -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) -> SUBPARTITIONS 2 -> ( PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (2000), -> PARTITION p2 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.14 sec) mysql> select partition_name,subpartition_name from information_schema.partitions where table_name='ts'; +----------------+-------------------+ | PARTITION_NAME | SUBPARTITION_NAME | +----------------+-------------------+ | p0 | p0sp0 | | p0 | p0sp1 | | p1 | p1sp0 | | p1 | p1sp1 | | p2 | p2sp0 | | p2 | p2sp1 | +----------------+-------------------+ 6 rows in set (0.00 sec)
mysql> insert into ts values(1,now());
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> insert into ts values(2,'1980-01-01');
Query OK, 1 row affected (0.10 sec)
mysql> select * from ts partition(p1);
Empty set (0.00 sec)
mysql> select * from ts partition(p0);
+------+------------+
| id | purchased |
+------+------------+
| 2 | 1980-01-01 |
+------+------------+
1 row in set (0.00 sec)
mysql> select * from ts partition(p2);
+------+------------+
| id | purchased |
+------+------------+
| 1 | 2019-04-01 |
+------+------------+
1 row in set (0.00 sec)
mysql> select * from ts partition(p0sp1);
Empty set (0.00 sec)
mysql> select * from ts partition(p0sp0);
+------+------------+
| id | purchased |
+------+------------+
| 2 | 1980-01-01 |
+------+------------+
1 row in set (0.00 sec)
mysql> select purchased,TO_DAYS(purchased) from ts;
+------------+--------------------+
| purchased | TO_DAYS(purchased) |
+------------+--------------------+
| 1980-01-01 | 723180 |
| 2019-04-01 | 737515 |
+------------+--------------------+
2 rows in set (0.00 sec)
mysql> select * from ts partition(p0sp0);
+------+------------+
| id | purchased |
+------+------------+
| 2 | 1980-01-01 |
+------+------------+
1 row in set (0.00 sec)
[root@centos7 A2]# ll ts*
-rw-r----- 1 mysql mysql 114688 4月 1 07:34 ts#P#p0#SP#p0sp0.ibd
-rw-r----- 1 mysql mysql 114688 4月 1 07:30 ts#P#p0#SP#p0sp1.ibd
-rw-r----- 1 mysql mysql 114688 4月 1 07:30 ts#P#p1#SP#p1sp0.ibd
-rw-r----- 1 mysql mysql 114688 4月 1 07:30 ts#P#p1#SP#p1sp1.ibd
-rw-r----- 1 mysql mysql 114688 4月 1 07:30 ts#P#p2#SP#p2sp0.ibd
-rw-r----- 1 mysql mysql 114688 4月 1 07:34 ts#P#p2#SP#p2sp1.ibd
[root@centos7 A2]# pwd
/usr/local/mysql/data/A2
子分区使用限制
子表分区必须是范围/列表分区+哈希/key子表分区的组合
mysql> explain select * from ts where purchased='2019-04-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ts | p2_p2sp1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
不同的表分区对NULL值的处理方式不同
• 对范围表分区来说，如果插⼊入的是NULL值，则将数据放到最小的分区表⾥
mysql> 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 ); Query OK, 0 rows affected (0.08 sec) mysql> insert into t1 values(-1,'a'); Query OK, 1 row affected (0.02 sec) mysql> insert into t1 values(null,'b'); Query OK, 1 row affected (0.02 sec) mysql> select * from t1 partition(p0); +------+------+ | c1 | c2 | +------+------+ | -1 | a | | NULL | b | +------+------+ 2 rows in set (0.00 sec)
对list表分区来说，⽀支持NULL值的唯⼀一情况就是某个分区的允许值中包含NULL
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.23 sec) mysql> insert into ts1 values(null,'a') -> ; ERROR 1526 (HY000): Table has no partition for value 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.09 sec) mysql> insert into ts2 values(null,'a'); Query OK, 1 row affected (0.03 sec) mysql> select * from ts2 partition(p3); +------+------+ | c1 | c2 | +------+------+ | NULL | a | +------+------+ 1 row in set (0.01 sec)
对哈希表分区和Key表分区来说，NULL值会被当成0值对待
mysql> create table th(c1 int,c2 varchar(20)) -> partition by hash(c1) -> partitions 2; Query OK, 0 rows affected (0.04 sec) mysql> insert into th values(null,'a'); Query OK, 1 row affected (0.06 sec) mysql> select * from th partition(p0); +------+------+ | c1 | c2 | +------+------+ | NULL | a | +------+------+ 1 row in set (0.00 sec) mysql> select * from th partition(p1); Empty set (0.01 sec)
通过alter table命令可以执⾏行行增加，删除，重新定义，合并或者拆分表分区的管理理动作
删除表分区的动作不不光会把分区删掉，也会把表分区⾥里里原来的数据给删除掉
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.26 sec) mysql> CREATE TABLE tr2 (id INT, name VARCHAR(50), purchased DATE); Query OK, 0 rows affected (0.06 sec) mysql> select * from tr2; Empty set (0.01 sec) mysql> delete from tr2 where purchased<'1990-01-01'; Query OK, 0 rows affected (0.00 sec) mysql> select * from tr2 where purchased<'1990-01-01'; Empty set (0.00 sec) mysql> insert into tr values(1, 'abc','1999-12-21'); Query OK, 1 row affected (0.08 sec) mysql> insert into tr values(1, 'abc','1989-12-21'); Query OK, 1 row affected (0.06 sec) mysql> alter table tr drop partition p0; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from tr; +------+------+------------+ | id | name | purchased | +------+------+------------+ | 1 | abc | 1999-12-21 | +------+------+------------+ 1 row in set (0.00 sec)
mysql> show create table tr;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr | CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) 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.00 sec)
在原分区上增加⼀一个表分区可以通过alter table … add partition语句句来完成
但对范围表分区来说，增加的表分区必须在尾部增加，在头部或者在中间增加都会失败
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.16 sec) mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010)); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table members; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | members | 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!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) 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 mysql> insert into members values(1,'a','a','1967-01-01'); Query OK, 1 row affected (0.02 sec) mysql> insert into members values(2,'a','a','1977-01-01'); Query OK, 1 row affected (0.03 sec)
为解决这个问题，可以使⽤用REORGANIZE命令
mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1985) ); ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range 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.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from members; +------+-------+-------+------------+ | id | fname | lname | dob | +------+-------+-------+------------+ | 1 | a | a | 1967-01-01 | | 2 | a | a | 1977-01-01 | +------+-------+-------+------------+ 2 rows in set (0.00 sec) mysql> select * from members partition(n1); +------+-------+-------+------------+ | id | fname | lname | dob | +------+-------+-------+------------+ | 2 | a | a | 1977-01-01 | +------+-------+-------+------------+ 1 row in set (0.00 sec) mysql> select * from members partition(n0); +------+-------+-------+------------+ | id | fname | lname | dob | +------+-------+-------+------------+ | 1 | a | a | 1967-01-01 | +------+-------+-------+------------+ 1 row in set (0.01 sec) mysql> show create table members; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | members | 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!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.01 sec)
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.07 sec)
mysql> ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tt;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`id` int(11) DEFAULT NULL,
`data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`data`)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB,
PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1980) );
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE members REORGANIZE PARTITION p0,n1 INTO ( PARTITION n2 VALUES LESS THAN (1980) );
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table members;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| members | 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`dob`))
(PARTITION n2 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)
缩小范围丢数据
mysql> ALTER TABLE members REORGANIZE PARTITION n2,p1,p2,p3 INTO ( PARTITION m0 VALUES -> LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2010)); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from members; +------+-------+-------+------------+ | id | fname | lname | dob | +------+-------+-------+------------+ | 1 | a | a | 1967-01-01 | | 2 | a | a | 1977-01-01 | +------+-------+-------+------------+ 2 rows in set (0.00 sec)
list
mysql> show create table tt; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tt | CREATE TABLE `tt` ( `id` int(11) DEFAULT NULL, `data` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY LIST (`data`) (PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB, PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB, PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB) */ | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8)); Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tt; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tt | CREATE TABLE `tt` ( `id` int(11) DEFAULT NULL, `data` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY LIST (`data`) (PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB, PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB, PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB, PARTITION np VALUES IN (4,8) ENGINE = InnoDB) */ | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
mysql> ALTER TABLE tt REORGANIZE PARTITION p1,np INTO -> ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) ); ); ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 mysql> insert into tt values(1,10),(2,5); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tt; +------+------+ | id | data | +------+------+ | 1 | 10 | | 2 | 5 | +------+------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE tt REORGANIZE PARTITION p0,p1 INTO ( PARTITION p0 VALUES IN (6, 18), PARTITION p1 -> VALUES in (5,15)); Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from tt; +------+------+ | id | data | +------+------+ | 2 | 5 | +------+------+ 1 row in set (0.00 sec)
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.20 sec) mysql> insert into clients values(1,'a','a','2017-01-01'),(2,'a','a','2017-02-01'),(3,'a','a','2017-03-01'),(4,'a','a','2017-04-01'); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE clients COALESCE PARTITION 4; Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select partition_name,table_rows from information_schema.partitions where table_name='clients'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 0 | | p4 | 0 | | p5 | 0 | | p6 | 0 | | p7 | 0 | +----------------+------------+ 8 rows in set (0.00 sec) mysql> ALTER TABLE clients ADD PARTITION PARTITIONS 6; Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table clients; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | clients | CREATE TABLE `clients` `id` int(11) DEFAULT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `signed` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (month(`signed`)) PARTITIONS 14 */ | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from clients; +------+-------+-------+------------+ | id | fname | lname | signed | +------+-------+-------+------------+ | 1 | a | a | 2017-01-01 | | 2 | a | a | 2017-02-01 | | 3 | a | a | 2017-03-01 | | 4 | a | a | 2017-04-01 | +------+-------+-------+------------+ 4 rows in set (0.00 sec) mysql> select * from tr; +------+------+------------+ | id | name | purchased | +------+------+------------+ | 1 | abc | 1999-12-21 | +------+------+------------+ 1 row in set (0.00 sec) mysql> select * from tr_history; +------+------+------------+ | id | name | purchased | +------+------+------------+ | 1 | a | 2000-01-01 | +------+------+------------+ 1 row in set (0.01 sec) mysql> desc tr; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | purchased | date | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc tr_history; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | purchased | date | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table tr_history modify name varchar(100); Query OK, 1 row affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table tr exchange partition p3 with table tr_history; ERROR 1736 (HY000): Tables have different definitions mysql> mysql> alter t_history modify name varchar(50); Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table tr exchange partition p1 with table tr_history; ERROR 1737 (HY000): Found a row that does not match the partition mysql> alter table tr exchange partition p1 with table tr_history WITHOUT VALIDATION; Query OK, 0 rows affected (0.20 sec) mysql> SELECT * FROM tr partition(p1); +------+------+------------+ | id | name | purchased | +------+------+------------+ | 1 | a | 2000-01-01 | +------+------+------------+ 1 row in set (0.01 sec) mysql> show create table tr; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tr | CREATE TABLE `tr` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) (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.00 sec) mysql> explain select * from tr ; +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tr | p1,p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select * from tr where purchased='2010-01-01'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tr | p5 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
