mysql分区表测试Word文件下载.docx
《mysql分区表测试Word文件下载.docx》由会员分享，可在线阅读，更多相关《mysql分区表测试Word文件下载.docx（12页珍藏版）》请在冰豆网上搜索。
二、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
）;
以员工工资为依据做范围分区。
birthdatedatenotnull,
partitionbyrange（year（birthdate））
partitionp1valueslessthan（1980）,
partitionp2valueslessthan（1990）,
以year（birthdate）表达式（计算员工的出生日期）作为范围分区依据。
这里最值得注意的是表达式必须有返回值。
2）创建list分区
partitionbylist（deptno）
partitionp1valuesin（10）,
partitionp2valuesin（20）,
partitionp3valuesin（30）
以部门作为分区依据，每个部门做一分区。
3）创建hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。
在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；
而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
partitionbyhash（year（birthdate））
partitions4;
4）创建key分区
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD（）一样的运算法则。
“CREATETABLE...PARTITIONBYKEY”的语法规则类似于创建一个通过HASH分区的表的规则。
它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。
partitionbykey（birthdate）
5）创建复合分区
range-hash（范围哈希）复合分区
subpartitionbyhash（year（birthdate））
subpartitions3
partitionp1valueslessthan（2000）,
partitionp2valueslessthanmaxvalue
range-key复合分区
subpartitionbykey（birthdate）
list-hash复合分区
CREATETABLEemp（
empnovarchar（20）NOTNULL,
empnamevarchar（20）,
birthdatedateNOTNULL,
salaryint
PARTITIONBYlist（deptno）
PARTITIONp1VALUESin（10）,
PARTITIONp2VALUESin（20）
list-key复合分区
CREATETABLEempk（
6）分区表的管理操作
删除分区:
altertableempdroppartitionp1;
不可以删除hash或者key分区。
一次性删除多个分区，altertableempdroppartitionp1,p2;
增加分区:
altertableempaddpartition（partitionp3valueslessthan（4000））;
altertableempladdpartition（partitionp3valuesin（40））;
分解分区:
Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。
分解前后分区的整体范围应该一致。
altertablete
reorganizepartitionp1into
partitionp1valueslessthan（100）,
partitionp3valueslessthan（1000）
----不会丢失数据
合并分区:
Merge分区：
把2个分区合并为一个。
reorganizepartitionp1,p3into
（partitionp1valueslessthan（1000））;
----不会丢失数据
重新定义hash分区表:
Altertableemppartitionbyhash（salary）partitions7;
重新定义range分区表:
Altertableemppartitionbyrange（salary）
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），
salaryint,
primarykey（empno）
PARTITIONBYrange（salary）
PARTITIONp1VALUESlessthan（100）,
PARTITIONp2VALUESlessthan（200）
这样的语句会报错。
MySQLDatabaseError:
APRIMARYKEYmustincludeallcolumnsinthetable'
spartitioningfunction；
deptnoint（11）,
salaryint（11）,
primarykey（empno，salary）
在主键中加入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.partitions
where
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'
andc3<
1995-12-31'
+----------+
|count（*）|
|
795181|
1rowinset（2.62sec）
查询普通表：
1rowinset（7.33sec）
分区表的执行时间比普通表少70%。
4.通过explain语句来分析执行情况
mysql>
explainselectcount（*）frompart_tabwherec3>
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|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
explainselectcount（*）fromno_part_tabwherec3>
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|1|SIMPLE|no_part_tab|ALL|NULL|NULL|NULL|NULL|8000206|Usingwhere|
mysql>
分区表执行扫描了7980796行，而普通表则扫描了8000206行。
升级会员 