mysql从MyISAM迁移到InnoDB的步骤与优化建议

并非所有MyISAM表都需迁移至InnoDB；只读或极小表（≤1GB）可不迁，否则用pt-online-schema-change工具无锁转换；MySQL 8.0+中ENGINE变更仍需COPY，不支持INPLACE；迁移后须调优innodb_buffer_pool_size（50%–75%物理内存）、降低key_buffer_size、酌情设innodb_flush_log_at_trx_commit=2，并处理FULLTEXT语法差异、NOT NULL主键约束、INSERT DELAYED替换及事务隔离与隐式提交问题。

确认表是否真的需要迁移

不是所有 MyISAM 表都该迁到 InnoDB。如果表是只读的、数据量极小（FULLTEXT 索引（MySQL 5.6 之前 InnoDB 不支持），强行迁移反而增加开销。先运行：

SELECT table_name, engine, table_rows FROM information_schema.tables WHERE table_schema = 'your_db' AND engine = 'MyISAM';

ALTER TABLE 转换时避免锁表失败

直接执行 ALTER TABLE t ENGINE=InnoDB 在大表上会锁表，且可能因临时空间不足失败。关键点：

• 确保 innodb_file_per_table=ON（MySQL 5.6+ 默认开启），否则所有表共享 ibdata1，后续难以回收空间
• 检查磁盘剩余空间：转换过程需约 2 倍原表大小（MyISAM .MYD + .MYI → InnoDB 的 .ibd + redo log）
• 对大表（>1GB），改用 pt-online-schema-change 工具，它通过触发器实现无锁变更，命令形如：

  pt-online-schema-change --alter "ENGINE=InnoDB" D=your_db,t=your_table --execute

• 若用 MySQL 8.0+，可启用 ALGORITHM=INPLACE（但仅限部分操作支持，ENGINE 变更仍需 COPY）

迁移后必须调整的 InnoDB 配置项

MyISAM 和 InnoDB 的行为差异极大，不调参会导致性能反降：

• innodb_buffer_pool_size 必须设为物理内存的 50%–75%，否则大量数据从磁盘读取，比 MyISAM 还慢
• 关闭 innodb_flush_log_at_trx_commit=2（非金融场景）可提升写入吞吐，但崩溃可能丢失 1 秒数据
• MyISAM 的 key_buffer_size 应大幅调低（如从 256M 改为 32M），避免内存争抢
• InnoDB 默认 innodb_lock_wait_timeout=50，而 MyISAM 无锁等待概念——高并发更新时，应用层需捕获 Lock wait timeout exceeded 错误并重试

全文索引与自增主键的兼容性处理

MySQL 5.6+ 的 InnoDB 支持 FULLTEXT，但语法和行为与 MyISAM 不同：

• MyISAM 的 MATCH ... AGAINST 中布尔模式（+/-）在 InnoDB 中部分不支持（如嵌套括号），需测试验证
• InnoDB 的 FULLTEXT 索引重建耗时长，且不支持 REPAIR TABLE，出错只能 DROP + CREATE
• MyISAM 允许 NULL 列做 AUTO_INCREMENT，InnoDB 不允许——迁移前需检查：SHOW CREATE TABLE t，确保主键列定义为 NOT NULL
• 若原表用 INSERT DELAYED（MyISAM 特有），必须改用普通 INSERT 或队列异步处理