日期表查找效率- MySQL

Question

我需要计算"工作分钟"的数量。在两个datetime值之间，我们称它们为'Created'和'Finished'。

'Finished'总是在'Created'之后。这两个值可以相差1秒到几年。中位数差异为50,000秒或大约14小时。

工作时间定义为周一至周五上午9点至下午17点之间的工作时间;不包括我们国家的周末和法定假日。

我决定使用一个查找表，所以我生成了一个所有工作时间的表，明确地不包括周末、晚上和节假日…

CREATE TABLE `work_minutes` (
`min` datetime NOT NULL,
PRIMARY KEY (`min`),
UNIQUE KEY `min_UNIQUE` (`min`)
)

我用程序填充了所有的"工作时间"。从2017年到2024年，在这一点上，我开始觉得我的效率非常低，因为表开始膨胀到几十万行。

我可以很容易地进行查找，例如:

SELECT COUNT(min) FROM `work_minutes` AS wm
WHERE wm.min > '2022-01-04 00:04:03'
AND wm.min <= '2022-02-03 14:13:09';
#Returns 10394 'working minutes' in 0.078 sec

这对于一次性查找来说已经足够好了，但是查询一个包含70,000个值对的表需要90分钟以上。

因此，我对查询的缓慢和查找表不必要地膨胀的感觉感到不舒服。

我想我需要设置两个表，一个只是日期和另一个只是分钟，但不知道如何实现。日期逻辑从来不是我的强项。对我来说最重要的是查找可以合理快速有效地查询超过70,000个值。

工作在MySQL 5.7.30。提前感谢你的专业知识。

Answer 1

1. 将时间范围划分为3部分-开始和完成未完成的一天部分，以及由许多完整的日子组成的中间部分。当然，如果开始和结束的时间戳都有相同的日期部分，那么它将只是一个部分，如果它们的日期是连续的，那么你将有两个部分来处理。
2. 在未完成的工作日部分计算工作分钟数是没有问题的。工作日检查的常见重叠公式将有所帮助。
3. 创建静态日历/服务表，从早于开始时间戳中的任何可能日期开始，并保证包括结束时间戳中任何可能日期之后的所有日期。计算表中每个日期的累积工作分钟数。此表允许通过单个减法计算任何完整天数范围内的工作时间。

Answer 2

方案A:通过TO_SECONDS()将DATETIME的值转换为秒(从某个任意时间)，然后用简单的算术操作它们。

方案B:使用DATEDIFF()功能

您的COUNT(min)计算min IS NOT NULL。你也可以说COUNT(*)。但是你真的想要计算行数吗?