MySQL大表字段更新优化

1. 使用percona-toolkit

原理是创建一个影子表，在影子表上执行 ALTER，然后通过 触发器（Triggers） 同步增量数据，最后完成替换。

命令

# 测试运行，返回很多OK即可
docker run --rm -it percona/percona-toolkit \
pt-online-schema-change \
  --host=172.18.0.1 \
  --user=root \
  --port=13306 \
  --password='123456' \
  --alter "MODIFY COLUMN isp_group VARCHAR(20) GENERATED ALWAYS AS (CASE WHEN isp LIKE '%gmail%' THEN 'Gmail' WHEN isp LIKE '%outlook%' THEN 'Outlook' WHEN isp LIKE '%163%' THEN '163' WHEN isp LIKE '%126%' THEN '126' WHEN isp LIKE '%qq%' THEN 'QQ' WHEN isp LIKE '%sina%' THEN 'Sina' WHEN isp LIKE '%yahoo%' THEN 'Yahoo' WHEN isp LIKE '%hotmail%' THEN 'Hotmail' WHEN isp LIKE '%aol%' THEN 'AOL' WHEN isp LIKE '%foxmail%' THEN 'Foxmail' WHEN isp LIKE '%softbank%' THEN 'Softbank' WHEN isp LIKE '%naver%' THEN 'Naver' WHEN isp LIKE '%icloud%' THEN 'iCloud' WHEN isp LIKE '%hanmail%' THEN 'Hanmail' WHEN isp LIKE '%docomo%' THEN 'Docomo' WHEN isp LIKE '%.au.com%' THEN 'AU' WHEN isp LIKE 'au.com%' THEN 'AU' WHEN isp LIKE '%ezweb.ne.jp%' THEN 'AU' ELSE 'other' END) VIRTUAL" \
  D=stats,t=email_isp_stats \
  --dry-run
  
  
# 执行
docker run --rm -it percona/percona-toolkit \
pt-online-schema-change \
  --host=172.18.0.1 \
  --user=root \
  --port=13306 \
  --password='123456' \
  --alter "MODIFY COLUMN isp_group VARCHAR(20) GENERATED ALWAYS AS (CASE WHEN isp LIKE '%gmail%' THEN 'Gmail' WHEN isp LIKE '%outlook%' THEN 'Outlook' WHEN isp LIKE '%163%' THEN '163' WHEN isp LIKE '%126%' THEN '126' WHEN isp LIKE '%qq%' THEN 'QQ' WHEN isp LIKE '%sina%' THEN 'Sina' WHEN isp LIKE '%yahoo%' THEN 'Yahoo' WHEN isp LIKE '%hotmail%' THEN 'Hotmail' WHEN isp LIKE '%aol%' THEN 'AOL' WHEN isp LIKE '%foxmail%' THEN 'Foxmail' WHEN isp LIKE '%softbank%' THEN 'Softbank' WHEN isp LIKE '%naver%' THEN 'Naver' WHEN isp LIKE '%icloud%' THEN 'iCloud' WHEN isp LIKE '%hanmail%' THEN 'Hanmail' WHEN isp LIKE '%docomo%' THEN 'Docomo' WHEN isp LIKE '%.au.com%' THEN 'AU' WHEN isp LIKE 'au.com%' THEN 'AU' WHEN isp LIKE '%ezweb.ne.jp%' THEN 'AU' ELSE 'other' END) VIRTUAL" \
  D=stats,t=email_isp_stats \
  --execute \
  --progress

优缺点

• 优点：非常成熟，自动处理主从延迟监控。
• 注意：原表上不能已有触发器（Triggers），否则无法运行。

2. 使用 gh-ost (GitHub 开源，更安全)

gh-ost 的原理是不使用触发器，而是通过 解析二进制日志（Binlog） 来同步数据。它对系统负载的影响更小，且可以随时暂停。

前提条件

• MySQL 开启了 binlog。
• binlog_format 必须设置为 ROW。

