一、效能檢測與瓶頸分析常用指令

• SHOW STATUS：狀態變數
• SHOW PROCESSLIST：當前執行線程、鎖狀態
• SHOW VARIABLES：系統參數
• OS 層：uptime, top, vmstat, iostat, free -m

二、關鍵效能指標計算

三、慢查詢配置與分析

1. 開啟慢查詢日誌

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1          # 秒級
long_query_time=0.1        # 5.6+ 支援毫秒級

或動態開啟：SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=0.1;（重啟失效）。

2. mysqldumpslow 分析工具

mysqldumpslow -s c -t 10 /var/log/mysql/slow.log   # 按次數 Top 10
mysqldumpslow -s t -t 10 -g "left join" slow.log   # 按耗時 Top 10 含 left join

參數：-s 排序(c/t/l/r)，-t Top N，-g 正則過濾。

3. EXPLAIN 執行計劃分析

關鍵欄位：

• type：system > const > eq_ref > ref > range > index > ALL（由好到壞）
• key：實際使用索引
• rows：預估掃描行數
• Extra：Using index（覆蓋索引）、Using where、Using temporary、Using filesort

4. PROFILING 精準剖析

SET profiling=1;
SELECT * FROM test;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;  -- 顯示每階段耗時：Opening tables, Sending data, ...
SET profiling=0;

四、索引與查詢優化

索引類型

• 普通、唯一、主鍵、全文（FULLTEXT）
• 單列 vs 多列（複合索引，最多 16 列，支援前綴）

最左前綴原則

複合索引 (a,b,c) 可支援 a、a,b、a,b,c 三種查詢條件。

導致索引失效的常見寫法

• WHERE col != ... / NOT IN / <>
• WHERE func(col) = ...（函數運算）
• LIKE '%xxx%'（前導通配符）
• 隱式類型轉換（字串比數字）

SQL 寫法微優化

• 單行結果加 LIMIT 1
• 禁用 SELECT *，明確列名
• 用 JOIN 替代子查詢（避免臨時表）
• 優先 NOT NULL、固定長度、ENUM/CHAR 代替 VARCHAR
• 大批量 DELETE/INSERT 分批執行

五、核心參數配置優化

連線相關

• max_connections：理想值 Max_used_connections / max_connections ≈ 85%
• back_log：預設 50，高併發調 128~512
• interactive_timeout：預設 28800，建議 7200

緩衝區相關（全域/會話）

• key_buffer_size（MyISAM 索引）：命中率 > 99.9%
• query_cache_size：碎片率 > 20% 執行 FLUSH QUERY CACHE 或調 query_cache_min_res_unit
• 會話級：read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size（建議 16M，注意 × 連線數）
• table_open_cache：1G 機器 128-256，4G 機器 256-384
• tmp_table_size / max_heap_table_size：64-256M，磁盤臨時表比例 < 25%
• thread_cache_size：預設 110，建議 80
• thread_concurrency：CPU 核心數 × 2

InnoDB 專屬參數

• innodb_buffer_pool_size：單實例機器物理記憶體 50%-80%（2G 機器建議 1G）
• innodb_flush_log_at_trx_commit：1(最安全) 2(折中) 0(最快，可能丟 1 秒數據)。批次寫入建議合併事務。
• innodb_log_buffer_size：4-8M
• innodb_additional_mem_pool_size：20M 起步
• innodb_thread_concurrency：2 × (CPU + 磁碟數)