大表结构优化需分三步：先稳住业务、再提升性能、最后释放扩展能力，每步须可验证、可回滚、影响最小。
大表结构优化不是只改几个字段或加个索引就能解决的事，核心在于“先稳住业务、再提升性能、最后释放扩展能力”。关键不在于一步到位，而在于每一步都可验证、可回滚、对线上影响最小。
结构设计阶段：克制比堆砌更重要
很多大表问题，其实埋在建表之初。字段冗余、类型宽泛、默认值滥用、NULL泛滥，都会随着数据增长被放大。
- 用最小够用类型：比如用户状态用 TINYINT UNSIGNED（0–255），不用 INT；时间戳优先选 TIMESTAMP（自动时区转换+更省空间）；IP 地址存为 INT UNSIGNED，别用 VARCHAR(15)
- 避免 NULL：字段设为 NOT NULL + 默认值，既减少索引开销，也规避查询时的 IS NULL 判定陷阱
- 单表字段数控制在 20 以内：超了就考虑垂直拆分，比如把日志类、扩展属性类字段拎到附表
- 主键必须是自增整型或雪花 ID：别用 UUID 或字符串主键，B+Tree 索引分裂和插入热点问题会随数据量指数级恶化
索引与查询协同优化
索引不是越多越好，而是要匹配真实查询模式。大表上一个错误的索引，可能比没索引还伤性能。
MySQL8 CRM Schema Design Expert
下载
面向 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在低峰重建
