mysql - 我怎样才能选择一些行，比如 "get as many rows as possible in 5 seconds"？

Question

目标是:在 5 秒后获取最多的行数并且获取的行数不超过加载的行数。目的不是创建超时。

几个月后，我认为这可能行得通，但没有成功:

declare @d1 datetime2(7); set @d1=getdate();
select c1,c2 from t1 where (datediff(ss,@d1,getdate())<5)

Answer 1

虽然近年来关系数据库的趋势越来越倾向于基于成本的查询优化，但据我所知，没有一种 RDBMS 本身支持为查询指定最大成本(时间或 I/O) .

“让它超时并使用到目前为止收集的记录”的想法是一个有缺陷的解决方案。缺陷在于复杂查询可能会花费前 5 秒对查询计划的子树执行散列，以生成将由计划的后续部分使用的数据。所以5秒后，你可能还是没有记录。

要在 5 秒内获得尽可能多的记录，您需要一个具有已知估计执行计划的查询，然后可以使用该计划来估计要请求的最佳记录数，以便使查询运行时间尽可能接近 5 秒。换句话说，知道查询优化器估计它每秒可以处理 875 条记录，您可以请求 4,375 条记录。查询的运行时间有时可能会超过 5 秒，但随着时间的推移，您的平均执行时间应该会下降到接近 5 秒。

那么...如何实现这一点？

在您的特定情况下，这是不可行的。问题是“已知的估计执行计划”。为了使这项工作可靠，您需要一个具有已知执行计划的存储过程，而不是临时查询。由于您无法在您的环境中创建存储过程，因此这是行不通的。不过，对于想要探索该解决方案的其他人，here's an academic paper by a team who implemented this concept in Oracle.我没有阅读完整的论文，但根据摘要，听起来他们的工作可以转化为任何具有基于成本的优化的 RDBMS(例如 MS SQL、MySQL 等)

好的，那么在您的情况下您可以做什么？

如果你不能以“正确”的方式做到这一点，那就用 hack 来解决。

我的建议:保留您自己的“预估成本”统计数据。

提前进行一些测试并估计您通常可以在 4 秒内返回多少行。假设这个数字是 18,000。

因此您将查询限制为 18,000 行。但是每次运行它时，您也会跟踪执行时间，并保持移动平均值，例如，最近 50 次执行。如果该平均值小于 4.5 秒，则将查询大小增加 1% 并重置移动平均值。所以现在您的应用程序每次请求 18,180 行。迭代 50 次后，如果移动平均低于 4.5s，则再次增加 1%。

如果您的移动平均值超过 4.75 秒，则减去 1%。

随着时间的推移，此方法应该会收敛到针对您的特定查询/环境/等优化的 N 行解决方案。并且应该在条件发生变化时(例如高并发与低并发)进行调整(缓慢但稳定地)

只有一个——从头开始，两个——更多的东西……

1. 作为一名 DBA，我不得不说...任何查询超过 5 秒的情况应该极为罕见。特别是，如果它是一个频繁运行并被前端应用程序使用的查询，那么它绝对不应该运行 5 秒。如果您确实有无法在 5 秒内完成的面向用户的查询，则表明数据库设计需要改进。

2. Jonathan VM 的 Greenbar 报告法则 我曾经为一家公司工作，该公司仍然使用大型机应用程序，每天都会吐出大量的 greenbar 点阵打印报告。其中大部分被忽略了，在使用的少数人中，大多数人从未阅读过第一页。一份报告可能有数千行按帐户年龄降序排列……而用户所需要做的只是查看年龄最大的 10 行。我的法则是:实际需要查看大量行的用例数量是无限小的。想想——认真想想——你的查询的用例，以及是否有很多很多记录确实是用户所需要的。