扎心，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’));

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

知识无界限，不再加原创

喜欢就转走，铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com