我应该总是喜欢MySQL InnoDB在MyISAM上吗？ [英] Should I always prefer MySQL InnoDB over MyISAM?
问题描述
MyISAM 堆组织。记录由表中的行偏移量标识，索引将此偏移量存储为行指针。
InnoDB 支持事务，并且是索引组织的。记录由 PRIMARY KEY 的值（或隐藏的内部列没有定义 PRIMARY KEY ），并存储在 B-Tree 中。次要索引将 PRIMARY KEY 的值存储为行指针。
涉及全表扫描或在 MyISAM 表中的次要索引查找通常更快。
涉及的查询PRIMARY KEY 寻求通常在 InnoDB 表格上更快。
MyISAM 表存储表中元数据表中的记录数，这就是为什么这样的查询：
SELECT COUNT（*）
FROM myisamtable
即时。
MyISAM 表完全锁定在 DML 操作（有几个例外）。
InnoDB 表锁定单个记录和索引间隙，但这些是记录和扫描的间隙，而不是只有那些由 WHERE 条件匹配的人。这可能导致记录被锁定，尽管它们不匹配。
InnoDB 表支持引用完整性（ FOREIGN KEY s）。
有几种情况可以显示两种引擎的优势。
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 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 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.
Queries that involve full table scans or secondary index lookups are usually faster on MyISAM tables.
Queries that involve PRIMARY KEY seeks are usually faster on InnoDB tables.
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
are instant.
MyISAM tables are completely locked on the DML operations (with several exceptions).
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 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屋！
