mysql分区操作示例演示

上篇文章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分区操作示例演示
本站内容除特别标注外均为原创，欢迎转载，但请保留出处！

mysql分区操作示例演示

相关推荐

请填写您的昵称和邮箱！

标签

热门文章

近期文章