扎心,MySQL表分区太多了
导读
之前分享过,表分区数量特别大,当有大事务时,可能会导致主从延迟特别严重。
那么如何减少表分区数量呢?
之前叶师傅分享的 意想不到的MySQL复制延迟原因 一文中提到,规避问题的方法有:
-
删除不用的历史表分区;
-
将大表拆分;
-
将大事务拆分,不要在一个事务里做大批量更新;
针对第一条方法,今天我们来讲讲怎么做表分区的合并,也就是把历史表分区合并到一起。
首先,检查下哪些表的分区数量最大,优先对它们下手:
yejr@imysql.com[information_schema]> SELECT TABLE_NAME, COUNT(*) AS CNT
FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;
+------------+-----+
| TABLE_NAME | CNT |
+------------+-----+
| t1 | 600 |
...
+------------+-----+
可以看到表 t1 的分区共有 600 个之多。
再来看看这个表的分区情况:
再看看这个表各个分区的数据量分布:
每天就要生成700-800万行数据,这个量是够大的了。
现在,我们把2016年的所有分区,按照每月一个分区(之前是每天一个分区),合并在一起。
yejr@imysql.com[mydb]> ALTER TABLE t1 REORGANIZE PARTITION
p20160101,p20160102...p20160131 into (
partition p201601 VALUES LESS THAN ('2016-01-31'));
Query OK, 0 rows affected (9 hours 56 min 55.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
苍天啊,竟然要跑9个多小时,好可怕。
合并完分区后,还记得要执行 ANALYZE TABLE 哟,及时更新统计信息。在有大量表分区的情况下,执行 ANALYZE TABLE 可能会略慢,要有耐心,在另一篇分享 细说ANALYZE TABLE 文中也介绍过了。
我们可以用下面方法构造批量执行表分区合并的SQL指令:
yejr@imysql.com[information_schema]> SELECT TABLE_NAME,
CONCAT('ALTER TABLE ', TABLE_SCHEMA,
'.' ,TABLE_NAME, ' REORGANIZE PARTITION ',
GROUP_CONCAT(PARTITION_NAME),
' INTO (PARTITION P2017 VALUES LESS THAN (''2018-01-01''));')
FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME;
SQL中的条件请自行替换。
当然,除了合并分区,我们还可以利用MySQL 5.7支持 分区 & 物理表 直接交换 的新特性,将历史分区交换到外部物理表,再归档到历史库里。这个方案对在线数据库影响很小,叶师傅优先推荐此法。
yejr@imysql.com[mydb]> ALTER TABLE t1 EXCHANGE PARTITION p20160101 WITH TABLE t1_20160101;
备注:一个分区只能交换到一个外部物理表中,不能将多个分区交换到同一个表。
最后,如果哪天boss抽风要求把原来的分区打散,重新变成每天一个分区,咋整呢?
其实也不难啊,让叶师傅手把手地教你
yejr@imysql.com[mydb]> ALTER TABLE t1 REORGANIZE PARTITION
p201707 INTO (partition p20170701 VALUES LESS THAN ('2017-07-02’) ,
partition p20170702 VALUES LESS THAN ('2017-07-03’) ,
...
partition p20170731 VALUES LESS THAN ('2017-08-01’));
最后祝大家的表分区越来越多,嘿,别打我,快溜~