以下是分析请求的一些示例：
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`,?)
重复恶作剧(约7,000次).这是一个更新,收集在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 SQL查询的结果进行插入或更新.这导致了我的特殊情况下大概30％的速度增长,因为它至少将一个数据库的行程从该方程中删除,并且数千个请求真的加起来.
> 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文档：
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.If multiple clients are inserting a lot of rows,you can get higher speed
by using the INSERT DELAYED statement.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.When loading a table from a text file,use LOAD DATA INFILE. This is
usually 20 times faster than using
INSERT statements.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.
