MySQL索引：数据库的「超级目录」

想象你有一本1000页的百科全书，要快速找到某个知识点（如“光合作用”）：

• ❌ 无索引：逐页翻找 → 全表扫描（慢！）
• ✅ 有索引：直接查目录 → 精准定位（快！）

索引的本质：预先对数据排序+存储位置信息，加速检索的特殊数据结构。

---

一、索引类型及原理

1. 数据结构

索引类型	数据结构	适用场景	特点
B+树索引	多叉平衡树	默认索引（InnoDB）	范围查询快，适合磁盘存储
哈希索引	哈希表	精确匹配（Memory引擎）	等值查询极快，不支持范围查询
全文索引	倒排索引	文本搜索（MATCH AGAINST）	解决LIKE '%word%'低效问题

📌 B+树为什么快？

• 叶子节点形成链表 → 范围查询高效（如WHERE id > 100）
• 非叶子节点只存索引 → 单节点存储更多key
• 所有数据在叶子节点 → 查询路径长度一致

2. 逻辑分类

索引类型	描述	示例
主键索引	唯一标识，不允许NULL	PRIMARY KEY (id)
唯一索引	保证列值唯一，允许NULL	UNIQUE KEY (email)
普通索引	加速查询，允许重复值	INDEX (name)
联合索引	多列组合索引	INDEX (city, age)

---

二、索引生效与失效场景

✅ 生效场景

-- 1. 全值匹配  
SELECT * FROM users WHERE name = 'Alice';  -- 2. 最左前缀原则（联合索引）  
INDEX (a, b, c)  -- 生效: WHERE a=? / WHERE a=? AND b=? / WHERE a=? AND b=? AND c=?  -- 3. 范围查询（部分生效）  
SELECT * FROM orders WHERE amount > 100 AND status = 1;  -- (amount)索引生效  -- 4. 覆盖索引（直接从索引拿数据）  
SELECT id FROM products WHERE price > 50;  -- 索引包含(id,price)  

❌ 失效场景

-- 1. 违反最左前缀  
INDEX (a, b, c)  
WHERE b = 2;     -- ❌ 索引失效  -- 2. 对索引列运算  
WHERE YEAR(create_time) = 2023;  -- ❌ 改用: create_time BETWEEN '2023-01-01' AND '2023-12-31'  -- 3. 隐式类型转换  
WHERE phone = 13800138000;  -- ❌ phone是varchar类型  -- 4. LIKE左模糊  
WHERE name LIKE '%Lee';     -- ❌ 全表扫描  -- 5. OR条件未全覆盖  
WHERE age = 18 OR name = 'Bob';  -- 若name无索引 → 全表扫描  

---

三、索引优化策略

1. EXPLAIN诊断工具

EXPLAIN SELECT * FROM employees WHERE department_id = 3;  

关键指标：

• type：system > const > ref > range > index > ALL（性能从优到差）
• key：实际使用的索引
• rows：扫描行数（越小越好）

2. 设计原则

策略	说明
只为高频查询建索引	避免维护成本（增删改变慢）
短字段优先	整型索引比字符串快，考虑用city_code代替city_name
避免冗余索引	INDEX(a,b) 和 INDEX(a) 同时存在 → 后者冗余
前缀索引	长文本可截取前N字符：ALTER TABLE t ADD INDEX (text_col(10))

3. 慢查询优化示例

问题SQL：

SELECT * FROM logs WHERE user_id = 1001 AND DATE(create_time) = '2023-10-01';  

优化步骤：

1. 避免对create_time计算 → 改用范围查询
2. 建立联合索引(user_id, create_time)

-- 优化后  
SELECT * FROM logs  
WHERE user_id = 1001  AND create_time >= '2023-10-01 00:00:00'  AND create_time < '2023-10-02 00:00:00';  

---

四、索引的代价

• 空间代价：索引占用额外存储（特别是B+树的非叶子节点）
• 时间代价：
  • INSERT：需更新索引 → 性能下降约10%
  • UPDATE：若修改索引列 → 触发索引重组
  • DELETE：标记删除 → 产生索引碎片

💡 黄金法则：
不要为小表建索引（全表扫描更快）
中大型表重点优化WHERE和JOIN列

---

五、高级技巧

1. 索引下推（ICP）

MySQL 5.6+

INDEX (age, city)  
SELECT * FROM users WHERE age > 20 AND city = 'Beijing';  

• 旧版本：先按age>20回表查数据 → 再过滤city
• 开启ICP：在索引层直接过滤city → 减少回表次数

2. 覆盖索引优化

-- 原始查询  
SELECT id, name FROM products WHERE category = 'Electronics';  -- 优化方案：  
ALTER TABLE products ADD INDEX (category, id, name);  -- 覆盖索引  

数据直接从索引返回 → 避免回表查主键

---

总结：索引使用指南

1. 建索引前问3个问题：

   • 数据量是否足够大？
   • 查询频率是否高？
   • 该字段过滤性是否好？（如性别字段不适合单独建索引）

2. 优先考虑：

   • WHERE条件列、JOIN关联列、ORDER BY排序列

3. 定期维护：

   ANALYZE TABLE users;       -- 更新索引统计信息  
   OPTIMIZE TABLE orders;     -- 重建表+索引（解决碎片问题）  
   

🚨 最后警告：
索引不是越多越好！

• 表数据量<1万 → 通常不需要索引
• 每增加一个索引 → INSERT速度降低约10%