共计 6108 个字符，预计需要花费 16 分钟才能阅读完成。

Online DDL 与零停机策略（ALGORITHM/LOCK，Expand→Contract）
gh-ost / pt-online-schema-change（对比与上手）
蓝绿 / 灰度发布与读写治理（ProxySQL 最小实践）
连接池与可用性工程（重试、超时、模式）
基准测试与容量评估（sysbench 基础）
优化方法论与计划稳定性（直方图 / 不可见索引 /Hint）
CDC（Change Data Capture）与数据管道
审计、安全与合规（TLS/ 加密 / 审计日志 / 口令策略）
小型实战：零停机字段改名（影子列 + 回填 + 切换）
练习题（含参考答案）

适用：MySQL 8.0（InnoDB）。默认承接 shop 库。命令与参数按环境适配。

1) Online DDL 与零停机策略

1.1 ALGORITHM / LOCK 速写

ALGORITHM：INSTANT（极快，受限场景，如在表尾新增列等）、INPLACE（在线，多数变更）、COPY（离线，复制临时表）。
LOCK：NONE（最友好）、SHARED（读锁）、EXCLUSIVE（写锁）。

-- 显式声明算法与锁级别（若不支持会报错，避免隐式退化为 COPY）ALTER TABLE orders
  ADD COLUMN channel VARCHAR(16) NULL
  ALGORITHM=INPLACE, LOCK=NONE;  -- 支持与否以实际版本为准

原则：先在 影子库 / 影子表 演练；线上变更一律显式指明 ALGORITHM/LOCK 并验证。

1.2 Expand → Contract 模式（两段式迁移）

Expand：向后兼容地“加东西”（加列 / 加索引 / 加表；新旧代码均可用）。
Contract：验证新路径稳定后，清理旧字段 / 旧索引 / 旧代码。

字段改名（推荐走影子列，不直接 RENAME）：

ADD COLUMN new_col（可空）；
回填历史（批次 / 限流）；
触发器或应用层双写保证新旧一致；
应用切读 new_col；
移除双写；
（可选）删除旧列 old_col。

2) gh-ost / pt-online-schema-change

2.1 工具对比

pt-online-schema-change（Percona）：成熟、依赖触发器；对高写入表有额外负担。
gh-ost（GitHub）：基于 binlog，低侵入，可在主或影子副本执行，支持自动限流。

2.2 pt-osc 快速示例

pt-online-schema-change \
  --alter "ADD COLUMN channel VARCHAR(16)" \
  --execute \
  --host=127.0.0.1 --user=root --password=*** \
  D=shop,t=orders
# 常用参数：--max-lag=1s --chunk-time=0.1 --check-interval=1 --nocheck-replication-filters

2.3 gh-ost 快速示例

gh-ost \
  --host=127.0.0.1 --user=root --password=*** \
  --database=shop --table=orders \
  --alter="ADD COLUMN channel VARCHAR(16)" \
  --allow-on-master --cut-over=default --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 --exact-rowcount --approve-renamed-columns \
  --execute

生产要点：流量低谷执行；设置 max-load/critical-load；对延迟敏感业务优先选 gh-ost。

3) 蓝绿 / 灰度发布与读写治理（ProxySQL）

3.1 最小可用拓扑

Writer：主库（Hostgroup 10）
Readers：只读副本池（Hostgroup 20）

-- ProxySQL 管控示例（伪脚本）INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES
 (10,'db-writer',3306), (20,'db-replica-1',3306), (20,'db-replica-2',3306);

-- 基本读写分流规则
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
 (10,1,'^\s*SELECT',20,1),
 (20,1,'.*',10,1);  -- 其他走写

-- 副本延迟保护（示例）：超过 1s 延迟则摘除
UPDATE mysql_servers SET max_replication_lag=1 WHERE hostgroup_id=20;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

灰度切换思路：在 ProxySQL 以权重/ 白名单逐步引流；对比双写或双读的一致性指标。

4) 连接池与可用性工程

短事务，少长连接：避免持有事务跨网络调用。
超时与重试：读请求可幂等重试，写请求谨慎（确保幂等键或去重表）。
池大小：pool_size ≈ (CPU_cores × 2~4)/ 实例；应用池总和 < DB max_connections 的 60–70%。
保护阈值：设置 wait_timeout、lock_wait_timeout、innodb_lock_wait_timeout 合理值。
预编译 与批处理：减少解析与往返；将小写入聚合为批。

5) 基准测试与容量评估（sysbench）

# 安装略。准备 8 张 100 万行的测试表
aaa=sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=*** \
  --mysql-db=shop --tables=8 --table-size=1000000 prepare

# 压测（并发 64，持续 5 分钟）sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=*** \
  --mysql-db=shop --tables=8 --table-size=1000000 \
  --threads=64 --time=300 run

# 清理
aaa=sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=*** \
  --mysql-db=shop --tables=8 --table-size=1000000 cleanup

读指标：TPS、95/99 分位延迟、错误率、InnoDB 缓存命中、磁盘队列。基线入库并与上线后对比。

6) 优化方法论与计划稳定性

6.1 SARGable（可索引化）

避免 WHERE func(col)=...；改为生成列 / 函数索引。
将 OR 分解为 UNION ALL 以命中索引。

6.2 直方图（提高基数估计）

-- 为选择性差的列建立直方图（持久）ANALYZE TABLE products UPDATE HISTOGRAM ON color WITH 32 BUCKETS;
-- 查看
SELECT * FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME='shop' AND TABLE_NAME='products';

