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

相关文章

来自专栏高性能服务器开发

B-Tree

B-Tree就是我们常说的B树,一定不要读成B减树,否则就很丢人了。B树这种数据结构常常用于实现数据库索引,因为它的查找效率比较高。

942
来自专栏企鹅号快讯

什么是B-Tree

B-Tree就是我们常说的B树,一定不要读成B减树,否则就很丢人了。B树这种数据结构常常用于实现数据库索引,因为它的查找效率比较高。 磁盘IO与预读 磁盘读取依...

22610
来自专栏工科狗和生物喵

【计算机本科补全计划】CCF计算机职业资格认证 2016-12 试题详解

正文之前 最近练算法,不过最可气的,我写出来的程序一直算法越界!!题目要求到1000个输入都能正常工作,但是我的一般500个就直接越界了!!!咋办!!!咋办!!...

40610
来自专栏Java架构师历程

Mysql面试题目

5>.InnoDB不支持全文索引,而MyISAM支持。(X) (2) 问各种不同mysql版本的2者的改进 (3)2者的索引的实现方式

1063
来自专栏java初学

B树

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

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

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

1083
来自专栏文渊之博

mysql表分区简述

数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应...

663
来自专栏Java3y

多线程之死锁就是这么简单

1835
来自专栏java达人

数据库事务与隔离级别

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

1856
来自专栏JavaQ

三分钟学习分布式ID方案

在分布式系统中,当数据库数据量达到一定量级的时候,需要进行数据拆分、分库分表操作,传统使用方式的数据库自有的自增特性产生的主键ID已不能满足拆分的需求,它只能保...

932

扫码关注云+社区