用好 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