一、查看MySQL是否支持分区
1、MySQL5.6以及之前版本
show variables like '%partition%';
2、MySQL5.7
show plugins;
二、分区表的分类与限制
1、分区表分类
RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区:在MySQL 5.6版本中,只支持RANGE和LIST的子分区,且子分区的类型只能为HASH和KEY。
2、分区表限制
1)分区键必须包含在表的所有主键、唯一键中。
2)MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区,而不能根据表达式的值去过滤分区,即使这个表达式就是分区函数也不行。
3)最大分区数: 不使用NDB存储引擎的给定表的最大可能分区数为8192(包括子分区)。如果当分区数很大,但是未达到8192时提示 Got error … from storage engine: Out of resources when opening file,可以通过增加open_files_limit系统变量的值来解决问题,当然同时打开文件的数量也可能由操作系统限制。
4)不支持查询缓存: 分区表不支持查询缓存,对于涉及分区表的查询,它自动禁用。 查询缓存无法启用此类查询。
5)分区的innodb表不支持外键。
6)服务器SQL_mode影响分区表的同步复制。 主机和从机上的不同SQL_mode可能会导致sql语句; 这可能导致分区之间的数据分配给定主从位置不同,甚至可能导致插入主机上成功的分区表在从库上失败。 为了获得最佳效果,您应该始终在主机和从机上使用相同的服务器SQL模式。
7)ALTER TABLE … ORDER BY: 对分区表运行的ALTER TABLE … ORDER BY列语句只会导致每个分区中的行排序。
三、创建分区表
1、range分区
`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
+-----------+--------+
| 分区 | 行数 |
+-----------+--------+
| p20170801 | 1 |
| p20170901 | 3 |
+-----------+--------+
2 rows in set (0.00 sec)12345678
可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换,然后分区。
2、list分区
-> 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
ERROR 1525 (HY000): Table has no partition for value 312
3、hash分区
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;123
= MOD(2005,4)
= 1123
4、key分区
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;1234567
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;12345
5、Column分区
1)针对日期字段的分区就不需要再使用函数进行转换了,例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。
2)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、子分区(组合分区)
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
四、普通表转换为分区表
1、用alter table table_name partition by命令重建分区表
alter table jxfp_data_bak PARTITION BY KEY(SH) PARTITIONS 8;
五、分区表操作
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;
源库:192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的
目标库:192.168.2.100 mysql5.7.18 test下 (将zhangdb的emp表,导入到目标库的test schema下)
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 //
+------------+----------------+
| TABLE_NAME | PARTITION_NAME |
+------------+----------------+
| emp | NULL |
| emp_2 | p1 |
| emp_2 | p2 |
| emp_2 | p3 |
+------------+----------------+
4 rows in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 999 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
从上面可以看出,emp_2分区表已经创建完成,并且有3个子分区,每个分区都有一点数据。
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) */ ;
-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)
-rw-r----- 1 mysql mysql 8604 May 25 04:14 emp_2.frm
mysql> flush tables emp_2 for export;
Query OK, 0 rows affected (0.00 sec)
[root@localhost test]# chown mysql.mysql emp_2#*
Query OK, 0 rows affected (0.96 sec)
+----------+
| 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) */
3)其他的操作方法都一样了。
六、如何获取分区的相关信息
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表
4. 通过 EXPLAIN PARTITIONS SELECT 语句查看对于具体的SELECT语句,会访问哪个分区。
七、MySQL5.7对于partition表的改进
index condition pushdown:MySQL5.7.3分区表开始支持ICP;
load data:MySQL5.7开始使用缓存来实现性能提升,每个分区使用130KB缓冲区来实现这一点;
Per-partition索引缓存:MySQL5.7开始支持使用CACHE INDEX和LOAD INDEX INTO CACHE语句对分区的MyISAM表支持索引缓存;
FOR EXPORT选项(FLUSH TABLES):MySQL 5.7.4分区的InnoDB表开始支持FLUSH TABLES语句FOR EXPORT选项;
从MySQL 5.7.2开始,子分区支持ANALYZE,CHECK,OPTIMIZE,REPAIR和TRUNCATE操作;