用好 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 条评论
登录 后参与评论

相关文章

来自专栏Rgc

mysql数据库优化(三)--分区

分区:把一个数据表的文件和索引分散存储在不同的物理文件中。 特点:业务层透明,无需任何修改,即使从新分表,也是在mysql层进行更改(业务层代码不动)

523
来自专栏数据和云

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

3587
来自专栏我是攻城师

2015年240个数据库引擎排名参考

38810
来自专栏在线教育技术团队专栏

MySQL 开发实践 8 问,你能 hold 住几个?

最近研发的项目对 DB 依赖比较重,梳理了这段时间使用 MySQL 遇到的8个比较具有代表性的问题。

8.2K4
来自专栏数据库

记一次排查DB死锁的分析

文章摘要 在线上环境遇到数据库死锁问题该如何分析并解决问题呢? 虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却...

1808
来自专栏Jackson0714

30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

2716
来自专栏匠心独运的博客

数据库技术之记一次排查DB死锁的分析与思考

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到...

571
来自专栏性能与架构

如何有效使用Mysql的Query Cache

须要根据Query Cache失效机制来判断哪些表适合使用Query哪些表不适合。 由于Query Cache的失效主要是因为Query所依赖的Table数据发...

3374
来自专栏java达人

数据库事务与隔离级别

事务(transaction)是数据库管理系统的执行单位,可以是一个数据库操作(如Select操作)或者是一组操作序列。事务ACID属性,即原子性(Atomic...

1836
来自专栏微信公众号:Java团长

深入理解Mysql——锁、事务与并发控制

本文对锁、事务、并发控制做一个总结,看了网上很多文章,描述非常不准确。如有与您观点不一致,欢迎有理有据的拍砖!

743

扫码关注云+社区