用好 mysql 分区表

为了保证MySQL的性能,我们都建议mysql单表不要太大,也经常有人问我这样的问题,整体来说呢,建议是:单表小于2G,记录数小于1千万,十库百表。如果但行记录数非常小,那么记录数可以再偏大些,反之,可能记录数到百万级别就开始变慢了。

那么,业务量在增长,数据到瓶颈了怎么办呢,除了使用分布式数据库,我们也可以自行分库分表,或者利用mysql的分区功能实现。

本文主要介绍几种分区的选型建议和语法,其实影响分区性能最重要的一点还有索引的设计,非常关键,如果索引没设计好,可能分区表的性能并不理想,后续单独整理分享。

分区的优势:

1、冷热分离:表非常大且只在表的最后部分有热点数据,冷数据根据分区规则自动归档。

2、定期淘汰历史数据:按时间写入,历史数据可淘汰,可快速删除,空间可快速回收。

3、优化查询:在where字句中包含分区列时,分区可以大大提高查询效率,减少缓存开销、减少IO开销。

4、统计性能提升:在涉及sum()和count()这类聚合函数的查询时,可以在每个分区上面并行处理,最终只需要汇总所有分区得到的结果。

MySQL的分区规则:

范围 :PARTITIONED BY RANGE COLUMNS

列表 :PARTITION BY LIST COLUMNS

HASH:PARTITION BY HASH

KEY :PARTITION BY KEY

子分区:SUBPARTITION BY XXX

一、RANGE partitioning

CREATE TABLE members01 (
    id int(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATETIME NOT NULL,
    PRIMARY KEY (id,joined),
    UNIQUE KEY `uk_username` (username,email,joined)
)
PARTITION BY RANGE( TO_DAYS(joined) ) (
    PARTITION p20170801 VALUES LESS THAN (736908),
    PARTITION p20170802 VALUES LESS THAN (736909)
);

这种是最常见的,也是我们MDB平台提供自动按天见分区的格式。一般也比较适合按天分区,或者固定范围的分区,比如时间范围,只能按照数字大小(年龄/编号)进行区间划分。

优势:

1、按分区快速淘汰历史数据

2、按分区字段的范围查询

这里不得不吐槽一下,有的人,每天把数据往一个统计表里面存,不做分区,也不做历史数据淘汰,等到了300G,甚至1T以后,数据出不来,火急火燎的跑过来问题要怎么删除历史数据,而且表连一个主键、索引都没有,我只能说删表吧哥(非常无赖)

二、LIST partitioning

CREATE TABLE members02 (
    id int(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATETIME NOT NULL,
    PRIMARY KEY (id,joined),
    UNIQUE KEY `uk_username` (username,email,joined)
)
PARTITION BY LIST( TO_DAYS(joined) ) (
    PARTITION p20170801 VALUES IN (736905,736907),
    PARTITION p20170803 VALUES IN (736908,736909)
);

表面上看,咦?好像使用list分区的都可以使用rang分区实现呢,其实大部分场景两种分区方式都是可以实现的,线上实际只能使用list分区的场景也比较少。

连续数据更趋向于使用range分区, list分区一般比较适合离散数据的分区,同时可以将多个离散的属性归类存储,比如我需要把20170801、20170803、20170809三个时间的数据放一个分区,20170802、20170805、20170808放个分区,这种就适合使用list分区,针对自己业务特性进行离散的分区,可以非常灵活的将数据打散到不同的分区。可以看出这种分区策略就不适合where条件的范围查询,适合固定值的in条件查询。

优势:

1、灵活的离散数据分区,可自定义分区list规则。

2、 离散分区不适合where条件date>20170801 and date >20170809,适合固定分区的等值查询或in条件查询

三、HASH partitioning

CREATE TABLE members03 (
    id int(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATETIME NOT NULL,
    PRIMARY KEY (id,joined),
    UNIQUE KEY `uk_username` (username,email,joined)
)
PARTITION BY hash(TO_DAYS(joined)) 
PARTITIONS 2;

hash分区很好理解,就是对指定列做hash,均匀的存到指定的分区,比如按用户名hash分区,那么按用户名进行查找的速度就会快很多,这种针对分区列数据不固定,想把数据根据分区列离散的存储到固定分区数的表中,不需要做数据淘汰的场景比较适合。

优势:

1、维护简单,分区数固定,根据hash自动分区。

2、适合固定条件的等值查询

3、对于分区列数据不固定,分区列值不固定(不适合list),可根据hash值均匀打散数据到不同分区。

四、KEY partitioning

CREATE TABLE members04 (
id int(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATETIME NOT NULL,
    PRIMARY KEY (id,joined),
    UNIQUE KEY `uk_username` (username,email,joined)
)
PARTITION BY key(joined) 
PARTITIONS 4;

同样,使用key分区跟hash分区有着神奇的相似,不同的是,如果表有主键或者唯一键的时候无需指定key的列名,key分区自动根据键值进行分区。

优势:

对于有主键的表,可无需关心分区列,MySQL自行根据主键/唯一键分区。如果主键设置不合理,查询条件都不带主键,查询性能会很差。

五、删除分区

移除分区:ALTER TABLE tablename REMOVE PARTITIONING ; 
删除分区:ALTER TABLE tablename DROP PARTITIONING ;

移除分区仅仅修改表分区定义,数据不会被删除;删除分区会删除分区定义同时删除分区上的数据。

更多分区管理:(增删修改)

https://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html

分区表sql操作优化器如何选择:

https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html

分区类型定义说明:

https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

潘昌伟的专栏

1 篇文章1 人订阅

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

3007
来自专栏杨龙飞前端

scrollto 到指定位置

2964
来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.4K7
来自专栏hbbliyong

WPF Trigger for IsSelected in a DataTemplate for ListBox items

<DataTemplate DataType="{x:Type vm:HeaderSlugViewModel}"> <vw:HeaderSlug...

4224
来自专栏我和未来有约会

Kit 3D 更新

Kit3D is a 3D graphics engine written for Microsoft Silverlight. Kit3D was inita...

2936
来自专栏一个会写诗的程序员的博客

Spring Reactor 项目核心库Reactor Core

Non-Blocking Reactive Streams Foundation for the JVM both implementing a Reactiv...

2822
来自专栏张善友的专栏

Silverlight + Model-View-ViewModel (MVVM)

     早在2005年,John Gossman写了一篇关于Model-View-ViewModel模式的博文,这种模式被他所在的微软的项目组用来创建Expr...

3308
来自专栏大内老A

The .NET of Tomorrow

Ed Charbeneau(http://developer.telerik.com/featured/the-net-of-tomorrow/) Exciti...

39010
来自专栏一个爱瞎折腾的程序猿

sqlserver使用存储过程跟踪SQL

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_s...

2890
来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

7236

扫码关注云+社区