Written by arstercz - 2016-06-29

有效的升级 MySQL 库中所有表的字段属性

背景介绍 本文基于一个 discuz 论坛表的需求描述 – 将论坛中所有表为 mediumint(8) 的字段升级为 int(10)，以存储更大的用户 id 信息; 通过批量升级字段的方式也适用于其它相关的需求. 顺便吐槽下 discuz 这么多年以来对基础表的优化接近于无, 在用户较多或访问较频繁的场景下, 基础表的结构依然缺乏比较完善的设计.

步骤考虑到论坛运行一段时间后, 一些表会比较大, 如果业务需要持续运行的话, 不太适合直接 alter table 的方式更新表, 下文会介绍使用 percona 的工具完成修改, 所以在整个流程中分为以下步骤操作:

找出论坛中数据较多的表;
生成相关的 DDL sql 语句, 忽略较大的表;
执行 DDL sql 语句;
如果表较大, 使用 pt-online-schema-change 工具在线修改表结构;
如果表较较大, 而且没有主键或唯一键, 则只能手工执行 alter 操作;

1. 找出论坛中较大的表, 可以通过 `information_schema.tables` 完成:

mysql> SELECT TABLE_NAME, TABLE_COLLATION, CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2), 'M') AS size 
FROM TABLES WHERE TABLE_SCHEMA = 'bbs_test_com' 
GROUP BY TABLE_NAME 
HAVING SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 > 500;

+-------------------------------+-----------------+----------+
| TABLE_NAME                    | TABLE_COLLATION | size     |
+-------------------------------+-----------------+----------+
| bbs_common_credit_log         | utf8_general_ci | 856.64M  |
| bbs_forum_post                | utf8_general_ci | 2123.04M |
| bbs_member_avatar             | utf8_general_ci | 660.97M  |
| bbs_ucenter_members           | utf8_general_ci | 5903.42M |
+-------------------------------+-----------------+----------+
4 rows in set (0.37 sec)

2. 通过 information_schema.columns 表来生成相应的 DDL sql, 找出 bbs_test_com 库中属性为 mediumint 的字段, 生成对应的 alter table sql, 其它相关的属性保持不变, 比如 default 值信息, not null 等需要和以前一致, 忽略数据较大的表:

select CONCAT('ALTER TABLE ', 
    table_name, ' MODIFY ', column_name, ' int(10) ',
    IF(COLUMN_TYPE REGEXP 'unsigned', 'unsigned ', ''), 
    IF(IS_NULLABLE = 'NO', 'NOT NULL ', ''), 
    IF(COLUMN_DEFAULT IS NOT NULL, CONCAT('DEFAULT ', '\'', COLUMN_DEFAULT, '\''), ''), 
    IF(EXTRA = 'auto_increment', ' AUTO_INCREMENT', ''), ';') 
AS syntax_define 
FROM columns 
WHERE 
    table_schema = 'bbs_test_com' and column_type like 'mediumint%' 
    AND table_name NOT IN ('bbs_common_credit_log', 'bbs_member_avatar', 'bbs_ucenter_members', 'bbs_forum_post')
INTO OUTFILE '/tmp/alter.txt';

可以生成以下 sql:

......
ALTER TABLE wm_common_block MODIFY bid int(10) unsigned NOT NULL  AUTO_INCREMENT;
ALTER TABLE wm_common_block MODIFY uid int(10) unsigned NOT NULL DEFAULT '0';
ALTER TABLE wm_common_block_favorite MODIFY favid int(10) unsigned NOT NULL  AUTO_INCREMENT;
ALTER TABLE wm_common_block_favorite MODIFY uid int(10) unsigned NOT NULL DEFAULT '0';
ALTER TABLE wm_common_block_favorite MODIFY bid int(10) unsigned NOT NULL DEFAULT '0';
......

如果担心 DDL 语句有问题, 可以在执行前全备一次论坛数据, 再执行 DDL 语句: 全备 bbs_test_com 库:
```
mysqldump -p --default-character-set=utf8 -B bbs_test_com >/web/bbs.sql
```
转换操作:
```
mysql> source /tmp/alter.txt
```
如果表存在主键或唯一键可以使用 pt-online-schema-change 工具进行在线更新, 详见 pt-OSC

# ./pt-online-schema-change --alter "MODIFY fid int(10) unsigned NOT NULL DEFAULT '0', MODIFY tid int(10) unsigned NOT NULL DEFAULT '0', MODIFY authorid int(10) unsigned NOT NULL DEFAULT '0'" A=gbk,h=127.0.0.1,P=3306,D=bbs_test_com,t=bbs_forum_post,u=root --execute
No slaves found.  See --recursion-method if host 127.0.0.1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `bbs_test_com`.`bbs_forum_post`...
Creating new table...
Created new table bbs_test_com._bbs_forum_post_new OK.
Altering new table...
Altered `bbs_test_com`.`_bbs_forum_post_new` OK.
2016-06-05T14:11:53 Creating triggers...
2016-06-05T14:11:53 Created triggers OK.
2016-06-05T14:11:53 Copying approximately 1546543 rows...
Copying `bbs_test_com`.`bbs_forum_post`:  31% 01:06 remain
....
....
2016-06-05T14:16:22 Dropping old table...
2016-06-05T14:16:25 Dropped old table `bbs_test_com`.`_bbs_forum_post_old` OK.
2016-06-05T14:16:25 Dropping triggers...
2016-06-05T14:16:25 Dropped triggers OK.
Successfully altered `bbs_test_com`.`bbs_forum_post`.

如果表不存在主键或唯一键则手动执行 alter 语句, 或者将 sql 生成到 DDL 语句里一并执行

最后检查看看库中是否还有 mediumint 字段:

mysql> select table_name, column_name, data_type, collation_name, column_type from columns where table_schema = 'bbsdota2new' and column_type like 'mediumint%';
Empty set (0.00 sec)

总结合理使用 information_schema 中的统计信息可以方便我们的批量更新操作. 对于需要持续服务的业务, 可以使用 pt-OSC 工具进行在线修改(pt-OSC 需要有主键或唯一键实现分组更新).

← → Top

有效的升级 MySQL 库中所有表的字段属性

1. 找出论坛中较大的表, 可以通过 information_schema.tables 完成:

2. 通过 information_schema.columns 表来生成相应的 DDL sql, 找出 bbs_test_com 库中属性为 mediumint 的字段, 生成对应的 alter table sql, 其它相关的属性保持不变, 比如 default 值信息, not null 等需要和以前一致, 忽略数据较大的表:

1. 找出论坛中较大的表, 可以通过 `information_schema.tables` 完成: