只有最大值和最小值的MySQL索引

问题描述：

我有一个巨大的表格，有数百万行存储从某些气象站获得的值。每行包含收集数值的站点，度量（例如，温度，噪音水平等），日期和数值本身。只有最大值和最小值的MySQL索引

这是它的结构：

station：INT（8）
metric：INT（8）
date：日期时间
value：浮动

而这些是我定义的指数：

PRIMARY KEY：station+metric+date
KEY：metrica（外键）

有时候，我感兴趣的检索最后一次每站都有送一些价值。然后我使用这个查询：

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

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

所以我的问题是如果有可能创建一个索引来索引一些范围，理想情况下只跟踪最大值。

您可能会更好地将这些信息存储在另一个表格中 - 比如说“Stations”表格 - 并且使用触发器在每次插入行时更新信息。 –

您可以将PK更改为“station + date + metric”吗？在功能上它会是同样的东西，但实际上它会（几乎）与添加额外索引时的情况一样快。 – deroby

@deroby我试过了，实际上这个查询会立即运行。但是另一个频繁的查询，例如“列出一个站的温度值的一周值”现在执行得更慢（不是很多，大约慢了25％）。鉴于我的应用程序中最后一个更频繁，我将继续使用以前的主键。谢谢！ –

答

不是我所知道的。但你有其他解决方案。

在其他数据库中，我建议使用物化视图，但MySQL不支持物化视图（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）。

答

INDEX(station, date)

将有效地处理是查询。或者，您可以将PRIMARY KEY重新排列为(station, date, metric)。

如果您还想在该日期的温度，那么你是一个更复杂的groupwise-max。

只有最大值和最小值的MySQL索引

相关推荐