高流量網站若每次點擊都即時更新資料庫計數欄位,會造成嚴重的資料庫連線壓力與鎖競爭。本文實作「記憶體快取累計 + 定時批次持久化」模式,大幅降低資料庫負載。
LinkedList 作為記憶體緩衝區package com.tot.count;
public class CountBean {
private String countType;
private int countId;
public CountBean() {}
public void setCountType(String t) { this.countType = t; }
public void setCountId(int id) { this.countId = id; }
public String getCountType() { return countType; }
public int getCountId() { return countId; }
}
package com.tot.count;
import java.util.LinkedList;
public class CountCache {
public static LinkedList<CountBean> list = new LinkedList<>();
public static void add(CountBean cb) {
if (cb != null) list.add(cb);
}
}
package com.tot.count;
import tot.db.DBUtils;
import java.sql.*;
public class CountControl {
private static long lastExecuteTime = 0;
private static final long EXECUTE_INTERVAL = 60_000; // 60 秒
public synchronized void executeUpdate() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtils.getConnection();
conn.setAutoCommit(false);
ps = conn.prepareStatement("UPDATE t_news SET hits = hits + 1 WHERE id = ?");
while (!CountCache.list.isEmpty()) {
CountBean cb = CountCache.list.removeFirst();
ps.setInt(1, cb.getCountId());
ps.addBatch(); // 累積批次
}
ps.executeBatch(); // 一次性提交
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try { if (conn != null) conn.rollback(); } catch (Exception ignore) {}
} finally {
try { if (ps != null) { ps.clearParameters(); ps.close(); } } catch (Exception ignore) {}
DBUtils.closeConnection(conn);
}
}
public void run() {
long now = System.currentTimeMillis();
if (now - lastExecuteTime > EXECUTE_INTERVAL) {
lastExecuteTime = now;
executeUpdate();
}
}
}
<%
CountBean cb = new CountBean();
cb.setCountId(Integer.parseInt(request.getParameter("cid")));
CountCache.add(cb);
out.print("快取隊列: " + CountCache.list.size() + "<br>");
CountControl ctrl = new CountControl();
ctrl.run();
out.print("執行後隊列: " + CountCache.list.size() + "<br>");
%>
| 技術 | 效果 |
|---|---|
PreparedStatement.addBatch() + executeBatch() | 單次網路往返執行多條 UPDATE,減少 Round-trip |
conn.setAutoCommit(false) + commit() | 單一交易包裹批次,保證原子性 |
LinkedList.removeFirst() | O(1) 取出頭部元素,適合佇列模式 |
| 時間間隔觸發 | 避免頻繁喚醒,平衡即時性與效能 |
synchronized 修飾 executeUpdate | 多執行緒環境下保護快取與資料庫操作 |
ScheduledExecutorService 固定頻率排程,而非依賴請求觸發ConcurrentLinkedQueue 或 Disruptor 實現無鎖高併發hits 欄位有索引,考慮使用 UPDATE ... SET hits = hits + ? 合併同一文章多次點擊INCR 原子計數,定時持久化至 MySQL