MySQL 學習筆記精華匯總：慢查詢、架構、鎖、優化工具與 Schema 設計

MySQL 學習筆記精華匯總：慢查詢、架構、鎖、優化工具與 Schema 設計

一、慢查詢日誌配置

[mysqld]
slow_query_log = 1
long_query_time = 5
log_queries_not_using_indexes = 1

記錄執行 >5s 或未用索引的查詢。

二、MySQL 日誌體系

1. Error Log：啟動/運行錯誤
2. Binlog：邏輯變更（DML），支援複制與 PITR
3. mysql-bin.index：Binlog 文件索引
4. Slow Query Log：文本格式，含時間、耗時、用戶
5. InnoDB Redo Log：物理頁變更，保證事務持久化

三、SQL Layer 核心模塊（17 大組件）

• 初始化、核心 API、網絡交互、Client/Server 協議
• 用戶模塊、訪問控制、連接管理/線程池
• Query 解析轉發、Query Cache、Query 優化器
• 表變更管理、表維護、系統狀態管理、表管理器
• 日誌記錄、復制模塊、存儲引擎接口

四、性能監控三板斧

SET profiling = 1;           # 開啟（部分版本不支援）
-- 執行 SQL --
SHOW PROFILES;               # 耗時總覽
SHOW PROFILE CPU, BLOCK IO FOR QUERY ;  # 細粒度分析

五、鎖機制全景

監控：SHOW STATUS LIKE '%table_locks%'（MyISAM）、SHOW STATUS LIKE '%Innodb_row_lock%'（InnoDB）

六、壓力測試工具 mysqlslap

# 自動生成 SQL
mysqlslap --concurrency=50,100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=write --engine=myisam --number-of-queries=200

# 指定腳本
mysqlslap --concurrency=25,50 --create-schema=test --query=/path/test.sql

七、索引失效八大場景

1. MyISAM 索引鍵長 > 1000 字節
2. BLOB/TEXT 僅支援前綴索引
3. 不支援函數索引
4. != / <> 無法用索引
5. 列參與函數運算（ABS(col)）
6. JOIN 條件類型不一致
7. LIKE '%abc' 通配符開頭
8. 非等值查詢無法用 Hash 索引

八、排序算法兩種模式

1. 單路：取排序鍵+行指針 → SortBuffer 排序 → 回表取其他列
2. 雙路：一次取所有需求列 → 非排序列存記憶體 → 排序鍵+指針排序 → 匹配合併返回

九、EXPLAIN 核心列解讀

重點關注：type（訪問類型：ALL/index/range/ref/eq_ref/const...）、key（實際用索引）、rows（預估掃描行）、Extra（Using index/temporary/filesort/where...）

十、Schema 設計三原則

1. 適度冗余換查詢速度
2. 大字段垂直拆分
3. 大表水平分片

時間類型選擇：TIMESTAMP 4 字節（1970~2038）、DATETIME 8 字節（1001~9999）。

十一、Binlog 優化關鍵參數

• sync_binlog=1：最安全，性能損耗 ~5 倍；=0 最快但 Crash 丟緩存事務
• binlog_cache_size / max_binlog_cache_size：多語句事務調大
• max_binlog_size=512M~1G：事務跨文件不切分

十二、Query Cache 負面影響

高併發下 Hash 開銷、頻繁失效導致命中率低、Result Set 重複緩存浪費記憶體。寫多讀少場景建議關閉。

十三、Thread Cache 命中率公式

(Connections - Threads_created) / Connections * 100%  → 目標 ≥ 90%

十四、Buffer 記憶體預算實例（8GB、500 連接、混合引擎）

系統 800MB + 線程獨佔 2GB + MyISAM Key Cache 1.5GB = InnoDB Buffer Pool 3.7GB。