我应该总是更喜欢 MySQL InnoDB 而不是 MyISAM 吗？
刚刚有人告诉我 InnoDB 比 MyISAM 好得多。那么当我创建表时，我应该总是尝试使用InnoDB引擎而不是MyISAM吗？或者两者都有很大的好处吗？
Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
MyISAM是无事务的并且是堆组织的。记录由表中的行偏移量标识，索引将该偏移量存储为行指针。InnoDB支持事务并且是索引组织的。记录由PRIMARY KEY的值标识（如果没有定义PRIMARY KEY，则为隐藏的内部列），并存储在B-Tree 中。二级索引将PRIMARY KEY的值存储为行指针。涉及全表扫描或二级索引查找的查询在 MyISAM 表上通常更快。
涉及
PRIMARY KEY搜索的查询通常在InnoDB表上更快。MyISAM表将表中的记录数存储在表的元数据中，这就是为什么像这样的查询是即时的。
MyISAM表完全锁定在DML操作上（有几个例外）。InnoDB表锁定单个记录和索引间隙，但是这些是扫描的记录和间隙，而不仅仅是那些与WHERE条件匹配的记录和间隙。这可能会导致记录被锁定，尽管它们并不匹配。InnoDB表支持引用完整性（FOREIGN KEY）。MyISAM表则不然。有几种场景可以显示这两种引擎的优势。
MyISAMis transactionless and heap-organized. The records are identified by the row offset in the table and the indexes store this offset as a row pointer.InnoDBsupports transactions and is index-organized. The records are identified by the value of thePRIMARY KEY(or a hidden internal column is there is noPRIMARY KEYdefined) and are stored in aB-Tree. The secondary indexes store the value of thePRIMARY KEYas a row pointer.Queries that involve full table scans or secondary index lookups are usually faster on
MyISAMtables.Queries that involve
PRIMARY KEYseeks are usually faster onInnoDBtables.MyISAMtables store the number of records in the table in the table's metadata, that's why the queries like this:are instant.
MyISAMtables are completely locked on theDMLoperations (with several exceptions).InnoDBtables lock individual records and index gaps, however these are the records and the gaps that are scanned, not only those matched by theWHEREcondition. This can lead to the records being locked despite the fact they don't match.InnoDBtables support referential integrity (FOREIGN KEYs) .MyISAMtables don't.There are several scenarios that can show benefits of both engines.
简单的回答：不，你不应该。
关于每个引擎的几点：
InnoDB
MyISAM
请参阅手册中的更多信息：
Simple answer: No, you shouldn't.
A few points about each engine:
InnoDB
MyISAM
See more in the Manual:
简单地说：
你应该使用 InnoDB：
你应该使用 MyISAM：
To put it simply:
You should use InnoDB:
You should use MyISAM:
恕我直言，您应该总是更喜欢
InnoDB而不是MyISAM，因为事务支持是每个关系数据库系统的核心。IMHO you should always prefer
InnoDBoverMyISAMbecause of the transactional support which is at the heart of every relational database system.InnoDB 是一个完全符合 ACID 的数据库引擎，因此提供对事务等的支持。因此，它可能比 MyISAM 数据库慢，而 MyISAM 数据库往往在不同的方向进行优化。
因此，如果您需要事务，InnoDB（或其他 RDBMS，例如 PostgreSQL）是显而易见的选择。
Wikipedia 上有一个合理的比较
InnoDB is a fully ACID compliant database engine and therefore offers support for transactions, etc. As such, it can therefore be slower than the MyISAM database which tends to be optimised in a different direction.
Therefore if you need transactions InnoDB (or another RDBMS such as PostgreSQL) is the obvious choice.
There's a reasonable comparison over on Wikipedia
你可以两者都用。您可以使用 InnoDB 进行写入数据库/事务，但从非规范化的 MyISAM 数据库读回。这提供了两全其美的效果。您知道您的数据通过 InnoDB 的引用完整性是安全的，并且您可以使用 MyISAM 尽可能快地读取数据。
You could use both. You could use InnoDB for your write database/transactions, but read back from an MyISAM database, which is denormalized. This gives the best of both worlds. You know you're data is safe with referential integrity from InnoDB, and you can read it as fast as you'd like with MyISAM.