MySQL 什么时候使用 MyISAM 和 InnoDB？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15678406/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When to use MyISAM and InnoDB?
提问by ajay
MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.
MyISAM 的设计理念是，您的数据库被查询的次数远远超过其更新次数，因此它执行非常快速的读取操作。如果您的读写（插入|更新）比率小于 15%，最好使用 MyISAM。
InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance
InnoDB 使用行级锁定，具有提交、回滚和崩溃恢复功能来保护用户数据。支持事务和容错
above differences is correct between MyISAM and InnobDB? please guide if any other limitations are there for MYISAM and InnobDB. when should i use MyiSAM or when Innodb?Thank you!
MyISAM 和 InnobDB 之间的上述差异是否正确？请指导MYISAM和InnobDB是否有任何其他限制。我什么时候应该使用 MyiSAM 或 Innodb？谢谢！
回答by Tony Stark
Read about Storage Engines.
阅读有关存储引擎的信息。
MyISAM:
我的ISAM：
The MyISAMstorage engine in MySQL.
MySQL 中的MyISAM存储引擎。
- Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
- Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. -- Mostly no longer true.
- Full-text indexing. -- InnoDB has it now
- Especially good for read-intensive (select) tables. -- Mostly no longer true.
- Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM.
- 设计和创建更简单，因此更适合初学者。不用担心表之间的外来关系。
- 由于结构更简单，因此总体上比 InnoDB 更快，因此服务器资源的成本要低得多。- 大多数情况下不再正确。
- 全文索引。-- InnoDB 现在有了
- 特别适用于读取密集型（选择）表。- 大多数情况下不再正确。
- 磁盘占用空间比 InnoDB 少 2 到 3 倍。-- 从 5.7 版开始，这可能是 MyISAM 唯一真正的优势。
InnoDB:
InnoDB：
The InnoDBstorage engine in MySQL.
MySQL 中的InnoDB存储引擎。
- Support for transactions (giving you support for the ACIDproperty).
- Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
- Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
- InnoDB is more resistant to table corruption than MyISAM.
- Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
- MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0.
- 支持事务（为您提供对ACID属性的支持）。
- 行级锁定。与例如MyISAM相比，拥有更细粒度的锁定机制可为您提供更高的并发性。
- 外键约束。让你让数据库保证数据库状态的完整性，以及表之间的关系。
- InnoDB 比 MyISAM 更能抵抗表损坏。
- 支持数据和索引的大型缓冲池。MyISAM 密钥缓冲区仅用于索引。
- MyISAM 停滞不前；所有未来的增强都将在 InnoDB 中进行。随着 8.0 版的推出，这一点变得非常清楚。
MyISAM Limitations:
MyISAM 限制：
- No foreign keys and cascading deletes/updates
- No transactional integrity (ACID compliance)
- No rollback abilities
- 4,284,867,296 row limit (2^32) -- This is old default. The configurable limit (for many versions) has been 2**56 bytes.
- Maximum of 64 indexes per table
- 没有外键和级联删除/更新
- 没有事务完整性（ACID 合规性）
- 没有回滚能力
- 4,284,867,296 行限制 (2^32)——这是旧的默认值。可配置的限制（对于许多版本）为 2**56 字节。
- 每个表最多 64 个索引
InnoDB Limitations:
InnoDB 限制：
- No full text indexing (Below-5.6 mysql version)
- Cannot be compressed for fast, read-only (5.5.14 introduced
ROW_FORMAT=COMPRESSED) - You cannot repair an InnoDB table
- 没有全文索引（低于 5.6 mysql 版本）
- 无法快速压缩，只读（5.5.14 引入
ROW_FORMAT=COMPRESSED） - 您无法修复 InnoDB 表
For brief understanding read below links:
如需简要了解，请阅读以下链接：
回答by Argeman
Use MyISAM for very unimportant data or if you really need those minimal performance advantages. The read performance is not better in every case for MyISAM.
将 MyISAM 用于非常不重要的数据，或者如果您确实需要那些最小的性能优势。MyISAM 的读取性能并非在每种情况下都更好。
I would personally never use MyISAM at all anymore. Choose InnoDB and throw a bit more hardware if you need more performance. Another idea is to look at database systems with more features like PostgreSQL if applicable.
我个人永远不会再使用 MyISAM。如果您需要更高的性能，请选择 InnoDB 并投入更多的硬件。另一个想法是查看具有更多功能的数据库系统，例如 PostgreSQL（如果适用）。
EDIT: For the read-performance, this link shows that innoDB often is actually notslower than MyISAM: http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
编辑：对于读取性能，此链接显示 innoDB 实际上通常并不比 MyISAM 慢：http: //www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part -1/
