MySQL 查詢優化三大技巧：Prepared Statement、索引設計與分頁優化

背景

在高併發、大數據量場景（如 P2P 節點管理），查詢與寫入效能至關重要。以下三個經實測有效的優化方向，適用於 MySQL 5.x/8.x。

技巧一：使用 Prepared Statement（預處理語句）

預處理將 SQL 解析、優化、生成執行計劃的過程前置，重複執行時省去解析開銷，適合「SQL 固定、參數變化、高頻執行」的場景。

C API 範例流程

1. 初始化： MYSQL_STMT *stmt = mysql_stmt_init(mysql);
2. 準備： mysql_stmt_prepare(stmt, "INSERT INTO t(a,b,c) VALUES(?,?,?)", len);
3. 綁定參數： 填充 MYSQL_BIND 陣列，指定型別、緩衝區、長度、is_null。
4. 綁定結果（查詢時）： 同理綁定 MYSQL_BIND 接收欄位。
5. 執行/取值： mysql_stmt_execute(stmt) / mysql_stmt_fetch(stmt)。
6. 釋放： mysql_stmt_close(stmt);

關鍵程式碼片段（插入範例）

// 1. 準備
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].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (char *)param->sessionid;
bind[0].buffer_length = ID_LENGTH;
bind[0].is_null = 0;
bind[0].length = 0;
// ... bind[1]~bind[6] 同理對應各欄位

mysql_stmt_bind_param(stmt, bind);

// 3. 執行（每次只需填充 param 結構體再呼叫）
strcpy(param->sessionid, sessionid);
// ... 填充其他欄位
mysql_stmt_execute(stmt);

效能提升

• 減少 SQL 解析與優化器開銷。
• 防止 SQL 注入（參數與語法分離）。
• 網路傳輸更精簡（二進制協議）。

技巧二：索引設計核心原則

• 最左前綴：複合索引遵循最左匹配，查詢條件順序應與索引欄位順序一致。
• 覆蓋索引：SELECT 欄位全在索引中，避免回表。
• 區分度：高基數欄位優先，低基數（如性別、狀態）單獨索引效果差。
• 避免函數運算：WHERE DATE(create_time) = '2024-01-01' 會導致索引失效，改用範圍查詢。
• 定期 ANALYZE TABLE：更新統計資訊，幫助優化器做正確決策。

技巧三：大數據量分頁優化

傳統 LIMIT 100000, 20 需掃描前 10 萬行，極慢。改用「延遲關聯」或「游標分頁」：

延遲關聯法

SELECT t.* FROM `table` t
INNER JOIN (
  SELECT id FROM `table` WHERE condition ORDER BY id LIMIT 100000, 20
) tmp USING (id);

子查詢只掃描主鍵索引，再回表取完整欄位。

游標分頁（Keyset Pagination）

-- 第一頁
SELECT * FROM `table` WHERE condition ORDER BY id LIMIT 20;

-- 後續頁（記住上一頁最後一筆 id）
SELECT * FROM `table` WHERE condition AND id > 12345 ORDER BY id LIMIT 20;

適用於「不需跳頁、只需下一頁」的無限滾動場景，效能極佳。

綜合建議

1. 開啟 slow_query_log，設定 long_query_time = 1。
2. 使用 EXPLAIN 分析執行計劃，關注 type、key、rows、Extra。
3. 適度反正規化、引入快取（Redis）、讀寫分離，從架構層面分擔壓力。