InnoDB与MyISAM的插入查询时间对比

Question

我有一个大的MySQL表格（约1000万行，6.5G），我用它来读写。它是MyISAM格式的，由于MyISAM在写入时锁定整个表格，所以我经常遇到锁问题。

我决定尝试使用InnoDB，因为它适合读写表格，并且只在写入时锁定特定的行。

转换后，我测试了插入语句，结果发现在InnoDB表格中需要花费大约15倍的时间（从0.1秒到1.5秒），而在MyISAM表格中只需要很短的时间。这是为什么呢？

我还没有为InnoDB做任何配置，计划添加分区，但是这个数字对我来说仍然出乎意料。当然，表格是相同的，索引也一样。

根据请求提供的其他信息：

2个索引。主要索引是类型为Big INT的data_id，非唯一的user_id是varchar(255)类型。

插入是一起的约150行，具有相同的user_id。

索引大小：MyISAM中为200MB，在InnoDB中为400MB。

- normalppl

---

您可以展示一下索引吗？这很可能是导致插入时间长的最常见原因。 - wallyk

我有两个索引，data_id是主键，user_id不唯一。我的插入操作一次会插入大约150行数据，这些数据具有相同的user_id（该字段已经创建了索引）。 - normalppl

但是每个索引的内容是什么呢？如果有一堆的二进制大对象，那么确实很难建立索引。但如果它们是整数或其他简单类型，那么就很难理解为什么性能会变差。 - wallyk

抱歉。user_id是varchar(255)，而data_id是Big INT。表中没有BLOB。 - normalppl

@normalppl - 有没有办法提供一个样本数据库和代码来演示你的问题？ - Jared Farrish

我可以尝试您建议的任何事，并分享结果。目前无法提供这样的示例。 - normalppl

Answer 1

有一篇相关的解答指出，当写入操作比读取操作更多时，将innodb_flush_log_at_trx_commit变量设置为2可能会提高性能。更多信息请参见官方文档。

- wallyk

---

我现在只是在测试，所以没有读取操作，只有一次插入150行数据，想先搞清楚这个问题。 - normalppl

这对我来说有很大的影响。在不改变 innodb_flush_log_at_trx_commit 的情况下插入7500行需要5分钟。将其更改为0或2，相同的插入只需要3秒钟。 - qris

Answer 2

我认为，InnoDB实现了真正的ACID，并且进行了大量的fsync()来保存数据。而MyISAM不是真正的ACID，做的fsync()较少。

当需要加载大量数据时，有建议杀掉fsync。点击这里。

If you want to load data into InnoDB quickly:
* use as large an InnoDB buffer cache as possible
* make the InnoDB log files as large as possible
* minimize the number of unique indexes on your tables
* disable all calls to fsync from InnoDB. You have to hack the code to
get this, or look at the Google patch. Of course, you only want to run
in this mode when loading the table.

And lists says:

MyISAM总是运行在“nosync”模式下，也就是说，它从不调用fsync()将文件刷新到磁盘。

InnoDB的nosync在测试某些OS/计算机是否极慢时非常有用。但是在生产系统中不应该使用。

同样的消息说，InnoDB有时会使用另一种同步方法：

然后，InnoDB使用fsync()来刷新数据和日志文件。如果指定了O_DSYNC，则InnoDB使用O_SYNC打开和刷新日志文件，但使用fsync()刷新数据文件。如果指定了O_DIRECT（从MySQL-4.0.14开始在某些Linux版本上可用），则InnoDB使用O_DIRECT打开数据文件，并使用fsync()刷新数据和日志文件。请注意，InnoDB不使用fdatasync()或O_DSYNC，因为在许多Unix版本中存在问题。

- osgx

Answer 3

请注意InnoDB处理键的方式可能会引起问题。由于所有数据都按主键顺序存储在磁盘上，具有非自动增量主键可能会导致表中的大部分数据在任何插入时在磁盘上移动（当我使用组合id作为主键时，遇到了这个问题）。在磁盘上移动数据很慢。

此外，由于每个索引还包含主键，因此InnoDB的索引大小可能会更大。请确保您没有遇到任何内存限制。

- jisaacstone

---

我没想到它会更快，我知道它应该帮助处理许多读写操作。但是，如果我通常执行的基本插入需要15倍的时间（从0.1秒到1.5秒），这听起来好像有些不对劲，不是吗？ - normalppl

是的，抱歉，应该读作15％而不是15倍-可能存在一些问题。 - jisaacstone

Answer 4

首先，你的测试无效，因为只有在并发情况下，行级锁相对于表级锁才能获得速度优势！只有一个线程进行插入，两种情况下都需要执行1次锁定/解锁操作，并且插入不会等待释放表级锁。

其次，如JIStone所述，非顺序主键对于插入操作来说是性能杀手，当表大小超过缓冲池时更是如此。

第三，在InnoDB中，缓冲池大小是最重要的设置之一。尽量将其设置为尽可能大（建议设置为可用RAM的80％）。

接下来，正如@wallyk所述，innodb_flush_log_at_trx_commit对于I/O操作的速度至关重要。

还有，innodb_log_file_size和innodb_buffer_file_size也很重要。

请记住，在InnoDB中有2个唯一索引，因此在插入行之前，必须检查索引中的值是否存在，而且你的索引很大。

没有关于表和索引的详细信息，我不能给你更多的建议，但请记住，没有存储引擎是万能的，虽然通常你可以通过简单地更改存储引擎、添加索引或调整一个变量来获得很多速度提升，在大规模系统中，事情比这更复杂。但是，正如我所说，你不应该在孤立的测试中比较原始插入速度，而是要尽可能地让你的测试接近实际应用程序。

更新：还有一个提示，在MyISAM和InnoDB中，多行插入（insert into .... values(...),(...),(...)）更快。此外，在InnoDB中，你可以在事务中进行插入，这会禁用非唯一索引在事务完成之前的更新，并且这也更快（但不要执行大型事务，因为由于使用的隔离级别和行版本控制方式，这实际上会减慢速度）。

- Maxim Krizhanovsky