MySQL索引及索引优化详解

索引（Index）

一、是什么？

索引是数据库中一种特殊数据结构（如B+树），用于加速数据检索。类似于书籍的目录，通过预先建立特定字段的映射关系，减少磁盘I/O次数，提升查询速度。

二、解决什么问题

1. 全表扫描性能低：无索引时查询需遍历所有数据（时间复杂度O(n)）。
2. 高并发场景延迟：大量数据时简单查询可能阻塞写入。
3. 排序与分组效率低：ORDER BY、GROUP BY操作需要临时表。

三、核心索引类型

类型	数据结构	特点	适用场景
B+Tree	多叉平衡树	支持范围查询、有序访问	默认索引，90%场景适用
Hash	哈希表	等值查询O(1)，不支持范围查询	精确匹配（如内存表）
Full-Text	倒排索引	文本关键词搜索	WHERE MATCH(content) AGAINST('word')
空间索引	R-Tree	地理坐标数据	GIS应用

四、应用场景

1. WHERE条件过滤（如WHERE user_id=100）
2. 表连接字段（如JOIN ON a.id=b.user_id）
3. 排序字段（如ORDER BY create_time DESC）
4. 聚合字段（如GROUP BY department）

五、Java示例

// 使用Spring Data JPA创建索引
@Entity
@Table(indexes = @Index(name = "idx_email", columnList = "email"))
public class User {
    @Id
    private Long id;
    private String email; // 索引字段
}

六、重要注意事项

1. 维护代价：索引增删改需同步更新，降低写入速度。
2. 磁盘占用：索引可能占数据量20%-30%空间。
3. 最左前缀原则：联合索引(a,b,c)只支持a、(a,b)、(a,b,c)查询。
4. 隐式转换陷阱：WHERE phone=13800138000（phone是字符串）会导致索引失效。

---

索引优化（Index Optimization）

一、是什么？

通过策略性设计索引和查询重写，解决索引滥用、失效等问题，最大化查询效率。

二、为什么需要优化

1. 索引失效：如对索引字段使用函数WHERE YEAR(create_time)=2023。
2. 冗余索引：多个索引重复覆盖相同查询。
3. 索引选择错误：优化器错误选择低效索引。
4. 大数据量性能衰减：10万行以上需精细调优。

三、优化方法

1. EXPLAIN分析：

   EXPLAIN SELECT * FROM orders WHERE user_id=100;

   • 关注type（扫描类型）、key（使用索引）、rows（扫描行数）

2. 覆盖索引优化：
   让索引包含查询所需所有字段，避免回表：

   -- 原始：需回表
   SELECT user_name FROM users WHERE age>20; 
   -- 优化：创建联合索引(age, user_name)

3. 索引下推（ICP）：
   MySQL 5.6+特性，在存储引擎层过滤数据，减少Server层负载：

   -- 联合索引(name, age)
   SELECT * FROM users WHERE name LIKE '张%' AND age>10;
   -- ICP在索引内部先过滤age>10

4. 索引合并：
   优化器自动合并多个索引：

   -- 索引1: (age), 索引2: (city)
   SELECT * FROM users WHERE age>25 OR city='北京';

四、应用场景

1. 慢查询日志中耗时>100ms的SQL
2. 分页查询深度翻页（LIMIT 100000,10）
3. 高频查询接口性能瓶颈

五、Java示例

// 使用Hints强制索引（MyBatis示例）
@Select("SELECT /*+ INDEX(users idx_email) */ * FROM users WHERE email=#{email}")
User findByEmail(@Param("email") String email);

六、重要注意事项

1. 避免过度索引：单表索引建议不超过5个。
2. 前缀索引：对长文本使用INDEX(email(10))指定前N字符。
3. 更新统计信息：定期执行ANALYZE TABLE users更新索引基数。
4. 监控工具：使用Percona Toolkit或Prometheus监控索引命中率。

---

索引类型区别

总结

维度	索引	索引优化
核心目标	加速数据定位	提升索引效率，避免失效
关键方法	选择合适类型（B+Tree/Hash等）	EXPLAIN分析、覆盖索引、ICP
适用阶段	表设计时	慢查询出现后
影响范围	全生命周期	高频查询语句
工具支持	CREATE INDEX	EXPLAIN、OPTIMIZER_TRACE

最佳实践：

1. 优先使用B+Tree索引
2. 联合索引字段顺序：高频查询字段在前，低区分度字段在后
3. 更新频繁的字段避免建索引
4. 每2周使用pt-index-usage分析未使用索引

通过合理设计索引+持续优化，百万数据查询可控制在10ms内，千万级数据分页查询性能提升10倍以上。