InnoDB和MyISAM之间的主要区别是什么?


180

InnoDB和MyISAM之间的主要区别是什么?

+13

如果需要数据库引擎,请使用InnoDB。[你无法比较两者](https://jeremystein.com/journal/innodb-versus-myisam-no-comparison/)。 10 1月. 132013-01-10 15:12:50

112

我看到的第一个主要区别是InnoDB实现了行级锁定,而MyISAM只能执行表级锁定。您将在InnoDB中找到更好的崩溃恢复。但是,直到v5.6它没有FULLTEXT搜索索引,MyISAM也是如此。InnoDB还实现了事务,外键和关系约束,而MyISAM则没有。

该列表可以更进一步。然而,他们都有自己独特的优势,相互之间有利。它们中的每一个在某些场景中比在另一场景中更适合。

总结一下( TL; DR ):

  • InnoDB具有行级锁定功能,MyISAM只能进行全表级锁定。
  • InnoDB具有更好的崩溃恢复能力。
  • MyISAM有FULLTEXT搜索索引,InnoDB直到MySQL 5.6(2013年2月)。
  • InnoDB实现了事务,外键和关系约束,MyISAM没有。
  0

亲爱的先生,最终应该使用什么?MyISAM或InnoDB?我很困惑...我的网站使用的是mysql,我需要决定这一点。 30 8月. 122012-08-30 11:44:30

+2

取决于应用程序,写下您需要的功能列表(例如全文搜索,外键......)并尝试决定一个(尝试评估每个功能,然后计算分数)。你不可能拥有它们,但是由你来决定最需要女巫的功能。 30 8月. 122012-08-30 12:14:29

+2

我编辑了他的帖子以便澄清。 23 7月. 132013-07-23 06:24:14

+1

@MathiasLykkegaardLorenzen谢谢,这是我们喜欢stackexchange的原因之一 25 7月. 132013-07-25 23:37:26

  0

从版本5.6.4开始,InnoDB支持'FULLTEXT'搜索。https://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html 02 11月. 142014-11-02 04:34:25

  0

InnoDB不记得自动增量值(它会在重启时忘记它们)。 10 2月. 182018-02-10 03:26:03

  0

@sqlchild,如果您的网络服务器从客户端吸收数据,最好使用InnoDB,外键功能对于保持数据库处于一致状态至关重要。 03 3月. 182018-03-03 19:08:23


16

根据我的经验,最重要的区别是每个引擎处理锁定的方式。InnoDB使用行锁定,而MyISAM使用表锁定。根据经验,我使用InnoDB来编写大量表,使用MyISAM来读取繁重的表。

其他重要的差异包括:

  1. InnoDB支持事务和外键。MyISAM没有。
  2. MyISAM使用全文索引。
  3. MyISAM在执行数据完整性方面表现不佳。

7

我倾向于将MyISAM视为MySQL的“默认”表选择,因此我将指出InnoDB的大多数用户的差异

  • 行级锁定
  • 外键执法
  • 交易支持
  • 性能受到高使用率系统的影响
+4

除了最新的MySQL版本不再使用MyISAM作为默认引擎。在5.5中,他们将默认值更改为InnoDB :)。而且我不同意InnoDB一般只会获得“性能损失”的概括。设计良好的InnoDB表具有适当的索引和良好配置的内存设置,可以使InnoDB表与MyISAM中的相同模式一样好 24 6月. 112011-06-24 13:44:30

+2

在MySQL 5.6开发周期的中途添加了对InnoDB的FULLTEXT搜索。[引用的URL](http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html)现在也涵盖了InnoDB。 20 12月. 122012-12-20 06:52:59


26

还有一件事:你可以通过拍摄文件系统的快照来备份InnoDB表。备份MyISAM需要使用mysqldump,并且不保证一致(例如,如果插入父表和子表,您可能只在备份中找到子表的行)。

基本上,如果你有另一个数据副本并且只是在MySQL中缓存它,例如允许从PHP网站访问它的标准方法,那么MyISAM就好了(即它比平面CSV文件或日志文件更好用于查询和并发访问)。如果数据库是数据的实际“主副本”,如果你使用来自用户的真实数据进行INSERTUPDATE ,那么使用除InnoDB以外的任何东西都是愚蠢的,任何规模的MyISAM都是不可靠和难以管理的,你将在一半时间内做myisamchk ,否定任何性能提升......

(我个人的经验:MyISAM中的2TB数据库)。


64

尚未提及的另一个主要差异是如何完成每个存储引擎的缓存。

MYISAM

使用的主要机制是密钥缓存。它只缓存来自.MYI文件的索引页面。要调整密钥缓存的大小,请运行以下查询:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

