MySQL 查詢效能優化三大實戰技巧：預處理語句、LIMIT 隨機查詢、連線池

場景背景

P2P 應用節點數達萬級，頻繁變動，需極致的查詢與插入效能。以下三項優化經實測有效提升吞吐量。

技巧一：Prepared Statement 預處理語句

預先建立查詢語法樹，重複執行時省去解析優化開銷，適合「條件固定、高頻執行」的場景。

C API 實作要點

// 1. 綁定參數
MYSQL_STMT *stmt = mysql_stmt_init(conn);
mysql_stmt_prepare(stmt, "INSERT INTO HostCache(...) VALUES (?,?,?,?,?,?,?)", ...);
MYSQL_BIND bind[7] = {0};
// 設定各欄位 buffer_type、buffer、length、is_null
mysql_stmt_bind_param(stmt, bind);

// 2. 執行階段僅填值並呼叫
mysql_stmt_execute(stmt);

實測可大幅降低 CPU 佔用，尤其在高併發寫入時效果顯著。

技巧二：LIMIT 優化隨機抽樣查詢

需求：從大量符合條件的記錄中隨機抽取 N 筆（統計分析常見）。

方案比較

核心代碼邏輯

// 步驟 1：取得總筆數
SELECT COUNT(*) FROM HostCache WHERE ChannelID=? AND ISPtype=?

// 步驟 2：計算隨機起始位置
limit_lower = (total <= N) ? 0 : rand() % (total - N);
limit_upper = limit_lower + N;

// 步驟 3：單次 LIMIT 查詢
SELECT SessionID, ExternalIP... FROM HostCache 
WHERE ChannelID=? AND ISPtype=? LIMIT ?, ?

技巧三：連線池管理

高併發下頻繁建立/銷毀連線開銷巨大。雙佇列設計：

• idle_list：閒置連線佇列
• busy_list：使用中連線佇列
• 互斥鎖保護佇列操作

核心流程

// 初始化：建立 N 個連線放入 idle_list
// 取用：
mutex.lock();
conn = idle_list.pop_front();
busy_list.push_back(conn);
mutex.unlock();

// 歸還：
mutex.lock();
busy_list.remove(conn);
idle_list.push_back(conn);
mutex.unlock();

搭配 RAII 風格封裝，確保異常時也能正確歸還連線。

總結

三項技巧分別針對 CPU 解析開銷、大數據隨機存取、連線建立開銷三大瓶頸，組合使用可將 P2P 節點服務支撐量級提升數倍。