上篇文章Mysql分区介绍中对Mysql分区进行了介绍,这篇文章主要介绍如何创建分区
mysql分区表常用操作示例
1)创建range分区 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date, salary int ) partition by range(salary) ( partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than maxvalue ); 以员工工资为依据做范围分区。 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by range(year(birthdate)) ( partition p1 values less than (1980), partition p2 values less than (1990), partition p3 values less than maxvalue ); 以year(birthdate)表达式(计算员工的出生日期)作为范围分区依据。这里最值得注意的是表达式必须有返回值。 2)创建list分区 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by list(deptno) ( partition p1 values in (10), partition p2 values in (20), partition p3 values in (30) ); 以部门作为分区依据,每个部门做一分区。 3)创建hash分区 HASH分区主要用来确保数据在预先确定数目的分区中平均分布。 在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中; 而在HASH分区中,MySQL自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式, 以及指定被分区的表将要被分割成的分区数量。 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by hash(year(birthdate)) partitions 4; 4)创建key分区 按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式, 而KEY分区的哈希函数是由MySQL服务器提供,服务器使用其自己内部的哈希函数, 这些函数是基于与PASSWORD()一样的运算法则。 “CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。 它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区 只采用一个或多个列名的一个列表。 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by key(birthdate) partitions 4; 5)创建复合分区 range - hash(范围哈希)复合分区 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by range(salary) subpartition by hash(year(birthdate)) subpartitions 3 ( partition p1 values less than (2000), partition p2 values less than maxvalue ); range- key复合分区 create table emp( empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by range(salary) subpartition by key(birthdate) subpartitions 3 ( partition p1 values less than (2000), partition p2 values less than maxvalue ); list - hash复合分区 CREATE TABLE emp ( empno varchar(20) NOT NULL, empname varchar(20) , deptno int, birthdate date NOT NULL, salary int ) PARTITION BY list (deptno) subpartition by hash(year(birthdate)) subpartitions 3 ( PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) ); list - key 复合分区 CREATE TABLE empk ( empno varchar(20) NOT NULL, empname varchar(20) , deptno int, birthdate date NOT NULL, salary int ) PARTITION BY list (deptno) subpartition by key(birthdate) subpartitions 3 ( PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) ); 6)分区表的管理操作 删除分区: alter table emp drop partition p1; 不可以删除hash或者key分区。 一次性删除多个分区: alter table emp drop partition p1,p2; 增加分区: alter table emp add partition (partition p3 values less than (4000)); alter table empl add partition (partition p3 values in (40)); 分解分区: Reorganizepartition关键字可以对表的部分分区或全部分区进行修改, 并且不会丢失数据。分解前后分区的整体范围应该一致。 alter table te reorganize partition p1 into ( partition p1 values less than (100), partition p3 values less than (1000) ); ----不会丢失数据 合并分区: Merge分区:把2个分区合并为一个。 alter table te reorganize partition p1,p3 into (partition p1 values less than (1000)); ----不会丢失数据 重新定义hash分区表: Alter table emp partition by hash(salary) partitions 7; ----不会丢失数据 重新定义range分区表: Alter table emp partitionbyrange(salary) ( partition p1 values less than (2000), partition p2 values less than (4000) ); ----不会丢失数据 删除表的所有分区: Alter table emp removepartitioning;--不会丢失数据 重建分区: 这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。 它可用于整理分区碎片。 ALTER TABLE emp rebuild partitionp1,p2; 优化分区: 如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列) 作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间, 并整理分区数据文件的碎片。 ALTER TABLE emp optimize partition p1,p2; 分析分区: 读取并保存分区的键分布。 ALTER TABLE emp analyze partition p1,p2; 修补分区: 修补被破坏的分区。 ALTER TABLE emp repairpartition p1,p2; 检查分区: 可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。 ALTER TABLE emp CHECK partition p1,p2; 这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。 如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
mysql分区表的局限性
1.在5.1版本中分区表对唯一约束有明确的规定,每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。 CREATE TABLE emptt ( empno varchar(20) NOT NULL, empname varchar(20), deptno int, birthdate date NOT NULL, salary int , primary key (empno) ) PARTITION BY range (salary) ( PARTITION p1 VALUES less than (100), PARTITION p2 VALUES less than (200) ); 这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table's partitioning function; CREATE TABLE emptt ( empno varchar(20) NOT NULL , empname varchar(20) , deptno int(11), birthdate date NOT NULL, salary int(11) , primary key (empno,salary) ) PARTITION BY range (salary) ( PARTITION p1 VALUES less than (100), PARTITION p2 VALUES less than (200) ); 在主键中加入salary列就正常。 2.MySQL分区处理NULL值的方式 如果分区键所在列没有notnull约束。 如果是range分区表,那么null行将被保存在范围最小的分区。 如果是list分区表,那么null行将被保存到list为0的分区。 在按HASH和KEY分区的情况下,任何产生NULL值的表达式mysql都视同它的返回值为0。 为了避免这种情况的产生,建议分区键设置成NOT NULL。 3.分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。 唯一的例外是当分区类型为KEY分区的时候,可以使用其他类型的列作为 分区键( BLOB or TEXT 列除外)。 4.对分区表的分区键创建索引,那么这个索引也将被分区,分区键没有全局索引一说。 5.只有RANG和LIST分区能进行子分区,HASH和KEY分区不能进行子分区。 6.临时表不能被分区。
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='employees';
可以查看表是不是分区表
show table status;
显示扫描哪些分区,及他们是如何使用的
explain partitions select * from employees;
对Mysql分区进行性能测试
1. 创建两张表
/*表1*/ CREATE TABLE part_tab ( c1 INT DEFAULT NULL, c2 VARCHAR (30) DEFAULT NULL, c3 date NOT NULL ) PARTITION BY RANGE (YEAR(c3))( PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN (MAXVALUE) ); /*表2*/ CREATE TABLE no_part_tab( c1 int default NULL, c2 varchar(30) default NULL, c3 date not null);
2. 用存储过程插入100万条数据
CREATE PROCEDURE load_part_tab() begin declare v int default 0; loop1:while v < 1000000 do insert into part_tab values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); set v = v + 1; end while loop1; end insert into no_part_tab select * from part_tab;
3. 通过explain测试sql性能
explain partitions select count(*) from part_tab where c3 > '1995-01-01' and c3 < '1995-12-31'; explain partitions select count(*) from no_part_tab where c3 > '1995-01-01' and c3 < '1995-12-31';
本文固定链接:心知博客 » mysql分区操作示例演示
本站内容除特别标注外均为原创,欢迎转载,但请保留出处!