MySQL 性能優化八大「妙手」：字段設計、連接替代子查詢、UNION、事務、鎖表、外鍵、索引與查詢重寫

1. 選取最適用字段屬性

• 字段長度足夠用多小用多小：CHAR(6) 存郵編優於 CHAR(255)，MEDIUMINT 優於 BIGINT。
• 盡量 NOT NULL：避免 NULL 比較開銷，索引統計更準確。
• 枚舉型優先：ENUM('male','female') 內部存數值，比較/排序極快。

2. 用 JOIN 替代子查詢

-- 子查詢（MySQL 4.1+ 支持）
DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

-- LEFT JOIN 等價，性能更優（尤其 salesinfo.CustomerID 有索引）
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL;

原理：JOIN 避免內存臨時表創建，直接利用索引關聯。

3. 用 UNION 替代手動臨時表

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 恢復

• ACID 保障多表操作一致性。
• 行鎖並發控制，防止髒讀/幻讀。

5. 鎖表優化特定場景

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

• 適用於「讀-算-寫」短流程，避免事務長持有鎖。
• 僅限單表、邏輯簡單、極短耗時，否則並發災難。

6. 外鍵維護關聯完整性

CREATE TABLE customerinfo (CustomerID INT NOT NULL, PRIMARY KEY(CustomerID)) 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 級聯刪除關聯記錄。
• 必須 ENGINE=INNODB，MyISAM 不支持外鍵。

7. 索引加速檢索

• 建索引列：JOIN、WHERE、ORDER BY 涉及的列。
• 避免低基數列索引（如 ENUM、性別、省份），反而拖慢寫入。
• 全文索引：FULLTEXT 僅限 MyISAM，大數據量先裸載再建索引更快。

8. 查詢語句重寫避開索引失效