知序
返回
保存草稿
发布
H1
H2
H3
1
2
3
Excalidraw 图
AI 写作
AI 优化
目录
输入内容后显示目录
Markdown
# 08 - DDL、表设计与字符集 ## Online DDL 是什么 ``` Online DDL 的目标是: 在执行 ALTER TABLE 这类改表操作时, 尽量减少对业务读写的阻塞。 ``` ``` 但“尽量减少阻塞”不等于“完全不阻塞”。 DDL 在开始和结束阶段通常仍然需要拿到更强的元数据锁。 ``` ## 常见 DDL 算法 ``` 1. COPY 新建临时表,拷贝数据,成本高,阻塞更重 2. INPLACE 尽量在原表基础上改,很多场景比 COPY 更轻 3. INSTANT 只改元数据,不重建表,速度最快 ``` ``` 优先级通常是: 能 INSTANT 就优先 INSTANT, 不行再看能不能 INPLACE, 最后才退化到 COPY。 ``` 三种 DDL 算法执行过程对比:  *图:Online DDL 三种算法与 MDL 锁持有时间* ``` INSTANT(MySQL 8.0.12+,只改元数据): ┌──────────────┐ │ 修改数据字典 │ ← 仅改 metadata,秒级完成 │ 不触碰数据文件 │ └──────────────┘ 耗时:秒级,即刻完成 示例:ALTER TABLE t ADD COLUMN c INT(8.0.12+ 支持) ALTER TABLE t ALTER COLUMN c SET DEFAULT 1 INPLACE(原地修改,不重建表): ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ 准备阶段 │→│ 执行阶段 │→│ 提交阶段 │ │ 获取 MDL 锁 │ │ 原地修改 │ │ 更新元数据 │ │ 初始化 │ │ (可能扫描表) │ │ 释放 MDL 锁 │ └──────────────┘ └──────────────┘ └──────────────┘ 耗时:分钟级(取决于表大小) 示例:ALTER TABLE t ADD INDEX idx_name(name) ALTER TABLE t DROP INDEX idx_name COPY(重建表,代价最高): ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ 创建临时表│→│ 复制数据 │→│ 切换表 │→│ 删除旧表 │ │ (新结构) │ │ (逐行拷贝)│ │ (rename) │ │ │ └──────────┘ └──────────┘ └──────────┘ └──────────┘ 耗时:小时级(取决于表大小),需要额外磁盘空间 示例:ALTER TABLE t MODIFY COLUMN name VARCHAR(255) ALTER TABLE t ENGINE=InnoDB 如何判断一条 DDL 用哪种算法? EXPLAIN 不会直接告诉你 需要查官方文档或经验: ├── 修改默认值、添加列(8.0.12+)→ INSTANT ├── 添加/删除索引、修改 AUTO_INCREMENT → INPLACE └── 修改列类型、修改字符集 → COPY(需要重建表) MDL 锁与 Online DDL: 不管哪种算法,DDL 在开始和结束都需要 MDL(元数据锁) INSTANT:MDL 持有时间极短(毫秒级) INPLACE:准备和提交阶段需要 MDL(秒级) COPY:全程持有 MDL(分钟~小时级) 如果线上有长事务或慢 SQL 持有 MDL 读锁 → DDL 申请 MDL 写锁被阻塞 → DDL 阻塞后续所有请求 → 雪崩! ```   [编辑 Excalidraw 源图](/images/excalidraw/08-ddl-table-design-online-ddl-algorithms-diagram.excalidraw) ## 线上大表做 DDL 变更,一般怎么做 ``` 思路不是“直接跑 ALTER TABLE”, 而是先评估风险: 1. 是否支持 INSTANT / INPLACE 2. 会不会重建大表 3. 会不会造成主从延迟 4. 会不会被长事务卡住 MDL ``` ``` 常见做法: 1. 先在测试环境或从库验证 2. 选择低峰执行 3. 大表、高风险变更优先考虑 gh-ost / pt-osc 4. 准备监控、限流和回滚方案 ```  [编辑 Excalidraw 源图](/images/excalidraw/08-ddl-table-design-large-table-ddl-diagram.excalidraw) ## 为什么很多团队不推荐外键 ``` 外键的优点是能保证引用完整性, 但在高并发和分布式场景里问题也很明显: 1. 增加写入和删除时的约束检查成本 2. 更容易带来锁冲突 3. 不利于分库分表 4. 级联更新、级联删除容易放大风险 ``` ``` 所以很多互联网团队更倾向于: 把“关联约束”放到应用层显式控制。 ``` ## truncate、delete、drop 的区别 ``` DELETE: 删除数据,通常可按条件删 逐行删除,事务和回滚语义更完整 TRUNCATE: 清空整张表 更快,通常可理解为重建表 DROP: 直接删除表结构和数据 ``` ``` 结论: 删部分数据用 DELETE, 清空整表常用 TRUNCATE, 连表本身都不要了才用 DROP。 ``` ## 为什么要尽量避免 SELECT * ``` SELECT * 的问题: 1. 多传输不需要的列 2. 更难命中覆盖索引 3. 表结构变更后结果不稳定 4. 容易让调用方和表结构耦合过深 ``` ``` 所以更推荐: 按需查询明确字段。 ``` ## varchar(100) 和 varchar(10) 有什么区别 ``` 它们都属于变长字段, 不是说 varchar(100) 就一定比 varchar(10) 多占很多空间。 真正差异主要体现在: 1. 可存储的最大长度不同 2. 排序和临时表场景里,宽字段可能带来更高代价 3. 索引场景下,字段越长,索引占用越大 ``` ## utf8mb3 vs utf8mb4 ``` utf8mb3: 最多 3 字节 无法完整表示 emoji 等 4 字节字符 utf8mb4: 最多 4 字节 能完整支持 Unicode ``` ``` 生产环境结论很简单: 新系统优先统一用 utf8mb4。 ``` ## 面试常见问题 **Q:为什么 Online DDL 也可能把业务卡住?** A:因为 Online DDL 不是完全无锁。它在开始和结束阶段仍然需要申请更强的 MDL 锁,如果线上有长事务或慢 SQL 长时间占着元数据读锁,DDL 就可能卡住,甚至反过来堵住后续请求。 **Q:什么时候会考虑 gh-ost 或 pt-osc?** A:大表改列、加索引、主从结构明确且对切换窗口敏感时,常用这类工具。它们通过影子表和增量同步,把直接改原表的风险降下来,更适合高风险线上 DDL。 **Q:为什么很多公司强制用 utf8mb4?** A:因为 utf8mb3 不能完整表示 4 字节字符,比如 emoji。只要系统里有昵称、评论、国际化文本这类场景,utf8mb4 才是更稳妥的默认选择。 --- 上一篇:[07 - 主从复制与高可用](./07-replication-high-availability.md) | 下一篇:[09 - 加密与安全](./09-security.md)
预览
发布文章
添加摘要让读者更快了解文章内容
取消
确认发布
AI 写作助手
输入一个主题,AI 将为你生成一篇 Markdown 博客文章
AI 正在写作中...
取消
开始生成
AI 优化助手
描述你的修改需求,AI 将在原文基础上进行补充或微调
AI 正在优化中...
取消
开始优化
画布
从已有图里选
✕
图名
保存后生成 /images/excalidraw/<图名>.svg(只能用字母、数字、短横线、下划线、点)
取消
保存并插入