首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式碼之家 >> 數據庫知識 >  >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 十大效能優化技巧:從查詢快取到 IP 儲存最佳實踐

     編輯:MySQL 綜合教學

MySQL 優化不只靠加索引,更在於寫法細節與架構配合。本文精選十大高回報優化技巧,涵蓋查詢快取利用、EXPLAIN 分析、LIMIT 1、索引設計、JOIN 條件、RAND() 陷阱、SELECT * 危害、PROCEDURE ANALYSE、Prepared Statement、IP 整數儲存,附對比程式碼與驗證圖示。

1. 善用查詢快取:避免非確定性函數


-- 失效: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 每次不同,快取失效。

2. EXPLAIN 看透執行計畫

  • 關注 type(system > const > eq_ref > ref > range > index > ALL)、key(實際用到的索引)、rows(預估掃描行數 = 影響結果集)、Extra(Using index / Using where / Using temporary / Using filesort)。
  • phpMyAdmin / Workbench 視覺化呈現,加上索引前後對比一目瞭然。

3. LIMIT 1 取唯一存在性檢查


-- 低效:掃描所有符合行
$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) ...

4. 搜尋欄位必加索引

WHERE、JOIN ON、ORDER BY、GROUP BY 涉及的欄位,若無索引必走全表掃描。複合索引遵循最左前綴原則。

5. JOIN 條件雙邊同類型、同字元集、同編碼


-- 錯誤:users.state VARCHAR(20) utf8  vs  companies.state CHAR(2) latin1
-- MySQL 無法使用索引,退化全表掃描

-- 正確:雙邊同類型、同字元集、同長度
ALTER TABLE companies MODIFY state VARCHAR(20) CHARSET utf8mb4;
-- 並雙邊加索引

6. 禁用 ORDER BY RAND()


-- 災難:全表產生隨機數 + 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");

7. 避免 SELECT *

  • 傳輸不必要欄位增加網路延遲、記憶體佔用、磁碟 IO。
  • 破壞覆蓋索引(Index Only Scan)優化機會。
  • 習慣明確列出需要欄位:SELECT username FROM user WHERE user_id=1。

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


SELECT * FROM tbl PROCEDURE ANALYSE();
-- 輸出建議:最佳資料類型、長度、是否 NOT NULL、ENUM 化等

需表內有實際資料才具參考價值。

9. Prepared Statement:效能與安全雙贏


$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();
  • 預編譯一次,多次執行省去解析優化開銷。
  • 參數綁定原生防 SQL 注入,免手動跳脫。

10. IP 位址存無符號整數(UNSIGNED INT)


-- 寫入
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)極高效。
  • MySQL 內建 INET_ATON()/INET_NTOA() 轉換,PHP 有 ip2long()/long2ip()。

總結檢查清單

  1. 查詢是否含非確定性函數殺快取?
  2. 核心 SQL 是否跑過 EXPLAIN?
  3. 存在性檢查是否用 LIMIT 1?
  4. WHERE/JOIN/ORDER BY 欄位是否有索引?
  5. JOIN 雙邊類型字元集是否一致?
  6. 是否誤用 ORDER BY RAND()?
  7. 是否寫 SELECT *?
  8. 是否嘗試 PROCEDURE ANALYSE()?
  9. 核心寫入/查詢是否用 Prepared Statement?
  10. IP/整數型資料是否用最小足夠類型?
  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式碼之家 All Rights Reserved