MySQL 性能優化 21 條軍規：從查詢緩存到存儲引擎選型全覽

5. JOIN 欄位同類型、同字符集、建索引

DECIMAL 不能與 INT JOIN 走索引；utf8 與 latin1 JOIN 會隱含轉換導致索引失效。

6. 杜絕 ORDER BY RAND()

-- ❌ 全表排序，災難性效能
SELECT * FROM table ORDER BY RAND() LIMIT 1;

-- ✅ 方案一：主鍵範圍隨機
SELECT * FROM table 
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM table))) 
LIMIT 1;

-- ✅ 方案二：應用層隨機 ID 查詢

7. 避免 SELECT *

只取需要的欄位：減少網絡傳輸、內存佔用、覆蓋索引命中率提升。

8. 每張表必有單調遞增整數主鍵

BIGINT UNSIGNED AUTO_INCREMENT 或 INT UNSIGNED。業務主鍵（如 email）建唯一索引，不做主鍵。例外：關聯表的組合主鍵（外鍵組合）。

9. 枚舉值用 ENUM 代替 VARCHAR

內部存儲為 TINYINT，極致緊湊。適用於：性別、狀態、類型、部門等固定集合。

10. 用 PROCEDURE ANALYSE() 獲取結構優化建議

phpMyAdmin 「Propose table structure」基於此。數據量大時建議更準確。

11. 能 NOT NULL 就 NOT NULL

NULL 需額外位圖標記，比較邏輯複雜（IS NULL/IS NOT NULL），索引統計不準。Oracle 中空字符串 = NULL，MySQL 不同。

12. 採用 Prepared Statements

• 防 SQL 注入（參數綁定）
• 重複執行只解析一次
• 二進制協議傳輸效率高
• 5.7+ 支援查詢緩存

PHP 使用 mysqli 或 PDO。

13. 大結果集用無緩衝查詢

-- mysql_unbuffered_query() / mysqli_use_result()
-- 優點：記憶體佔用低，首行即可處理
-- 缺點：必須讀完或 free_result 才能執行下一查詢；無法用 num_rows/data_seek

14. IP 地址存 UNSIGNED INT

-- 存儲
INSERT INTO t(ip) VALUES (INET_ATON('192.168.1.1'));  -- 3232235777

-- 查詢
SELECT INET_NTOA(ip) FROM t WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

-- PHP
$ipLong = ip2long('192.168.1.1');
$ipStr = long2ip($ipLong);

二、表結構優化

15. 固定長度表更快

無 VARCHAR/TEXT/BLOB 的表為 Static 格式，偏移量計算 O(1)，掃描快、緩存友好。副作用：空間浪費。可用「垂直分割」分離變長欄位。

16. 垂直分割

• 低頻大欄位（地址、簡介）移至擴展表
• 高頻更新欄位（last_login）移出，避免緩存失效
• 慎用 JOIN 關聯，否則得不償失

17. 大批量 DELETE/INSERT 分批執行

-- ❌ 鎖表 30 秒，網站掛掉
DELETE FROM logs WHERE created < '2023-01-01';

-- ✅ 分批刪除，每批 1000 行
WHILE (SELECT COUNT(*) FROM logs WHERE created < '2023-01-01') > 0 DO
    DELETE FROM logs WHERE created < '2023-01-01' LIMIT 1000;
    DO SLEEP(0.1);  -- 讓出 CPU
END WHILE;

18. 越小的列越快

TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT；DATE < DATETIME。預留擴展空間但不濫用。

三、引擎與架構選型

19. 選對存儲引擎

20. 引入 ORM（如 Doctrine/Hibernate/MyBatis）

• 延遲加載減少無效查詢
• 批量操作自動合併事務
• 警惕 N+1 問題：用 JOIN FETCH/EntityGraph/resultMap 解決

21. 謹慎用「永久連線」

mysql_pconnect() / PDO::ATTR_PERSISTENT 理論減少連線開銷，實際易導致：連線泄漏、閒置連線佔用、Max connections 耗盡、Apache 多進程模型下連線池失控。建議：應用層連線池（HikariCP/Druid）替代。

附錄：強制/忽略索引語法

-- 強制使用索引
SELECT * FROM t FORCE INDEX (idx_name) WHERE name='abc';
SELECT * FROM t FORCE INDEX (PRIMARY, idx_name) WHERE id>100;

-- 忽略索引
SELECT * FROM t IGNORE INDEX (idx_name) WHERE name='abc';
SELECT * FROM t IGNORE INDEX (PRIMARY) WHERE id>100;