欢迎投稿

今日深度:

MySQL优化(5):分区,

MySQL优化(5):分区,


分区:

分区也是MySQL优化中的一个重要方式

将一个表中的数据和索引,分散到不同的文件中进行存储

通常情况下,一个表,对应一组数据和索引文件,一个表的数据和索引集中存储在这组文件中

 当一个表出现了大量的记录时,可以将其分布到不同的数据和索引文件中进行存储

Innodb来说,一个表对应多个ibd文件

MyISAM来说,一个表对应多个myi,myd文件

 

分布之后,每个文件中包含的记录数量显著减少,保证单独文件的执行效率

 

最常用的分区语法是:使用ID,将数据分布到多个分区中

在设计表的时候使用partition选项完成分区,需要提供分区算法和算法参数,完成分区操作

create table [table-name](
id int unsigned auto_increment primary key,
subject varchar(255),
content text
)charset utf8 engine innodb
partition by hash(id) partitions 10
;

正常的建表语句,最后一行表示利用ID字段,使用HASH算法,将数据分布到十个分区内

建表成功之后,可以发现数据库中虽然只是一张表,但是文件夹中会有十个ibd文件,一个frm文件(结构文件)和一个par文件(分区结构文件)

 

分区成功后:客户端和以前没有任何区别,只是服务器端将数据分散到了不同的分区中存储

当前例子,使用HASH算法,利用ID值求余的算法,通过余数将记录分布到某个分区中

HASH(ID)算法,逻辑上表示将记录均匀地分布到不同的区域中,该算法也是使用最广最常用的算法

适用于数据量很大但是没有明显的逻辑区分时,使用该算法

 

MySQL提供了四种分区算法:HASH算法,KEY算法,RANGE算法,LIST算法

HASH算法:

上文讲了,使用一个整数的值,将记录分布到分区中,采用求余方案;

(哈希是一类算法,使用某个输入可以得到某个特定的输出,相同输入那么输出也相同)

 

KEY算法:

和HASH算法很像,是一个更加通用的HASH算法

HASH中只能对整数求余运算,而KEY算法可以使用非整型字段,输入数据不一定是整数

我们进行分区的时候,可以不采用KEY(ID),而使用字符串KEY(username)

注意:分区要求必须是主键的一部分,这里需要primary key(id,username),分区字段一定是强检索字段

 

RANGE算法:

一种条件分区算法,将数据使用某种条件分散到不同的区中

范围条件算法,主要使用小于来实现条件

使用示例:利用文章的发布时间,将文章分布到不同的区域中:

create table articles(
id ...
subject ...
content ...
pubtime int,
primary key (id,pubtime)
)charset=utf8 engine=innodb
partition by range(pubtime) (
partition p201710 values less than (1509465599),
partition p201711 values less than (1512057599),
partition p201712 values less than (1514735999)
);

分为10月11月12月三个月,后边的数字是时间戳,p201710是自己进行命名的

 

LIST算法:

 一种条件分区,条件使用IN

使用示例:文章状态,1代表正在写,2代表已保存,3代表发布

create table articles(
id ...
subject ...
content ...
pubtime int,
status tinyint,
primary key (id,status)
)charset=utf8 engine=innodb
partition by list(status) (
partition writing values in (1,2),
partition published values in (3)
);

那么这里就将文章分为已发布和未发布两个分区

 

分区的管理语法:

对于LIST和RANGE可以删除和新增分区:

添加分区

alter table articles add partition(
partition p201801 values less than (1517414400),
partition p201802 values less than (1519833600)
);

删除分区,删除分区后,分区的数据也会随之删除,不可恢复

alter table articles drop partition p201710;

 

对于HASH和KEY可以修改分区的数量:

在原有的基础上再加四个分区,数据不丢失

alter table artiles add partiotion partitions 4;

合并为六个分区(注意语法中没有partitions,而且数据不会丢失)

alter table articles coalesce partition 6;

加入和合并分区由于要保证数据不丢失,所以效率较低,时间较长

 

总结:

(1)分区是在客户端程序不变的情况下,将服务器端数据分布到不同的物理文件中

(2)当数据表中数据量很大的时候,分区可以提升效率,只有检索字段为分区字段的时候,分区效率才会比较明显

(3)可以将分区文件部署到不同的磁盘上,充分利用磁盘的性能

 

www.htsjk.Com true http://www.htsjk.com/Mysql/42428.html NewsArticle MySQL优化(5):分区, 分区: 分区也是MySQL优化中的一个重要方式 将一个表中的数据和索引,分散到不同的文件中进行存储 通常情况下,一个表,对应一组数据和索引文件,一个表的...
相关文章
    暂无相关文章
评论暂时关闭