根据您当前的数据集,这将给出MyISAM密钥缓存的推荐设置( key_buffer_size )( the query will cap the recommendation at 4G (4096M).对于32位操作系统,4GB是限制。对于64位,8GB。

InnoDB的

使用的主要机制是InnoDB缓冲池。它缓存来自InnoDB表的数据和索引页面。要调整InnoDB缓冲池的大小,请运行以下查询:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

这将根据您当前的数据集给出InnoDB缓冲池( innodb_buffer_pool_size )大小的推荐设置。

不要忘记调整InnoDB日志文件(ib_logfile0和ib_logfile1)的大小。MySQL源代码放置所有InnoDB日志文件的组合大小的上限必须<4G(4096M)。为简单起见,只给出两个日志文件,以下是如何调整它们的大小:

  • 步骤1)将innodb_log_file_size = NNN添加到/etc/my.cnf(NNN应为innodb_buffer_pool_size或2047M的25%,以较小者为准)
  • 步骤2) service mysql stop
  • 步骤3) rm /var/log/mysql/ib_logfile[01]
  • 步骤4) service mysql start (重新创建ib_logfile0和ib_logfile1)

警告

在两个查询的结尾处是内联查询(SELECT 2 PowerOf1024) B.

  • (SELECT 0 PowerOf1024)给出了字节设置
  • (SELECT 1 PowerOf1024)给出以千字节为单位的设置
  • (SELECT 2 PowerOf1024)给出以兆字节为单位的设置
  • (SELECT 3 PowerOf1024)给出以千兆字节为单位的设置
  • 不接受低于0或大于3的功率

结语

常识无可替代。如果您的内存有限,存储引擎混合或其组合,则必须针对不同的方案进行调整。

  • 如果您有2GB RAM和16GB InnoDB,请将512M分配为innodb_buffer_pool。
  • 如果您有2GB RAM和4GB MyISAM索引,请将512M分配为key_buffer_size。
  • 如果您有2GB RAM和4GB MyISAM索引和16GB InnoDB,请将512M分配为key_buffer_size,将512M分配为innodb_buffer_pool_size。

可能的场景是无止境的

请记住,无论您分配什么,请为数据库连接和操作系统留出足够的RAM。

  0

这些都是糟糕的公式! 23 6月. 112011-06-23 00:06:29

  0

(哎呀 - 忘记不能有段落)...我会添加一个“答案”。 23 6月. 112011-06-23 00:07:21

  0

在低内存环境方面,我实际上给了@Rick +1的答案。我的答案,即使使用他的回答中的URL,也被证实是正确的。从这里开始,我将它留给DBA.SE社区,在其完整的背景下判断所有答案。谢谢 !!! 23 6月. 112011-06-23 02:51:42

  0

Rolando的缓存大小公式不实用。- 不需要2的权力。- 32位操作系统上的4GB是不可能的 - 等等。这是关于如何设置它们的简要说明:http://mysql.rjweb.org/doc.php/memory(它解决了影响内存使用的各种其他设置。) 23 6月. 112011-06-23 00:13:03

+2

@Rick:2的力量意味着以不同的单位显示答案。正在执行(SELECT 2 PowerOfTwo)以MB为单位设置应答的显示。正在执行(SELECT 3 PowerOfTwo)以GB为单位设置显示。(SELECT 1 PowerOfTwo)以KB为单位显示。(SELECT 0 PowerOfTwo)以字节显示。这就是(SELECT 2 PowerOfTwo)的作用。所以只需要显示,而不是在架构中强加任何假设值。 23 6月. 112011-06-23 02:25:59

  0

@Rick:MySQL网站文档说32位操作系统最大4GB:在32位平台上,key_buffer_size的最大允许设置为4GB。64位平台允许大于4GB的值。有效最大大小可能会更小,具体取决于您的可用物理RAM和操作系统或硬件平台强加的每进程RAM限制。此变量的值表示请求的内存量。在内部,服务器尽可能多地分配内存,但实际分配可能更少。 23 6月. 112011-06-23 02:30:58

+2

@Rick:你知道吗?我实际上会给你一个+1的两个非常重要的原因。1)您的URL确认我的答案是正确的,因为4GB是分配给key_buffer_size的最大数字。2)你的答案,你的URL,对于内存非常低的机器是有意义的。我会在信用到期时给予信任。 23 6月. 112011-06-23 02:41:27

  0

@Rick:我的意思并不简单,但是如果你从我的回答中读到了我的最后一个音符,你会看到我明确表示使用512MB的innodb_buffer_pool_size和key_buffer_size。我确实试图保守低内存环境。我完全清楚像我这样的公式并不是绝对的。 23 6月. 112011-06-23 02:48:45


