MySQL 數據庫性能優化八大黃金法則：字段設計、查詢重寫、索引策略與事務控制

1. 選取最適用字段屬性：小而精

• 定長優於變長：CHAR(6) 存郵編優於 VARCHAR(255)；固定長度列用 CHAR。
• 整型精打細算：TINYINT(1B) > SMALLINT(2B) > MEDIUMINT(3B) > INT(4B) > BIGINT(8B)。1600 萬行表用 MEDIUMINT 自增 ID 省 10MB+。
• NOT NULL 默認加上：避免 NULL 比較開銷，節省位圖空間。
• 枚舉代替字符串：ENUM('male','female') 視為數值處理，比 VARCHAR 快，僅 1-2 字節。

2. 用 JOIN 替代子查詢

# 子查詢（建立臨時表，無索引）
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

# LEFT JOIN（利用 salesinfo.CustomerID 索引，無臨時表）
SELECT c.* FROM customerinfo c
LEFT JOIN salesinfo s ON c.CustomerID = s.CustomerID
WHERE s.CustomerID IS NULL;

原理：MySQL 子查詢物化到無索引臨時表；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 恢復初始狀態

雙重作用：原子性（全成或全敗）+ 隔離性（鎖定資源防併發干擾）。

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 PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE salesinfo (
  SalesID INT, CustomerID INT,
  PRIMARY KEY (CustomerID, SalesID),
  FOREIGN KEY (CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE
) ENGINE=InnoDB;

關鍵：ON DELETE CASCADE 級聯刪除；必須用 InnoDB（MyISAM 不支持外鍵）。