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