mysql 索引优化

MySQL索引优化是提升查询性能的关键，合理使用索引可以显著减少数据检索时间。以下为MySQL索引优化的系统总结：

---

一、索引基础

1. 索引类型

   • 主键索引 (PRIMARY KEY)：唯一且非空，每表仅一个。
   • 唯一索引 (UNIQUE)：确保列值唯一，可多列组合。
   • 普通索引 (INDEX)：加速查询，无唯一约束。
   • 全文索引 (FULLTEXT)：用于文本搜索（如MATCH AGAINST）。
   • 组合索引 (Composite Index)：多列联合索引，遵循最左前缀原则。

2. 数据结构

   • B+树索引（InnoDB默认）：支持范围查询、排序，叶子节点存储数据或主键。
   • 哈希索引（Memory引擎）：仅精确匹配，不支持排序，适用于等值查询。
   • 自适应哈希索引（InnoDB自动创建）：针对频繁访问的索引页自动优化。

---

二、索引使用场景

• 适合建索引：
  • WHERE、JOIN、ORDER BY、GROUP BY频繁的列。
  • 高区分度（选择性高）的列（如用户ID）。
  • 外键关联字段。
• 不适合建索引：
  • 数据量小的表。
  • 频繁更新的列（写开销大）。
  • 低区分度列（如性别）。
  • 大文本字段（可考虑前缀索引）。

---

三、索引优化策略

1. 覆盖索引

   • 查询字段均在索引中，避免回表（EXPLAIN显示Using index）。
   • 示例：索引(a,b)，查询SELECT a,b FROM table WHERE a=1。

2. 最左前缀原则

   • 联合索引(a,b,c)生效场景：a, a AND b, a AND b AND c。
   • 顺序优化：高区分度列优先，或按查询频率调整。

3. 索引下推（ICP，5.6+）

   • 存储引擎层过滤数据，减少回表次数。
   • 示例：索引(a,b)，查询WHERE a=1 AND b>10，ICP在引擎层过滤b>10。

4. 避免索引失效

   • 禁止对索引列使用函数或表达式（如YEAR(create_time)=2023）。
   • 避免隐式类型转换（如字符串列用数字查询）。
   • 慎用OR（若某条件无索引，全表扫描）。
   • 前导模糊查询LIKE '%abc'无法使用索引，LIKE 'abc%'可以。
   • 联合索引需满足最左前缀，否则失效。

---

四、高级技巧

1. 索引选择性

   • 公式：选择性 = 不同值数 / 总行数，越接近1效率越高。
   • 低选择性列可结合高选择性列建组合索引。

2. EXPLAIN分析

   • 关注type字段：ref（索引查询）、range（范围扫描）、index（全索引扫描）、ALL（全表扫描）。
   • Extra字段：Using index（覆盖索引）、Using filesort（需额外排序）。

3. 维护优化

   • 定期ANALYZE TABLE更新统计信息。
   • 删除冗余索引（如已有(a,b)，无需单独建a索引）。
   • 碎片整理：OPTIMIZE TABLE或重建索引（ALTER TABLE）。

---

五、实战场景优化

• 分页查询：大偏移量时，改用覆盖索引+子查询。

  SELECT * FROM table 
  JOIN (SELECT id FROM table ORDER BY id LIMIT 1000000,10) AS tmp 
  USING(id);
  

• 排序/分组：利用索引避免filesort。
• NULL处理：IS NULL可能使用索引，需结合数据分布判断。

---

六、注意事项

• 平衡读写：索引过多降低写性能（每次写操作更新索引）。
• 监控使用：通过SHOW INDEX或性能模式分析索引利用率。
• 分区表：分区键需与索引结合，避免跨分区扫描。

---

通过合理设计索引、分析执行计划及持续优化，可显著提升MySQL查询性能。实际应用中需结合业务场景和数据特点灵活调整。