MySQL 表優化全攻略：引擎選型、鎖機制、碎片整理與調度策略

一、選對表類型：性能基石

二、HEAP 表加速多表聯結實戰

-- 原查詢：三表聯結慢
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3 
WHERE tab1.a=tab2.a AND tab2.a=tab3.a AND tab2.c!=0;

-- 優化：將 tab2+tab3 聯結結果物化到 HEAP 表
CREATE TEMPORARY TABLE test TYPE=HEAP 
SELECT tab2.a AS a2, tab3.a AS a3 FROM tab2, tab3 
WHERE tab2.a=tab3.a AND tab2.c=0;

SELECT tab1.a, test.a3 FROM tab1, test WHERE tab1.a=test.a2;

三、表數量與打開表緩存

• 同庫表過多 → 目錄文件多 → 打開/關閉/創建變慢
• 增大 table_cache 減少緩存抖動
• mysqladmin status 中 Open_tables > 實際表數屬正常（多線程並發各自打開）

四、表級鎖與調度策略深度解析

默認策略

1. 寫請求按到達順序
2. 寫優先於讀

經典飢餓場景

長 SELECT → UPDATE 等待 → 新 SELECT 被 UPDATE 阻塞 → 雙重等待。

解決方案工具箱

• --low-priority-updates 啟動參數 / SET SQL_LOW_PRIORITY_UPDATES=1
• 語句級 LOW_PRIORITY (INSERT/UPDATE/DELETE) / HIGH_PRIORITY (SELECT)
• max_write_lock_count 閾值觸發讀插隊
• MyISAM 並發插入（無刪除洞時）
• INSERT DELAYED 隊列化寫入
• DELETE ... LIMIT 分批刪除

五、碎片整理與優化時機

易碎片操作：頻繁 VARCHAR 更新、大量 BLOB/TEXT 刪改、大批量刪除。

優化命令：

• SQL：OPTIMIZE TABLE tbl_name
• 工具：myisamchk -r / isamchk -r

黃金原則：低峰期執行、確保無業務訪問、備份後操作。