MYSQL分区表测试.docx
- 文档编号:28965324
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:17
- 大小:19.19KB
MYSQL分区表测试.docx
《MYSQL分区表测试.docx》由会员分享,可在线阅读,更多相关《MYSQL分区表测试.docx(17页珍藏版)》请在冰豆网上搜索。
MYSQL分区表测试
MYSQL分区表测试
MYSQL分区表测试
一、mysql分区简介
数据库分区
数据库分区是一种物理数据库设计技术。
虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应用来说分区完全是透明的。
MYSQL的分区主要有两种形式:
水平分区和垂直分区
水平分区(HorizontalPartitioning)
这种形式的分区是对根据表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。
所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。
水平分区一定要通过某个属性列来分割。
常见的比如年份,日期等。
垂直分区(VerticalPartitioning)
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应所有行。
可以用showvariableslike'%partition%';
命令查询当前的mysql数据库版本是否支持分区。
分区的作用:
数据库性能的提升和简化数据管理
在扫描操作中,mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。
分区技术使得数据管理变得简单,删除某个分区不会对另外的分区造成影响,分区有系统直接管理不用手工干预。
mysql从5.1版本开始支持分区。
每个分区的名称是不区分大小写。
同个表中的分区表名称要唯一。
二、mysql分区类型
根据所使用的不同分区规则可以分成几大分区类型。
RANGE分区:
基于属于一个给定连续区间的列值,把多行分配给分区。
LIST分区:
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区:
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。
这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
KEY
分区:
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。
必须有一列或多列包含整数值。
复合分区:
基于RANGE/LIST类型的分区表中每个分区的再次分割。
子分区可以是HASH/KEY等类型。
三、mysql分区表常用操作示例
以部门员工表为例子:
1)创建range分区
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedate,
salaryint
)
partitionbyrange(salary)
(
partitionp1valueslessthan(1000),
partitionp2valueslessthan(2000),
partitionp3valueslessthanmaxvalue
);
以员工工资为依据做范围分区。
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedatenotnull,
salaryint
)
partitionbyrange(year(birthdate))
(
partitionp1valueslessthan(1980),
partitionp2valueslessthan(1990),
partitionp3valueslessthanmaxvalue
);
以year(birthdate)表达式(计算员工的出生日期)作为范围分区依据。
这里最值得注意的是表达式必须有返回值。
2)创建list分区
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedatenotnull,
salaryint
)
partitionbylist(deptno)
(
partitionp1valuesin(10),
partitionp2valuesin(20),
partitionp3valuesin(30)
);
以部门作为分区依据,每个部门做一分区。
3)创建hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。
在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedatenotnull,
salaryint
)
partitionbyhash(year(birthdate))
partitions4;
4)创建key分区
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL服务器提供,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
“CREATETABLE...PARTITIONBYKEY”的语法规则类似于创建一个通过HASH分区的表的规则。
它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表。
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedatenotnull,
salaryint
)
partitionbykey(birthdate)
partitions4;
5)创建复合分区
range-hash(范围哈希)复合分区
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedatenotnull,
salaryint
)
partitionbyrange(salary)
subpartitionbyhash(year(birthdate))
subpartitions3
(
partitionp1valueslessthan(2000),
partitionp2valueslessthanmaxvalue
);
range-key复合分区
createtableemp
(empnovarchar(20)notnull,
empnamevarchar(20),
deptnoint,
birthdatedatenotnull,
salaryint
)
partitionbyrange(salary)
subpartitionbykey(birthdate)
subpartitions3
(
partitionp1valueslessthan(2000),
partitionp2valueslessthanmaxvalue
);
list-hash复合分区
CREATETABLEemp(
empnovarchar(20)NOTNULL,
empnamevarchar(20),
deptnoint,
birthdatedateNOTNULL,
salaryint
)
PARTITIONBYlist(deptno)
subpartitionbyhash(year(birthdate))
subpartitions3
(
PARTITIONp1VALUESin(10),
PARTITIONp2VALUESin(20)
)
;
list-key复合分区
CREATETABLEempk(
empnovarchar(20)NOTNULL,
empnamevarchar(20),
deptnoint,
birthdatedateNOTNULL,
salaryint
)
PARTITIONBYlist(deptno)
subpartitionbykey(birthdate)
subpartitions3
(
PARTITIONp1VALUESin(10),
PARTITIONp2VALUESin(20)
)
;
6)分区表的管理操作
删除分区:
altertableempdroppartitionp1;
不可以删除hash或者key分区。
一次性删除多个分区,altertableempdroppartitionp1,p2;
增加分区:
altertableempaddpartition(partitionp3valueslessthan(4000));
altertableempladdpartition(partitionp3valuesin(40));
分解分区:
Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。
分解前后分区的整体范围应该一致。
altertablete
reorganizepartitionp1into
(
partitionp1valueslessthan(100),
partitionp3valueslessthan(1000)
);----不会丢失数据
合并分区:
Merge分区:
把2个分区合并为一个。
altertablete
reorganizepartitionp1,p3into
(partitionp1valueslessthan(1000));
----不会丢失数据
重新定义hash分区表:
Altertableemppartitionbyhash(salary)partitions7;
----不会丢失数据
重新定义range分区表:
Altertableemppartitionbyrange(salary)
(
partitionp1valueslessthan(2000),
partitionp2valueslessthan(4000)
);----不会丢失数据
删除表的所有分区:
Altertableempremovepartitioning;--不会丢失数据
重建分区:
这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。
它可用于整理分区碎片。
ALTERTABLEemprebuildpartitionp1,p2;
优化分区:
如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTERTABLE...OPTIMIZEPARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。
ALTERTABLEempoptimizepartitionp1,p2;
分析分区:
读取并保存分区的键分布。
ALTERTABLEempanalyzepartitionp1,p2;
修补分区:
修补被破坏的分区。
ALTERTABLEemprepairpartitionp1,p2;
检查分区:
可以使用几乎与对非分区表使用CHECKTABLE相同的方式检查分区。
ALTERTABLEempCHECKpartitionp1,p2;
这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。
如果发生了这种情况,使用“ALTERTABLE...REPAIRPARTITION”来修补该分区。
【mysql分区表的局限性】
1.在5.1版本中分区表对唯一约束有明确的规定,每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。
CREATETABLEemptt(
empnovarchar(20)NOTNULL,
empnamevarchar(20),
deptnoint,
birthdatedateNOTNULL,
salaryint,
primarykey(empno)
)
PARTITIONBYrange(salary)
(
PARTITIONp1VALUESlessthan(100),
PARTITIONp2VALUESlessthan(200)
);
这样的语句会报错。
MySQLDatabaseError:
APRIMARYKEYmustincludeallcolumnsinthetable'spartitioningfunction;
CREATETABLEemptt(
empnovarchar(20)NOTNULL,
empnamevarchar(20),
deptnoint(11),
birthdatedateNOTNULL,
salaryint(11),
primarykey(empno,salary)
)
PARTITIONBYrange(salary)
(
PARTITIONp1VALUESlessthan(100),
PARTITIONp2VALUESlessthan(200)
);
在主键中加入salary列就正常。
2.MySQL分区处理NULL值的方式
如果分区键所在列没有notnull约束。
如果是range分区表,那么null行将被保存在范围最小的分区。
如果是list分区表,那么null行将被保存到list为0的分区。
在按HASH和KEY分区的情况下,任何产生NULL值的表达式mysql都视同它的返回值为0。
为了避免这种情况的产生,建议分区键设置成NOTNULL。
3.分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。
唯一的例外是当分
区类型为KEY分区的时候,可以使用其他类型的列作为分区键(BLOBorTEXT列除外)。
4.对分区表的分区键创建索引,那么这个索引也将被分区,分区键没有全局索引一说。
5.只有RANG和LIST分区能进行子分区,HASH和KEY分区不能进行子分区。
6.临时表不能被分区。
四、获取mysql分区表信息的几种方法
1.showcreatetable表名
可以查看创建分区表的create语句
2.showtablestatus
可以查看表是不是分区表
3.查看information_schema.partitions表
select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='test';
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
4.explainpartitionsselect语句
通过此语句来显示扫描哪些分区,及他们是如何使用的.
五、分区表性能比较
1.创建两张表:
part_tab(分区表),no_part_tab(普通表)
CREATETABLEpart_tab
(c1intdefaultNULL,c2varchar2(30)defaultNULL,c3datenotnull)
PARTITIONBYRANGE(year(c3))
(PARTITIONp0VALUESLESSTHAN(1995),
PARTITIONp1VALUESLESSTHAN(1996),
PARTITIONp2VALUESLESSTHAN(1997),
PARTITIONp3VALUESLESSTHAN(1998),
PARTITIONp4VALUESLESSTHAN(1999),
PARTITIONp5VALUESLESSTHAN(2000),
PARTITIONp6VALUESLESSTHAN(2001),
PARTITIONp7VALUESLESSTHAN(2002),
PARTITIONp8VALUESLESSTHAN(2003),
PARTITIONp9VALUESLESSTHAN(2004),
PARTITIONp10VALUESLESSTHAN(2010),
PARTITIONp11VALUESLESSTHAN(MAXVALUE));
CREATETABLEno_part_tab
(c1intdefaultNULL,c2varchar2(30)defaultNULL,c3datenotnull);
2.用存储过程插入800万条数据
CREATEPROCEDUREload_part_tab()
begin
declarevintdefault0;
whilev<8000000
do
insertintopart_tab
values(v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod3652));
setv=v+1;
endwhile;
end;
insertintono_part_tabselect*frompart_tab;
3.测试sql性能
查询分区表:
selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
+----------+
|count(*)|
+----------+
|795181|
+----------+
1rowinset(2.62sec)
查询普通表:
selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
+----------+
|count(*)|
+----------+
|795181|
+----------+
1rowinset(7.33sec)
分区表的执行时间比普通表少70%。
4.通过explain语句来分析执行情况
mysql>explainselectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|1|SIMPLE|part_tab|ALL|NULL|NULL|NULL|NULL|7980796|Usingwhere|
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1rowinset
mysql>explainselectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|1|SIMPLE|no_part_tab|ALL|NULL|NULL|NULL|NULL|8000206|Usingwhere|
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
1rowinset
mysql>
分区表执行扫描了7980796行,而普通表则扫描了8000206行。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 分区表 测试