时间戳

首页 > 解决方案 > SQL - 从大型数据集中返回多条记录中的最新记录

mysql - SQL - 从大型数据集中返回多条记录中的最新记录

问题描述

背景

我有一张stock_price表，其中存储了大约 1000 只股票的历史盘中股价。尽管定期清除旧数据，但该表定期有 5M+ 记录。结构松散：

| id     | stock_id | value | change |  created_at         |
|--------|----------|-------|--------|---------------------|
| 12345  | 1        | 50    | 2.12   | 2020-05-05 17:39:00 |
| 12346  | 2        | 25    | 1.23   | 2020-05-05 17:39:00 |

我经常需要为 API 端点一次获取大约 20 支股票的最新股票价格。最初的实现对每只股票执行一个查询：

select * from stock_prices where stock_id = 1 order by created_at desc limit 1

第 1 部分：低效查询

20 多个查询的效率有点低，但它确实有效。代码 (Laravel 6) 已更新为使用正确的关系 ( stock hasMany stock_prices )，进而生成如下查询：

select
  *
from
  `stock_prices`
where
  `stock_prices`.`stock_id` in (1, 2, 3, 4, 5)
order by
  `id` desc

虽然这可以节省查询，但运行需要 1-2 秒。运行explain显示它仍然必须在任何给定时间查询 50k+ 行，即使使用外键索引也是如此。我的下一个想法是，我将limit在查询中添加一个仅返回等于我要求的股票数量的行数。查询现在是：

select
  *
from
  `stock_prices`
where
  `stock_prices`.`stock_id` in (1, 2, 3, 4, 5)
order by
  `id` desc
limit
  5

第 2 部分：查询有时会丢失记录

性能是惊人的 - 毫秒级处理。但是，它可能无法返回一个/多个股票的价格。由于limit已添加，如果任何股票在下一只股票之前有多个价格（行），它将“消耗”其中一个行数。

这是一个非常真实的场景，因为一些股票每分钟提取一次数据，其他股票每 15 分钟提取一次数据，等等。因此，在某些情况下，上述查询limit会为一只股票提取多行数据，随后不会为其他股票返回数据：

| id   | stock_id | value | change | created_at     |
|------|----------|-------|--------|----------------|
| 5000 | 1        | 50    | 0.5    | 5/5/2020 17:00 |
| 5001 | 1        | 51    | 1      | 5/5/2020 17:01 |
| 6001 | 2        | 25    | 2.2    | 5/5/2020 17:00 |
| 6002 | 3        | 35    | 3.2    | 5/5/2020 17:00 |
| 6003 | 4        | 10    | 1.3    | 5/5/2020 17:00 |

在这种情况下，您可以看到stock_id的1具有更频繁的数据间隔，因此在运行查询时，它返回了该 ID 的两条记录，然后沿着列表继续向下。stock id在它达到 5 条记录后，它就停止了，这意味着它5没有返回任何数据，尽管它确实存在。可以想象，当没有数据返回时，这会破坏应用程序中的内容。

第 3 部分：尝试解决

  1. 最明显的答案似乎是添加 aGROUP BY stock_id作为一种方式，要求我获得与每只股票预期数量相同的结果。不幸的是，这使我回到了第 1 部分，其中该查询在运行时需要 1-2 秒，因为它最终必须遍历相同的 50k+ 行，就像之前没有限制一样。这让我好不了多少。

  2. 下一个想法是任意制作LIMIT比它需要的更大的值，以便它可以捕获所有行。这不是一个可预测的解决方案，因为查询可以是数千只股票的任意组合，每只股票都有不同的可用数据间隔。最极端的例子是每天与每分钟拉动的股票，这意味着在第二只股票出现之前可能有接近 350 多行的股票。将其乘以一个查询中的股票数量——比如 50，这仍然需要查询 15k+ 行。可行，但不理想，并且可能不可扩展。

第 4 部分：建议？

让一个 API 调用启动可能 50 多个数据库查询只是为了获取股票价格数据是一种糟糕的做法吗？是否有一些LIMIT我应该使用的阈值来最大程度地减少失败的机会以使自己感到舒适？是否有其他 SQL 方法可以让我返回所需的行而无需查询大量表？

任何帮助表示赞赏。

标签： mysqldatabaselaravelmariadbgroupwise-maximum

解决方案

最快的方法是union all：

(select * from stock_prices where stock_id = 1 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 2 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 3 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 4 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 5 order by created_at desc limit 1)

这可以使用上的索引stock_prices(stock_id, created_at [desc])。不幸的是，当您使用 时in，无法有效地使用索引。

推荐阅读