扎心,MySQL表分区太多了

  • A+
所属分类:Mysql

导读

之前分享过,表分区数量特别大,当有大事务时,可能会导致主从延迟特别严重。
那么如何减少表分区数量呢?

之前叶师傅分享的 意想不到的MySQL复制延迟原因 一文中提到,规避问题的方法有:

  • 删除不用的历史表分区;

  • 将大表拆分;

  • 将大事务拆分,不要在一个事务里做大批量更新;

针对第一条方法,今天我们来讲讲怎么做表分区的合并,也就是把历史表分区合并到一起。

首先,检查下哪些表的分区数量最大,优先对它们下手:

  1. yejr@imysql.com[information_schema]> SELECT TABLE_NAME, COUNT(*) AS CNT

  2.    FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL

  3.    GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;

可以看到表 t1 的分区共有 600 个之多。

再来看看这个表的分区情况:扎心,MySQL表分区太多了

再看看这个表各个分区的数据量分布:扎心,MySQL表分区太多了

每天就要生成700-800万行数据,这个量是够大的了。

现在,我们把2016年的所有分区,按照每月一个分区(之前是每天一个分区),合并在一起。

  1. yejr@imysql.com[mydb]> ALTER TABLE t1 REORGANIZE PARTITION

  2.    p20160101,p20160102...p20160131 into (

  3.    partition p201601 VALUES LESS THAN ('2016-01-31'));Query OK, 0 rows affected (9 hours 56 min 55.33 sec)

  4. Records: 0  Duplicates: 0  Warnings: 0

苍天啊,竟然要跑9个多小时,好可怕。

合并完分区后,还记得要执行 ANALYZE TABLE 哟,及时更新统计信息。在有大量表分区的情况下,执行 ANALYZE TABLE 可能会略慢,要有耐心,在另一篇分享 细说ANALYZE TABLE 文中也介绍过了。

我们可以用下面方法构造批量执行表分区合并的SQL指令:

  1. yejr@imysql.com[information_schema]> SELECT TABLE_NAME,

  2.    CONCAT('ALTER TABLE ', TABLE_SCHEMA,

  3.    '.' ,TABLE_NAME, ' REORGANIZE PARTITION ',

  4.    GROUP_CONCAT(PARTITION_NAME),

  5.    ' INTO (PARTITION P2017 VALUES LESS THAN (''2018-01-01''));')

  6.    FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL

  7.    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抽风要求把原来的分区打散,重新变成每天一个分区,咋整呢?

其实也不难啊,让叶师傅手把手地教你

  1. yejr@imysql.com[mydb]> ALTER TABLE t1  REORGANIZE PARTITION

  2.    p201707 INTO (partition p20170701 VALUES LESS THAN ('2017-07-02’) ,

  3.    partition p20170702 VALUES LESS THAN ('2017-07-03’) ,

  4.    partition p20170731 VALUES LESS THAN ('2017-08-01’));

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

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin