MySQL 大表结构优化：从设计到迁移流程-linux运维-PHP中文网

大表结构优化需分三步：先稳住业务、再提升性能、最后释放扩展能力，每步须可验证、可回滚、影响最小。

mysql 大表结构优化：从设计到迁移流程

大表结构优化不是只改几个字段或加个索引就能解决的事，核心在于“先稳住业务、再提升性能、最后释放扩展能力”。关键不在于一步到位，而在于每一步都可验证、可回滚、对线上影响最小。

很多大表问题，其实埋在建表之初。字段冗余、类型宽泛、默认值滥用、NULL泛滥，都会随着数据增长被放大。

用最小够用类型：比如用户状态用 TINYINT UNSIGNED（0–255），不用 INT；时间戳优先选 TIMESTAMP（自动时区转换+更省空间）；IP 地址存为 INT UNSIGNED，别用 VARCHAR(15)
避免 NULL：字段设为 NOT NULL + 默认值，既减少索引开销，也规避查询时的 IS NULL 判定陷阱
单表字段数控制在 20 以内：超了就考虑垂直拆分，比如把日志类、扩展属性类字段拎到附表
主键必须是自增整型或雪花 ID：别用 UUID 或字符串主键，B+Tree 索引分裂和插入热点问题会随数据量指数级恶化

索引不是越多越好，而是要匹配真实查询模式。大表上一个错误的索引，可能比没索引还伤性能。

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

用 EXPLAIN 看执行计划，重点关注 type（是否走索引）、key（用哪个索引）、rows（扫描行数）、Extra（是否 Using filesort / Using temporary）
组合索引遵循“最左前缀”：WHERE a = ? AND b > ? ORDER BY c，适合建 INDEX(a,b,c)；但 WHERE b = ? 就无法命中
高频查询尽量走 覆盖索引：SELECT id, name, email WHERE status=1 AND created_at > '2025-01-01'，可建 INDEX(status, created_at, id, name, email)
删除无用索引：长期未被 used 的索引（可通过 performance_schema.table_io_waits_summary_by_index_usage 查）及时清理，减少写入开销和内存占用

线上大表改结构，严禁直接 ALTER TABLE。必须走“新建→迁移→校验→切换→清理”路径，全程不锁主表、不中断写入。

建新表：用 CREATE TABLE new_table LIKE old_table 复制结构，再手动调整字段类型、约束、注释；索引全部暂不创建
分批迁移：按主键自增 ID 或时间字段分段，每次拉 5k–1w 行，用事务包裹，避免长事务阻塞：
```
INSERT INTO new_table SELECT * FROM old_table WHERE id BETWEEN 1000001 AND 1010000;
```
双写过渡期（可选）：应用层同时写新旧两表，确保增量数据不丢；或通过触发器/监听 binlog 补充同步
原子切换：停写一小段时间（秒级），用 RENAME TABLE old_table TO old_bak, new_table TO old_table 完成切换；切换后立刻开启新表写入
后续收尾：新表上线后补建索引（此时数据已静止，建索引更快）、校验数据行数和 checksum（如 pt-table-checksum）、归档 old_bak 表

优化不是终点，而是让系统进入可持续运维状态。

设置 慢查询阈值 ≤ 100ms，每天分析 top SQL，建立“慢查-索引-改写”闭环
对历史数据做分级归档：近 90 天热数据留主表；1 年内温数据迁入压缩表（ROW_FORMAT=COMPRESSED）；超 1 年冷数据导出至 ClickHouse 或对象存储
启用 自动分区（如按月 RANGE 分区），配合 ALTER TABLE ... DROP PARTITION 快速清理过期数据，避免 DELETE 全表扫描
定期检查 表碎片率（DATA_FREE / DATA_LENGTH），超 30% 时用 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 在低峰重建