大表的MySQL性能问题

Question

我被要求开发一个网络软件，可以存储一些从热计量设备中读取的数据，并将热量费用分摊给所有的业主。我选择使用MySQL engine MyISAM在php中工作。

我不习惯于处理大数据，所以我只创建了一个逻辑数据库：

1. 一个用于构建的表，以id作为主键索引(现在我们在db中有大约1200座建筑物)。
2. 一个表，包含所有建筑物中的所有单位，以id作为主键索引，并将building_id链接到建筑物( 32k+公寓周围)
3. 一张有所有公寓内所有加热器的表，以id作为主键索引，flat_id链接到公寓( 280k+加热器周围)
4. 一个具有所有读取值、读取时间戳、一个id作为主键和heater_id链接到加热器的表(现在2.7M+读取)

还有一个单独的表格，与大楼相连，存放着开始日期和费用分摊的结束日期。

当需要从建筑物中获取所有数据时，我使用的方法是使用单个查询从DB获取原始数据，在php中详细说明，而不是进行下一个查询。

下面是我使用的操作序列：

1. 使用单个查询从特定表获取起始日期和结束日期。
2. 将日期存储在php变量中。
3. 把这栋楼的所有公寓都找出来：SELECT * FROM flats where building_id=my_building_id
4. 使用php while循环解析php中的所有数据。
5. 在时间周期的每一步中，我都会查询获取该特定平台的所有加热器：SELECT * FROM heaters where flat_id=my_flat_id
6. 用php while循环解析加热器的所有数据。
7. 在这个内部循环的每一步中，我将得到该特定加热器的最后一个读取值：SELECT * FROM reading_values where heater_id=my_heater_id AND data<my_data

现在的问题是，我有严重的性能问题。

在有人指出之前，我不能只看到上面列出的所有前6步的数值，因为我需要打印账单，在每一张账单上我必须写出所有的平面信息和所有加热器的信息，所以我必须得到所有的单位和加热器的数据。

因此，我想就如何提高脚本性能提出一些建议：

• 所有的表都是索引的，但是我必须在其他地方添加索引吗？
• 使用带有子查询的单个查询而不是php代码中的几个查询会提高性能吗？
• 还有其他建议吗？

我没有插入特定的代码，因为我认为这会使问题太重，但如果被问到，我可以插入一些。

Answer 1

有些：

1. 不要使用'SELECT *‘，如果您可以避免它，->只需得到您真正需要的字段
2. 在您的特定情况下，我没有测试它，但是通常，连接所有三个表的单个查询应该获得更好的性能，而不是使用php遍历结果。
3. 如果您出于某种原因需要循环，那么至少要使用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

了解它在您的环境中的表现应该是很有趣的。

问候