MySQL：索引

MySQL索引

1. 索引的作用

• 加速查询：通过减少全表扫描，快速定位数据，类似于书籍目录。
• 约束数据：如唯一索引保证列值的唯一性，主键索引标识唯一记录。

2. 索引类型

• 主键索引（PRIMARY KEY）：唯一且非空，每个表只能有一个，InnoDB中为聚簇索引（数据存储在B+树叶子节点）。
• 唯一索引（UNIQUE）：列值唯一，允许空值，用于避免重复数据。
• 普通索引（INDEX）：无唯一性限制，单纯加速查询。
• 全文索引（FULLTEXT）：针对文本内容分词搜索，适用于MATCH AGAINST操作，InnoDB（5.6+）和MyISAM支持。
• 空间索引（SPATIAL）：用于地理数据类型（如GEOMETRY），仅MyISAM支持。
• 复合索引：多列组合的索引，遵循最左前缀原则。

3. 数据结构

• B+树索引（默认）：
  • 支持范围查询、排序、模糊匹配（如LIKE 'prefix%'）。
  • 叶子节点存储数据或主键（聚簇索引 vs 非聚簇索引）。
• 哈希索引：
  • 仅支持精确查询（=, IN），速度快但不支持排序/范围查询。
  • 显式支持于Memory引擎，InnoDB有自适应哈希索引（内部自动管理）。
• 其他：R树（空间索引）、倒排索引（全文搜索）。

4. 优点与缺点

• 优点：
  • 大幅提升查询效率，减少磁盘I/O。
  • 唯一性约束保证数据完整性。
  • 覆盖索引可避免回表，提升性能。
• 缺点：
  • 占用额外存储空间。
  • 增删改操作需维护索引，降低写性能。
  • 不合理的索引设计可能拖慢查询。

5. 使用注意事项

• 适用场景：
  • WHERE、JOIN、ORDER BY、GROUP BY中的高频列。
  • 高选择性列（如用户ID）优于低选择性列（如性别）。
• 索引优化：
  • 最左前缀原则：复合索引(a,b,c)需从最左列开始使用，如a=1 AND b=2生效，但b=2不生效。
  • 覆盖索引：查询字段均包含在索引中，避免回表。
  • 前缀索引：对长字符串前N个字符建索引，节省空间（如INDEX(email(10))）。
  • 避免索引失效：如对索引列使用函数、类型转换、LIKE '%后缀'、OR连接非索引列等。
• 维护技巧：
  • 定期分析表（ANALYZE TABLE）更新统计信息。
  • 使用EXPLAIN查看执行计划，优化查询语句。
  • 删除冗余或使用率低的索引。

6. 示例与操作

• 创建索引：

  CREATE INDEX idx_name ON table(column);
  ALTER TABLE table ADD UNIQUE (column);
  

• 查看索引：

  SHOW INDEX FROM table;
  

• 删除索引：

  DROP INDEX idx_name ON table;
  

7. 总结

合理设计索引是数据库性能优化的关键。需权衡查询加速与写操作成本，结合业务场景选择索引类型和列，并通过执行计划分析持续调优。避免过度索引，优先考虑高频、高选择性查询条件，并利用复合索引和覆盖索引提升效率。