我被要求开发一个网络软件,能够存储一些来自热量计量设备的读数数据,并将热量费用分配给所有的业主。我选择用MySQL引擎MyISAM在php中工作。
我不习惯处理大数据,所以我简单地创建了一个逻辑数据库,我们有:
- 一个用于构建的表,以id作为主键索引(现在我们有~1200)db中的建筑物
- 一个包含所有建筑物中所有单位的表,以id作为主键索引,并以building_id链接到建筑物(总共约32k+单位)
- 一个包含所有公寓中所有加热器的表,以id作为索引的主键,并以flat_id链接到公寓(大约280k+加热器)
- 一个包含所有读取值的表,带有读取的时间戳,id作为主键,heater_id链接到加热器(现在大约2.7M+读取)
还有一个单独的表,与大楼相连,其中存储了开始日期和结束日期,两者之间必须进行费用划分。
当需要从建筑物中获取所有数据时,我使用的方法是通过单个查询从DB中获取原始数据,然后在php中进行详细说明,然后进行下一个查询。
下面是我使用的操作顺序:- 通过单个查询 从特定表中获取开始和结束日期
- 将日期存储在php变量 中
- 获取建筑物的所有单位:
SELECT * FROM flats where building_id=my_building_id
- 使用php while循环解析php中的所有数据
- 在while循环的每一步上,我都会查询该特定公寓的所有加热器:
SELECT * FROM heaters where flat_id=my_flat_id
- 使用php while循环 解析加热器的所有数据
- 在这个内部while循环的每一步上,我将获得该特定加热器的最后读数值:
SELECT * FROM reading_values where heater_id=my_heater_id AND data<my_data
现在的问题是我有严重的性能问题。
在有人指出它之前,我不能只获得读取值跳过上面列表的所有前6步,因为我需要打印账单,并且我必须在每张账单上写下所有的公寓信息和所有的加热器信息,所以无论如何我必须获得所有的公寓和加热器数据。
所以我想要一些关于如何提高脚本性能的建议:
- 所有的表都被索引了,但我必须在其他地方添加一些索引?
- 将使用一个查询子查询,而不是几个php代码中的一个提高性能?
- 还有其他建议吗?
我没有插入特定的代码,因为我认为它会使问题太重,但如果问我可以插入一些
Some:
- 如果可以避免,不要使用'SELECT *' ->只获取你真正需要的字段
- 我没有测试它在您的特定情况下,但通常一个查询连接所有三个表应该实现更好的性能,而不是通过循环结果与php。
- 如果你需要循环出于某种原因,那么至少使用mysql准备语句,这应该再次提高性能给定的查询量:)
希望有帮助!
对
编辑:只是为了举例说明一个替代查询,不确定这是否适合您的特定需求,也没有进行测试(这可能意味着我忘记了什么):
SELECT
a.field1,
b.field2,
c.field3,
d.field4
FROM heaters a
JOIN reading_values b ON (b.heater_id = a.heater_id)
JOIN flats c ON (c.flat_id = a.flat_id)
JOIN buildings d ON (d.building_id = c.building_id)
WHERE
a.heater_id = my_heater_id
AND b.date < my_date
GROUP BY a.heater_id
编辑2
根据您的评论,我修改了查询,以便它检索您想要的信息:给定建筑物id,它将根据给定日期列出所有加热器及其最新读数值:
SELECT
a.name,
b.name,
c.name,
d.reading_value,
d.created
FROM buildings a
JOIN flats b ON (b.building_id = a.building_id)
JOIN heaters c ON (c.flat_id = b.flat_id)
JOIN reading_values d ON (d.reading_value_id = (SELECT reading_value_id FROM reading_values WHERE created <= my_date AND heater_id = c.heater_id ORDER BY created DESC LIMIT 1))
WHERE
a.building_id = my_building_id
GROUP BY c.heater_id
了解它在您的环境中的表现应该是很有趣的。
对