开发项目过程中总是提到优化的概念,本篇文章是对Mysql数据优化实践的一次探索旅程,简要介绍了分区原因,方法,分区表管理方法和一次简单的实践。
【为什么分区】
【分区方法】
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 list(deptno)
(
partition p1 values in (10,15),
partition p2 values in (20,25),
partition p3 values in (30,35)
);
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;
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;
【分区的管理操作方法】
删除分区:
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”来修补该分区。
【分区实践】
-- 创建分区表
CREATE TABLE part_tab
(c1 int NULL, c2 VARCHAR(30), 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) );
-- 创建没有分区表
CREATE TABLE nopart_tab
(c1 int NULL, c2 VARCHAR(30), c3 date not null)
-- 创建生成数据事物
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
set v = v + 1;
end while;
end;
insert into test.nopart_tab select * from test.part_tab
-- 查询分区情况
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='part_tab';
select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
执行时间:
执行未分区表查询语句:
select count(*) from nopart_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
【分区局限性】
1. MySQL分区处理NULL值的方式
如果分区键所在列没有notnull约束。
如果是range分区表,那么null行将被保存在范围最小的分区。
如果是list分区表,那么null行将被保存到list为0的分区。
在按HASH和KEY分区的情况下,任何产生NULL值的表达式mysql都视同它的返回值为0。
为了避免这种情况的产生,建议分区键设置成NOT NULL。
2. 分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。唯一的例外是当分
区类型为KEY分区的时候,可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。
3. 对分区表的分区键创建索引,那么这个索引也将被分区,分区键没有全局索引一说。
4. 只有RANG和LIST分区能进行子分区,HASH和KEY分区不能进行子分区。
5. 临时表不能被分区。