从 MyISAM 更改为 InnoDB 时有哪些陷阱/您需要了解的事情
我的一个项目在 MySQL 中使用 MyISAM 引擎，但我正在考虑将其更改为 InnoDB，因为我到处都需要事务支持。
- 在这样做之前我应该查看或考虑什么？
- 我可以只更换引擎吗，还是应该为此准备数据？
One of my projects use the MyISAM engine in MySQL, but I'm considering changing it to InnoDB as I need transaction support here and there.
- What should I look at or consider before doing this?
- Can I just change the engine, or should the data be prepared for it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
是的，当然，有很多事情，您应该非常彻底地测试您的应用程序：
迁移过程本身需要仔细计划，因为如果您有大量数据，则需要很长时间（在此期间数据要么是只读的，要么完全不可用 - 请检查！）
Yes absolutely, there are many things, you should test your application extremely thoroughly:
The migration process itself will need to be carefully planned, as it will take a long time if you have a lot of data (during which time the data will be either readonly, or completely unavailable - do check!)
有一个很大的警告。 如果在写入过程中遇到任何类型的硬件故障（或类似的故障），InnoDB 将损坏表。
MyISAM 也会，但是 mysqlcheck --auto-repair 将修复它们。 对 InnoDB 表尝试此操作将会失败。 是的，这是经验之谈。
这意味着你需要有一个良好的定期数据备份计划才能使用InnoDB。
There is one big caveat. If you get any kind of hardware failure (or similar) during a write, InnoDB will corrupt tables.
MyISAM will also, but a mysqlcheck --auto-repair will repair them. Trying this with InnoDB tables will fail. Yes, this is from experience.
This means you need to have a good regular data backup plan to use InnoDB.
其他一些注意事项：
在删除表/数据库或删除记录后，InnoDB 不会重新分配文件系统上的可用空间，这可以通过“转储和导入”或在 my.cnf 中设置
innodb_file_per_table=1来解决。 cnf。在大型 InnoDB 表上添加/删除索引可能非常痛苦，因为它会锁定当前表，使用更改的索引创建一个临时表并逐行插入数据。 Innobase 有一个插件，但它可以工作仅适用于 MySQL 5.1
InnoDB 的内存消耗也更大，我建议您在服务器内存允许的情况下使用尽可能大的
innodb_buffer_pool_size变量（70-80% 应该是一个安全的选择）。 如果您的服务器是 UNIX/Linux，请考虑将 sysctl 变量vm.swappiness减少为 0 并使用innodb_flush_method=O_DIRECT以避免双缓冲。 始终测试切换这些值时是否点击交换。您可以随时在 Percona 博客，这很棒。此外，您可以使用
--single-transaction --skip-lock-tables运行mysqlbackup，并且在备份开始时没有表锁。无论如何，InnoDB 是伟大的，不要让一些陷阱让你灰心。
Some other notes:
InnoDB does not reallocate free space on the filesystem after you drop a table/database or delete a record, this can be solved by "dumping and importing" or setting
innodb_file_per_table=1in my.cnf.Adding/removing indexes on a large InnoDB table can be quite painfull, because it locks the current table, creates a temporary one with your altered indexes and inserts data - row by row. There is a plugin from Innobase, but it works only for MySQL 5.1
InnoDB is also MUCH MORE memory intense, I suggest you to have as large
innodb_buffer_pool_sizevariable as your server memory allows (70-80% should be a safe bet). If your server is UNIX/Linux, consider reducing sysctl variablevm.swappinessto 0 and useinnodb_flush_method=O_DIRECTto avoid double buffering. Always test if you hit swap when toggling those values.You can always read more at Percona blog, which is great.Also, you can run
mysqlbackupwith--single-transaction --skip-lock-tablesand have no table locks while the backup is commencing.In any case, InnoDB is great, do not let some pitfalls discourage you.
只需更改表格并设置引擎就可以了。
select count(*) from MyTable在 InnoDB 中比 MyISAM 慢得多。Just altering the table and setting the engine should be fine.
select count(*) from MyTableis much slower in InnoDB than MyISAM.如果您打算使用 InnoDB 作为获得并发查询的方式，那么您将需要设置 innodb_file_trx_commit=1 以便获得一些性能恢复。 OTOH，如果您希望重新编码您的应用程序以使其具有事务感知能力，那么决定此设置将是 InnoDB 设置所需的一般性能审查的一部分。
另一个需要注意的主要事情是 InnoDB 不支持 FullText 索引，也不支持 INSERT DELAYED。 但是，MyISAM 不支持引用完整性。 :-)
但是，您只能移动需要事务感知的表。 我已经做到了这一点。 小表（最多几千行）通常可以顺便进行即时更改。
If you are intending to use InnoDB as a way to get concurrent queries, then you will want to set
innodb_file_trx_commit=1so you get some performance back. OTOH, if you were looking to re-code your application to be transaction aware, then deciding this setting will be part of the general performance review needed of the InnoDB settings.The other major thing to watch out for is that InnoDB does not support FullText indices, nor INSERT DELAYED. But then, MyISAM doesn't support referential integrity. :-)
However, you can move over only the tables you need transaction aware. I've done this. Small tables (up to several thousand rows) can often be changed on-the-fly, incidentally.
性能特征可能不同，因此您可能需要密切关注负载。
数据就会好的。
The performance characteristics can be different, so you may need to keep an eye on the load.
The data will be fine.