MySQL 實戰筆記：商品系統設計 (二) — 定時任務聚合點擊量

背景回顧

承接上篇「商品主表設計」，點擊日誌表 prod_clicklog 原記錄每次點擊明細，隨時間增長極快。優化方向：同一日同用戶同商品合併計數，大幅減少行數。

優化點擊日誌表結構

• click_date：類型改為 DATE (僅保留日期)
• 新增 click_num INT DEFAULT 1：點擊次數計數器
• 唯一鍵：(prod_id, user_ip, user_id, click_date)

存儲過程：載入商品並記錄點擊 (Upsert 邏輯)

CREATE PROCEDURE sp_load_prod(
    IN _prod_id INT,
    IN _user_ip VARCHAR(15),
    IN _user_id INT
)
BEGIN
    -- 1. 查詢商品主表
    SELECT * FROM prod_main WHERE prod_id = _prod_id LIMIT 1;
    SET @num = FOUND_ROWS();

    IF @num = 1 THEN
        -- 2. 檢查今日是否已有點擊記錄
        SELECT COUNT(*) INTO @c
        FROM prod_clicklog
        WHERE prod_id = _prod_id
          AND user_ip = _user_ip
          AND user_id = _user_id
          AND click_date = CURRENT_DATE;

        IF @c > 0 THEN
            -- 3a. 已存在：計數器 +1
            UPDATE prod_clicklog
            SET click_num = click_num + 1
            WHERE prod_id = _prod_id
              AND user_ip = _user_ip
              AND user_id = _user_id
              AND click_date = CURRENT_DATE;
        ELSE
            -- 3b. 首次點擊：插入新記錄 (click_num 默認 1)
            INSERT INTO prod_clicklog(prod_id, user_ip, user_id, click_date)
            VALUES (_prod_id, _user_ip, _user_id, CURRENT_DATE);
        END IF;
    END IF;
END

測試驗證：多次 CALL sp_load_prod(1, '192.168.1.1', 100); 觀察 click_num 遞增。

聚合邏輯：游標遍歷更新主表總點擊量

商品主表 prod_main 中的 prod_click_all 需定期同步。使用游標逐行處理聚合結果：

CREATE PROCEDURE count_prod_click()
BEGIN
    DECLARE is_end INT DEFAULT 0;
    DECLARE p_id INT;
    DECLARE c_num INT;

    -- 定義游標：按商品聚合點擊總數
    DECLARE cur CURSOR FOR
        SELECT prod_id, SUM(click_num) FROM prod_clicklog GROUP BY prod_id;

    -- 游標結束處理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1;

    OPEN cur;
    FETCH cur INTO p_id, c_num; -- 預讀第一行

    WHILE is_end != 1 DO
        UPDATE prod_main SET prod_click_all = c_num WHERE prod_id = p_id;
        FETCH cur INTO p_id, c_num; -- 讀取下一行
    END WHILE;

    CLOSE cur;
END

執行 CALL count_prod_click(); 後，主表 prod_click_all 即更新為最新總量。

自動化：MySQL Event Scheduler 定時觸發

1. 檢查/啟用事件調度器：

   SHOW VARIABLES LIKE 'event_scheduler'; -- OFF 則執行
   SET GLOBAL event_scheduler = ON;

2. 建立事件，每 10 秒執行一次聚合：

   CREATE EVENT event_count_prodclick
   ON SCHEDULE EVERY 10 SECOND
   DO CALL count_prod_click();

實戰應用建議

• 生產環境建議調整頻率 (如每 5-15 分鐘)，平衡即時性與資料庫負載。
• 大數據量下，游標逐行 UPDATE 效能較低，可改用 INSERT ... ON DUPLICATE KEY UPDATE 或臨時表 + JOIN UPDATE 批次處理。
• 點擊日誌表需定期歸檔/分區，避免單表過大。

版權聲明：本筆記整理自「程序員在囧途」沈逸老師《Web 級 MySQL 顛覆實戰課程》，轉載請保留來源。