MySQL 索引仅用于最大值和最小值

Question

我有一个包含数百万行的巨大表，其中存储从一些气象站获得的值。每行包含收集该值的站点、指标(例如温度、湿度、噪音水平等)、日期和值本身。

这是它的结构:

• 车站:int(8)
• 指标:int(8)
• 日期:日期时间
• 值: float

这些是我定义的索引:

• 主键:站点+指标+日期
• KEY:metrica(用于外键)

有时，我有兴趣检索每个站上次发送某些值的时间。然后我使用这个查询:

 SELECT station, MAX(date)
 FROM MyTable
 GROUP BY station

这个查询非常慢，因为它必须读取整个表。如果我为 station+date 添加索引，查询现在可以使用它并且变得非常快。但表存储也增加了很多，对我来说，索引所有日期值没有用，因为我只对最大值感兴趣。

所以我的问题是是否可以创建一个索引某个范围的索引，最好只跟踪最大值。

Answer 1

据我所知。但您还有其他解决方案。

在其他数据库中，我建议使用物化 View ，但 MySQL 不支持物化 View ( SO#3991912 )，因此您必须自己创建和管理自己的聚合表。

如果源表更新得不太频繁，CREATE TABLE last_observation AS SELECT station, MAX(date) AS date FROM Observations GROUP BY station 即可完成这项工作。只需在任何相关请求之前发出声明即可。

如果您的服务器有足够的资源，您可以将表保留在MEMORY中，以获得超快的响应。在这种情况下，您需要显式命名列CREATE TABLE last_observation (station VARCHAR(x), lastDate DATE) ENGINE=MEMORY AS SELECT station, MAX(date) AS lastDate FROM Observations GROUP BY station。当然，每次打开 mysql 时都应该定期发出该语句。

如果您的表经常更新，您可以使用源表 ( Full tutorial here ) 上的触发器来管理内容。

另一个完全不同的解决方案是使用面向列的数据库。几年前我们使用了Infobright，它有一个免费的社区版，并且对您来说是完全透明的(只需安装它并像以前一样使用mysql)。