华体会-文班评历史最佳5人组：库里、乔丹、詹姆斯、邓肯、奥尼尔

　　导读

　　之前分享过，表分区数量特别大，当有大事务时，可能会导致主从延迟特别严重。

　　那么如何减少表分区数量呢？

　　之前叶师傅分享的 意想不到的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’));

　　最后祝大家的表分区越来越多，嘿，别打我，快溜～

知识无界限，不再加原创

喜欢就转走，铁粉加密圈

https://yejinrong.com