我应该总是更喜欢 MySQL InnoDB 而不是 MyISAM? [英] Should I always prefer MySQL InnoDB over MyISAM?

问题描述

刚刚有人告诉我 InnoDB 比 MyISAM 好得多.所以当我创建一个表时，我应该总是尝试使用 InnoDB Engine 而不是 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?

推荐答案

MyISAM 是无事务和堆组织的.记录由表中的行偏移量标识，索引将此偏移量存储为行指针.

MyISAM is 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.

InnoDB 支持事务并且是索引组织的.记录由 PRIMARY KEY 的值标识(或隐藏的内部列，没有定义 PRIMARY KEY)并存储在 B 树中.二级索引将 PRIMARY KEY 的值存储为行指针.

InnoDB supports transactions and is index-organized. The records are identified by the value of the PRIMARY KEY (or a hidden internal column is there is no PRIMARY KEY defined) and are stored in a B-Tree. The secondary indexes store the value of the PRIMARY KEY as a row pointer.

涉及全表扫描或二级索引查找的查询通常在 MyISAM 表上更快.

Queries that involve full table scans or secondary index lookups are usually faster on MyISAM tables.

涉及 PRIMARY KEY 查找的查询通常在 InnoDB 表上更快.

Queries that involve PRIMARY KEY seeks are usually faster on InnoDB tables.

MyISAM 表将表中的记录数存储在表的元数据中，这就是查询如下的原因:

MyISAM tables store the number of records in the table in the table's metadata, that's why the queries like this:

SELECT  COUNT(*)
FROM    myisamtable

是即时的.

MyISAM 表完全锁定在 DML 操作上(有几个例外).

MyISAM tables are completely locked on the DML operations (with several exceptions).

InnoDB 表锁定单个记录和索引间隙，但这些是扫描的记录和间隙，而不仅仅是那些与 WHERE 条件匹配的记录和间隙.这可能导致记录被锁定，尽管它们不匹配.

InnoDB tables lock individual records and index gaps, however these are the records and the gaps that are scanned, not only those matched by the WHERE condition. This can lead to the records being locked despite the fact they don't match.

InnoDB 表支持参照完整性 (FOREIGN KEYs) .MyISAM 表没有.

InnoDB tables support referential integrity (FOREIGN KEYs) . MyISAM tables don't.

有几个场景可以显示两种引擎的优势.

There are several scenarios that can show benefits of both engines.

这篇关于我应该总是更喜欢 MySQL InnoDB 而不是 MyISAM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！