6.3 不可见索引与 Hint

-- 先把新索引设为不可见，验证计划再转可见
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at) INVISIBLE;
EXPLAIN SELECT * FROM orders WHERE user_id=1 AND created_at>=NOW()-INTERVAL 30 DAY;
ALTER TABLE orders ALTER INDEX idx_orders_user_created VISIBLE;

-- Hint（谨慎使用）SELECT /*+ INDEX(o idx_orders_user_created) */ *
FROM orders o WHERE user_id=1 AND created_at>=NOW()-INTERVAL 30 DAY;

原则：索引先不可见验证 → 可见；不要过度依赖 Hint 固化计划，避免升级后遗留。

7) CDC 与数据管道

binlog 基于行（ROW）；为 CDC 专设账号，授予 REPLICATION SLAVE/CLIENT。
工具：Debezium/Maxwell/Canal 等，将变更推入 Kafka/ 消息队列。

-- CDC 账号
CREATE USER 'cdc'@'%' IDENTIFIED BY 'Cdc@123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc'@'%';
FLUSH PRIVILEGES;

设计要点：

事件有序与去重（primary_key + binlog file/pos 或 GTID）。
下游幂等写入（UPSERT）与重放能力（Checkpoints）。
大表回填（Snapshot）与增量（Binlog）打通，避免双写冲突。

8) 审计、安全与合规

TLS：为客户端连接启用 REQUIRE SSL；服务端配置证书。
加密：表空间 /Redo/Undo 加密 + Keyring（文件 /HashiCorp/Vault 等）。
审计日志：启用审计插件，最小化记录范围（登录、DDL、敏感表 DML）。
口令策略 ：validate_password 插件；最小权限基于角色分配；定期轮换。
数据脱敏：视图 / 列级权限；必要时使用应用层 Token 化与 KMS 托管密钥。

9) 小型实战：零停机字段改名

目标：把 orders.status 改为 orders.state，不中断服务。

9.1 Expand（增加影子列）

ALTER TABLE orders ADD COLUMN state ENUM('paid','canceled','refunded') NULL ALGORITHM=INPLACE, LOCK=NONE;

9.2 回填（分批 / 限流）

-- 按主键分块回填（示例，每次 10k 行）UPDATE orders SET state = status WHERE order_id BETWEEN :l AND :r;
-- 循环区间：应用脚本 / 存储过程控制，间隔小睡，监控 IO/ 复制延迟

9.3 双写保持一致

方式 A：触发器（写入时同步）

DELIMITER $$
CREATE TRIGGER trg_orders_au AFTER UPDATE ON orders FOR EACH ROW
BEGIN
  IF NEW.state IS NULL THEN SET NEW.state = NEW.status; END IF; -- 兜底
END $$
DELIMITER ;

方式 B：应用层双写（推荐，透明可控）。

9.4 切读与回收

应用切换读取 state；
观察 1~2 个发布周期；
移除双写 / 触发器；
（可选）删除旧列 status：

ALTER TABLE orders DROP COLUMN status ALGORITHM=INPLACE, LOCK=NONE;

验收清单：

行数 / 校验和一致（可借助 pt-table-checksum）。
复制延迟始终 < 阈值；
慢日志无新增长尾查询；
回滚预案与开关明确。

10) 练习题（含参考答案）

练习 1： 为 orders(user_id, created_at) 新增联合索引，先以 不可见 方式上线，验证后再切换可见。

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at) INVISIBLE;
EXPLAIN SELECT * FROM orders WHERE user_id=1 AND created_at>=NOW()-INTERVAL 30 DAY;
ALTER TABLE orders ALTER INDEX idx_orders_user_created VISIBLE;

练习 2： 用 ANALYZE TABLE ... UPDATE HISTOGRAM 为 products.color 建立 64 桶直方图并观察对查询计划的影响。

ANALYZE TABLE products UPDATE HISTOGRAM ON color WITH 64 BUCKETS;
EXPLAIN SELECT * FROM products WHERE color IN ('black','white');

练习 3： 给出一条 gh-ost 命令，将 orders 表新增列 extra_notes VARCHAR(255)，设置当 Threads_running>80 时限流并在切换前暂停确认。

gh-ost --host=127.0.0.1 --user=root --password=*** \
  --database=shop --table=orders \
  --alter="ADD COLUMN extra_notes VARCHAR(255)" \
  --max-load=Threads_running=80 --test-on-replica --exact-rowcount \
  --cut-over=default --approve-renamed-columns --execute

练习 4： 写出 ProxySQL 规则，使 SELECT ... FOR UPDATE 始终走主库（避免读写不一致）。

INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
 (15,1,'FOR\\s+UPDATE',10,1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

练习 5： 设计一条 CDC 账号与最小权限授权的 SQL，并说明为什么需要 REPLICATION CLIENT。

CREATE USER 'cdc'@'%' IDENTIFIED BY 'Cdc@123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc'@'%';
-- REPLICATION CLIENT 允许读取 master status / binlog 列表与位点，便于从指定位置继续。

小结

任何变更先演练，线上显式 ALGORITHM/LOCK，遵循 Expand→Contract。
在线 DDL 不等于“零风险”：配合 gh-ost/pt-osc、慢日志与复制延迟监控。
读写分流要有延迟保护与灰度回滚；连接池小心重试语义。
sysbench 建立性能基线，配合直方图 / 不可见索引实现计划稳定。
用 CDC 打通在线库到分析 / 搜索；用 TLS/ 加密 / 审计守住底线。

打赏赞

MySQL 入门教程 · 第五课

目录