42

InnoDB提供:

  • ACID交易
  • 行级锁定
  • 外键约束
  • 自动崩溃恢复
  • 表压缩(读/写)
  • 空间数据类型(无空间索引)

在InnoDB中,除TEXT和BLOB之外的所有数据最多可占用8,000个字节。在MySQL 5.6(2013年2月)之前,InnoDB中不提供全文索引。在InnoDB中的COUNT(*) S(当WHEREGROUP BY ,或JOIN不使用)执行比的MyISAM因为行计数不内部存储慢。InnoDB将数据和索引存储在一个文件中。InnoDB使用缓冲池来缓存数据和索引。

MyISAM提供:

  • 快速COUNT(*) S(当WHEREGROUP BY ,或JOIN不使用)
  • 全文索引(更新:来自MySQL 5.6的InnoDB支持)
  • 更小的磁盘空间
  • 非常高的表压缩(只读)
  • 空间数据类型和索引(R-tree)(更新:来自MySQL 5.7的InnoDB支持)

MyISAM具有表级锁定,但没有行级锁定。没有交易。没有自动崩溃恢复,但它确实提供了修复表功能。没有外键约束。与InnoDB表相比,MyISAM表在磁盘上的大小通常更紧凑。如果需要,可以通过使用myisampack进行压缩来进一步大大减小MyISAM表的大小,但是变为只读。MyISAM将索引存储在一个文件中,数据存储在另一个文件中MyISAM使用密钥缓冲区来缓存索引,并将数据缓存管理留给操作系统。

总的来说,我会推荐InnoDB用于大多数用途,而MyISAM仅用于专门用途。InnoDB现在是新MySQL版本的默认引擎。

+2

我读了你的答案并将其与已经在这里的其他人进行了比较。你是唯一提到BLOB的人。他们通常被视为理所当然。你们也是唯一提到myisampack的人,myisampack是快速阅读的MyISAM表的无名英雄之一。你的今天是+1! 23 8月. 112011-08-23 03:45:37


3

MYISAM

MYISAM提供表级锁定,FULLTEXT搜索.MYISAM具有最灵活的AUTO_INCREMENTED列处理所有存储引擎.MYISAM不支持事务。

INNODB

INNODB是事务安全存储引擎.INNODB具有提交,回滚和崩溃恢复功能..INNODB支持外键引用完整性。


4

包括MySQL 5.6更改

INNODB存储引擎:

  • 它提供完整的ACID(原子性,一致性,隔离性,耐久性)合规性。多版本控制用于将交易与另一个交互隔离。
  • InnoDB在MySQL服务器或运行服务器的主机崩溃后提供自动恢复。
  • InnoDB支持外键和引用完整性,包括级联删除和更新。
  • MySQL 5.6构建在InnoDB平台上,完全集成为默认存储引擎
  • 持久优化器统计信息 :提供InnoDB索引统计信息的准确性提高,以及MySQL重启的一致性。
  • 修剪InnoDB表缓存:为了减轻具有大量表的系统上的内存负载,InnoDB现在可以释放与打开的表关联的内存。LRU算法选择在没有被访问的情况下最长的表。
  • 支持全文搜索:一种特殊的索引,FULLTEXT索引,帮助InnoDB处理涉及基于文本的列及其包含的单词的查询和DML操作。这些索引在物理上表示为整个InnoDB表。
  • InnoDB在全文搜索上的速度似乎比MyISAM快

因此,如果您已经升级到5.6,那么使用MyISAM Engine是没有意义的,如果没有则不要等到升级到MySQL 5.6。

InnoDB VS MyISAM performance using MySQL 5.6


17

游戏有点晚......但这里有一个非常全面的post I wrote a few months back ,详细介绍了MYISAM和InnoDB之间的主要区别。拿一杯茶(也许是一块饼干),享受吧。


MyISAM和InnoDB之间的主要区别在于引用完整性和事务。还有其他差异,例如锁定,回滚和全文搜索。

参照完整性

参照完整性可确保表之间的关系保持一致。更具体地说,这意味着当表(例如,列表)具有指向不同表(例如,产品)的外键(例如,产品ID)时,当指向表的更新或删除时,这些更改被级联到链接表。在我们的示例中,如果重命名产品,链接表的外键也将更新;如果从“产品”表中删除了产品,则任何指向已删除条目的列表也将被删除。此外,任何新的列表必须具有指向有效的现有条目的外键。

InnoDB是一个关系型DBMS(RDBMS),因此具有参照完整性,而MyISAM则没有。

交易与原子性

