MySQL InnoDB和MyISAM插入操作的比较

Question

我有一个包含1700万行的数据表。我需要提取其中一个列并将其全部插入到另一个表中。这是我的做法:

INSERT IGNORE INTO table1(name) SELECT name FROM main WHERE ID < 500001

InnoDB花费约3分45秒执行，但MyISAM只需不到4秒钟。为什么会有这样的差异？

我看到很多人都在赞扬InnoDB，但老实说我不明白它对我有何好处。它太慢了。我知道它在保持完整性方面非常出色，但我的许多表格都不会被更新（只是读取）。我是否应该费心去使用InnoDB呢？

- nick

---

我只在处理关系表时使用InnoDB。否则，如果没有外键，我更喜欢使用MyISAM！ - Ben Ashton

只是想指出，这两个表都有索引。"主"表目前是MyISAM。 - nick

Ben，我想使用关系表，但我可能会处理数亿行数据 - 我需要对几十个列进行索引，所以我不知道该怎么做。完整性并不是很重要，至少在这个部分不是。 - nick

@BenAshton：MyISAM表也是“关系型”的，只是它们不支持事务处理。 - user330315

@a_horse_with_no_name，我从未说过MyISAM表不能是关系型的。我只是说我更喜欢将它们用于关系型表。这只是一条评论，而不是答案！ - Ben Ashton

Answer 1

差异很可能是由于InnoDB的配置问题，这需要比MyISAM更多的调整。InnoDB的理念是将大部分数据存储在内存中，在你有一些闲置CPU周期时才刷新/读取到磁盘。

是否应该使用InnoDB是一个非常好的问题。如果您仍然要使用MySQL，则强烈建议您积累一些InnoDB方面的经验。但是，如果您正在为一个不会有太多流量的数据库做一个快速而简单的工作，并且不担心规模的问题，那么MyISAM的易用性可能只是对您有利。在许多情况下，InnoDB可能过于复杂，而有些人只需要一个简单的数据库。

如果您的表99%是只读的，则仍然可以从InnoDB中获得性能提升。如果您将缓冲池大小配置为可以容纳整个数据库的内存大小，即使它错过了MySQL查询缓存，InnoDB也永远不必去磁盘获取您的数据。

在MyISAM中，您很可能需要从磁盘读取行，并且留给操作系统来为您进行缓存和优化。

我的第一个猜测是检查innodb_buffer_pool_size，它默认设置为8M。建议将其设置为总内存的80%左右。一旦达到限制，InnoDB的性能将显著下降，因为它需要将某些内容从缓冲区刷新出来以腾出空间来存储新数据，这可能很昂贵。

此外，在加载表时，请确保关闭autocommit，否则每次插入都会发生刷新。您可以在完成后将其打开，并且它是客户端设置，非常安全。

通常只会发生一次加载表的情况。想想看，你真的想要调整数据库以适应“插入1700万行”吗？你会经常这样做吗？在这种情况下，MyISAM可能更快，但当你有100个并发连接同时读取和修改这个表时，你会发现经过良好调整的InnoDB会胜出，而MyISAM会被表锁卡住。 MyISAM如何看待此操作
MyISAM在没有任何调整的情况下非常擅长这项工作，因为在内部，你只是将每一行附加到一个文件中（并更新索引）。你的操作系统和磁盘缓存将处理所有这些性能问题。 InnoDB如何看待此操作
InnoDB知道需要写入表格，因此它将行投入插入缓冲区。在下一个插入之前不给它时间，因此InnoDB没有时间处理缓冲区，它用完空间后被迫“挂起”插入，同时写入缓冲池并更新索引。接下来，你的缓冲池充满了，InnoDB被迫“挂起”插入并将一些页面从缓冲池刷新到磁盘上。而你继续疯狂地投掷插入命令。 请注意，即使你对InnoDB进行了调整，以便在执行此操作后快速给你一个MySQL>提示，InnoDB仍在运行中，以赶上它的空闲时间，但愿意为你执行新事务。 必读：
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html（参见大数据加载技巧）

- FlipMcF

---

请任何MySQL性能专家（特别是来自Percona的）纠正我是否出错或漏掉了什么。我会更新答案。 - FlipMcF

“ hitting a limit on innodb-buffer-pool-size ” 有点不准确，实际上刷新与“innodb_max_dirty_pages_pct”有关。但对于这个问题来说，这只是纠结于细节了。 - FlipMcF

还有一篇不错的文章供您阅读：http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/ - FlipMcF

Answer 2

你说的在某种程度上是对的。InnoDB比MyISAM慢，但在哪些情况下呢？并不是所有东西都能满足每个人的要求。InnoDB是一种事务型数据库引擎，而MyISAM不是。因此，为了使其符合ACID规范和支持事务存储引擎，我们必须以响应时间为代价。此外，如果正确使用my.ini或其他配置文件来调整，InnoDB可以运行得更快。最后，我能够理解人们赞扬InnoDB的以下原因：

1.它是符合ACID标准并支持事务的引擎。

2.在处理表时，它采用行级锁定，而MyISAM则采用表级锁定。

3.InnoDB非常适合多核/多进程机器进行高度可调整性以提高并发性。最后，我想说的是，任何东西都不能满足“每个人”的需求，因此完全取决于你比较两种引擎的情境。

- Mian Zeshan Farooqi

Answer 3

在维基百科上查看MYISAM与Innodb的比较。

http://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

- Teja