为了保证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://www.toymoban.com/news/detail-478317.html
移除分区仅仅修改表分区定义，数据不会被删除；删除分区会删除分区定义同时删除分区上的数据。文章来源地址https://www.toymoban.com/news/detail-478317.html
到了这里，关于用好 mysql 分区表的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
