MySQL索引知识点总结

发表于 分类于 ， ，

MySQL索引知识点总结

什么是索引

索引是数据库中用于加速数据检索的数据结构，类似于书的目录。合理的索引可以大幅提升查询性能。

索引的分类

按数据结构

  • B+Tree索引：MySQL默认索引类型，适用于范围查询
  • Hash索引：适用于等值查询，Memory引擎支持
  • 全文索引：适用于文本搜索
  • R-Tree索引：适用于空间数据

按存储方式

  • 聚簇索引：数据与索引存储在一起，每个表只能有一个
  • 非聚簇索引：索引与数据分离，通过指针关联

按字段特性

  • 主键索引：不允许重复，不允许为空
  • 唯一索引：允许NULL，但不允许重复
  • 普通索引：允许重复
  • 前缀索引：索引字段前缀部分

索引的优缺点

优点

  • 加快数据检索速度
  • 减少IO操作
  • 加速表连接和排序

缺点

  • 占用磁盘空间
  • 增加写操作的开销（INSERT/UPDATE/DELETE）
  • 过多索引会降低维护效率

何时创建索引

  • WHERE子句中频繁使用的字段
  • JOIN操作的连接字段
  • ORDER BY和GROUP BY的字段
  • 区分度高的字段

何时避免使用索引

  • 数据量少的表
  • 频繁更新的字段
  • 区分度低的字段（如性别、状态）
  • 不需要查询的字段

索引使用技巧

1. 覆盖索引

查询的列都在索引中，无需回表

1
SELECT id, name FROM user WHERE name = '张三';

2. 最左前缀原则

复合索引遵循最左前缀，跳跃会导致索引失效

1
2
3
-- 索引 (a, b, c)
-- 可以使用: a / a,b / a,b,c
-- 不可使用: b / b,c / c

3. 索引下推

在索引层面过滤数据，减少回表次数

索引失效的常见情况

  1. 使用函数或运算
  2. 类型转换
  3. 使用OR连接（部分情况）
  4. 模糊查询以%开头
  5. 隐式类型转换

性能优化建议

  1. 分析慢查询日志
  2. 使用EXPLAIN分析执行计划
  3. 避免SELECT *
  4. 控制索引数量（建议不超过5个）
  5. 定期维护碎片

总结

索引是优化数据库查询的关键技术。需要根据实际业务场景合理设计索引，同时注意索引带来的负面影响。