导读
之前分享过,表分区数量特别大,当有大事务时,可能会导致主从延迟特别严重。
那么如何减少表分区数量呢?
之前老叶分享的意想不到的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 个之多。
再来看看这个表的分区情况:
yejr@imysql.com[mydb]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mydate` date NOT NULL,
...
PRIMARY KEY (`id`,`mydate`),
....
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(`mydate`)
(
PARTITION p20160101 VALUES LESS THAN ('2016-01-01’) ENGINE = InnoDB,
PARTITION p20160102 VALUES LESS THAN ('2016-01-02’) ENGINE = InnoDB,
...
PARTITION p20170630 VALUES LESS THAN ('2017-06-30') ENGINE = InnoDB)
1 row in set (0.00 sec)
再看看这个表各个分区的数据量分布:
yejr@imysql.com[information_schema]> SELECT PARTITION_NAME, TABLE_ROWS FROM
PARTITIONS WHERE TABLE_NAME = ‘t1’;
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p20160101 | 8391013 |
| p20160102 | 7749149 |
| p20160103 | 8308234 |
....
| p20170729 | 0 |
| p20170730 | 0 |
+----------------+------------+
每天就要生成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 可能会略慢,要有耐心,在另一篇分享里 XX 也介绍过了。
我们可以用下面方法构造批量执行表分区合并的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’));
最后祝大家的表分区越来越多,嘿,别打我,快溜~