表中的数据使用数据操作语言(DML)语句进行管理,例如SELECT,INSERT,UPDATE和DELETE。一个事务组将两个或多个DML语句一起组成一个单独的工作单元,因此要么应用整个单元,要么不应用整个单元。

MyISAM不支持InnoDB的交易。

如果在使用MyISAM表时操作被中断,则操作立即中止,并且受影响的行(甚至每行中的数据)仍会受到影响,即使操作未完成也是如此。

如果在使用InnoDB表时操作被中断,因为它使用具有原子性的事务,任何未完成的事务都不会生效,因为没有提交。

表锁定与行锁定

当查询针对MyISAM表运行时,它将查询的整个表将被锁定。这意味着后续查询只会在当前查询完成后执行。如果您正在阅读大型表,和/或频繁的读写操作,这可能意味着大量的查询积压。

当查询针对InnoDB表运行时,仅锁定所涉及的行,表的其余部分仍然可用于CRUD操作。这意味着查询可以在同一个表上同时运行,前提是它们不使用同一行。

InnoDB中的此功能称为并发。与并发性一样,存在一个主要的缺点,适用于选择范围的表,因为在内核线程之间切换存在开销,并且应该对内核线程设置限制以防止服务器停止。

交易和回滚

在MyISAM中运行操作时,将设置更改;在InnoDB中,可以回滚这些更改。用于控制事务的最常用命令是COMMIT,ROLLBACK和SAVEPOINT。1. COMMIT - 您可以编写多个DML操作,但只有在进行COMMIT时才会保存更改2. ROLLBACK - 您可以放弃尚未提交的任何操作3. SAVEPOINT - 在列表中设置一个点ROLLBACK操作可以回滚到的操作

可靠性

MyISAM不提供数据完整性 - 硬件故障,不干净的关闭和取消的操作可能导致数据损坏。这将需要完整修复或重建索引和表。

另一方面,InnoDB使用事务日志,双写缓冲区以及自动校验和验证来防止损坏。在InnoDB进行任何更改之前,它会将事务之前的数据记录到名为ibdata1的系统表空间文件中。如果发生崩溃,InnoDB将通过重播这些日志进行自动恢复。

FULLTEXT索引

在MySQL 5.6.4版之前,InnoDB不支持FULLTEXT索引。截至撰写本文时,许多共享托管服务提供商的MySQL版本仍然低于5.6.4,这意味着InnoDB表不支持FULLTEXT索引。

但是,这不是使用MyISAM的正当理由。最好转换为支持最新MySQL版本的托管服务提供商。并非使用FULLTEXT索引的MyISAM表无法转换为InnoDB表。

结论

总之,InnoDB应该是您选择的默认存储引擎。在满足特定需求时选择MyISAM或其他数据类型。


1

MyISAM数据

MyISAM是MySQL的存储引擎。在MySQL 5.5之前,它是MySQL的默认存储引擎。它基于较旧的ISAM存储引擎.MyISAM针对具有大量读取操作,少量写入或根本没有写入的环境进行了优化。MyISAM允许快速读取的原因是其索引的结构:每个条目指向数据文件中的记录,指针偏离文件的开头。这样可以快速读取记录,尤其是当格式为FIXED时。因此,行具有恒定的长度。人们可能更喜欢MyISAM的典型区域是数据仓库,因为它涉及对非常大的表的查询,并且在数据库不使用时(通常是夜晚)完成这些表的更新。插入也很简单,因为新行被附加到数据文件的末尾。但是,删除和更新操作更成问题:删除必须留空空格,否则行的偏移会改变;随着行的长度变短,更新也是如此;如果更新使行更长,则行被分段。要对行进行碎片整理并声明空白空间,OPTIMIZE TABLE必须执行命令。由于这种简单的机制,通常MyISAM索引统计数据非常准确。MyISAM的其他主要缺点是缺少事务支持和外键。

InnoDB的

InnoDB是MySQL的存储引擎。MySQL 5.5及更高版本默认使用它。它提供符合ACID标准的交易功能,以及外键支持(声明参照完整性)。它实现了SQL和XA事务,表空间,FULLTEXT遵循OpenGIS标准的索引和空间操作。它是MySQL AB分发的大多数二进制文件的标准版本,但某些OEM版本除外。该软件由Oracle Corporation双重许可;它是根据GNU通用公共许可证分发的,但也可以授权给希望将InnoDB与专有软件结合使用的各方。

福克斯

MariaDB有一个名为Aria的存储引擎,它被描述为“MyISAM的安全替代品”。MariaDB和Percona Server默认使用名为XtraDB的InnoDB分支。XtraDB由Percona维护。Oracle InnoDB的更改会定期导入XtraDB,并添加了一些错误修复和额外功能。