为什么即使行数相差很大，查询仍需要相同的时间来获取数据？

Question

我的 VISITS 表中有 29,938,766 行，该表如下所示

USER_ID (INT)	VISITED_IN（DATETIME）
65	2020-08-26 07:57:43
1182	2019-03-15 02:46:48
1564	2015-07-04 10:59:44
73	2021-03-18 00:25:08
3791	2017-10-17 12:22:45
51	2022-05-02 19:11:09
917	2017-11-20 15:32:06
3	2019-12-29 15:15:51
51	2015-02-08 17:48:30
1531	2020-08-05 08:44:55
等等...	等等...

运行此查询时，需要 17-20 秒并返回 63,514（用户有 63,514 次访问）

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 917

运行此查询时，需要 17-20 秒并返回 193（用户有 193 次访问）

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 716

问题是，即使用户只有 3、50、70 或 1,000,000 次访问，查询 29,938,766 行始终需要 17-20 秒。

我认为问题是因为它正在循环所有行？

第二个查询必须比第一个查询更快。这取决于行数。但两个查询花费相同的时间！

您对我有何建议以避免此问题？

---

表结构

---

更新：这是一个新的建议场景：

当用户进入他或其他人的个人资料时，他可以看到个人资料访问次数，并且可以使用这种方式过滤访问

Last 24 hours
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Last 7 days
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Last 30 days
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY);

All time
|
---> SELECT VISITS FROM USERS WHERE USER_ID = 5;

此外，我将创建一个每天执行此命令的重复事件。

DELETE FROM VISITS WHERE VISITED_IN <= DATE_SUB(NOW(), INTERVAL 30 DAY);

此外，在 VISITS 表中添加新行时，我将确保增加 VISITS 列。

UPDATE USERS SET VISITS = VISITS + 1 WHERE ID = 5

Answer 1

INDEX(user_id, visited_in)

将加速您提到的所有 SELECTs 。他们将不得不扫描索引的一大块；他们不必“扫描整个表格”。

DELETE 需要 `INDEX(visited_in)。但如果运行得不够频繁，就会出现问题。这是因为一次删除数千行可能是一个问题。考虑至少每小时运行一次删除操作。

如果表非常大等，请考虑使用“时间序列”分区。有了 DROP PARTITION，速度快多了。 分区

任何缓存服务都会提供过时的计数，但有时会更快。

“每次有人打开页面时都可以访问数据库”，但前提是查询足够高效。做索引。

在我对您的其他问题的回答中，我解释了汇总表如何可以更快地加快速度。然而，它假设“最后 N 天”是从午夜到午夜测量的。您当前的查询是 NOW() - INTERVAL N DAY。这比午夜实施起来更混乱。您愿意改变“最后N天”的含义吗？

（一些 INDEX 基础知识...）

任何索引的一个重要原因是它能够根据某些列快速查找行。

• INDEX 是映射到行的键列表。
• UNIQUE INDEX 是 INDEX 加上唯一性约束 - 意味着索引中没有两行具有相同的值。
• 唯一的 PRIMARY KEY 是一个唯一索引，指定用于唯一标识表中的每一行。

“key”和“index”是同义词。

索引（在MySQL的InnoDB引擎中）被实现为BTree（实际上是B Tree；参见维基百科）。在 PK 的情况下，其余列与 PK 值一起坐在那里。对于“辅助”键，BTree 的“值”部分是 PK 列。

任何索引都可以包含 1 列或多列（称为“复合”）

INDEX(lastname) 不太可能是唯一的 INDEX(lastname,firstname) 仍然不太可能是唯一的，但它是“复合的”。