如何提高MySQL数据库InnoDB每秒写入速度

Question

在我的服务器上，向MySQL数据库插入记录非常慢。就服务器状态而言，InnoDB每秒写入约为20次。我不是专家，只是刚从大学毕业，对此没有太多经验。有什么方法可以提高InnoDB写入的速度吗？如果不升级服务器硬件，还有其他方法吗？我的服务器不好，所以我安装了Microsoft Windows Server 2003 R2。硬件信息如下：

CPU：Intel Xeon E5649 2.53GHZ RAM：2GB

请留言，谢谢。

- Eric

---

摆脱不必要的索引将有助于提高写入性能。 - Sam

在你去除它之前，最好确保该索引是不必要的。 - Dan Bracuk

如果你需要大量的INSERT操作，请使用一个自增长的int作为主键。 - svidgen

你能把你正在插入数据的表的CREATE语句放在这里吗？ - Mad Dog Tannen

@svidgen 你好svidgen，我有很多Inserts。有12张表，每个表需要插入2000条数据。我使用了自增的PK。 - Eric

谢谢你们的建议。我会尝试每一个建议。 - Eric

Answer 1

一些提示：

• 尽量减少索引的数量 - 这样可以减少索引维护。这显然是与 SELECT 性能权衡的结果。
• 最大化每个事务中的 INSERT 数量 - “耐久性成本”会更低（即在事务的其余部分仍在执行时可以在后台进行物理写入磁盘，如果事务足够长）。一个大事务通常比许多小事务快，但这显然取决于您要实现的实际逻辑。
• 将表移动到更快的存储介质，例如 SSD。读取可以被缓存，但是 durable 事务必须被物理写入磁盘，所以仅仅缓存是不够的。

此外，如果您能向我们展示您确切的数据库结构和使用的确切的 INSERT 语句，那将非常有帮助。

- Branko Dimitrijevic

---

索引在UPDATE/DELETE的性能方面也是有益的。任何涉及JOIN、WHERE或ORDER BY的操作都可以使用索引。在更新或删除少量行时，使用索引对于获得行级锁是至关重要的。否则，它必须锁定整个表格。 - Bill Karwin

Answer 2

• Modify your config for MySQL server

  innodb_flush_log_at_trx_commit = 0
  

• then Restart MySQL server

- vega ho

Answer 3

如果使用InnoDB引擎和本地磁盘，请尝试使用innodb_flush_method = O_DSYNC进行基准测试。使用O_DSYNC可提高我们的批量插入（包含TRANSACTION）效率。

调整刷新方法

在某些GNU/Linux和Unix版本中，使用Unix fsync()调用（默认情况下InnoDB使用）和类似方法将文件刷新到磁盘上会非常缓慢。如果数据库写入性能是个问题，请使用innodb_flush_method参数设置为O_DSYNC进行基准测试。

https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html

- Ezekiel Baniaga

Answer 4

请将innodb_buffer_pool_size设置为512M，这可能会提高性能。

SET GLOBAL innodb_buffer_pool_size=512M

- user3058132

---

谢谢您的回答。快速问题：如何找到缓冲池大小？ - Eric

@Eric，SELECT @@innodb_buffer_pool_size; - Bill Karwin

@user3058132，你不能使用SET GLOBAL动态地更改innodb_buffer_pool_size大小，你需要编辑my.cnf文件并重新启动mysqld。 - Bill Karwin

@BillKarwin 你好，Bill，我已将缓冲池大小更改为54MB，但插入速度仍然非常慢。它花费了2个小时将10MB的数据插入MySQL数据库。这是我的服务器信息： CPU：Intel Xeon E5649 2.53GHZ 2.53GHZ RAM：4GB， 上个周末，我对服务器进行了升级。基于我的服务器信息，什么样的缓冲池大小最佳？ 谢谢 - Eric

@Eric，我没有足够的信息来回答这个问题。缓冲池大小不是一个魔术值，使一切运行变快。有多个可调整配置参数。最佳调优设置与您插入的数据类型以及插入速度以及服务器上正在运行的其他内容有关。当然，两个小时似乎过长，所以我认为这里还存在其他因素。我建议尝试http://tools.percona.com来帮助进行初始配置，您也可以联系Percona支持获取更具体的帮助。 - Bill Karwin

Answer 5

根据您的实现，建议可能会有所不同。以下是直接从MySQL文档中复制的一些注意事项：

批量数据加载提示

在将数据导入InnoDB时，请确保MySQL未启用自动提交模式，因为这需要为每个插入执行日志刷新到磁盘。在导入操作期间禁用自动提交，请使用SET autocommit和COMMIT语句将其包围。

如果需要插入多行，请使用多行INSERT语法以减少客户端和服务器之间的通信开销：

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

如果正在进行大量批量插入，请尝试避免在插入后使用“select from last_insert_id”，因为它会严重减缓插入速度（使6分钟的插入变成13小时的插入），如果您需要该数字进行另一个插入（例如子表），请将自己的编号分配给ID（前提是您确定没有其他人同时进行插入）。

如前所述，您可以增加InnoDB缓冲池的大小（innodb_buffer_pool_size变量）。这通常是一个好主意，因为默认大小相当小，大多数系统都可以容纳更多的内存来提高缓冲池的性能。这将增加大多数查询的速度，特别是SELECT查询（因为在查询之间将保留更多记录在缓冲区中）。插入缓冲区也是缓冲池的一部分，并将存储最近插入的记录，如果您基于先前插入的值进行未来插入，则会增加速度。希望这可以帮助您 :)

- Joy Rê

---

此外，如果设计允许的话，请考虑使用LOAD DATA INFILE（http://dev.mysql.com/doc/refman/5.0/en/load-data.html）。它比常规插入要快得多。 - Andrew