前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >用好 mysql 分区表

用好 mysql 分区表

原创
作者头像
潘昌伟
修改2017-09-01 10:01:24
10.4K0
修改2017-09-01 10:01:24
举报
文章被收录于专栏:潘昌伟的专栏

为了保证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

代码语言:javascript
复制
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

代码语言:javascript
复制
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

代码语言:javascript
复制
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

代码语言:javascript
复制
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自行根据主键/唯一键分区。如果主键设置不合理,查询条件都不带主键,查询性能会很差。

五、删除分区

代码语言:javascript
复制
移除分区: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

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

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

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 分区的优势:
    • 一、RANGE partitioning
    • 优势:
      • 二、LIST partitioning
      • 优势:
        • 三、HASH partitioning
          • 四、KEY partitioning
          • 五、删除分区
          相关产品与服务
          云数据库 SQL Server
          腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档