您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

資料庫技巧：MySQL 十大優化技巧 (下) — 索引一致性、避免 RAND()、拒絕 SELECT *、PROCEDURE ANALYSE、Prepared Statement、IP 儲存優化

編輯：MySQL 資料庫相關知識

接續上篇 (索引、EXPLAIN、LIMIT 1)，本文涵蓋第 5-10 招優化實戰技巧。

5. 確保關聯欄位索引型別一致

多表 JOIN 時，關聯欄位兩邊必須同樣建立索引，且資料型別、字元編碼完全一致。否則 MySQL 無法使用索引，退化為全表掃描。

-- 錯誤範例：users.state (VARCHAR) JOIN companies.state (INT) 或編碼不同
SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id;

6. 禁止使用 ORDER BY RAND()

ORDER BY RAND() 會為每行產生隨機數並排序，極度消耗 CPU。替代方案：

-- 兩步法：先取總數，再隨機 OFFSET
SELECT COUNT(*) FROM user; -- 得到 $total
$rand = mt_rand(0, $total - 1);
SELECT username FROM user LIMIT $rand, 1;

7. 盡量避免 SELECT *

只選需要的欄位，減少磁碟 I/O、網路傳輸、記憶體佔用。良好習慣：

-- 不佳
SELECT * FROM user WHERE user_id = 1;
-- 推薦
SELECT username FROM user WHERE user_id = 1;

8. 善用 PROCEDURE ANALYSE() 獲取結構優化建議

執行 SELECT ... PROCEDURE ANALYSE()，MySQL 依現有資料分析欄位型別、長度、基數，建議最佳型別 (如 ENUM 替代 VARCHAR、INT 替代 BIGINT 等)。需表中已有實際資料。

PROCEDURE ANALYSE 輸出範例

9. 採用 Prepared Statement (預備陳述式)

雙重優勢：效能 (單次解析、多次執行) + 安全 (參數綁定預設防 SQL 注入)。

if ($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();
}

10. IP 位址儲存為 UNSIGNED INT

VARCHAR(15) 佔 15 位元組，改用 INT UNSIGNED 僅 4 位元組、固定長度、利於範圍查詢。MySQL 內建函數互轉：

INSERT: INET_ATON('192.168.1.1')  -- 轉整數存入
SELECT: INET_NTOA(ip_column)      -- 讀取轉回點分十進制

務必定義為 UNSIGNED INT，因 IPv4 使用 32 位無號整數。

十大優化技巧總結：索引策略、EXPLAIN 分析、LIMIT 1、型別一致、拒 RAND()、拒 SELECT *、PROCEDURE ANALYSE、Prepared Statement、IP 整數化。原文：https://www.52cfml.com/post/MySQL_optimize.html