MySQL 中的 InnoDB 和 MyISAM 是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3818759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is InnoDB and MyISAM in MySQL?
提问by user130561
What is InnoDBand MyISAMin MySQL?
什么是InnoDB和MyISAM在MySQL？
采纳答案by Siva
InnoDBand MYISAM, are storage engines for MySQL.
InnoDB和MYISAM是 的存储引擎MySQL。
These two differ on their locking implementation: InnoDBlocks the particular row in the table, and MyISAMlocks the entire MySQLtable.
这两者在锁定实现上有所不同：InnoDB锁定表中的特定行，并MyISAM锁定整个MySQL表。
You can specify the type by giving MYISAMOR InnoDBwhile creating a table in DB.
您可以通过在 DB 中创建表时给出MYISAMOR来指定类型InnoDB。
回答by Adriaan Stander
Have a look at
看一下
InnoDB is a storage engine for MySQL, included as standard in all current binaries distributed by MySQL AB. Its main enhancement over other storage engines available for use with MySQL is ACID-compliant transaction support
MyISAM is the default storage engine for the MySQL relational database management system versions prior to 5.5 1. It is based on the older ISAM code but has many useful extensions. The major deficiency of MyISAM is the absence of transactions support.Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.
InnoDB 是 MySQL 的存储引擎，作为标准包含在 MySQL AB 分发的所有当前二进制文件中。与可用于 MySQL 的其他存储引擎相比，它的主要增强是符合 ACID 的事务支持
MyISAM 是 MySQL 关系数据库管理系统 5.5 之前版本的默认存储引擎1。它基于较旧的 ISAM 代码，但有许多有用的扩展。 MyISAM 的主要缺陷是缺乏事务支持。MySQL 5.5 及更高版本已切换到 InnoDB 引擎，以确保参照完整性约束和更高的并发性。
回答by mluebke
They are storage engines.
它们是存储引擎。
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
MyISAM: The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.
MyISAM：默认的 MySQL 存储引擎，也是 Web、数据仓库等应用环境中使用最多的一种。MyISAM 在所有 MySQL 配置中均受支持，并且是默认存储引擎，除非您已将 MySQL 配置为默认使用不同的引擎。
InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
InnoDB：MySQL 的事务安全（ACID 兼容）存储引擎，具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁定（不升级到更粗粒度的锁定）和 Oracle 风格的一致非锁定读取增加了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了维护数据完整性，InnoDB 还支持 FOREIGN KEY 引用完整性约束。
回答by Damir Bulic
I wanted to add that having ability to specify a specific storage engine per table is one of the key strengths of MySQL (besides easy of use and good performance with no tweaking). For all operations where transactions are needed, just stick with InnoDB. However, MyISAM can really speed things up when transactions are not needed in certain situations - and requires less disk space and RAM compared to InnoDB.
我想补充一点，能够为每个表指定特定的存储引擎是 MySQL 的主要优势之一（除了易于使用和无需调整的良好性能）。对于需要事务的所有操作，只需坚持使用 InnoDB。然而，当在某些情况下不需要事务时，MyISAM 确实可以加快速度 - 与 InnoDB 相比，它需要更少的磁盘空间和 RAM。
That said, InnoDB is getting better all the time:
也就是说，InnoDB 一直在变得更好：
回答by hi.nitish
MyISAM does not follow ACID as opposed to InnoDB which follows transactions to maintain integrity of the data.
与遵循事务以维护数据完整性的 InnoDB 不同，MyISAM 不遵循 ACID。
MyISAM supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table. MySqlDoc
MyISAM 支持并发插入：如果一个表在数据文件中间没有空闲块，你可以在其他线程从表中读取的同时插入新行。数据库文档
That is why, MyISAM is faster and takes less space. For instance, the MySQL MyISAM Storage Engine does not support tranactions.constraints of MySQL MYISAMThere is a bit called concurrent-insertBy default, the variable is set to 1 and concurrent inserts are handled as just described. If it is set to 0, concurrent inserts are disabled. If it is set to 2, concurrent inserts at the end of the table are permitted even for tables that have deleted rows. An INSERT statement can be executed to add rows to the end of the table with select at same time if there are no holes/deleted rows in middle of table (at time of concurrent insert).
这就是为什么 MyISAM 速度更快，占用空间更少。例如，MySQL MyISAM 存储引擎不支持事务。MySQL MYISAM 的约束有一个叫做concurrent-insert默认情况下，该变量设置为 1，并发插入如刚才描述的那样处理。如果设置为 0，则禁用并发插入。如果设置为 2，则即使对于已删除行的表，也允许在表末尾进行并发插入。如果表中间没有空洞/删除的行（并发插入时），则可以执行 INSERT 语句以同时使用 select 在表的末尾添加行。
The default isolation level og mysql InnoDB is "Read Repeatable". For MyISAM, there is no transaction. InnoDB uses row level locking while MyISAM can only use table level locking that is why InnoDB has crash revovery is better than MyISAM. One has to manually acquire the table level lockin MyISAM if one wants to avoid the concurrency effects.
mysql InnoDB 的默认隔离级别是“Read Repeatable”。对于 MyISAM，没有事务。InnoDB 使用行级锁定，而 MyISAM 只能使用表级锁定，这就是为什么 InnoDB 的崩溃恢复比 MyISAM 更好的原因。如果要避免并发影响，必须手动获取MyISAM 中的表级锁。
回答by Steffi Keran Rani J
InnoDBis a transactional storage engine of MySQL whereas MyISAMis a non-transactional storage engine. In other words, InnoDB follows the ACID properties to maintain the integrity of data but MyISAM doesn't follow ACID properties thus failing to maintain the integrity of the data.
InnoDB是 MySQL 的事务存储引擎，而MyISAM是非事务存储引擎。换句话说，InnoDB 遵循 ACID 属性来维护数据的完整性，而 MyISAM 不遵循 ACID 属性，因此无法维护数据的完整性。
In an InnoDB (transactional) table, the transactional changes can be easily undone if a rollback is required. But changes made to a MyISAM (non-transactional) table cannot be undone when rolling back a transaction is required.
在 InnoDB（事务）表中，如果需要回滚，可以轻松撤消事务更改。但是当需要回滚事务时，对 MyISAM（非事务性）表所做的更改无法撤消。
For example, you want to transfer money from your checking account to saving account. This is done by a transaction which includes 5 queries.
例如，您想将钱从您的支票账户转移到储蓄账户。这是由包含 5 个查询的事务完成的。
1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;
Suppose, the process crashes at step 4. If a InnoDB table was used here, a rollback would undo the changes and you are saved from the risk of losing money. Literally, the table is unaware of any crash as the changes will not be commited to the table unless step 5 is successfully executed.
假设进程在第 4 步崩溃。如果这里使用了 InnoDB 表，回滚将撤消更改，您就可以避免赔钱的风险。从字面上看，表不知道任何崩溃，因为除非成功执行步骤 5，否则更改不会提交到表。
But in the case of a MyISAM table, one cannot undo the transactional changes when a rollback is called or if there is a crash leading to the failure of the transaction. This means, if the transaction crashed at step 3, money will be deducted from your checking account. But money wouldnot have been added to your savings account.
但是在 MyISAM 表的情况下，当调用回滚或发生导致事务失败的崩溃时，无法撤消事务更改。这意味着，如果交易在第 3 步崩溃，将从您的支票账户中扣除资金。但是钱不会被添加到您的储蓄账户中。
Example courtesy:"High Performance MySQL: Optimization, Backups, and Replication" - Book by Arjen Lentz, Derek J. Balling, Jeremy Zawodny, Peter Zaitsev, and Vadim Tkachenko
示例礼貌：“高性能 MySQL：优化、备份和复制” - Arjen Lentz、Derek J. Balling、Jeremy Zawodny、Peter Zaitsev 和 Vadim Tkachenko 合着的书
回答by Alex Gnatko
When your MySQL server crashes, the data can be recovered much easier from a set of MyISAM tables than from that big InnoDB transaction file. Each MyISAM table has a separate file, and if no write operations were being made to this table during the crash - it will be totally unaffected. In case of InnoDB, the entire transaction file of the entire MySQL server has to be re-indexed or whatever it does after a crash. That can get quite messy.
当您的 MySQL 服务器崩溃时，从一组 MyISAM 表中恢复数据比从大型 InnoDB 事务文件中恢复要容易得多。每个 MyISAM 表都有一个单独的文件，如果在崩溃期间没有对该表进行写操作，它将完全不受影响。在 InnoDB 的情况下，整个 MySQL 服务器的整个事务文件必须重新索引或在崩溃后执行任何操作。这可能会变得非常混乱。
回答by adwairi
InnoDB is the default NOT myISAM https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html"InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table"
InnoDB 是默认的 NOT myISAM https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html"InnoDB 是默认的 MySQL 存储引擎。除非您配置了不同的默认存储引擎，否则发出没有 ENGINE= 子句的 CREATE TABLE 语句创建一个 InnoDB 表”
