MySQL MyISAM 和 InnoDB 有什么区别？

What's the difference between MyISAM and InnoDB?

提问by Scott

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their differences without respect to someones specific database.

我知道以前有人问过这个问题，但大多数时候是针对特定的数据库或表提出的。我无法在此站点上找到描述两种引擎及其差异的答案，而不考虑某人的特定数据库。

I want to be able to make more informed decisions in the future with respect to designing a table or database, so am looking for a comprehensive answer on the differences between the two storage engines.

我希望将来能够在设计表或数据库方面做出更明智的决定，因此我正在寻找有关两种存储引擎之间差异的全面答案。

What's the difference between MyISAMand InnoDB, and what should I be looking for when trying to decide between one or the other?

MyISAM和InnoDB之间有什么区别，在尝试决定一个或另一个时我应该寻找什么？

回答by spencer7593

The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".

InnoDB 和 MyISAM 之间的主要区别（“关于设计表或数据库”）是对“参照完整性”和“事务”的支持。

If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.

如果您需要数据库强制执行外键约束，或者您需要数据库支持事务（即由两个或多个 DML 操作进行的更改作为单个工作单元处理，应用所有更改，或还原所有更改）那么你会选择 InnoDB 引擎，因为这些特性在 MyISAM 引擎中是不存在的。

Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

这是两个最大的区别。另一个很大的区别是并发性。使用 MyISAM，DML 语句将获得表上的排他锁，并且在持有该锁时，没有其他会话可以对表执行 SELECT 或 DML 操作。

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.

您询问的这两个特定引擎（InnoDB 和 MyISAM）具有不同的设计目标。MySQL还有其他存储引擎，有自己的设计目标。

So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.

因此，在 InnoDB 和 MyISAM 之间进行选择时，第一步是确定您是否需要 InnoDB 提供的功能。如果没有，则需要考虑 MyISAM。

A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space... how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

对差异进行更详细的讨论是不切实际的（在本论坛中）缺少对问题空间的更详细讨论......应用程序将如何使用数据库、多少表、表大小、事务负载、选择量、插入、更新、并发要求、复制功能等。

---

---

The logical design of the database should be centered around data analysis and user requirements; the choice to use a relational database would come later, and even later would the the choice of MySQL as a relational database management system, and then the selection of a storage engine for each table.

数据库的逻辑设计应以数据分析和用户需求为中心；使用关系型数据库的选择会在以后出现，甚至更晚会选择 MySQL 作为关系数据库管理系统，然后为每个表选择一个存储引擎。

回答by medina

MYISAM:

马来西亚：

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it's done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

1. MYISAM 支持表级锁
2. MyISAM 专为速度需求而设计
3. MyISAM 不支持外键，因此我们将 MySQL 与 MYISAM 称为 DBMS
4. MyISAM 使用单独的三个不同文件将其表、数据和索引存储在磁盘空间中。（表名.FRM，表名.MYD，表名.MYI）
5. MYISAM 不支持事务。你不能用 MYISAM 提交和回滚。一旦你发出命令，它就完成了。
6. MYISAM 支持全文检索
7. 您可以使用 MyISAM，如果表更静态，有很多选择和较少的更新和删除。

INNODB:

创新数据库：

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

1. InnoDB 支持行级锁定
2. InnoDB 旨在在处理大量数据时实现最高性能
3. InnoDB 支持外键，因此我们将 MySQL 与 InnoDB 称为 RDBMS
4. InnoDB 将其表和索引存储在表空间中
5. InnoDB 支持事务。您可以使用 InnoDB 提交和回滚