一、MySQL分区简介
数据库分区
数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。
MYSQL的分区主要有两种形式：水平分区和垂直分区
水平分区（HorizontalPartitioning）
这种形式的分区是对根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。
所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份，日期等。
垂直分区（VerticalPartitioning）
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。
可以用 showvariables like '%partition%';
命令查询当前的mysql数据库版本是否支持分区。
分区的作用：数据库性能的提升和简化数据管理
在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。
分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不用手工干预。
mysql从5.1版本开始支持分区。每个分区的名称是不区分大小写。同个表中的分区表名称要唯一。
二、MySQL分区类型
根据所使用的不同分区规则可以分成几大分区类型。
RANGE 分区：
基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：
基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY
分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区：
基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。
三、创建分区
1. range分区
行数据基于一个给定的连续区间的列值放入分区。
CREATE TABLE `test_11` (
`id` int(11) NOT NULL,
`t` date NOT NULL,
PRIMARY KEY (`id`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(t))
(PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB);123456789
然后插入4条数据：
insert into test_11 values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824");1
然后查看information下partitions对分区别信息的统计：
select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="mysql_test" and table_name="test_11";
+-----------+--------+
| 分区 | 行数 |
+-----------+--------+
| p20170801 | 1 |
| p20170901 | 3 |
+-----------+--------+
2 rows in set (0.00 sec)12345678
可以看出分区p20170801插入1行数据，p20170901插入的3行数据。
可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换，然后分区。
下面是对已有表数据分区
ALTER TABLE sy_login_log PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (500),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN MAXVALUE );如果报错：
A PRIMARY KEY must include all columns in the table's partitioning function
这是因为 MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括。
解决办法：
顺应MYSQL的要求，就把分区字段加入到主键中，组成复合主键
2. list分区
和range分区一样，只是list分区面向的是离散的值
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.11 sec)123456789
与RANGE分区的情况不同，没有“catch-all”，如MAXVALUE; 分区表达式的所有预期值应在PARTITION … VALUES IN（…）子句中涵盖。 包含不匹配的分区列值的INSERT语句失败并显示错误，如此示例所示：
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 312
3. hash分区
根据用户自定义表达式的返回值来进行分区，返回值不能为负数
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;123
如果你插入col3的数值为’2005-09-15’，那么根据以下计算来选择插入的分区：
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1123
4. key分区
根据MySQL数据库提供的散列函数进行分区
CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;1234567
KEY仅列出零个或多个列名称。 用作分区键的任何列必须包含表的主键的一部分或全部，如果该表具有一个。 如果没有列名称作为分区键，则使用表的主键（如果有）。如果没有主键，但是有一个唯一的键，那么唯一键用于分区键。但是，如果唯一键列未定义为NOT NULL，则上一条语句将失败。
与其他分区类型不同，KEY使用的分区不限于整数或空值。 例如，以下CREATE TABLE语句是有效的：
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;12345
注意：对于key分区表，不能执行ALTER TABLE DROP PRIMARY KEY，因为这样做会生成错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table.
5. Column分区
COLUMN分区是5.5开始引入的分区功能，只有RANGE COLUMN和LIST COLUMN这两种分区；支持整形、日期、字符串；RANGE和LIST的分区方式非常的相似。
COLUMNS和RANGE和LIST分区的区别
1）针对日期字段的分区就不需要再使用函数进行转换了，例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。
2）COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。
column支持的数据类型：
1）所有的整型，float和decimal不支持
2）日期类型：date和datetime，其他不支持
3）字符类型：CHAR, VARCHAR, BINARY和VARBINARY，blob和text不支持
单列的column range分区mysql> show create table list_c;
CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
多列的column range分区mysql> show create table list_c;
CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */
单列的column list分区mysql> show create table list_c;
CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */
6. 子分区（组合分区）
在分区的基础上再进一步分区，有时成为复合分区;
MySQL数据库允许在range和list的分区上进行HASH和KEY的子分区。例如：
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
);
[root@mycat-3 ~]# ll /data/mysql_data_3306/mysql_test/ts*
-rw-r----- 1 mysql mysql 8596 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts.frm
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p0#SP#p0sp0.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p0#SP#p0sp1.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p1#SP#p1sp0.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p1#SP#p1sp1.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p2#SP#p2sp0.ibd
-rw-r----- 1 mysql mysql 98304 Aug 8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p2#SP#p2sp1.ibd
1234567891011121314151617
ts表根据purchased进行range分区，然后又进行了一次hash分区，最后形成了3*2个分区，可以从物理文件证实此分区方式。可以通过subpartition语法来显示指定子分区名称。
注意：每个子分区的数量必须相同；如果一个分区表的任何子分区已经使用subpartition，那么必须表明所有的子分区名称；每个subpartition子句必须包括子分区的一个名字；子分区的名字必须是一致的
另外，对于MyISAM表可以使用index directory和data direactory来指定各个分区的数据和索引目录，但是对于innodb表来说，因为该存储引擎使用表空间自动的进行数据和索引的管理，因此会忽略指定index和data的语法。
四、对已有的表进行分区
1. 可以使用ALTER TABLE来进行更改表为分区表
这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。
ALTER TABLE tbl_rtdata PARTITION BY RANGE (Month(fld_date))
(
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p_Dec VALUES LESS THAN MAXVALUE );
2. 新建一个和原来表一样的分区表，然后把数据从原表导出，接着倒入新表。
先创建分区表，然后导出原表数据，新表名称改为原表名，然后插入。
CREATE TABLE `news` (
`new_id` int(5) NOT NULL AUTO_INCREMENT,
`new_title` varchar(130) NOT NULL,
`new_type` int(2) NOT NULL,
`new_img` varchar(100) NOT NULL,
`new_desc` text NOT NULL,
`new_user` varchar(100) NOT NULL,
`add_time` varchar(13) NOT NULL,
PRIMARY KEY (`new_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
PARTITION BY RANGE (new_id)
(PARTITION p1 VALUES LESS THAN (200) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (400) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (600) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM);
表建完之后，只需要吧原表的数据拷贝进新建的表中就好了。
五、如何获取分区的相关信息
1. 通过 SHOW CREATE TABLE 语句来查看分区表的分区子句
譬如:mysql> show create table e/G
2. 通过 SHOW TABLE STATUS 语句来查看表是否分区对应Create_options字段
譬如:
mysql> show table status/G
*************************** 1. row ***************************
Name: e Engine: InnoDB Version: 10 Row_format: Compact Rows: 6 Avg_row_length: 10922 Data_length: 65536Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2015-12-07 22:26:06 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment:
3. 查看 INFORMATION_SCHEMA.PARTITIONS表
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='sy_login_log'; 
可以清楚的看到每个区有几条数据
4. 通过 EXPLAIN PARTITIONS SELECT 语句查看对于具体的SELECT语句,会访问哪个分区。
EXPLAIN SELECT * from sy_login_log
可以看到语句使用到了哪几个分区，查询条数等
六、分区表操作
CREATE TABLE t1 (
id INT,
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999)
);
1、ADD PARTITION （新增分区）
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
2、DROP PARTITION （删除分区）
ALTER TABLE t1 DROP PARTITION p0, p1;
3、TRUNCATE PARTITION（截取分区）
ALTER TABLE t1 TRUNCATE PARTITION p0;
ALTER TABLE t1 TRUNCATE PARTITION p1, p3;
4、COALESCE PARTITION（合并分区）
CREATE TABLE t2 (
name VARCHAR (30),
started DATE
)
PARTITION BY HASH( YEAR(started) )
PARTITIONS 6;
ALTER TABLE t2 COALESCE PARTITION 2;
5、REORGANIZE PARTITION（拆分/重组分区）
1）拆分分区
ALTER TABLE table ALGORITHM=INPLACE, REORGANIZE PARTITION;
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
2）重组分区
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
);
ALTER TABLE tbl_name
REORGANIZE PARTITION partition_list
INTO (partition_definitions);
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
6、ANALYZE 、CHECK PARTITION（分析与检查分区）
1）ANALYZE 读取和存储分区中值的分布情况
ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;
ALTER TABLE t1 ANALYZE PARTITION p1, p2;
2）CHECK 检查分区是否存在错误
ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;
7、REPAIR分区
修复被破坏的分区
ALTER TABLE t1 REPAIR PARTITION p0,p1;
8、OPTIMIZE
该命令主要是用于回收空闲空间和分区的碎片整理。对分区执行该命令,相当于依次对分区执行 CHECK PARTITION, ANALYZE PARTITION,REPAIR PARTITION命令。
譬如:
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
9、REBUILD分区
重建分区,它相当于先删除分区中的数据,然后重新插入。这个主要是用于分区的碎片整理。
ALTER TABLE t1 REBUILD PARTITION p0, p1;
10、EXCHANGE PARTITION（分区交换）
分区交换的语法如下:
ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt
其中,pt是分区表,p是pt的分区(注:也可以是子分区),nt是目标表。
其实,分区交换的限制还是蛮多的:
1） nt不能为分区表
2）nt不能为临时表
3）nt和pt的结构必须一致
4）nt不存在任何外键约束,即既不能是主键,也不能是外键。
5）nt中的数据不能位于p分区的范围之外。
具体可参考MySQL的官方文档
11、迁移分区（DISCARD 、IMPORT ）
ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
实验环境：（都是mysql5.7）
源库：192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的
目标库：192.168.2.100 mysql5.7.18 test下 （将zhangdb的emp表，导入到目标库的test schema下）
--：在源数据库中创建测试分区表emp_2，然后导入数据
MySQL [zhangdb]> CREATE TABLE emp_2(
id BIGINT unsigned NOT NULL AUTO_INCREMENT,
x VARCHAR(500) NOT NULL,
y VARCHAR(500) NOT NULL,
PRIMARY KEY(id)
)
PARTITION BY RANGE COLUMNS(id)
(
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (3000)
);
（接着创建存储过程，导入测试数据）
DELIMITER //
CREATE PROCEDURE insert_batch()
begin
DECLARE num INT;
SET num=1;
WHILE num < 3000 DO
IF (num%10000=0) THEN
COMMIT;
END IF;
INSERT INTO emp_2 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500));
SET num=num+1;
END WHILE;
COMMIT;
END //
DELIMITER ;
mysql> select TABLE_NAME,PARTITION_NAME from information_schema.partitions where table_schema='zhangdb';
+------------+----------------+
| TABLE_NAME | PARTITION_NAME |
+------------+----------------+
| emp | NULL |
| emp_2 | p1 |
| emp_2 | p2 |
| emp_2 | p3 |
+------------+----------------+
4 rows in set (0.00 sec)
mysql> select count(*) from emp_2 partition (p1);
+----------+
| count(*) |
+----------+
| 999 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from emp_2 partition (p2);
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from emp_2 partition (p3);
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
从上面可以看出，emp_2分区表已经创建完成，并且有3个子分区，每个分区都有一点数据。
--：在目标数据库中，创建emp_2表的结构，不要数据（要在源库，使用show create table emp_2\G 的方法 查看创建该表的sql）
MySQL [test]> CREATE TABLE `emp_2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`x` varchar(500) NOT NULL,
`y` varchar(500) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(id)
(PARTITION p1 VALUES LESS THAN (1000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB) */ ;
[root@localhost test]# ll
-rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p2.ibd
注意：
※约束条件、字符集等等也必须一致，建议使用show create table t1; 来获取创建表的SQL，否则在新服务器上导入表空间的时候会提示1808错误。
--：在目标数据库上，丢弃分区表的表空间
MySQL [test]> alter table emp_2 discard tablespace;
Query OK, 0 rows affected (0.12 sec)
[root@localhost test]# ll ---这时候在看，刚才的3个分区的idb文件都没有了
-rw-r----- 1 mysql mysql 8604 May 25 04:14 emp_2.frm
--：在源数据库上运行FLUSH TABLES … FOR EXPORT 锁定表并生成.cfg元数据文件，最后将cfg和ibd文件传输到目标数据库中
mysql> flush tables emp_2 for export;
Query OK, 0 rows affected (0.00 sec)
[root@localhost zhangdb]# scp emp_2* root@192.168.2.100:/mysql/data/test/ --将文件cp到目标数据库
mysql> unlock tables; ---最后将表的锁是否
--：在目标数据库中对文件授权，然后导入表空间查看数据是否完整可用
[root@localhost test]# chown mysql.mysql emp_2#*
MySQL [test]> alter table emp_2 import tablespace;
Query OK, 0 rows affected (0.96 sec)
MySQL [test]> select count(*) from emp_2;
+----------+
| count(*) |
+----------+
| 2999 |
+----------+
1 row in set (0.63 sec)
从上面的查看得知，分区表都已经导入到目标数据库中了，
另外，也可以将部分子分区导入到目标数据库中，（往往整个分区表会很大，可用只将需要用到的子分区导入到目标数据库中），
将部分子分区导入到目标数据库的方法是：
1）在创建目标表的时候，只需要创建要导入的分区即可，如： 只创建了p2 p3两个分区
CREATE TABLE `emp_2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`x` varchar(500) NOT NULL,
`y` varchar(500) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(id)
(
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB) */
2）从源库cp到目标库的文件，当然也就是这俩的，就不需要其他分区的了，
3）其他的操作方法都一样了。
扫一扫
362
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
