Warum benötigt die Abfrage die gleiche Zeit, um die Daten abzurufen, obwohl die Anzahl der Zeilen sehr unterschiedlich ist?

Question

Ich habe 29.938.766 Zeilen in meiner VISITS-Tabelle, die so aussieht

USER_ID (INT)	VISITED_IN(DATETIME)
65	26.08.2020 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	20.11.2017 15:32:06
3	29.12.2019 15:15:51
51	2015-02-08 17:48:30
1531	2020-08-05 08:44:55
Warte...	Warte...

Das Ausführen dieser Abfrage dauert 17–20 Sekunden und gibt 63.514 zurück (Benutzer hat 63.514 Besuche)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 917

Das Ausführen dieser Abfrage dauert 17–20 Sekunden und gibt 193 zurück (Benutzer hat 193 Besuche)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 716

Das Problem besteht darin, dass die Abfrage von 29.938.766 Zeilen immer 17–20 Sekunden dauert, selbst wenn der Benutzer nur 3, 50, 70 oder 1.000.000 Besuche hat.

Ich denke, das Problem liegt darin, dass die Schleife alle Zeilen durchläuft?

Die zweite Abfrage muss schneller sein als die erste Abfrage. Es kommt auf die Anzahl der Reihen an. Aber beide Abfragen dauern gleich lange!

Welche Vorschläge haben Sie für mich, um dieses Problem zu vermeiden?

---

Tabellenstruktur

---

UPDATE: Hier ist ein neues vorgeschlagenes Szenario:

Wenn ein Benutzer das Profil von ihm oder einer anderen Person aufruft, kann er die Anzahl der Profilbesuche sehen und die Besuche auf diese Weise filtern

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;

Zusätzlich werde ich ein wiederkehrendes Ereignis erstellen, das diesen Befehl jeden Tag ausführt.

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

Außerdem werde ich beim Hinzufügen einer neuen Zeile in der VISITS-Tabelle darauf achten, die Spalte VISITS zu erhöhen.

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) 仍然不太可能是唯一的，但它是“复合的”。