如何以可扩展的方式执行多个 MySQL INSERT (PHP)
我们的 PHP 代码中有一个循环，它将行插入到表中。例如：
while ($info = mysql_fetch_assoc($someQuery)) {
mysql_query("INSERT INTO X (i,j) VALUES ($info['ii'],$info['jj'])");
}
几个月前这还好，因为循环只会迭代几次。然而，由于我们的网站获得了更多流量，此循环现在有时会迭代 1000 次或更多次。该表有一些开销（4,305 KiB），并且来自该表的选择显示在MySQL慢日志，可能是因为他们不得不等待一长串 INSERT 来释放锁？
我应该如何优化代码以使其能够更好地扩展？
我想我可以尝试一些事情：
- 插入延迟 - 需要调查一下。有帮助吗？
- 尝试在同一查询中插入多行。但我应该设置什么限制呢？ 50、500、1000？
We have a loop in our PHP code which inserts rows into a table. e.g.:
while ($info = mysql_fetch_assoc($someQuery)) {
mysql_query("INSERT INTO X (i,j) VALUES ($info['ii'],$info['jj'])");
}
This was fine a few months ago because the loop would only iterate several times. However, due to our website getting more traffic this loop now sometimes iterates 1000 or more times. The table has some overhead (4,305 KiB) and SELECTs from this table are appearing in the MySQL slow-log, probably because they are having to wait for a long list of INSERTs to release the locks?
How should I optimise the code so it can scale better?
Some things I thought I might try:
- INSERT DELAYED - Need to look into it. Could it help?
- Try inserting multiple rows in the same query. But what limit should I set? 50, 500, 1000?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
什么是$someQuery？您可以使用 INSERT ... SELECT 语法吗？
What is $someQuery? Could you use INSERT ... SELECT syntax?
不要重复
mysql_query()，而是使用准备好的语句< /a>.它们是使用不同值多次重复相同查询的更有效方法。另外，我会研究慢速日志中出现的那些查询。使用
EXPLAIN () 检查是否正在使用索引。Instead of repeating
mysql_query(), use prepared statements. They are a much more efficient way of repeating the same query many times with different values.Also, I would look into those queries that are appearing in the slow log. Use
EXPLAIN (<query>)to check that indexes are being used.您还可以构建如下语句：
您可以尝试不同的限制，以确定您的 SQL 在何处（如果有）变得太长。然后你可以将限制设置为理智的东西。
You can also build up a statement like this:
You can experiment with different limits to determine where, if anywhere, your SQL gets too long. Then you can set the limit to something sane.
本·詹姆斯所说的很重要。如果执行相同的 SQL，并且只更改其中的参数，准备好的语句会快得多。
另外，您可以尝试将整个循环更改为：
当然，您必须调整 $someQuery，因此我仅选择与列 i 和 j 类型相同（或可以自动转换）的两列。
如果你在 php 中没有非常复杂的机制，那么这应该是可能的，并且比任何 php 循环都要快得多。
What Ben James said is important. Prepared statements are much faster if you execute the same SQL, and just change parameters in it.
Also, You my try to change the whole loop to something like:
Of course you have to adapt the $someQuery, so I selects only two columns which are of the same type (or can by cast automaticly) as the columns i and j.
If you do not have very complicated mechanism in php, then this shloud be possible and will be much faster than any php loop.
将数据导入 MySQL 的最快方法之一是
加载数据INFILE。将其视为 CSV 导入。您可以一次将一行数据写入磁盘，然后进行批量加载。根据 此页面有关插入速度LOAD DATA INFILE比直接INSERT快 20 倍。不过，可能还有其他不良副作用，因为表很可能在整个过程中被锁定，而不是一次只锁定一行。例如，以 100 行批次运行此操作可能会使问题的两个部分都易于管理 - 我认为您只需要进行实验即可。
One of the fastest ways to get data into MySQL is
LOAD DATA INFILE. Think of it as a CSV import. You could write your data one row at a time to disk and then do a bulk load. According to this page on insert speedsLOAD DATA INFILEis 20x faster than a straightINSERT.There may be other undesirable side-effects, though, as the table may well be locked for the whole process rather than just one row at a time. Running this in e.g. 100 row batches may make both parts of the problem manageable - I think you'll just need to experiment.
新版本的MySQL中有存储过程和函数。您可以使用带有变量、循环和条件块的结构化语言。一个存储过程应该比许多独立插入更快。然而，这是一门需要学习的新语言。
您需要 php 中的 mysqli 扩展才能使用存储过程。
您还可以查看 mysqli_multi_query。
http://pl.php.net/manual/en/mysqli。多查询.php
There are stored procedures and function in new versions of MySQL. You can use there a structured language with variables, loops and conditional blocks. One stored procedure should be faster than many independent inserts. However, it is a new language to learn.
You need mysqli extension in php, to use stored procedures.
You can also look at mysqli_multi_query.
http://pl.php.net/manual/en/mysqli.multi-query.php