如何提高风数据SQL查询性能 [英] How to improve wind data SQL query performance
问题描述
我正在寻求有关如何通过更改以下内容来优化(如果可能)用于读取风信息(请参见下文)的SQL查询的性能的帮助.数据库结构，查询还是其他?
I'm looking for help on how to optimize (if possible) the performance of a SQL query used for reading wind information (see below) by changing the e.g. the database structure, query or something else?
我使用托管数据库来存储具有超过80万行的表格，其中包含风信息(速度和方向).每分钟从风速计添加新数据.使用PHP脚本访问该数据库，该脚本创建了一个网页，用于使用Google的可视化API绘制数据.
I use a hosted database to store a table with more than 800,000 rows with wind information (speed and direction). New data is added each minute from an anemometer. The database is accessed using a PHP script which creates a web page for plotting the data using Google's visualization API.
网页加载大约需要15秒.我在PHP和Javascript部分中都添加了一些时间测量，以分析代码并找到可能需要改进的地方.
The web page takes approximately 15 seconds to load. I've added some time measurements in both the PHP and Javascript part to profile the code and find possible areas for improvements.
我希望改进的部分是以下查询，该查询大约需要4秒钟才能执行.该查询的目的是将15分钟的风速(最小值/最大值/平均值)分组，并计算在此测量期间的平均值和最小值/最大值的总和.
One part where I hope to improve is the following query which takes approximately 4 seconds to execute. The purpose of the query is to group 15 minutes of wind speed (min/max/mean) and calculate the mean value and total min/max during this period of measurements.
SELECT AVG(d_mean) AS group_mean,
MAX(d_max) as group_max,
MIN(d_min) AS
group_min,
dir,
FROM_UNIXTIME(MAX(dt),'%Y-%m-%d %H:%i') AS group_dt
FROM (
SELECT @i:=@i+1,
FLOOR(@i/15) AS group_id,
CAST(mean AS DECIMAL(3,1)) AS d_mean,
CAST(min AS DECIMAL(3,1)) AS d_min,
CAST(max AS DECIMAL(3,1)) AS d_max,
dir,
UNIX_TIMESTAMP(STR_TO_DATE(dt, '%Y-%m-%d %H:%i')) AS dt
FROM table, (SELECT @i:=-1) VAR_INIT
ORDER BY id DESC
) AS T
GROUP BY group_id
LIMIT 0, 360
...
$oResult = mysql_query($sSQL);
该表具有以下结构:
1 ID int(11) AUTO_INCREMENT
2 mean varchar(5) utf8_general_ci
3 max varchar(5) utf8_general_ci
4 min varchar(5) utf8_general_ci
5 dt varchar(20) utf8_general_ci // Date and time
6 dir varchar(5) utf8_general_ci
使用以下设置:
- 数据库:MariaDB，5.5.42-MariaDB-1〜wheezy
- 数据库客户端版本:libmysql-5.1.66
- PHP版本:5.6
- PHP扩展名:mysqli
推荐答案
到目前为止，我完全同意这些注释-将数据放入表中后，将其清除.
I strongly agree with the comments so far -- Cleanse the data as you put it into the table.
完成清理后，让我们避免...
Once you have done the cleansing, let's avoid the subquery by doing...
SELECT MIN(dt) as 'Start of 15 mins',
FORMAT(AVG(mean), 1) as 'Avg wind speed',
...
FROM table
GROUP BY FLOOR(UNIX_TIMESTAMP(dt) / 900)
ORDER BY FLOOR(UNIX_TIMESTAMP(dt) / 900);
我不了解LIMIT的目的.我想您一次要几天.为此，我建议您在FROM和GROUP BY之间添加( 清洗后).
I don't understand the purpose of the LIMIT. I'll guess that you want to a few days at a time. For that, I recommend you add (after cleansing) between the FROM and the GROUP BY.
WHERE dt >= '2015-04-10'
AND dt < '2015-04-10' + INTERVAL 7 DAY
从"2015-04-10"上午开始，这将显示7天.
That would show 7 days, starting '2015-04-10' morning.
要处理800K的表，您肯定需要(再次清洗后 ):
In order to handle a table of 800K, you would decidedly need (again, after cleansing):
INDEX(dt)
要清理80万行，有多种方法.我建议创建一个新表，复制数据，进行测试，并最终进行交换.像...
To cleanse the 800K rows, there are multiple approaches. I suggest creating a new table, copy the data in, test, and eventually swap over. Something like...
CREATE TABLE new (
dt DATETIME,
mean FLOAT,
...
PRIMARY KEY(dt) -- assuming you have only one row per minute?
) ENGINE=InnoDB;
INSERT INTO new (dt, mean, ...)
SELECT str_to_date(...),
mean, -- I suspect that the CAST is not needed
...;
编写新选择并进行测试.
Write the new select and test it.
现在，new缺少较新的行.您可以重建它，并希望在一分钟内完成所有操作，或者玩一些其他游戏.让我们知道您是否需要那里的帮助.
By now new is missing the newer rows. You can either rebuild it and hope to finish everything in your one minute window, or play some other game. Let us know if you want help there.
这篇关于如何提高风数据SQL查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
