MySQL 效能優化八大實戰技巧：欄位設計、索引、查詢重構與鎖策略

MySQL 效能優化八大關鍵技術：從 schema 設計到查詢重寫

1. 選擇最適欄位屬性——越小越快

• 表越小，查詢越快。郵遞區號用 CHAR(6) 而非 CHAR(255) 或 VARCHAR
• 整數優先用 MEDIUMINT 而非 BIGINT
• 可行時設定 NOT NULL，避免 NULL 比較開銷
• 低基數欄位（省份、性別）用 ENUM，內部以數值處理，速度遠快於字串

2. 用 JOIN 取代子查詢

MySQL 4.1+ 支援子查詢，但 JOIN 效率通常更高（避免建立暫存表）。

-- 子查詢：找無訂單客戶
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

-- JOIN 改寫（配合 salesinfo.CustomerID 索引更快）
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL;

3. 用 UNION 取代手動暫存表

MySQL 4.0+ 支援 UNION 合併多個 SELECT，會話結束自動清理暫存表。

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

注意：所有 SELECT 欄位數必須相同。

4. 善用交易保證一致性

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

• 全成功或全失敗（ROLLBACK），維護資料完整性
• 並發存取時提供鎖定隔離，防止干擾

5. 表鎖定替代交易提升並發效能

大型系統交易鎖定全表影響並發，可用表鎖定細粒度控制：

LOCK TABLE inventory WRITE;
SELECT Quantity FROM inventory WHERE Item='book';
-- 計算後
UPDATE inventory SET Quantity=11 WHERE Item='book';
UNLOCK TABLES;

WRITE 鎖防止其他寫入，讀取仍可進行（視隔離等級）。

6. 外鍵維護關聯完整性

CREATE TABLE customerinfo (
  CustomerID INT NOT NULL PRIMARY KEY
) ENGINE=INNODB;

CREATE TABLE salesinfo (
  SalesID INT NOT NULL,
  CustomerID INT NOT NULL,
  PRIMARY KEY (CustomerID, SalesID),
  FOREIGN KEY (CustomerID) REFERENCES customerinfo(CustomerID)
    ON DELETE CASCADE
) ENGINE=INNODB;

• 確保每筆銷售記錄指向存在的客戶
• ON DELETE CASCADE 級聯刪除相關記錄
• **必須使用 InnoDB 引擎**（ENGINE=INNODB）

7. 索引策略：建在對的欄位上

• 建立於 JOIN、WHERE、ORDER BY 使用的欄位
• **避免** 高重複值欄位（如 ENUM 的省份）建索引，效益低甚至負面
• MySQL 3.23.23+ 支援全文索引（FULLTEXT），僅限 MyISAM
• 大量匯入資料時，**先匯入再建索引** 比先建索引再匯入快得多

8. 查詢語句優化細節

總結： 優化是系統工程，從 schema 設計、索引規劃、查詢重寫、鎖策略選擇層層把關，才能榨乾 MySQL 效能。

1. 上一篇文章： MySQL 防駭客入侵實戰：從連線加密到權限收斂
2. 下一篇文章： MySQL 中文亂碼徹底解決：my.cnf 設定、驗證與 JDBC 連線參數

MySQL 資料庫相關知識

MySQL 優化實戰：將 DELETE 子查詢重寫為多表 JOIN 大幅提升效能

問題背景 開發人員常遇到一個令人困惑的現象：同一條 SQL，

使用 SQL Server Publishing Wizard 遷移數據到遠程服務器 (上)：導出腳本生成與所有者問題分析

SQL Server 數據庫發布精靈遷移實戰 (一)：工具獲

使用 phpMyAdmin 將 Access 資料匯入 MySQL 兩大法

Access 資料遷移至 MySQL 主要有兩條路徑：圖形化

MySQL 5.0 Windows 圖文安裝教學 (含設定精靈完整步驟)

安裝前準備 下載：mysql-5.0.27-win32.z