Monty 談 MySQL 優化（五）：表快取原理、擴展優化技巧、索引使用條件與 EXPLAIN/SHOW PROCESSLIST 實戰

二十一、MyISAM 表快取工作原理

每個 MyISAM 表開啟實例使用一個索引檔 + 一個資料檔。同一表被兩執行緒使用或單查詢用兩次時，共享索引檔但開啟獨立資料檔實例。快取滿且全在用時臨時擴大，下一個釋放的表會被關閉。檢查 Opened_tables 狀態值，過高則需增大 table_cache。

二十二、MySQL 擴展/優化技巧彙總

• 優化表類型（HEAP/MyISAM/BDB）
• 優化欄位類型
• 可能時用定長行
• 不同鎖定類型（SELECT HIGH_PRIORITY、INSERT LOW_PRIORITY）
• AUTO_INCREMENT
• REPLACE (REPLACE INTO table VALUES (...))
• INSERT DELAYED
• LOAD DATA INFILE / LOAD_FILE()
• 多行 INSERT 一次插入多行
• SELECT INTO OUTFILE
• LEFT JOIN, STRAIGHT JOIN
• LEFT JOIN ... IS NULL 優化
• ORDER BY 某情況下用鍵值
• 只查詢索引列時僅用索引樹解決
• 聯結通常比子查詢快
• LIMIT (SELECT ... LIMIT 10,20, DELETE ... LIMIT 10)
• foo IN (常數列表) 高度優化
• GET_LOCK()/RELEASE_LOCK()
• LOCK TABLES
• INSERT 和 SELECT 可同時運行
• UDF 函數可動態載入
• 壓縮唯讀表
• CREATE TEMPORARY TABLE
• CREATE TABLE ... SELECT
• RAID 選項 MyISAM 表突破 2GB 檔案限制
• DELAY_KEY_WRITE
• 複製功能

二十三、MySQL 何時使用索引

使用索引場景：

• WHERE key_part1 = 1 AND key_part2 > 10
• WHERE key_part1 = 1 AND key_part2 LIKE 'prefix%'
• WHERE key_part1 > 1 AND key_part1 < 90

不使用索引場景：

• HEAP 表未用 = 搜尋所有鍵值部分
• HEAP 表用 ORDER BY
• 未用鍵值第一部分：WHERE key_part2 = 1
• LIKE 以通配符開頭：WHERE key_part1 LIKE '%jani%'
• 搜尋一索引卻按另一索引排序：WHERE key_part1 = # 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    |                                 |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+

ALL 和 range 類型提示潛在問題。

二十五、善用 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                    |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+

在 mysql 或 mysqladmin 中用 KILL 終止失控執行緒。