I have recently switched my database tables from MYISAM to INNODB and experience bad timeouts with queries, mostly inserts. One function I use previously took <2 seconds to insert, delete and update a large collection of records across ~30 MYISAM tables, but now that they are INNODB, the function causes a PHP timeout.
我最近将我的数据库表从MYISAM切换到INNODB，并经历了查询超时(主要是插入)。我以前使用的一个函数<2秒，可以在30个MYISAM表中插入、删除和更新大量记录，但是现在它们是INNODB，这个函数会导致PHP超时。
The timeout was set to 60 seconds. I have optimised my script enough that now, even though there are still many queries, they are combined together (multiple inserts, multiple deletes, etc) and the script now takes ~25 seconds, which is a substantial increase from what appeared to be at least 60 seconds.
超时设置为60秒。我已经对我的脚本进行了足够的优化，现在即使仍然有许多查询，它们仍然被组合在一起(多个插入、多个删除等等)，脚本现在需要大约25秒，这比看起来至少60秒的时间增加了很多。
This duration is still over 10x quicker when previously using MYISAM, is there any mistakes I could be making in the way I process these queries? Or are there any settings that could assist in the performance? Currently the MySQL is using the default settings of installation.
在以前使用MYISAM时，这个持续时间仍然比以前快了10倍，在处理这些查询的过程中，我可能会犯什么错误吗?或者有什么可以帮助性能的设置?目前，MySQL使用默认的安装设置。
The queries are nothing special, DELETE ... WHERE ... simple logic, same with the INSERT and UPDATE queries.
查询没什么特别的，删除……在那里……简单的逻辑，与插入和更新查询相同。
3 个解决方案
#1
2
Hard to say without knowing too much about your environment, but this might be more of a database tuning problem. InnoDB can be VERY slow on budget hardware where every write forces a true flush. (This affects writes, not reads.)
如果不太了解您的环境，很难说清楚，但这可能更多地是一个数据库调优问题。InnoDB在预算硬件上的速度可能会非常慢，因为每个写入操作都会产生一个真正的刷新。(这会影响写操作，而不是读操作。)
For instance, you may want to read up on options like:
例如，您可能需要阅读以下选项:
innodb_flush_log_at_trx_commit=2
sync_binlog=0
By avoiding the flushes you may be able to speed up your application considerably, but at the cost of potential data loss if the server crashes.
通过避免刷新，您可以显著地提高应用程序的速度，但是如果服务器崩溃，可能会造成数据损失。
If data loss is something you absolutely cannot live with, then the other option is to use better hardware.
如果数据丢失是您绝对无法忍受的事情，那么另一个选择是使用更好的硬件。
#2
1
Run explain for each query. That is, if the slow query is select foo from bar;, run explain select foo from bar;.
为每个查询运行explain。也就是说，如果慢速查询是从bar中选择foo;，运行explain select foo from bar;
Examine the plan, and add indices as necessary. Re-run the explain, and make sure the indices are being used.
检查计划，并在必要时添加指标。重新运行explain，并确保使用了索引。
#3
0
Innodb builds hash indexes which helps to speed up lookup by indexes by passing BTREE index and using hash, which is faster
Innodb构建哈希索引，它通过传递BTREE索引和使用哈希加快索引查找速度，哈希更快
#1
2
Hard to say without knowing too much about your environment, but this might be more of a database tuning problem. InnoDB can be VERY slow on budget hardware where every write forces a true flush. (This affects writes, not reads.)
如果不太了解您的环境，很难说清楚，但这可能更多地是一个数据库调优问题。InnoDB在预算硬件上的速度可能会非常慢，因为每个写入操作都会产生一个真正的刷新。(这会影响写操作，而不是读操作。)
For instance, you may want to read up on options like:
例如，您可能需要阅读以下选项:
innodb_flush_log_at_trx_commit=2
sync_binlog=0
By avoiding the flushes you may be able to speed up your application considerably, but at the cost of potential data loss if the server crashes.
通过避免刷新，您可以显著地提高应用程序的速度，但是如果服务器崩溃，可能会造成数据损失。
If data loss is something you absolutely cannot live with, then the other option is to use better hardware.
如果数据丢失是您绝对无法忍受的事情，那么另一个选择是使用更好的硬件。
#2
1
Run explain for each query. That is, if the slow query is select foo from bar;, run explain select foo from bar;.
为每个查询运行explain。也就是说，如果慢速查询是从bar中选择foo;，运行explain select foo from bar;
Examine the plan, and add indices as necessary. Re-run the explain, and make sure the indices are being used.
检查计划，并在必要时添加指标。重新运行explain，并确保使用了索引。
#3
0
Innodb builds hash indexes which helps to speed up lookup by indexes by passing BTREE index and using hash, which is faster
Innodb构建哈希索引，它通过传递BTREE索引和使用哈希加快索引查找速度，哈希更快