您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 查詢效能優化三大實戰技巧

編輯：MySQL 資料庫相關知識

場景背景

P2P 節點管理系統，萬級節點、頻繁變動，需極致查詢與寫入效能。本文分享三個經實測有效的優化技巧。

技巧一：預備語句綁定查詢

原理：預先解析 SQL、建立執行計畫，重複執行時省去解析優化開銷。適合「SQL 固定、參數變化、高頻執行」場景。

C API 實作範例

// 1. 綁定階段（僅執行一次）
MYSQL_STMT *stmt = mysql_stmt_init(conn);
const char *sql = "INSERT INTO HostCache (SessionID, ChannelID, ISPType, ExternalIP, ExternalPort, InternalIP, InternalPort) VALUES (?, ?, ?, ?, ?, ?, ?)";
mysql_stmt_prepare(stmt, sql, strlen(sql));

MYSQL_BIND bind[7];
memset(bind, 0, sizeof(bind));
// 設定各參數緩衝區、型別、長度...
mysql_stmt_bind_param(stmt, bind);

// 2. 執行階段（高頻呼叫）
void insert_host_cache(const char *sid, const char *cid, int isp, ...) {
    // 填充綁定變數
    strcpy(param.sid, sid);
    // ...
    mysql_stmt_execute(stmt);  // 極快，無解析開銷
}

效能增益

減少 SQL 解析、優化、執行計畫生成的 CPU 開銷
防止 SQL 注入（參數與語句分離）
網路傳輸僅發送參數，減少頻寬

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

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

方案比較

方法	做法	效能	適用場景
傳統法	查出所有符合記錄 → 應用層隨機挑選	差（大結果集傳輸、記憶體佔用高）	小資料集（< 1 萬筆）
LIMIT + OFFSET	先 `COUNT(*)` 取總數 → 計算隨機起始位置 → `LIMIT offset, count`	優（僅傳輸需求筆數）	大資料集、隨機抽樣

LIMIT 方案實作

// 1. 取得總筆數
SELECT COUNT(*) FROM HostCache WHERE ChannelID=? AND ISPType=?;
// 假設總數 100,000，需取 100 筆
$offset = rand(0, 100000 - 100);

// 2. 直接取樣
SELECT SessionID, ExternalIP, ExternalPort, InternalIP, InternalPort
FROM HostCache 
WHERE ChannelID=? AND ISPType=?
LIMIT $offset, 100;

注意事項

OFFSET 過大時 MySQL 仍需掃描前面行數（雖不回傳），極大偏移仍有效能問題
極大資料集可考慮「隨機主鍵法」：WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM t) ORDER BY id LIMIT 100

技巧三：連線池管理

高併發下頻繁建立/銷毀連線開銷極大，連線池複用連線為核心解法。

連線池架構

// 兩個佇列
idle_list  : 空閒連線佇列
busy_list  : 使用中連線佇列
mutex      : 執行緒同步鎖

// 取得連線
MYSQL* get_connection() {
    lock(mutex);
    if (!idle_list.empty()) {
        conn = idle_list.pop_front();
        busy_list.push_back(conn);
    } else if (current_size < max_size) {
        conn = create_new_connection();
        busy_list.push_back(conn);
    } else {
        conn = nullptr; // 或等待/拒絕
    }
    unlock(mutex);
    return conn;
}

// 歸還連線
void release_connection(MYSQL* conn) {
    lock(mutex);
    busy_list.remove(conn);
    idle_list.push_back(conn);
    unlock(mutex);
}

// 使用範例
bool delete_by_session(const char* sid) {
    MYSQL* conn = get_connection();
    if (!conn) return false;
    bool ret = execute_delete(conn, sid);
    release_connection(conn);
    return ret;
}