MySQL 索引優化核心原則與實戰避坑指南

索引本質：B+ 樹的數據結構優勢

索引將無序數據轉化為有序 B+ 樹結構，查詢從全表掃描 O(N) 降級為樹搜索 O(log N)。以三表關聯為例：

• 無索引：1000³ = 10 億次組合嘗試
• 有索引：1000 次主表掃描 × 2 次索引查找 = 3000 次操作
• 理論提升百萬倍

索引代價：寫入性能與空間換讀取性能

• 寫入放大：每次 INSERT/UPDATE/DELETE 需同步維護所有索引樹
• 空間佔用：MyISAM 索引文件獨立，InnoDB 聚簇索引與數據共存，輔助索引存主鍵值
• 優化器負擔：索引過多導致執行計劃選擇困難，甚至選錯索引

索引選擇五大黃金法則

1. 最左前綴匹配原則

-- ❌ 失效
WHERE FROM_UNIXTIME(create_time) = '2024-01-01'

-- ✅ 生效
WHERE create_time = UNIX_TIMESTAMP('2024-01-01')

原因：B+ 樹存原始值，函數計算需掃描全表所有行應用函數後再比較。

5. 擴展索引優於新建索引

已有 INDEX(a)，需 (a,b) 時：ALTER TABLE ... DROP INDEX a, ADD INDEX idx_a_b (a,b)。避免重複維護 a 列索引。

索引列類型優化

• 越小越好：MEDIUMINT 足夠別用 BIGINT；VARCHAR(20) 夠別用 CHAR(100)
• 字符串前綴索引：INDEX(name(10))，區分度足夠時節省 90% 空間
• 主鍵短小：InnoDB 輔助索引存主鍵值，主鍵長 → 所有輔助索引膨脹

索引類型選擇

實戰避坑清單

1. 隱式轉換：字符串列查數字 WHERE phone = 13800000000 → 索引失效，需 '13800000000'
2. OR 條件：WHERE a=1 OR b=2 若 a,b 無組合索引 → 全表掃描，改 UNION
3. LIKE 導通配符：LIKE '%abc%' 永遠不走索引，考慮全文索引或 ES
4. 不等於：!=、<>、NOT IN 通常不走索引
5. 頻繁更新列：狀態流轉欄位建索引導致寫入放大，考慮異步表/位圖索引
6. 統計信息過期：定期 ANALYZE TABLE，或 innodb_stats_auto_recalc=ON

慢查詢日誌驅動優化

# 開啟
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

# 分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t 按時間排序，-t 10 顯示前 10

同一慢查詢重複出現 → 必須優化；高負載下「慢」閾值自動降低，關注 Top SQL。

1. 上一篇文章： MySQL 中文亂碼徹底解決：從庫表創建到連接參數全鏈路配置
2. 下一篇文章： CentOS/RHEL 使用 MySQL 官方 Yum 源安裝 MySQL 5.7 完整教程

MySQL 綜合教學

MySQL 5.7.18 安裝報錯：MSVCR120.dll 缺失入口點 fesetround 解決方案

錯誤現象 Windows 安裝 MySQL 5.7.18 彈

MySQL 創建/刪除數據庫命令與 PHP 操作實戰 (mysqladmin / mysql_query)

前言：庫級 DDL 是權限最高的操作，謹慎執行 創建/刪除數

MySQL 進階核心技術：視圖、存儲過程、函數、事務與觸發器深度實戰

MySQL 進階核心技術：視圖、存儲過程、函數、事務與觸發器

慢查優化：聯表查詢驅動表選擇原理，讓 MySQL 優化器自動決定 JOIN 順序

核心概念：Nested Loop Join 與驅動表 MyS