MySQL 表存儲格式與查詢性能關係

1. 靜態表 (MyISAM Fixed Row Format)

• 無 VARCHAR/BLOB/TEXT 列，所有 CHAR 數值型列固定長度
• 極快：定位行 = 行號 × 固定長度，單次磁盤讀取
• 易緩存、崩潰恢復簡單 (記錄位置固定)
• 缺點：空間浪費 (CHAR 填充空格)、不支持變長列

2. 動態表 (MyISAM Dynamic Row Format)

• 含 VARCHAR/BLOB/TEXT 列
• 行頭記錄長度，更新變長可能產生碎片 (行分裂、行鏈接)
• OPTIMIZE TABLE 或 myisamchk -r 整理碎片
• 建議：將高頻變長列拆分到獨立表

3. 壓縮表 (myisampack)

• 只讀、極高壓縮比 (哈夫曼編碼、前後綴壓縮、Enum 化)
• 適用：歸檔數據、字典表、日志表
• 不支持 BLOB/TEXT、不支持寫入

4. 內存表 (MEMORY/HEAP)

• 100% 內存、Hash 索引 (默認)、固定長度行
• 適合：小/中型查找表、中間結果集、多表 JOIN 加速
• 限制：不支持 BLOB/TEXT、不支持範圍查詢、重啟數據丟失
• 優化技巧：CREATE TEMPORARY TABLE tmp ENGINE=MEMORY SELECT ... 做中間表

5. InnoDB 行格式 (Compact/Redundant/Dynamic/Compressed)

• Dynamic/Compressed (默認 5.7+)：長列溢出頁存儲，支持壓縮
• 聚簇索引 = 數據文件，主鍵查詢極快
• 二級索引葉子存主鍵值，回表開銷 = 2 次 B+Tree 查找

查詢優化核心技術

1. 臨時表優化聯接

-- 原查詢：三表聯接，大表驅動小表，慢
SELECT t1.a, t2.a, t3.a FROM t1, t2, t3
WHERE t1.a = t2.a AND t2.a = t3.a AND t2.c != 0;

-- 優化：小表先 JOIN 物化到內存表
CREATE TEMPORARY TABLE tmp ENGINE=MEMORY
SELECT t2.a AS a2, t3.a AS a3
FROM t2, t3 WHERE t2.a = t3.a AND t2.c != 0;

SELECT t1.a, tmp.a3 FROM t1, tmp WHERE t1.a = tmp.a2;
-- 後續複用 tmp 多次查詢，避免重複 JOIN

2. 索引優化策略

索引類型選擇

• B+Tree：通用、範圍、排序、前綴
• Hash：精確匹配 (MEMORY 表默認)
• Fulltext：全文搜索
• Spatial：GIS

字符串索引壓縮

• 前綴壓縮：共同前綴只存一次
• 空間壓縮：尾部空格壓縮
• VARCHAR 列索引體積遠小於 CHAR

索引使用場景

1. WHERE 條件列 (等值、範圍、IN)
2. JOIN ON 條件列
3. ORDER BY / GROUP BY 列 (避免 filesort)
4. MAX()/MIN() 列 (索引最左/最右)
5. 覆蓋索引：SELECT 列 ⊆ 索引列

3. WHERE 子句優化規則

-- 1. 常量傳播與簡化
WHERE (a=5 AND b=c)  →  WHERE a=5 AND b=c  (a=5 下推)
WHERE (a>5 AND a=5)  →  WHERE FALSE (直接返回空)

-- 2. 死碼消除
WHERE (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=100 AND 2=3)
→ WHERE b=5 OR b=6

-- 3. 常數表優先讀取
-- 0 行或 1 行表 (主鍵/唯一鍵查找) 先讀，當常數參與後續 JOIN

-- 4. 外連接優化
-- LEFT JOIN 內部優化：WHERE 條件下推、驅動表順序固定
-- 條件推到 WHERE 後，MySQL 仍能優化

-- 5. LIMIT 優化
-- LIMIT 10 → 掃描 10 行即停
-- LIMIT 100000, 10 → 掃描 100010 行 (優化見前文延遲關聯)
-- LIMIT 0 → 瞬時返回空集

4. JOIN 優化

