MySQL 效能優化深度解析（五）：表快取運作、擴展優化、索引使用與 EXPLAIN 實戰

本文整理自 Monty 在 O'Reilly Open Source Convention 2000 大會演講第五部分，聚焦表快取機制、MySQL 獨有擴展優化、索引使用條件、EXPLAIN 執行計劃分析與 SHOW PROCESSLIST 監控實戰。

二十一、MySQL 表快取運作機制

• 每個 MyISAM 表開啟實例佔用一個索引檔 + 一個資料檔
• 同一查詢中多次引用同表，或多執行緒並發存取時：共享索引檔，但各開啟獨立資料檔實例
• 快取滿時臨時擴容，釋放表時關閉多餘實例
• 監控指標：SHOW STATUS LIKE 'Opened_tables' 數值過高暗示 table_cache 偏小，需調大

二十二、MySQL 獨有擴展與優化技巧

表與儲存層面

• 選用優化表類型：HEAP、MyISAM、BDB
• 使用優化欄位類型、盡可能採定長行
• 善用鎖定優先級：SELECT HIGH_PRIORITY、INSERT LOW_PRIORITY
• AUTO_INCREMENT、REPLACE INTO、INSERT DELAYED、LOAD DATA INFILE
• 多行 INSERT 批次寫入、SELECT INTO OUTFILE 導出

查詢優化策略

• LEFT JOIN、STRAIGHT_JOIN、LEFT JOIN ... IS NULL 模式
• ORDER BY 特定情況可用索引
• 覆蓋索引：僅查詢索引欄位時，只走索引樹
• 聯結優於子查詢（多數 SQL 伺服器通則）
• LIMIT 限制結果集：LIMIT 10,20、DELETE ... LIMIT 10
• col IN (常數列表) 高度優化

並發與進階功能

• GET_LOCK()/RELEASE_LOCK() 應用層鎖
• LOCK TABLES 表鎖
• INSERT 與 SELECT 可並發執行（MyISAM 特性）
• UDF 使用者定義函數動態載入
• 壓縮唯讀表、CREATE TEMPORARY TABLE、CREATE TABLE ... SELECT
• RAID 選項突破 2GB 檔案限制、DELAY_KEY_WRITE、複寫功能

二十三、MySQL 何時使用索引

使用索引的情況

• 條件含 >、>=、=、<、<=、<>、IN()、BETWEEN 且作用於索引前綴
• LIKE 'prefix%'（非萬用字元開頭）
• HEAP 表使用 = 比對所有鍵部分
• HEAP 表 ORDER BY 索引欄位

不使用索引的典型場景

• 條件未使用索引第一欄：WHERE key_part2 = 1
• LIKE '%pattern%' 以萬用字元開頭
• 查詢用索引 A，排序用索引 B：WHERE key_part1 = 1 ORDER BY key2

二十四、善用 EXPLAIN 分析執行計劃

原則：凡覺得查詢緩慢，必跑 EXPLAIN。

mysql> EXPLAIN SELECT t3.DateOfAction, t1.TransactionID
    -> FROM t1 JOIN t2 JOIN t3
    -> WHERE t2.ID = t1.TransactionID AND t3.ID = t2.GroupID
    -> ORDER BY t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table | type   | possible_keys | key     | key_len | ref              | rows | Extra                           |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1    | ALL    | NULL          | NULL    | NULL    | NULL             | 11   | Using temporary; Using filesort |
| t2    | ref    | ID            | ID      | 4       | t1.TransactionID | 13   |                                 |
| t3    | eq_ref | PRIMARY       | PRIMARY | 4       | t2.GroupID       | 1    |                                 |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+

關鍵判讀：type=ALL（全表掃描）與 type=range 為潛在效能問題信號；Extra 欄出現 Using temporary、Using filesort 需優化。

二十五、SHOW PROCESSLIST 即時監控

mysql> SHOW PROCESSLIST;
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User  | Host      | db | Command | Time | State        | Info                                |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6  | monty | localhost | bp | Query   | 15   | Sending data | select * from station,station as s1 |
| 8  | monty | localhost |    | Query   | 0    |              | show processlist                    |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+

發現長時間運行或異常查詢，可用 KILL <Id> 終止（在 mysql 或 mysqladmin 中均可）。