MySQL 準備語句效能實測：綁定變數能提升多少性能？

實驗背景與目的

Oracle 資料庫中綁定變數是效能調優標準動作，能減少硬解析、提升快取命中、防止 SQL 注入。MySQL 5.1+ 透過 mysqli_stmt_prepare 支援伺服器端準備語句，但社區長期爭論其效能增益是否明顯。本文透過實測量化 PHP+MySQL 環境下綁定變數的實際提升幅度。

測試環境

測試方法

• 場景：插入 10 筆資料，重複執行 10 次（共 100 筆 INSERT）。
• 指標：SHOW PROFILES 記錄的 Duration 總和（資料庫端耗時）+ Linux time 指令測量腳本整體執行時間。
• 對比組：
  1. 綁定變數：prepare() → bind_param() → 循環 execute()
  2. 非綁定：循環拼接完整 SQL → query()

測試程式碼核心片段

綁定變數版

$mysqli = new mysqli("localhost", "root", "123321", "test");
$mysqli->query("SET @@profiling=1");
$stmt = $mysqli->prepare("INSERT INTO t(name, sex) VALUES (?, ?)");
$stmt->bind_param("si", $name, $sex);

$data = [['andy',0],['mandy',1],['michael',0],['happy',1],['PHP',1],
         ['MySQL',1],['Linux',1],['Oracle',1],['unix',1],['cisco',1]];
foreach ($data as $row) {
    $name = $row[0]; $sex = $row[1];
    $stmt->execute();
}
$stmt->close();
$result = $mysqli->query("SHOW PROFILES");
// 統計 Duration 總和...

非綁定版

$mysqli = new mysqli("localhost", "root", "123321", "test");
$mysqli->query("SET @@profiling=1");
$sqls = [
  "INSERT INTO t(name, sex) VALUES ('andy',0)",
  "INSERT INTO t(name, sex) VALUES ('mandy',1)",
  // ... 10 條不同 SQL
];
foreach ($sqls as $sql) {
    $mysqli->query($sql);
}
$result = $mysqli->query("SHOW PROFILES");
// 統計 Duration 總和...

實測結果

資料庫端 Duration 總和（秒）

腳本整體執行時間

結論分析

量化效能提升

資料庫端平均耗時降低約 5.5%（(0.000112 - 0.0001058) / 0.000112）。

腳本整體執行時間（含 PHP 解析、網路往返）無顯著差異，受限於 time 指令毫秒級精度與系統噪音。

為何提升有限？

1. MySQL 準備語句機制：每次 execute() 仍需傳送參數、執行開啟表/鎖定/寫入/日誌/釋放完整流程，僅省略「SQL 解析、優化、執行計畫生成」階段。簡單 INSERT 此階段耗時極短。
2. 無查詢快取複用：MySQL 準備語句不共用執行計畫快取（各連線獨立），不像 Oracle 的 Library Cache 全域共享。
3. 測試規模過小：10 次循環無法放大解析階段優勢；複雜查詢（多表 Join、子查詢）解析耗時佔比大時優勢明顯。

綁定變數的核心價值（非效能）

• 安全性：徹底防止 SQL 注入，參數與程式碼分離，無需手工 real_escape_string。
• 規範性：強制參數化寫法，避免字串拼接導致的語法錯誤與編碼問題。
• 可維護性：SQL 模板與業務邏輯分離，利於代碼審查與重構。
• 大型系統累積效應：高併發下減少 CPU 解析開銷，5% 提升乘以百萬 QPS 即可觀。

最佳實踐建議

1. 預設使用準備語句：所有動態參數查詢（INSERT/UPDATE/DELETE/SELECT）優先用 prepare/bind/execute。
2. 複雜查詢效益大：報表、搜尋、多表關聯等解析耗時長的 SQL，綁定變數效能優勢顯著。
3. 避免過度準備：極簡單、極低頻、無參數的靜態 SQL 可直接 query()。
4. 注意 mysqli_insert_id() 時機：準備語句需在 execute() 後立即呼叫，INSERT DELAYED 不支援。
5. 升級 PHP 8 + MySQL 8：新版本優化準備語句快取機制，效能差距進一步縮小。

總結

MySQL 綁定變數在簡單 INSERT 場景下資料庫端提升約 5-6%，整體腳本耗時無感知差異。但其安全性、規範性、可維護性價值遠超微小效能增益，應作為預設開發規範而非效能優化手段。效能調優應優先關注索引設計、查詢重寫、架構分層、快取策略等高影響力手段。