-- Nested Loop Join (MySQL 唯一算法)
-- 優化點：
-- 1. 小表驅動大表 (驅動表 rows * 被驅動表索引查找成本)
-- 2. 被驅動表 JOIN 列有索引 (ref/eq_ref type)
-- 3. Join Buffer (Block Nested Loop, BNL) 批量匹配減少內層掃描
-- 4. 直連表順序：STRAIGHT_JOIN 強制

-- 實例：優化前 type=ALL (全表掃描)
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id WHERE t1.status = 1;
-- 優化：t1 加 idx_status，t2 加 idx_t1_id
-- 再優化：小結果集先查
SELECT * FROM (SELECT * FROM t1 WHERE status = 1 LIMIT 100) t1 
JOIN t2 ON t1.id = t2.t1_id;

5. 子查詢優化 (MySQL 8.0 質變)

-- 8.0 前：子查詢常物化為臨時表，性能差
-- 8.0+：半連接、反連接、物化、子查詢展開為 JOIN

-- 半連接優化 (IN/EXISTS 子查詢)
SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE cond);
-- → 重寫為 JOIN 或使用 Semi-Join (FirstMatch/LooseScan/DuplicateWeedout)

-- 關閉半連接 (調試用)
SET optimizer_switch='semijoin=off';

進階優化技巧

1. Generated Columns + 索引 (5.7+)

-- JSON 字段提取建索引
ALTER TABLE orders 
ADD COLUMN buyer_name VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(extra, '$.buyer.name'))) VIRTUAL,
ADD INDEX idx_buyer_name (buyer_name);

-- 函數表達式建索引
ALTER TABLE events
ADD COLUMN event_date DATE GENERATED ALWAYS AS (DATE(created_at)) VIRTUAL,
ADD INDEX idx_event_date (event_date);

2. 不可見索引 (8.0+)

-- 測試索引影響不刪除
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;
-- 測試後
ALTER TABLE t ALTER INDEX idx_name VISIBLE;

3. 直方圖統計信息 (8.0+)

-- 手動收集列統計信息 (非索引列也可)
ANALYZE TABLE t UPDATE HISTOGRAM ON col1, col2 WITH 100 BUCKETS;
-- 優化器利用直方圖估算範圍查詢選擇度，避免全表掃描誤判

4. CTE 與 窗口函數 (8.0+) 替代複雜子查詢

-- 排名去重：保留每組最新一條
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM user_actions
)
SELECT * FROM ranked WHERE rn = 1;

性能診斷工具鏈

# 1. 慢查詢分析
pt-query-digest slow.log --limit 20 --group-by fingerprint

# 2. 索引分析
pt-index-usage --user=root --password=xxx --database=dbname slow.log
pt-duplicate-key-checker --user=root --password=xxx --database=dbname

# 3. 表碎片分析
pt-online-schema-change --analyze-only D=db,t=table

# 4. 實時監控
# Prometheus + Grafana + mysqld_exporter
# 關鍵指標：Questions、Slow_queries、Innodb_buffer_pool_reads、Innodb_row_lock_waits、Threads_connected、Threads_running

# 5. 會話級分析
SHOW PROFILE FOR QUERY 1;  -- 需 profiling=1
SELECT * FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%xxx%';

避坑指南：十大查詢殺手

1. SELECT *：傳輸無用列、阻礙覆蓋索引、增加網絡/IO
2. 隱式轉換：WHERE varchar_col = 123 導致全表掃描
3. 函數包裹列：WHERE DATE(dt) = '...' 索引失效
4. LIKE '%...%'：首字符通配符，全文索引或搜索引擎解決
5. 大事務/長事務：Undo 膨脹、鎖持久、主從延遲、回滾久
6. 頻繁 DDL：鎖表、複製延遲、碎片產生 (用 pt-osc)
7. 未命中索引的排序：Using filesort 磁盤臨時表，加聯合索引覆蓋 ORDER BY
8. 臨時表物化：Using temporary 內存不足落盤，優化 GROUP BY/DISTINCT
9. 錯誤的連接順序：大表驅動小表，用 STRAIGHT_JOIN 或優化器提示修正
10. 忽略統計信息：ANALYZE TABLE 定期執行，直方圖輔助選擇度估算

優化心法：
測量是起點，EXPLAIN 是地圖，索引是橋樑，SQL 是車，配置是路，架構是終點。
不要在應用層修補數據庫設計缺陷，不要在配置層彌補 SQL 寫法缺陷，不要在架構層掩蓋單機性能缺陷。
每一次優化，都要有基線數據、預期收益、驗證方案、回滾預案。