MySQL 索引詳細介紹：原理、設計原則、類型對比與使用機制

索引基礎認知

• 所有列類型可被索引
• 每表至少 16 個索引，總長度 ≥ 256 字節（引擎通常更高）
• MyISAM/InnoDB 前綴索引 ≤ 1000 字節 (5.1)
• FULLTEXT 僅 MyISAM 支持 (5.6 前)，僅 CHAR/VARCHAR/TEXT，全列索引
• 空間索引：MyISAM，R-Tree
• MEMORY 引擎默認 HASH，可選 BTREE

索引設計六大原則

1. 索引搜索列，非選擇列

WHERE/JOIN/ORDER BY 涉及的列優先，SELECT 列表列次之。

2. 高選擇性優先

唯一值密度高的列（如身份證、手機號）索引效果最好；低選擇性（性別、狀態、布爾值）索引幾乎無效。

3. 短索引原則

• 字符串列指定前綴長度：INDEX idx_name (name(10))
• 節省空間、減少 IO、提高緩存命中率
• 前綴長度覆蓋 90%+ 唯一值即可

4. 最左前綴原則

複合索引 (a,b,c) 等價於擁有 (a)、(a,b)、(a,b,c) 三個索引。查詢必須從最左列開始匹配。

5. 避免過度索引

• 每個索引佔用磁盤空間
• 寫入時維護所有索引（增刪改變慢）
• 優化器評估索引增加 CPU 開銷
• 冗余索引誤導優化器選擇錯誤計劃

6. 匹配比較類型

索引支持：=, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'prefix%'
不支持：LIKE '%suffix', STRCMP(), 函數包裝列

B-Tree vs Hash 索引對比

MySQL 如何使用索引

1. 快速定位：B+Tree 二分查找定位葉子頁
2. 回表：二級索引葉子存主鍵值，回聚簇索引取完整行
3. 覆蓋索引：查詢列全在索引中，無需回表（Extra: Using index）
4. 索引下推 (ICP, 5.6+)：存儲引擎層過濾 WHERE 條件，減少回表

索引失效常見模式

-- 1. 隱式類型轉換
WHERE varchar_col = 123          -- 應為 '123'

-- 2. 函數/運算作用於索引列
WHERE YEAR(create_time) = 2024   -- 應 create_time >= '2024-01-01' AND create_time < '2025-01-01'

-- 3. LIKE 通配符開頭
WHERE name LIKE '%abc'

-- 4. OR 兩側索引不全
WHERE indexed_col = 1 OR non_indexed_col = 2

-- 5. 複合索引非最左列
INDEX (a,b) → WHERE b = 1

-- 6. 優化器判斷全表掃描更快
WHERE indexed_col > 1 AND indexed_col < 90  -- 覆蓋 90% 數據

索引效果監控

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key 高 = 索引命中好
-- Handler_read_rnd_next 高 = 全表掃描多

實戰建議清單

1. ✅ 慢查詢日誌 + EXPLAIN 定位缺失索引
2. ✅ 複合索引順序：區分度高 → 區分度低 / 範圍列放最後
3. ✅ 覆蓋索引優化關鍵查詢
4. ✅ 定期 ANALYZE TABLE 更新統計信息
5. ✅ 刪除長期未用索引 (sys.schema_unused_indexes)
6. ✅ 文本搜索用全文索引/ES，別用 LIKE%

1. 上一篇文章： MySQL ORDER BY RAND() 性能優化：從全表掃描到毫秒級隨機查詢
2. 下一篇文章： MySQL 存儲引擎特性總結：MyISAM vs InnoDB vs Memory 選型指南

MySQL 資料庫相關知識

MySQL 事務隔離級別與 Binlog 格式組合選型指南

核心概念 隔離級別：Read Committed (R

MySQL 體系架構深度解析：插件式存儲引擎、InnoDB 核心組件、文件結構、索引算法、鎖機制、事務實現

整體架構分層 +---------------------+

MySQL 資料庫環境建置全攻略（四）：SQL-Front 連線設定與建立資料庫

SQL-Front 連線設定 安裝完成 SQL-Front

MySQL HandlerSocket 插件安裝配置與 PHP 整合完整教程

什麼是 HandlerSocket HandlerSocke