加速大量的mysql更新和插入 [英] Speeding up large numbers of mysql updates and inserts

问题描述

我有一个需要在大量条目上更新大量数据的应用程序.基本上，它执行约7,000次插入和/或更新，但需要花费很长的时间(例如将近9分钟...平均每个查询约0.08秒).本质上，我正在寻求提高速度来处理多个这样的请求(我不希望对我模糊的示例给出具体的答案……只是希望，可以帮助解释).

I have an app that needs to update a large amount of data over a large number of entries. Basically it does some 7,000 inserts and/or updates but it takes a looooong time (like almost 9 minutes... averaging about 0.08 seconds per query). Essentially I'm looking for general speed ups to making multiple such requests (I'm not expecting a specific answer to my vague example... that's just to, hopefully, help explain).

以下是对请求进行概要分析的一些示例:

Here are some samples from profiling the requests:

SELECT `habitable_planets`.* FROM `habitable_planets` WHERE (timestamp = '2010-10-15T07:30:00-07:00') AND (planet_id = '2010_Gl_581_c')

INSERT INTO `habitable_planets` (`planet_id`, `timestamp`, `weather_air_temp`, `weather_cell_temp`, `weather_irradiance`, `weather_wind_float`, `biolumin_to_date`, `biolumin_detected`, `craft_energy_usage`, `craft_energy_consumed_to_date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

SELECT `habitable_planets`.* FROM `habitable_planets` WHERE (timestamp = '2010-10-15T07:45:00-07:00') AND (planet_id = '2010_Gl_581_c')

INSERT INTO `habitable_planets` (`planet_id`, `timestamp`, `weather_air_temp`, `weather_cell_temp`, `weather_irradiance`, `weather_wind_float`, `biolumin_to_date`, `biolumin_detected`, `craft_energy_usage`, `craft_energy_consumed_to_date`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

重复广告恶心(嗯，大约7,000次).这是一个更新，它收集在24小时内每隔一段时间生成的数据，然后每天对数据库进行一次大规模更新.鉴于我向您展示的内容有限，您对加快此过程有什么建议吗?

Repeat ad nauseam (well, about 7,000 times). This is an update that collects data generated at intervals over a 24 hour period and then makes a massive update to the database once per day. Given the limited bit I've shown you are there any suggestions for speeding this process up?

例如...是否有意义，而不是对每个时间戳进行一次选择，而是一次对一个范围进行一次选择，然后在脚本中对其进行迭代?

For example... would it make sense to, rather than do a select for each timestamp, do one select for a range all at once and then iterate over them in the script?

有点像:

SELECT `habitable_planets`.* FROM `habitable_planets` WHERE (planet_id = '2010_Gl_581_c')

将该结果分配给$foo，然后执行:

assign that result to $foo and then do:

foreach ($foo as $bar)
{
    if ($bar['timestamp'] == $baz) // where $baz is the needed timestamp
    {
    // do the insert here
    }
}

要补充一点，在我的情况下，提高响应能力的一件事是更改了一堆代码以检查是否有记录，并根据需要进行插入或更新使用 INSERT... ON DUPLICATE KEY UPDATE sql的结果询问.在我的特殊情况下，这导致大约30％的速度提高，因为它使方程式中至少减少了一次到数据库的旅程，并且确实增加了成千上万的请求.

To add a bit to this, one thing that improved responsiveness in my situation was to change a bunch of code that checked for a record existing and either did an insert or an update depending on the result into using an INSERT... ON DUPLICATE KEY UPDATE sql query. This resulted in about a 30% speed gain in my particular case because it cut at least one trip to the database out of the equation and over thousands of requests this really adds up.

推荐答案

一些有用的链接:

• 加快MySQL查询速度的32条提示
• 打开在MySQL查询缓存上以提高查询性能?
• 在单个查询中多次插入– PHP/MySQL
• 3种加速方法开启MySQL

• 32 Tips To Speed Up Your MySQL Queries
• Turn on MySQL query cache to speed up query performance?
• Multiple Insert in Single Query – PHP/MySQL
• 3 Ways to Speed Up MySQL

来自MySQL文档:

INSERT语句的速度说:

• 如果要同时从同一客户端插入许多行，请使用 具有多个值的INSERT语句 列表以在一个位置插入几行 时间.这是相当快的 (在某些情况下，速度要快很多倍) 使用单独的单行INSERT 陈述.如果要将数据添加到 一个非空表，您可以调整 bulk_insert_buffer_size变量 加快数据插入速度.

• If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.

如果多个客户端插入很多行，则可以提高速度 通过使用INSERT DELAYED语句.

If multiple clients are inserting a lot of rows, you can get higher speed by using the INSERT DELAYED statement.

对于MyISAM表，您可以使用并发插入在行上添加行. 同时SELECT语句是 运行，如果没有删除的行 在数据文件的中间.

For a MyISAM table, you can use concurrent inserts to add rows at the same time that SELECT statements are running, if there are no deleted rows in middle of the data file.

从文本文件加载表时，请使用LOAD DATA INFILE.这是 通常比使用快20倍 INSERT语句.

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

通过一些额外的工作，甚至可以使LOAD DATA INFILE运行 对于MyISAM表，当 表中有很多索引.

With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes.

这篇关于加速大量的mysql更新和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！