This question already has an answer here:
这个问题已经有了答案:
- MyISAM versus InnoDB [closed] 27 answers
- MyISAM和InnoDB[结束]27个答案
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 MyISAM and InnoDB, and what should I be looking for when trying to decide between one or the other?
MyISAM和InnoDB的区别是什么?当我在两者之间做决定时，我应该寻找什么?
2 个解决方案
#1
392
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作为关系数据库管理系统，然后为每个表选择存储引擎。
#2
326
MYISAM:
MYISAM:
- MYISAM supports Table-level Locking
- MYISAM支持表级锁
- MyISAM designed for need of speed
- MyISAM是为需要速度而设计的
- MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
- MyISAM不支持外键，所以我们用MyISAM是DBMS来调用MySQL
- MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
- MyISAM使用三个不同的文件在磁盘空间中存储表、数据和索引。(表名。纳,表名。MYD tablename.MYI)
- MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
- MYISAM不支持事务。您不能使用MYISAM提交和回滚。一旦发出命令，它就完成了。
- MYISAM supports fulltext search
- MYISAM支持全文搜索
- You can use MyISAM, if the table is more static with lots of select and less update and delete.
- 如果表是静态的，有很多选择，更新和删除更少，那么可以使用ismyam。
INNODB:
INNODB:
- InnoDB supports Row-level Locking
- InnoDB支持行级锁
- InnoDB designed for maximum performance when processing high volume of data
- InnoDB设计用于处理大量数据时的最高性能
- InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
- InnoDB支持外键，因此我们用InnoDB是RDBMS来调用MySQL
- InnoDB stores its tables and indexes in a tablespace
- InnoDB将表和索引存储在表空间中。
- InnoDB supports transaction. You can commit and rollback with InnoDB
- InnoDB支持事务。您可以使用InnoDB进行提交和回滚
#1
392
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作为关系数据库管理系统，然后为每个表选择存储引擎。
#2
326
MYISAM:
MYISAM:
- MYISAM supports Table-level Locking
- MYISAM支持表级锁
- MyISAM designed for need of speed
- MyISAM是为需要速度而设计的
- MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
- MyISAM不支持外键，所以我们用MyISAM是DBMS来调用MySQL
- MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
- MyISAM使用三个不同的文件在磁盘空间中存储表、数据和索引。(表名。纳,表名。MYD tablename.MYI)
- MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
- MYISAM不支持事务。您不能使用MYISAM提交和回滚。一旦发出命令，它就完成了。
- MYISAM supports fulltext search
- MYISAM支持全文搜索
- You can use MyISAM, if the table is more static with lots of select and less update and delete.
- 如果表是静态的，有很多选择，更新和删除更少，那么可以使用ismyam。
INNODB:
INNODB:
- InnoDB supports Row-level Locking
- InnoDB支持行级锁
- InnoDB designed for maximum performance when processing high volume of data
- InnoDB设计用于处理大量数据时的最高性能
- InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
- InnoDB支持外键，因此我们用InnoDB是RDBMS来调用MySQL
- InnoDB stores its tables and indexes in a tablespace
- InnoDB将表和索引存储在表空间中。
- InnoDB supports transaction. You can commit and rollback with InnoDB
- InnoDB支持事务。您可以使用InnoDB进行提交和回滚