mysql选择存储引擎的原则与性能考虑

绝大多数新项目应选innodb，因其支持行级锁、外键、acid事务和崩溃自动恢复；myisam仅适合只读场景，存在表级锁、无事务、崩溃需手动修复等缺陷。

MyISAM 和 InnoDB 选哪个？看事务和崩溃恢复需求

绝大多数新项目应该直接选 InnoDB，除非你明确知道自己的表只读、不更新、且对事务完全无感。mysql 5.5+ 默认引擎已是 InnoDB，不是偶然——它支持行级锁、外键、ACID 事务、崩溃后自动恢复；而 MyISAM 只有表级锁、无事务、崩溃后可能丢数据或需手动 REPAIR table。

常见误用场景：MyISAM 被用于用户订单表（哪怕初期并发低），结果某次断电后索引文件损坏，select count(*) 返回错值，修复时还要停服务。

• 需要 ROLLBACK 或 SAVEPOINT？必须 InnoDB
• 表会频繁 UPDATE/delete？MyISAM 的表级锁会成瓶颈
• 用到 FOREIGN KEY？MyISAM 完全忽略该语法，不报错也不生效
• 备份依赖 mysqldump --single-transaction？这功能只对 InnoDB 有效

Memory 引擎适合什么场景？别当主库存储用

Memory 引擎把全部数据存在内存里，速度极快，但实例重启就清空——它不是缓存替代品，而是为特定临时结构设计的。

典型合理用法：etl 过程中做中间聚合表、IP 地址转城市名的只读映射表（配合应用层定期重载）、测试环境模拟超大数据集的骨架表。

• 不能存 BLOB/TEXT 字段（Memory 不支持）
• MAX_HEAP_TABLE_SIZE 参数限制单表内存上限，默认 16MB，超了会报 Error 1114 (HY000): The table 'xxx' is full
• 即使数据量小，也别把用户 session 表设成 Memory——服务重启即登出，体验崩坏
• 索引只能是 HASH（等值查询快）或 BTREE（范围查询可用），建表时得显式指定 using HASH

Archive 引擎真能省空间？先看查询模式

Archive 是压缩型只插入引擎，适合日志归档、审计记录这类「写一次、查极少、几乎不删」的场景。它用 zlib 压缩行，磁盘占用通常只有 InnoDB 的 15%~30%，但代价明显：

• 不支持索引（除自增主键外），WHERE 条件只能靠全表扫描 + 解压每一行
• 没有事务，INSERT 是原子的，但 SELECT 过程中其他线程不能写入该表
• OPTIMIZE TABLE 会重建并重新压缩整个表，期间锁表时间可能很长
• 如果业务要按时间范围查最近 7 天日志，Archive 会比带时间索引的 InnoDB 慢两个数量级

更务实的做法：用 InnoDB 存最近 3 个月热数据，按月分区；过期数据导出为压缩 CSV 归档，或转入 Archive 表（但确保查询入口已屏蔽对它的直接 SELECT）。

引擎切换要注意哪些坑？ALTER TABLE 不只是改个名字

执行 ALTER TABLE t ENGINE = InnoDB 不是元数据更新，而是重建整张表：MySQL 会创建新表、逐行拷贝、重建索引、再原子替换。过程受 innodb_online_alter_log_max_size 和磁盘 I/O 制约，大表可能卡住数小时。

尤其注意这些隐性成本：

• 原表在转换期间仍可读写，但 DDL 会持有一个 SNW（shared-no-write）锁，阻塞后续 ALTER、DROP 等操作
• 若原表是 MyISAM，其 FULLTEXT 索引不会自动迁移到 InnoDB，需手动 ADD FULLTEXT 并重建
• ROW_FORMAT 默认变为 DYNAMIC，若旧 MyISAM 表有超长 VARCHAR 字段，可能触发 ERROR 1118 (42000): Row size too large
• 从 Archive 切到 InnoDB 时，原表的自增 ID 可能丢失连续性，因 Archive 的 AUTO_INCREMENT 仅保证唯一不保证递增

ALTER TABLE logs_old ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;

线上操作前务必在从库验证耗时，并确认 tmpdir 有足够空间（重建过程会生成临时文件）。