MyISAM什么时候比InnoDB更好？
有时我在一些采访中被问到：InnoDB 相对于 MyISAM 有哪些优势以及 MyISAM 什么时候比 InnoDB 更好？问题的第一部分都很清楚：InnoDB 是事务兼容的，行级阻塞而不是表级阻塞，外键支持等等，这些点立即浮现在脑海中。
但什么时候MyISAM真的比InnoDB更好呢？
Sometimes I got asked on some interviews: what benefits does InnoDB have against MyISAM and when MyISAM is better than InnoDB? It's all clear about the first part of question: InnoDB is transaction compliant, row-level blocking instead of table-level blocking, foreign key support and some others, these points just came to mind immidiately.
But when MyISAM is really better than InnoDB?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
当您不需要那些高级功能并且存储速度比其他问题更重要时，MyISAM 比 InnoDB 更好。MyISAM 还允许在数据库引擎本身内部执行全文搜索，而不需要查询结果，然后将它们作为数组或应用程序中的任何内容进行搜索。
如果您需要存储具有复杂交互和关系的高保真度数据，InnoDB 是一个合理的选择。如果您需要在短时间内保存或加载大量记录，MyISAM 是一个合理的选择。
我不建议对重要数据使用 MyISAM。它非常适合记录或评论字段或任何您不特别关心记录是否消失在扭曲的虚空中的地方。当您关心数据、不需要快速搜索并且需要使用 MySQL 时，InnoDB 非常适合。
还值得一提的是，InnoDB 支持行级锁定，而 MyISAM 仅支持表级锁定 - 这就是说，对于许多常见情况，InnoDB 可以由于并行执行更多查询而显着更快。
底线：除非绝对必须使用 MyISAM，否则请使用 InnoDB。或者，针对 PostgreSQL 进行开发并充分利用两者的优点。
MyISAM is better than InnoDB when you don't need those advanced features and storage speed is more important than other concerns. MyISAM also allows full-text searches to be performed inside the database engine itself, instead of needing to query results and then search them as an array or whatever in your application.
InnoDB is a reasonable choice if you need to store data with a high degree of fidelity with complicated interactions and relationships. MyISAM is a reasonable choice if you need to save or load a large number of records in a small amount of time.
I wouldn't recommend using MyISAM for data that matters. It's great for logging or comments fields or anything where you don't particularly care if a record vanishes into the twisting nether. InnoDB is good for when you care about your data, don't need fast searches and have to use MySQL.
It's also worth mentioning that InnoDB supports row-level locking, while MyISAM only supports table-level locking - which is to say that for many common situations, InnoDB can be dramatically faster due to more queries executing in parallel.
The bottom line: Use InnoDB unless you absolutely have to use MyISAM. Alternatively, develop against PostgreSQL and get the best of both.
MyISAM 不支持事务（以及提到的其他事情），因此它可以更快地工作。当您不需要这些功能时，MyISAM 是一种获得更高性能的方法。
MyISAM doesn't support transactions (and the other things mentioned) so it can work faster. MyISAM is a way to achieve higher performance in those situations when you do not need these features.
如前所述，MyISAM 支持全文，但也支持 MERGE 表类型。当您有一个大表并且想要定期“交换”/归档其中的一部分时，这很方便。考虑一下您想要保留上一季度和/或一年的日志记录或报告数据。当您主要进行插入而很少更新或删除时，MyISAM 可以更好地处理此类大量数据。
一旦无法将索引放入内存中，InnoDB 性能就会快速显着下降。如果您的主键不是数字（即自动增量），那么您可能需要重新考虑使用 InnoDB。 InnoDB 表上的每个索引都会复制主键。因此，如果您有一个很大的主键和一些其他索引，您的 InnoDB 表将很快变得非常大。
MyISAM supports full text, as mentioned, but also supports the MERGE table type. This is handy when you have a large table and would like to "swap" out/archive parts of it periodically. Think about a logging or report data that you want to keep the last quarter and/or year. MyISAM handles large amounts of data like this better, when you are mainly inserting and rarely updating or deleting.
InnoDB performance drops pretty quickly and dramatically once you can't fit the indexes in memory. If your primary key is not going to be a number (i.e. auto increment), then you may want to rethink using InnoDB. The primary key is replicated for every index on an InnoDB table. So if you have a large primary key and a few other indexes, your InnoDB table will get very large very quick.
MySQL 仅为 MyISAM 实现了一些功能（例如本机全文索引）。
也就是说，InnoDB 对于大多数生产应用程序来说通常仍然更好。
There are a few features that MySQL only has implemented for MyISAM (such as native fulltext indexing).
That said, InnoDB is still typically better for most production apps.
另外：mySQL 中的全文搜索仅在 myISAM 表中受支持。
Also: Full-text search in mySQL is only supported in myISAM tables.
与 InnoDB 相比，MyISAM 的结构非常简单。没有行版本控制，每个表只有一个文件，并且行按顺序存储。然而，虽然它支持并发插入（SELECT 和 1 个 INSERT 可以一起运行），但它也具有表级锁（如果同一个表上有 2 个 INSERT，则 1 个必须等待）。此外，由于数据文件的结构，更新和删除速度很慢。
MyISAM 不支持事务或外键。
一般来说，如果您研究总体趋势（因此您不关心个别行的正确性）并且数据在夜间更新或从不更新，那么 MyISAM 应该会更好。此外，它还允许通过文件系统将单个表从一台服务器移动到另一台服务器。
InnoDB 支持很好的并发和事务。对全文有不错的支持，对外键也有不错的支持。
MyISAM has a very simple structure, when compared with InnoDB. There is no row versioning, there's one file per table and rows are stored sequentially. However, while it supports concurrent inserts (SELECTs and 1 INSERT can run together), it also has table-level locks (if there are 2 INSERTs on the same table, 1 has to wait). Also, UPDATEs and DELETEs are slow because of the structure of the data files.
MyISAM doesn't support transactions or foreign keys.
Generally, MyISAM should be better if you work on general trends (so you don't care about the correctness of individual rows) and data is updated by night or never. Also, it allows to move individual tables from one server to another, via the filesystem.
InnoDB supports very well concurrency and transactions. Has a decent support for fulltext and an almost-decent support for foreign keys.