MySQL 數據庫優化實戰總結：八大核心策略

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

• 表越小查詢越快 → 欄位長度設盡可能小（郵遞區號用 CHAR(6) 而非 VARCHAR(255)）。
• 整數優先用 MEDIUMINT 而非 BIGINT。
• 能用 NOT NULL 就不用 NULL（避免額外比較）。
• 枚舉值固定（省份、性別）用 ENUM，內部當數值處理，速度快於字串。

2. 用 JOIN 替代子查詢

子查詢需建立臨時表，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;

3. 用 UNION 替代手動臨時表

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

會話結束自動清理，保證整潔高效。

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 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。

7. 索引策略

• 建立在 JOIN、WHERE、ORDER BY 欄位上。
• 避開高重複值欄位（如 ENUM 類型的省份），反而拖慢寫入。
• 全文索引（FULLTEXT）僅支援 MyISAM，大量載入先建表後建索引更快。

8. 查詢語句優化細節

• 同類型欄位比較（INT vs BIGINT 會失效索引）。
• 索引欄避免函數操作：WHERE YEAR(dt) < 2001 → WHERE dt < '2001-01-01'。
• 數值運算移項：Amount/7 < 24 → Amount < 24*7。
• LIKE 'prefix%' 可用範圍查詢替代：name >= 'MySQL' AND name < 'MySQM'。
• 避免隱式類型轉換導致索引失效。