處理Visits表中數百萬行的最佳方法是什么？

根據這個問題，答案是正確的，使查詢更好，但不能解決整個問題。

CREATE TABLE `USERS` (
 `ID` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `NAME` char(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

USERS 表中只有 5 行。

CREATE TABLE `VISITS` (
 `USER_ID` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `VISITED_IN` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY `USER_ID` (`USER_ID`,`VISITED_IN`),
 CONSTRAINT `VISITS_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `USERS` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

VISITS 表內的索引：

VISITS 表中有 5,740,266 行：

C9XzpOxWtuh893z1GFB2sD4BIko2 = 4,359,264 profile visits
I2I7CZParyMatRKnf8NiByujQ0F3 = 1,237,286 profile visits
EJ12BBKcjAr2I0h0TxKvP7uuHtEg = 143,716 profile visits
VgqUQRn3W6FWAutAnHRg2K3RTvVL = 0 profile visits
M7jwwsuUE156P5J9IAclIkeS4p3L = 0 profile visits

查詢耗時：（秒會根據行數變化）

SELECT COUNT(*) FROM VISITS WHERE USER_ID = C9XzpOxWtuh893z1GFB2sD4BIko2

• 在應用 Rick James 的答案之前，查詢需要 90 到 105 秒
• 應用 Rick James 的答案后，查詢需要 55 到 65 秒

SELECT COUNT(*) FROM VISITS WHERE USER_ID = I2I7CZParyMatRKnf8NiByujQ0F3

• 在應用 Rick James 的答案之前，查詢需要 90 到 105 秒
• 應用 Rick James 的答案后，查詢需要 20 到 30 秒

SELECT COUNT(*) FROM VISITS WHERE USER_ID = EJ12BBKcjAr2I0h0TxKvP7uuHtEg

• 在應用 Rick James 的答案之前，查詢耗時 90 到 105 秒 在應用 Rick James 的答案之后，查詢耗時 4 到 8 秒

SELECT COUNT(*) FROM VISITS WHERE USER_ID = VgqUQRn3W6FWAutAnHRg2K3RTvVL

• 在應用 Rick James 的答案之前，查詢需要 90 到 105 秒
• 應用 Rick James 的答案后，查詢需要 1 到 3 秒

SELECT COUNT(*) FROM VISITS WHERE USER_ID = M7jwwsuUE156P5J9IAclIkeS4p3L

• 在應用 Rick James 的答案之前，查詢需要 90 到 105 秒
• 應用 Rick James 的答案后，查詢需要 1 到 3 秒

正如您在應用索引之前所看到的，即使用戶有幾行（訪問），也需要 90 到 105 秒來計算特定用戶的訪問。

應用索引后情況變得更好，但問題是：

1. 如果我訪問C9XzpOxWtuh893z1GFB2sD4BIko2個人資料，則需要 55 到 65 秒才能獲得個人資料訪問。
2. 如果我訪問I2I7CZParyMatRKnf8NiByujQ0F3個人資料，則需要 20 到 30 秒才能獲得個人資料訪問。
3. ETC...

有幾行（訪問）的用戶會很幸運，因為他的個人資料會加載得更快。

我可以忽略上面的所有內容并在 USERS 表中創建一個列來計算用戶訪問并在捕獲新訪問時增加它而不創建數百萬行但這對我不起作用，因為我允許用戶像這樣過濾訪問：

過去 60 分鐘
最近 24 小時
最近 7 天
最近 30 天
最近 6 個月
最近 12 個月
所有時間

我應該怎么辦？

uj5u.com熱心網友回復：

問題是您正在評估并不斷重新評估非常大的行數，這些行數實際上是歷史的一部分并且永遠不會改變。您不能每次都計算這些行，因為這需要很長時間。您想提供以下計數：

最后 60 分鐘

過去 24 小時

過去 7 天

過去 30 天

過去六個月

整天

您需要四個表：

表 1：一個小而快的表，保存今天和昨天的訪問記錄

表 2：從“前天（“D-2”）到“D-7”、欄位“D2toD7”、“D8toD30”、“D31toD183”和“D184andEarlier”期間的更小、非常快的表持有計數'

表 3：包含每個用戶每天的訪問次數的表

表 4：您已經擁有的非常大且速度慢的表，每次訪問都記錄了時間戳

然后，您可以通過對表 1 進行直接查詢來獲得“最近 60 分鐘”和“最近 24 小時”的計數，這將非常快。“過去 7 天”是表 1 中所有記錄的計數（對于您的用戶）加上表 2 中的 D2toD7 值（對于您的用戶）。“過去 30 天”是表 1 中所有記錄的計數（對于您的用戶) 加上 D2toD7，加上 D8toD30。“過去六個月”是表 1 加上 D2toD7、D8toD30 和 D31toD183。“所有時間”是表 1 加上 D2toDy，加上 D8toD30，加上 D31toD183，加上 D184andEarlier。

我將運行 php 腳本來檢索這些值——無需嘗試在一個復雜的查詢中完成所有操作。幾個，甚至幾個，非常快速地點擊資料庫，收集數字，回傳結果。該腳本將在不到一秒的時間內運行。

那么，如何更新表 2 中的計數？這就是您需要表 3 的地方，其中包含每個用戶每天的訪問次數。創建表 3 并使用包含所有訪問、GROUP BY 用戶和日期的龐大表中的資料的 COUNT 值填充它，這樣您就可以知道每個用戶每天的訪問次數。您只需要創建和填充表 3 一次。您現在需要一個 CRON 作業/腳本或類似的，每天運行一次。此腳本將從表 1 中洗掉記錄前天訪問的行。此腳本需要：

1. 確定每個用戶前天的訪問次數
2. 將這些計數與“前天”日期一起插入表 3。
3. 將計數值添加到表 2 中每個用戶的“D2toD7”值。
4. 從表 1 中洗掉“前天”行。
5. 在表 3 中查找每個用戶的（剛剛變為的）D8 的值。將此值從每個用戶的“D2 到 D7”值遞減。
6. 對于“D8toD30”、“D31toD183”等欄位中的每一個，對于現在屬于時間段的那一天遞增，根據從時間段退出的那一天遞減。使用存盤在表 3 中的值。

記住要保持分寸；183 天的時間大約為六個月，足以滿足任何實際訪問計數目的。

概述：您無法快速計算數百萬行。利用這些是永遠不會改變的歷史人物的事實。因為您有最新計數的表 1，所以您只需每天更新一次歷史期間計數。多個（甚至幾十個）非常非常快速的查詢將很快為您提供準確的結果。

uj5u.com熱心網友回復：

這不是答案，而是一個建議。

1. 如果他們不需要實時資料，我們能不能運行一個調度程式并每 x 分鐘將這些資料插入一個匯總表中。然后我們可以訪問該匯總表以供您計算。

注意：如果您需要按時間計算的登錄計數，我們可以在您的表中添加同步時間列。（然后你的夏季表也會動態增加）

表列前：

PK_Column、用戶ID、訪問次數、sync_time

2. 我們可以為您的前端使用異步（反應式）實作。這意味著，資料將在一段時間后加載，但用戶永遠不會在作業中遇到這種延遲。

3. 創建一個匯總表，每天上午 12 點運行一項作業，并將用戶明智和日期明智的最后一次訪問的摘要放入該表中。

user_visit_Summary 表：PK_Column、用戶 ID、Number_of_Visites、VISIT_Date

注意：為用戶 ID 和日期欄位創建索引

當您檢索資料時，您將通過 DB 函式訪問它

Select count(*)    (Select Number_of_Visites from VISITS 
where user_id = xxx were VISIT_Date <= ['DATE 12:00 AM' -1]   PK_Column desc limit 1)  as old_visits
where USER_ID = xxx and VISITED_IN > 'DATE 12:00 AM';

uj5u.com熱心網友回復：

對于一天或更長時間的任何查詢，請使用匯總表。

即構建并維護一個包含 3 列 user_id、date、count 的 Summary 表；PRIMARY KEY(user_id, date) 對于“所有時間”和“上個月”，查詢將是

SELECT CUM(count) FROM summary WHERE user_id='...';
SELECT CUM(count) FROM summary
    WHERE user_id='...'
      AND date >= CURDATE() - INTERVAL 1 MONTH

每晚午夜，在匯總表中將您當前的表向上滾動到每個用戶的一行，然后清除該表。該表將繼續用于較短的時間跨度。

這為每個用戶在每個時間范圍內實作了速度。

但是，有一個“錯誤”。我強迫“day”/“week”/etc 是午夜到午夜，不允許你真的說“過去 24 小時”。

我建議對該“錯誤”采取以下折衷方案：

• 對于較長的時間跨度，請使用匯總表，并從另一個表中計算今天的點擊次數。
• 為了讓“24 小時”到達昨天，將另一張桌子更改為回到昨天早上。也就是說，僅在 24 小時后清除，而不是 1 個日歷日。

要一次獲取所有計數器，請在子查詢中完成所有作業。有兩種方法，可能同樣快，但結果是行或列：

-- rows:
SELECT 'hour', COUNT(*) FROM recent ...
UNION ALL
SELECT '24 hr', COUNT(*) FROM recent ...
UNION ALL
SELECT 'month', SUM(count) FROM summary ...
UNION ALL
SELECT 'all', SUM(count) FROM summary ...
;

-- columns:
SELECT
    ( SELECT COUNT(*) FROM recent ... ) AS 'hour'.
    ( SELECT COUNT(*) FROM recent ... ) AS '24 hr',
    ( SELECT SUM(count) FROM summary ... ) AS 'last month'
    ( SELECT SUM(count) FROM summary ... ) AS 'all time'
;

“……”是

WHERE user_id = '...'
  AND datetime >= ...  -- except for "all time"

將多個查詢滾動到一個查詢中（無論哪種方式）都有一個優勢——這可以避免多次往返服務器和多次呼叫優化器。

forpas 提供了另一種方法https://stackoverflow.com/a/72424133/1766831但需要對其進行調整以達到兩個不同的表。

標籤：mysql sql 数据库 数据库设计

上一篇：如何獲取兩個值都可以在兩列中的行？

下一篇：如果資料存在，則使用executemany更新行