• 该工具需要连接到实例并具有副本（Replica）或直接在主库上运行（需指定 --allow-on-master）。

  下载

  复制

  # 下载
  wget https://gh-proxy.org/https://github.com/github/gh-ost/releases/download/v1.1.7/gh-ost-binary-linux-amd64-20241219160321.tar.gz
  
  # 解压
  tar -zxvf gh-ost-binary-linux-amd64-20241219160321.tar.gz 
  
  # 移动到可执行目录 
  sudo mv gh-ost /usr/local/bin/

  命令

  复制

  # 测试执行
  gh-ost \
  --host=172.18.0.1 \
  --user=root \
  --port=13306 \
  --password='123456' \
  --database="stats" \
  --table="email_isp_stats" \
  --verbose \
  --alter="MODIFY COLUMN isp_group VARCHAR(20) GENERATED ALWAYS AS (CASE WHEN isp LIKE '%gmail%' THEN 'Gmail' WHEN isp LIKE '%outlook%' THEN 'Outlook' WHEN isp LIKE '%163%' THEN '163' WHEN isp LIKE '%126%' THEN '126' WHEN isp LIKE '%qq%' THEN 'QQ' WHEN isp LIKE '%sina%' THEN 'Sina' WHEN isp LIKE '%yahoo%' THEN 'Yahoo' WHEN isp LIKE '%hotmail%' THEN 'Hotmail' WHEN isp LIKE '%aol%' THEN 'AOL' WHEN isp LIKE '%foxmail%' THEN 'Foxmail' WHEN isp LIKE '%softbank%' THEN 'Softbank' WHEN isp LIKE '%naver%' THEN 'Naver' WHEN isp LIKE '%icloud%' THEN 'iCloud' WHEN isp LIKE '%hanmail%' THEN 'Hanmail' WHEN isp LIKE '%docomo%' THEN 'Docomo' WHEN isp LIKE '%.au.com%' THEN 'AU' WHEN isp LIKE 'au.com%' THEN 'AU' WHEN isp LIKE '%ezweb.ne.jp%' THEN 'AU' ELSE 'other' END) VIRTUAL" \
  
  
  # 正式执行
  gh-ost \
  --host=172.18.0.1 \
  --user=root \
  --port=13306 \
  --password='123456' \
  --database="stats" \
  --table="email_isp_stats" \
  --verbose \
  --alter="MODIFY COLUMN isp_group VARCHAR(20) GENERATED ALWAYS AS (CASE WHEN isp LIKE '%gmail%' THEN 'Gmail' WHEN isp LIKE '%outlook%' THEN 'Outlook' WHEN isp LIKE '%163%' THEN '163' WHEN isp LIKE '%126%' THEN '126' WHEN isp LIKE '%qq%' THEN 'QQ' WHEN isp LIKE '%sina%' THEN 'Sina' WHEN isp LIKE '%yahoo%' THEN 'Yahoo' WHEN isp LIKE '%hotmail%' THEN 'Hotmail' WHEN isp LIKE '%aol%' THEN 'AOL' WHEN isp LIKE '%foxmail%' THEN 'Foxmail' WHEN isp LIKE '%softbank%' THEN 'Softbank' WHEN isp LIKE '%naver%' THEN 'Naver' WHEN isp LIKE '%icloud%' THEN 'iCloud' WHEN isp LIKE '%hanmail%' THEN 'Hanmail' WHEN isp LIKE '%docomo%' THEN 'Docomo' WHEN isp LIKE '%.au.com%' THEN 'AU' WHEN isp LIKE 'au.com%' THEN 'AU' WHEN isp LIKE '%ezweb.ne.jp%' THEN 'AU' ELSE 'other' END) VIRTUAL" \
  --allow-on-master \
  --execute

  优缺点

• 优点：不需要触发器，不锁表，可以动态调整迁移速度，迁移过程可监控（通过监听 TCP 端口）。

• 缺点：配置相对比 pt-osc 复杂一点。

  核心参数说明

• --allow-on-master: 允许直接在主库上操作（不推荐生产环境这样做，但如果没有从库，必须加这个）。
• --serve-socket-file=/tmp/gh-ost.sock: 允许你通过交互式命令查看进度。
• --panic-flag-file=/tmp/ghost.panic.flag: 非常有用！ 如果你发现服务器压力太大，只需在桌面上创建一个这个文件，gh-ost 会立即停止操作并清理现场。
• --max-lag-millis=1000: 如果从库延迟超过 1 秒，自动暂停同步。

• --chunk-size=1000: 每次拷贝多少行数据。

  监控进度

  复制

  # 查看详细进度
  echo "status" | nc -U /tmp/gh-ost.sock
  
  # 甚至可以动态调整速度（不需要重启任务）
  echo "throttle" | nc -U /tmp/gh-ost.sock  # 暂停任务
  echo "no-throttle" | nc -U /tmp/gh-ost.sock # 恢复任务

  总结：选 pt-osc 还是 gh-ost？

1. 如果你已经跑通了 pt-osc 的 --dry-run：建议直接用 pt-osc。因为它更简单，不依赖 Binlog 权限。
2. 如果你的表上有触发器（Triggers）：必须用 gh-ost。
3. 如果你非常担心业务压力：用 gh-ost，因为你可以随时在不停止任务的情况下让它“慢一点”。