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)可以将分区文件部署到不同的磁盘上,充分利用磁盘的性能