☰

当信息排序时（在本例中按日期排序），如何优化具有数百万行的表的数据库查询

tag-icon • tag-icon database postgresql query mongodb
当信息排序时（在本例中按日期排序），如何优化具有数百万行的表的数据库查询

想象一下，您正在 Whatsapp 上与您的 SO 聊天，并且对话持续数年，因此会有数千甚至数百万条消息。

CREATE INDEX date_index ON messages_table (date_column);

然后进行如下查询：

SELECT * FROM messages_table
WHERE date_column > '2023-01-01' AND date_column < '2023-12-31'
ORDER BY date_column ASC
LIMIT 50;

假设最常见的数据库查询是获取 50 条日期大于 X 且小于 Y 的消息，我有一些疑问：

  1. 这是最好的查询吗？
  2. 如何设计一个可以针对此进行优化的表格
  3. 获取最近 10 条消息是否有效？数据库如何实现这一点，而不必读取整个表（因此需要读取一百万条记录？）

我的猜测/我的想法 - 如果索引对磁盘上的数据进行排序并且使用 LIMIT，那么机器可以避免每次我们想要“最近的 50 条消息”以及类似的查询时读取所有百万以上的记录？

请注意，我还没有决定是否使用 Postgres 还是 MongoDB。

答案1

首次运行优化：

  1. 更新索引至：

    CREATE INDEX date_index ON messages_table (date_column DESC);

    WHERE DESC 似乎是列索引所需的（非反向）排序顺序。

  2. 使用前缀 重新运行查询EXPLAIN ANALYSE并检查查询计划，如下所示：

    SELECT * FROM messages_table
WHERE date_column > '2023-01-01' AND date_column < '2023-12-31'
ORDER BY date_column ASC
LIMIT 50;

在生成的查询计划中，您将发现您的查询是否利用了索引。

相关内容