代码之家 › 专栏 › 技术社区 › rg88

加速大量mysql更新和插入

mysql php

rg88 · 技术社区 · 14 年前

我有一个应用程序,需要更新大量的数据超过大量的条目。基本上它做了7000个插入和/或更新,但它需要很长的时间(大约9分钟。。。平均每个查询0.08秒)。本质上,我是在寻找一般的提速,以提出多个这样的要求(我不期待一个具体的答案,我的模糊的例子。。。希望这能帮你解释一下。

以下是分析请求的一些示例:

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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

反复无常(嗯,大约7000次)。这是一次更新,它收集24小时内每隔一段时间生成的数据,然后每天对数据库进行一次大规模更新。鉴于我向你展示的有限的一点,有什么建议可以加快这一进程吗?

例如。。。与其为每个时间戳做一个选择,不如一次为一个范围做一个选择,然后在脚本中遍历它们,这样做有意义吗?

有点像:

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

$foo 然后做:

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

编辑: 为了增加一点,在我的情况下,提高响应性的一件事是更改一堆检查记录是否存在的代码,并根据结果进行插入或更新以使用 INSERT... ON DUPLICATE KEY UPDATE

4 回复 | 直到 14 年前

Naveed 14 年前

一些有用的链接:

来自MySQL文档:

Speed of INSERT Statements

如果同时从同一客户端插入多行,请使用在时间。这要快得多使用单独的单行插入一个非空表,你可以调整 bulk_insert_buffer_size变量到使数据插入更快。

如果多个客户机插入许多行,则可以获得更高的速度使用INSERT DELAYED语句。

同时,SELECT语句正在运行,如果没有删除的行在数据文件的中间。

从文本文件加载表时,请使用“加载数据内嵌”。这是通常比使用快20倍插入语句。

通过一些额外的工作,可以使加载数据填充运行均匀表有许多索引。

Amber 14 年前

如果还没有,请使用准备好的语句(通过 mysqli PDO ,或其他支持它们的数据库库)。重用相同的准备好的语句并简单地更改参数值将有助于加快速度,因为MySQL服务器只需解析一次查询。

INSERT 可以通过提供多组 VALUES -插入多行的单个查询比每个插入一行的单个查询的等效数量快得多。

cherouvim 14 年前

你的建议是正确的。尝试减少发送到服务器的查询数量,因为这将节省您的多重通信开销。

当然,如果inital select ranged查询返回的数据太多,那么PHP可能会遇到瓶颈。

Jon Black 14 年前

如果可能的话,我可能会使用一个存储过程,然后从我的应用程序代码中调用它。此外,我会考虑适当的索引,并可能为planet_i d添加一个基于整数的替换代理键,作为一个13字节的字符键,在连接和查找中的性能不会像4字节的整数键那样好。

(我刚刚读到,估计宇宙中有1000万兆可居住的行星,所以也许你最好使用bigint unsigned:P)

drop procedure if exists update_insert_habitable_planets;

delimiter #

create procedure update_insert_habitable_planets
(
in p_planet_id int unsigned,
in p_timestamp datetime,
)
proc_main:begin

  start transaction;

  -- bulk update / insert whatever

  commit;

end proc_main #

delimiter ;