MySQL系列文章

深入解析MySQL執行計劃中最關鍵的指標之一，助你快速定位索引優化點，提升查詢性能！

一、key_len：索引使用的精準標尺

在MySQL執行計劃中，key_len表示查詢實際使用索引的字節長度。這個指標是索引優化的核心，它能揭示：

• 複合索引使用深度：顯示使用了複合索引的前幾列
• 索引利用效率：值越大，索引利用率越高
• 索引失效檢測：NULL值表示索引未被使用
• 數據類型成本：不同數據類型在索引中的開銷

二、key_len計算的核心規則（重點掌握！）

1. 基礎計算規則

key_len = 數據類型基礎長度 + NULL標記(1字節) + 變長類型額外開銷（2字節）

2. 常用數據類型計算表（utf8mb4環境）

核心要點：

1. VARCHAR類型在索引中固定增加2字節長度前綴
   （實際行存儲時規則不一致：≤255字符+1字節，>255字符+2字節）
2. 字符集直接影響長度：utf8mb4=4字節/字符，latin1=1字節/字符
3. NULL列增加1字節開銷

三、key_len實戰解析：從案例學優化

案例1：複合索引使用深度判斷

-- 表結構
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,  -- key_len:50×4+2=202
  age TINYINT NOT NULL,        -- key_len:1
  email VARCHAR(100) NOT NULL, -- key_len:100×4+2=402
  INDEX idx_profile (name, age, email)
) CHARSET=utf8mb4;

-- 場景1：僅使用name列
EXPLAIN SELECT * FROM users WHERE name = 'John';
-- key_len = 202（複合索引第一列）

-- 場景2：使用前兩列
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 30;
-- key_len = 203（202+1）

-- 場景3：使用所有列
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 30 AND email = 'john@example.com';
-- key_len = 605（202+1+402）

案例2：字符集對key_len的影響

-- latin1字符集對比
CREATE TABLE logs_latin1 (
  message VARCHAR(100) NOT NULL
) CHARSET=latin1;

CREATE TABLE logs_utf8mb4 (
  message VARCHAR(100) NOT NULL
) CHARSET=utf8mb4;

EXPLAIN SELECT * FROM logs_latin1 WHERE message = 'error';
-- key_len = 102 (100×1 + 2)

EXPLAIN SELECT * FROM logs_utf8mb4 WHERE message = 'error';
-- key_len = 402 (100×4 + 2)

案例3：NULL值的隱藏成本

-- 允許NULL的列
ALTER TABLE users MODIFY age TINYINT NULL;

-- 相同查詢條件
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 30;
-- key_len = 204（202+1+1，比非NULL多1字節）

四、key_len揭示的三大優化機會

1. 複合索引優化（核心！）

當key_len < 索引總長度時：

• 問題：索引未充分利用
• 解決方案：

  -- 1. 補充缺失查詢條件
  SELECT ... WHERE col1=1 AND col2=2 AND col3=3
  
  -- 2. 重建索引（高頻查詢列前置）
  ALTER TABLE orders DROP INDEX idx_old;
  ALTER TABLE orders ADD INDEX idx_new (status, user_id, created_at);
  
  -- 3. 使用覆蓋索引
  SELECT indexed_columns FROM table WHERE ...
  

2. VARCHAR列優化策略

-- 方案1：前綴索引（減少長度）
ALTER TABLE products ADD INDEX (description(20)); 
-- key_len從402降為82（VARCHAR(100)→20×4+2）

3. 消除NULL存儲開銷

-- 優化前（允許NULL）
ALTER TABLE users MODIFY phone VARCHAR(20) NULL;
-- key_len=20×4+2+1=83

-- 優化後（禁止NULL）
ALTER TABLE users 
  MODIFY phone VARCHAR(20) NOT NULL DEFAULT '';
-- key_len=82（節省1字節/行）

五、高級診斷技巧

1. EXPLAIN FORMAT=JSON（推薦）

EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE name='Lisa';

/* 輸出片段 */
{
  "query_block": {
    "table": {
      "key_length": 202,
      "used_key_parts": ["name"],
      // ...其他信息
    }
  }
}

2. 性能優化檢查清單

1. 檢查key_len是否接近索引長度
2. 確認複合索引是否滿足最左前綴原則
3. 分析VARCHAR列長度是否合理
4. 檢查是否有不必要的NULL列
5. 對比不同字符集下的索引大小

六、總結：key_len優化四原則

1. 追求最大key_len：值越接近索引總長度，索引利用越充分
2. 警惕NULL開銷：每允許一個NULL列，key_len增加1字節
3. VARCHAR成本控制：長文本字段優先考慮前綴索引或哈希
4. 最左前綴原則：確保查詢條件從複合索引最左側開始

終極技巧：當發現key_len顯著小於索引長度時，立即檢查：

• 是否缺少必要查詢條件？
• 索引列順序是否合理？
• 是否存在數據類型轉換？
• 字符集選擇是否合適？