MySQL 查詢效能優化三大實戰技巧：預處理語句、隨機抽樣與分頁優化

萬級節點 P2P 系統的 MySQL 優化實戰

在 P2P 應用中，節點數動輒上萬且頻繁變動，查詢與插入效能至關重要。以下是實測有效的三大優化技巧。

技巧一：使用 Prepared Statement 綁定查詢

原理：預先構建查詢語法樹，執行時跳過解析優化階段，顯著提升高頻固定模式查詢效能。

適用場景：查詢條件固定、極高頻執行的插入/查詢操作。

C API 實現流程

1. 綁定階段：創建 MYSQL_STMT，調用 mysql_stmt_prepare() 預編譯 SQL，佔位符 ? 對應參數
2. 參數綁定：填充 MYSQL_BIND 結構數組，指定類型、緩衝區、長度等
3. 執行階段：賦值參數，調用 mysql_stmt_execute() 執行

代碼示例（插入操作）

// 1. 準備語句
MYSQL_STMT *stmt = mysql_stmt_init(conn);
char *sql = "INSERT INTO HostCache(SessionID, ChannelID, ISPType, ExternalIP, ExternalPort, InternalIP, InternalPort) VALUES (?,?,?,?,?,?,?)";
mysql_stmt_prepare(stmt, sql, strlen(sql));

// 2. 綁定參數
MYSQL_BIND bind[7];
memset(bind, 0, sizeof(bind));
// 依次設置 bind[0]~bind[6] 的 buffer_type, buffer, length, is_null
mysql_stmt_bind_param(stmt, bind);

// 3. 執行插入
strcpy(param.sessionid, sessionid);
// ... 填充其他字段
mysql_stmt_execute(stmt);

技巧二：大數據量隨機抽樣優化

痛點：傳統 ORDER BY RAND() 需全表掃描排序，十萬條記錄查詢 140ms + 獲取結果集 500ms。

優化方案：LIMIT + 偏移量計算

1. 先查總數：SELECT COUNT(*) FROM table WHERE ...
2. 計算隨機起始位置與步長
3. 使用 LIMIT offset, count 直接定位抽取

代碼邏輯

int total = get_total_count();           // 第一次查詢
int need = min(total, TARGET_COUNT);
int quotient = total / need;
int remainder = total % need;
int start = rand() % (remainder + 1);

for (int i = 0; i < need; i++) {
    int pos = start + quotient * i;
    mysql_data_seek(result, pos);
    row = mysql_fetch_row(result);
    // 處理行數據
}

效果：雖查詢兩次，但大數據量下反而更高效，避免了全量排序與結果集傳輸開銷。

技巧三：高效分頁與游標優化

配合 mysql_data_seek() 直接定位行偏移，避免逐行 mysql_fetch_row() 的順序掃描開銷，適合大結果集分頁場景。

總結對比