MySQL分区

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL分区

小飞哥1112 2018-10-20 15:58:04 浏览1345
展开阅读全文


 分区概述

1  简介

Mysql 5.1版本开始支持分区。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库而言,从逻辑上讲,只有一个表或索引,但是在物理上这个表或者索引可能由多个物理分区组成。

Mysql数据库支持水平分区,不支持垂直分区。Mysql数据库的分区是局部分区索引。

水平分区指将同一个表不同行的记录分配到不同的物理文件中。

垂直分区值将同一个表不同列的记录分配到不同的物理文件中。

局部分区索引指一个分区中既存放数据有存放索引。

全局分区索引指数据存放在各个分区中,但是所有数据的索引存放在一个对象中。

 

2  分区表的优点

分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。

和单个磁盘或者文件系统相比,可以存储更多数据。

优化查询。在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果。

分区表更容易维护。例如:想批量删除大量数据可以清除整个分区。

可以使用分区表来避免某些特殊的瓶颈。例如InnoDB的单个索引的互斥访问,ext3问价你系统的inode锁竞争等。

 

3  分区表的限制

在5.6.7之前的版本,一个表最多有1024个分区;从5.6.7开始,一个表最多可以有8192个分区。

MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

分区表中无法使用外键约束。

 

4   分区注意事项

无论是何种类型的分区,如果表中存在主键或者唯一索引时,分区列必须是唯一索引的一个组成部分。

如果表中没有指定主键或者唯一索引,可以指定任何列作为分区列。

唯一索引允许是Null的,并且分区列只要是唯一索引的一个组成部分即可。

 

 分区类型

1  分区类型

RANGE分区:按照列值的区间范围分区。

LIST分区:与RANGE类似,区别是LIST面向的是离散的值。

HASH分区:根据用户自定义的表达式进行分区,表达式返回值不能是负数。

KEY分区:根据Mysql数据库提供的哈希函数进行分区。

Mysql5.5开始支持RANGE COLUMNS分区和LIST COLUMNS分区;

注意:在MySQL5.1版本中,RANGE,LIST,HASH分区要求分区键必须是INT类型,或者通过表达式返回INT类型。但KEY分区的时候,可以使用其他类型的列(BLOB,TEXT类型除外)作为分区键。

 

2  RANGE分区

以下脚本,是将user表根据age进行分区,p0中的user.age <18,p1中的user.age >=18 and user.age <30,p2中的user.age >=30 and user.age < 60,p3中的user.age >=60。


CREATE TABLE user (
    id INT NOT NULL,
    name VARCHAR(30) not null,
    sex int(11) not null,
    age int(11) not null
)
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (18),
    PARTITION p1 VALUES LESS THAN (30),
    PARTITION p2 VALUES LESS THAN (60),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
alter table user add index ix_age(age) ;

3   LIST分区

以下搅拌将user按照性别分区,男性放在male分区中,女性放在female分区中。


CREATE TABLE user (
    id INT NOT NULL,
    name VARCHAR(30) not null,
    sex int(11) not null,
    age int(11) not null
)
PARTITION BY LIST(sex) (
    PARTITION male VALUES IN (1),
    PARTITION female VALUES IN (2)
);

4  HASH分区

以下脚本通过hash(name)%4进行分区.


CREATE TABLE user (
    id INT NOT NULL,
    name VARCHAR(30) not null,
    sex int(11) not null,
    age int(11) not null
)
PARTITION BY HASH(name)
PARTITIONS 4
;


5  KEY分区

KEY分区类似于HASH分区,KEY分区的哈希函数是由MySQL服务器提供。Key分区与Hash分区很相似,只是Hash函数不同,定义时把Hash关键字替换成Key即可。MySQL簇(Cluster)使用函数MD5()来实现KEY分区;


CREATE TABLE user (
    id INT NOT NULL,
    name VARCHAR(30) not null,
    sex int(11) not null,
    age int(11) not null
)
PARTITION BY KEY(name)
PARTITIONS 4
;

6  分区中的NULL值

Mysql分区时,视NULL值比任何非NULL的值都小。

 

 分区与性能

假设表的数据里为1000W,通过主键划分10个分区,在这种情况下我们分析一下分区对性能的影响。

对于SELECT * FROM TABLE WHERE PK=#PK#查询:

假设100w和1000w行的数据构成的B+树都是2层,因为分区前后都是进行2次IO即可找到目标数据,所以分区对性能没有影响。

假设1000w数据的B+树是3层,100w数据的B+树是2层,因为分区前需要3次IO找到目标数据,分区后需要2次IO就可以找到目标数据,可以提升查询性能。

对于SELECT * FROM TABLE WHERE KEY=#KEY#查询:

分区前需要2-3次IO即可找到目标数据;分区之后即便是每个分区进行2次IO就能找到数据,但是因为会遍历10个分区表,所以一共需要20次IO。性能会急剧下降。

 

因为不当的分区方式或查询方式会导致多分区全表扫描,性能急剧降低。如果要分区,查询时务必带上分区字段作为条件,并且尽可能降低每个sql语句扫描的分区数目。

对于OLTP类型的应用而言,查询的场景一般都是多种多样的,所以需要非常谨慎的使用分区。

 

网友评论

登录后评论
0/500
评论
小飞哥1112
+ 关注