為什麼即使行數相差很大，查詢仍需要相同的時間來取得資料？

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