高性能MySQL，如何求出24小时发贴的数量？
看高性能MySQL的时候，看到一个如何求出过去24小时发布的信息
`CREATE TABLE msg_per_hr{
hr DATETIME NOT NULL, cnt INT UNSIGNED NOT NULL, PRIMARY KEY(hr)};//文中所说的另起一个汇总的表`
文中说：把下面3个查询的结果相加，就得到过去24小时内发布消息的精确数量了
1、`SELECT SUN(cnt) FROM msg_per_hr WHERE hr
BETWEEN
CONCAT(LEFT(NOW(), 14), '00:00') -INTERVAL 23 HOUR
AND
CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;`2、`SELECT COUNT(*) FROM message
WHERE
posted >= NOW() - INTERVAL 24 HOUR
AND
posted < CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR;`3、SELECT COUNT(*) FROM message WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');
既然有了汇总表，那么直接在汇总表查询不就行了吗？为啥还要去庞大的message表count？
3个查询的结果相加，不知道其逻辑关系是怎么，还有就是第二条语句 一时是NOW() - INTERVAL 24 HOUR 一时又是CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
不太明白，求大神解惑。
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
问题分析
您提到的问题涉及如何通过汇总表和原始数据表（message）来计算过去24小时内发布的消息数量。文中提到了三个查询，并建议将它们的结果相加以得到精确的数量。然而，您对以下几点存在疑问：
- 为何需要从原始表
message中进行COUNT(*)操作，而不是直接使用汇总表msg_per_hr？ - 三个查询的逻辑关系是什么？
- 第二条语句的时间范围为何不一致（
NOW() - INTERVAL 24 HOUR和CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR）？
接下来，我们将逐一解答这些问题。
1. 为何需要从原始表message中进行COUNT(*)操作？
汇总表msg_per_hr的设计是为了按小时统计消息数量，其结构如下：
CREATE TABLE msg_per_hr (
hr DATETIME NOT NULL,
cnt INT UNSIGNED NOT NULL,
PRIMARY KEY(hr)
);
该表的核心作用是按小时聚合数据，从而减少对原始表message的频繁扫描。然而，汇总表的数据更新可能存在延迟或不完全覆盖的情况，例如： - 汇总表可能尚未包含最近一小时的消息数据。 - 汇总表可能未及时处理某些异常情况（如数据延迟写入）。
因此，为了确保统计结果的准确性，文中建议结合原始表message进行补充查询。具体来说： - 第一条查询：从汇总表msg_per_hr中获取过去23小时的消息总数。 - 第二条查询：从原始表message中获取过去24小时内的消息总数。 - 第三条查询：从原始表message中获取当前小时的消息总数。
通过这种方式，可以弥补汇总表可能存在的数据延迟问题，同时利用汇总表的高效性减少对原始表的扫描范围。
2. 三个查询的逻辑关系
第一条查询
SELECT SUM(cnt)
FROM msg_per_hr
WHERE hr BETWEEN
CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;
- 作用：从汇总表
msg_per_hr中获取过去23小时的消息总数。 - 时间范围：从当前时间的整点减去23小时，到当前时间的整点减去1小时。
- 逻辑：汇总表按小时存储数据，因此可以直接通过
SUM(cnt)快速统计过去23小时的消息数量。
第二条查询
SELECT COUNT(*)
FROM message
WHERE posted >= NOW() - INTERVAL 24 HOUR;
- 作用：从原始表
message中获取过去24小时内的消息总数。 - 时间范围：从当前时间减去24小时，到当前时间。
- 逻辑：此查询覆盖了完整的24小时范围，包括汇总表可能未完全覆盖的部分。
第三条查询
SELECT COUNT(*)
FROM message
WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');
- 作用：从原始表
message中获取当前小时的消息总数。 - 时间范围：从当前时间的整点开始，到当前时间。
- 逻辑：此查询专门用于统计当前小时的消息数量，因为汇总表可能尚未包含这部分数据。
结果相加的逻辑
- 第一条查询：统计过去23小时的消息总数。
- 第二条查询：统计过去24小时的消息总数，覆盖了第一条查询未包含的当前小时部分。
- 第三条查询：单独统计当前小时的消息数量，避免重复计算。
通过将这三个查询的结果相加，可以确保统计结果既高效又准确。
3. 第二条语句的时间范围为何不一致？
时间范围1：NOW() - INTERVAL 24 HOUR
- 含义：从当前时间减去24小时，表示过去24小时的完整时间范围。
- 用途：用于从原始表
message中获取过去24小时内的所有消息。
时间范围2：CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
- 含义：从当前时间的整点减去23小时，表示过去23小时的整点时间范围。
- 用途：用于从汇总表
msg_per_hr中获取过去23小时的消息总数。
不一致的原因
- 汇总表的特点：汇总表按小时存储数据，因此时间范围必须对齐到整点。
- 原始表的特点：原始表存储的是每条消息的具体时间戳，因此可以直接使用
NOW() - INTERVAL 24 HOUR来表示过去24小时。
这种设计的目的是充分利用汇总表的高效性和原始表的完整性，确保统计结果既快速又准确。
总结与建议
-
为何需要从原始表
message中进行COUNT(*)操作？- 汇总表可能存在数据延迟或不完全覆盖的情况，因此需要结合原始表进行补充查询。
-
三个查询的逻辑关系是什么？
- 第一条查询：统计过去23小时的消息总数（汇总表）。
- 第二条查询：统计过去24小时的消息总数（原始表）。
- 第三条查询：统计当前小时的消息总数（原始表）。
- 将三个查询的结果相加，确保统计结果既高效又准确。
-
第二条语句的时间范围为何不一致？
- 汇总表按小时存储数据，时间范围需对齐到整点。
- 原始表存储具体时间戳，时间范围可直接使用
NOW() - INTERVAL 24 HOUR。
希望以上解答能够帮助您理解文中的逻辑！如果还有其他疑问，请随时提问。
