MySQL 查詢效能提升三大實戰技巧：Prepared Statement、覆蓋索引、分頁優化

技巧一：Prepared Statement（預處理語句）消除重複解析

原理：SQL 文本 → 解析樹 → 優化器 → 執行計畫。Prepared Statement 將解析/優化階段前置，重複執行時直接複用計畫。

適用場景：高頻、固定模式的 INSERT/UPDATE/SELECT（如日誌寫入、API 查詢）。

C API 範例

// 1. 準備
MYSQL_STMT* stmt = mysql_stmt_init(conn);
const char* sql = "INSERT INTO access_log(uid, url, ua, ip, create_time) VALUES (?, ?, ?, ?, ?)";
mysql_stmt_prepare(stmt, sql, strlen(sql));

// 2. 綁定參數
MYSQL_BIND bind[5];
memset(bind, 0, sizeof(bind));
// ... 針對每個欄位設定 buffer_type, buffer, length, is_null ...

mysql_stmt_bind_param(stmt, bind);

// 3. 迴圈執行
for (each_log_entry) {
    // 填充 bind 緩衝區
    mysql_stmt_execute(stmt);
}
mysql_stmt_close(stmt);

PHP PDO 範例

$pdo = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // 使用原生預處理
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ? AND created_at > ?");
$stmt->execute([1, '2023-01-01']);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { /* ... */ }

技巧二：覆蓋索引避免回表

定義：查詢所需欄位全部包含在索引中，InnoDB 直接從二級索引葉節點返回資料，無需回聚簇索引查找完整行。

實例：

-- 查詢
SELECT id, status FROM orders WHERE user_id = 100 AND status = 1;

-- 覆蓋索引
ALTER TABLE orders ADD INDEX idx_uid_status_cover (user_id, status, id);
-- 查詢計畫 Extra: Using index

效能提升：隨機 I/O 降為順序索引掃描，百萬級資料查詢從秒級降至毫秒級。

技巧三：大偏移量分頁優化

問題：LIMIT 1000000, 20 需掃描 1,000,020 行再丟棄前 1,000,000 行。

方案 A：延遲關聯

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

子查詢僅掃描索引（覆蓋索引），主查詢按主鍵精準回表 20 行。

方案 B：游標分頁（Keyset Pagination）

-- 首頁
SELECT * FROM `table` WHERE condition ORDER BY id LIMIT 20;
-- 記錄最後一筆 id = 1000020
-- 下一頁
SELECT * FROM `table` WHERE condition AND id > 1000020 ORDER BY id LIMIT 20;

無偏移量，效能恆定，適合無限滾動、API 分頁。

綜合檢查清單

• [ ] 高頻 SQL 是否使用 Prepared Statement？
• [ ] 核心查詢是否有覆蓋索引？(EXPLAIN 查看 Using index)
• [ ] 大分頁是否改用延遲關聯或游標分頁？
• [ ] 慢查詢日誌是否定期分析？

三招組合拳，查詢效能立竿見影。