SQL -从大数据集中返回多个记录的最新信息

Question

背景

我有一个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 of 1有更频繁的数据间隔，所以当查询运行时，它会返回该ID的两个记录，然后继续向下排列。在它到达5条记录之后，它停止了，这意味着stock id of 5没有任何数据返回，尽管它确实存在。正如你所能想象的那样，当没有返回数据时，应用程序中的内容就会被分解。

第3部分:解决问题的尝试

1. 最明显的答案似乎是添加一个GROUP BY stock_id作为一种方法，要求我获得与我预期的每只股票相同数量的结果。不幸的是，这使我回到了第1部分，在第1部分中，这个查询虽然工作，但花费了1-2秒，因为它最终必须遍历与之前没有限制的相同的50k+行。这对我没什么好处了。
2. 下一个想法是任意地使LIMIT大于它所需要的大小，这样它就可以捕获所有的行。这不是一个可预测的解决方案，因为查询可能是数千种股票的任意组合，每种股票都有不同的可用数据间隔。最极端的例子是每日相对于每分钟拉涨的股票，这意味着在第二个股票出现之前，一个人可能有接近350+行的位置。将其乘以一个查询中的股票数量--比如50，这仍然需要查询15k+加行。可行，但不理想，而且潜在的不可扩展性。

第四部分:建议？

让一个API调用启动潜在的50+ DB查询仅仅是为了获取股票价格数据是一种糟糕的做法吗？我是否应该使用LIMIT的一些阈值来最小化失败的可能性，使之足够舒适？是否有其他SQL方法允许我返回所需的行，而不必查询大量的表？

任何帮助都很感激。

Answer 1

最快的方法是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时，索引不能有效地使用。

Answer 2

群智-最大

SELECT b.*
    FROM ( SELECT stock_id, MAX(created_at) AS created_at
            FROM stock_proces
            GROUP BY stock_id
         ) AS a
    JOIN stock_prices AS b  USING(stock_id, created_at)

需要：

INDEX(stock_id, created_at)

如果同一股票在同一秒钟内可以有两行，这将给出2行。有关备选方案，请参阅下面的链接。

如果这对是唯一的，那么将其作为PRIMARY KEY并去掉id；这也将有助于性能。

更多讨论：http://mysql.rjweb.org/doc.php/groupwise_max#using_an_uncorrelated_subquery