MySQL 數據庫優化 15 大實戰技巧總結

MySQL 數據庫優化 15 大實戰技巧總結

從字段設計、SQL 改寫、索引策略、事務鎖控、架構拆分到大流量應對，匯總 15 條落地可執行的優化法則，附反面教材與驗證方法。

1. 選取最適用的字段屬性

• 盡量 NOT NULL：避免 NULL 判斷開銷、統計函數忽略 NULL、複合索引失效風險。
• 定長優於變長：CHAR(2) 存「省份、性別」優於 VARCHAR(20)；枚舉值用 ENUM 或 TINYINT。
• 整型優於字串：IP 存 INT UNSIGNED (INET_ATON/INET_NTOA)，金額存 DECIMAL 或分為單位 BIGINT。

2. 用 JOIN 替代子查詢

-- 低效：NOT IN 子查詢
DELETE FROM customerinfo WHERE customerid NOT IN (SELECT customerid FROM orderinfo);

-- 高效：LEFT JOIN + IS NULL
DELETE c FROM customerinfo c
LEFT JOIN orderinfo o ON c.customerid = o.customerid
WHERE o.customerid IS NULL;

MySQL 5.6+ 對子查詢優化較好，但 JOIN 穩定性更強，尤其大數據量。

3. 用 UNION 替代手工臨時表

SELECT name FROM nametest
UNION
SELECT username FROM nametest2;

UNION 自動去重，UNION ALL 保留重複、效能更高。

4. 事務處理保證數據一致性

START TRANSACTION;
INSERT INTO customerinfo (name) VALUES ('A');
UPDATE orderinfo SET status=1 WHERE customer_id=LAST_INSERT_ID();
COMMIT;

異常時 ROLLBACK。注意：長事務鎖定資源久，盡量縮短事務範圍。

5. 表鎖優化併發控制

LOCK TABLES customerinfo READ, orderinfo WRITE;
SELECT customerid FROM customerinfo WHERE id=1;
UPDATE orderinfo SET title='X' WHERE customerid=1;
UNLOCK TABLES;

僅限 MyISAM/MEMORY 或 InnoDB 顯式鎖表場景；InnoDB 行鎖通常無需手動鎖表。

6. 外鍵約束替代應用層檢查

CREATE TABLE customerinfo (customerid INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE orderinfo (
  orderid INT, customerid INT,
  PRIMARY KEY (customerid, orderid),
  FOREIGN KEY (customerid) REFERENCES customerinfo(customerid) ON DELETE CASCADE
) ENGINE=InnoDB;

注意：高併發下外鍵會增加鎖爭用，大廠常在應用層保證完整性、資料庫層不建外鍵。

7. 建立索引三大類

8. 優化查詢語句 (讓索引生效)

-- 惡例：函數導致索引失效
SELECT * FROM orders WHERE YEAR(order_date) < 2008;
-- 良例：範圍查詢走索引
SELECT * FROM orders WHERE order_date < '2008-01-01';

-- 惡例：運算在列上
SELECT * FROM orders WHERE amount/7 < 24;
-- 良例：常數運算移至右側
SELECT * FROM orders WHERE amount < 24*7;

-- 模糊查詢 % 開頭不走索引
SELECT * FROM orders WHERE title LIKE '%good%';
-- 改用全文索引或業務妥協

9. 常用函數速查

• NOW() 當前日期時間
• DATE() 日期格式化
• TIMESTAMP 列 DEFAULT CURRENT_TIMESTAMP 自動寫入
• mysql_num_rows() 結果集行數 (PHP)

10. 主流存儲引擎 (10+ 種)

MyISAM、InnoDB、Memory、Archive、CSV、Blackhole、Federated、Merge、NDB、RocksDB (MyRocks) 等。生產 99% 用 InnoDB。

11. 三大優化法則 (除硬件)

1. 服務配置優化：調大 key_buffer_size、innodb_buffer_pool_size、query_cache_size (5.7 後查詢快取已廢)。
2. 索引優化：高頻查詢欄位建索引、覆蓋索引、避免過度索引。
3. SQL 優化：減少 DISTINCT、GROUP BY、多表 JOIN、SELECT *。

12. 五條核心優化口訣

1. 字段屬性最適用 (NOT NULL、定長、整型)
2. JOIN 代替子查詢
3. UNION 代替臨時表
4. 少用 LIKE %通配符%
5. 善用事務與外鍵 (視業務權衡)

13. 大流量網站應對策略

1. 硬件評估 (CPU、內存、SSD、網卡)
2. SQL/索引/架構優化 (讀寫分離、分庫分表、緩存)
3. 防盜鏈、限流大文件下載
4. 多機房/多節點分流 (CDN、LVS、DNS 輪詢)
5. 流量分析 (Prometheus + Grafana、慢查詢日誌)

14. 範式與反範式權衡

• 1NF：列不可再分
• 2NF：非鍵列完全依賴主鍵
• 3NF：非鍵列不傳遞依賴主鍵 (生產常到 3NF)
• 反範式 (冗余列) 換查詢性能，需同步機制保證一致性。

15. PHP 連接資源類型對照

優化是持續過程：建立基線 → 找瓶頸 (慢查詢、EXPLAIN、SHOW STATUS) → 單點突破 → 驗證回歸 → 迭代。切忌為優化而優化，業務價值優先。