MySQL 優化不只靠加索引,更在於寫法細節與架構配合。本文精選十大高回報優化技巧,涵蓋查詢快取利用、EXPLAIN 分析、LIMIT 1、索引設計、JOIN 條件、RAND() 陷阱、SELECT * 危害、PROCEDURE ANALYSE、Prepared Statement、IP 整數儲存,附對比程式碼與驗證圖示。
-- 失效:CURDATE() 每次不同,快取無法命中
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
-- 生效:應用層產生固定日期字串
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
原理:查詢快取以完整 SQL 文字為 Key,任何動態函數(NOW()、RAND()、UUID()、CURDATE())導致 Key 每次不同,快取失效。
type(system > const > eq_ref > ref > range > index > ALL)、key(實際用到的索引)、rows(預估掃描行數 = 影響結果集)、Extra(Using index / Using where / Using temporary / Using filesort)。
-- 低效:掃描所有符合行
$r = mysql_query("SELECT * FROM user WHERE state='Alabama'");
if (mysql_num_rows($r) > 0) ...
-- 高效:找到第一筆即停止
$r = mysql_query("SELECT 1 FROM user WHERE state='Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) ...
WHERE、JOIN ON、ORDER BY、GROUP BY 涉及的欄位,若無索引必走全表掃描。複合索引遵循最左前綴原則。
-- 錯誤:users.state VARCHAR(20) utf8 vs companies.state CHAR(2) latin1
-- MySQL 無法使用索引,退化全表掃描
-- 正確:雙邊同類型、同字元集、同長度
ALTER TABLE companies MODIFY state VARCHAR(20) CHARSET utf8mb4;
-- 並雙邊加索引
-- 災難:全表產生隨機數 + filesort
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
-- 替代:應用層隨機主鍵
$cnt = (int)mysql_result(mysql_query("SELECT COUNT(*) FROM user"), 0);
$rand = mt_rand(0, $cnt-1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
SELECT username FROM user WHERE user_id=1。
SELECT * FROM tbl PROCEDURE ANALYSE();
-- 輸出建議:最佳資料類型、長度、是否 NOT NULL、ENUM 化等
需表內有實際資料才具參考價值。
$stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?");
$stmt->bind_param("s", $state);
$stmt->execute();
$stmt->bind_result($username);
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
-- 寫入
UPDATE users SET ip = INET_ATON('192.168.1.1') WHERE user_id=1;
-- 讀取
SELECT INET_NTOA(ip) FROM users WHERE user_id=1;
VARCHAR(15) 佔 15+ 位元組;UNSIGNED INT 固定 4 位元組,索引更小、比較更快、範圍查詢(BETWEEN)極高效。INET_ATON()/INET_NTOA() 轉換,PHP 有 ip2long()/long2ip()。