萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 資料庫效能優化實戰技巧

前言

MySQL 能夠輕鬆支撐大量資料的存取,但資料表越小,查詢速度越快。建立資料表時,應盡可能縮減欄位寬度,選擇最精簡的資料型別,並善用 NOT NULL 與 ENUM 等特性,從源頭提升效能。

1. 選用最適合的欄位屬性

  • 縮減欄位長度: 例如郵遞區號僅需 CHAR(6),無需 CHAR(255) 或 VARCHAR;整數欄位優先考慮 MEDIUMINT 而非 BIGINT。
  • 設定 NOT NULL: 可能的情況下將欄位設為 NOT NULL,查詢時避免比較 NULL 值,提升速度。
  • 善用 ENUM 型別: 如「省份」、「性別」等固定值欄位,使用 ENUM。MySQL 將其視為數值處理,速度遠快於文字型別。

2. 以 JOIN 取代子查詢

MySQL 4.1 起支援子查詢,雖然邏輯清晰,但某些情況下 JOIN 效率更高。例如查找無訂單的客戶:

-- 子查詢寫法
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

-- JOIN 寫法(配合索引更快)
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL;

JOIN 避免了在記憶體中建立臨時表,尤其在關聯欄位有索引時優勢明顯。

3. 使用 UNION 替代手動臨時表

MySQL 4.0 支援 UNION,可將多個 SELECT 合併為單一查詢,會話結束時自動清理臨時表。使用時需確保所有 SELECT 欄位數一致:

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product;

4. 事務處理保障資料一致性

當一連串 SQL 需全部成功或全部失敗時,使用事務。以 BEGIN 開啟,COMMIT 提交,錯誤時 ROLLBACK 回滾。事務亦能透過鎖定機制,在多用戶並發時提供安全存取。

BEGIN;
INSERT INTO salesinfo SET CustomerID = 14;
UPDATE inventory SET Quantity = 11 WHERE item = 'book';
COMMIT;

5. 鎖定表作為輕量替代方案

事務的獨占性可能影響大型系統效能。對於簡單邏輯,可用 LOCK TABLES ... WRITE 手動鎖定,操作完成後 UNLOCK TABLES 釋放,減少鎖定範圍與時間。

6. 外鍵維護關聯完整性

鎖表只能保證單表完整性,外鍵可確保跨表關聯。建立外鍵需使用 InnoDB 引擎,並可設定 ON DELETE CASCADE 自動級聯刪除關聯資料。

7. 索引策略

  • 索引建立在 JOIN、WHERE、ORDER BY 涉及的欄位上。
  • 避免對重複值極多的欄位(如 ENUM 的「省份」)建立索引,反而拖慢寫入。
  • MySQL 3.23.23+ 支援全文索引(FULLTEXT),僅限 MyISAM 表。

8. 查詢語句優化細節

  • 同類型比較: 避免 INT 與 BIGINT 混用;CHAR 與 VARCHAR 長度相同時可比較。
  • 避免索引欄位用函數: WHERE YEAR(OrderDate) < 2001 會失效索引,改為 WHERE OrderDate < '2001-01-01'。
  • 移動運算到右側: Amount/7 < 24 改為 Amount < 24*7。
  • 慎用 LIKE 通配符: LIKE 'MySQL%' 可用索引,LIKE '%MySQL%' 會全表掃描。可改用範圍查詢 name >= 'MySQL' AND name < 'MySQM' 替代。
copyright © 萬盛學電腦網 all rights reserved