MySQL 索引类型详解

1. 按数据结构分类

(1) B-Tree 索引（实际使用 B+Tree）

• 特点：
  • 默认的索引类型
  • 适合全键值、键值范围或键前缀查找
  • 支持排序和分组操作
• 适用场景：
  • 等值查询（=）
  • 范围查询（>, <, BETWEEN）
  • 排序（ORDER BY）
  • 分组（GROUP BY）
• 限制：
  • 不适用于以通配符开头的 LIKE 查询（如 LIKE '%abc'）
  • 必须从最左列开始使用索引

(2) 哈希索引

• 特点：
  • 基于哈希表实现
  • 只有精确匹配所有列的查询才有效
  • 检索速度非常快（O(1)时间复杂度）
• 适用场景：
  • 等值查询（=, IN()）
  • Memory 引擎的默认索引类型
• 限制：
  • 不支持范围查询
  • 不支持排序
  • 不支持部分索引列的查询
  • 有哈希冲突问题

(3) 全文索引（FULLTEXT）

• 特点：
  • 专门用于文本内容的搜索
  • 支持自然语言搜索和布尔搜索
  • 使用倒排索引实现
• 适用场景：
  • 文本字段的关键词搜索
  • 内容管理系统中的搜索功能
• 限制：
  • 仅适用于 MyISAM 和 InnoDB（5.6+）引擎
  • 对中文支持不够友好（需配合分词插件）

(4) R-Tree 索引（空间索引）

• 特点：
  • 用于地理空间数据类型
  • 支持 GIS 数据查询
• 适用场景：
  • 地理位置查询
  • 地图应用中的距离计算等

2. 按逻辑功能分类

(1) 普通索引（INDEX）

• 最基本的索引类型，无特殊约束
• 创建语法：CREATE INDEX index_name ON table(column)

(2) 唯一索引（UNIQUE）

• 索引列的值必须唯一，但允许 NULL 值
• 创建语法：CREATE UNIQUE INDEX index_name ON table(column)

(3) 主键索引（PRIMARY KEY）

• 特殊的唯一索引，不允许 NULL 值
• 一个表只能有一个主键
• 创建语法：ALTER TABLE table ADD PRIMARY KEY(column)

(4) 复合索引（多列索引）

• 在多个列上建立的索引
• 遵循”最左前缀原则”
• 创建语法：CREATE INDEX index_name ON table(col1, col2, col3)

(5) 前缀索引

• 对文本列的前N个字符建立索引
• 可节省索引空间
• 创建语法：CREATE INDEX index_name ON table(column(N))

3. 特殊索引类型

(1) 覆盖索引

• 查询的列都包含在索引中，无需回表
• 可显著提高查询性能

(2) 聚簇索引（InnoDB 主键索引）

• 索引的叶子节点存储了完整的数据记录
• InnoDB 的表必须有且只有一个聚簇索引

(3) 二级索引（非聚簇索引）

• 叶子节点存储的是主键值而非数据
• 查询时需要回表操作

索引选择建议

1. 适合建索引的列：
   • WHERE 子句中的常用列
   • JOIN 连接使用的列
   • ORDER BY/GROUP BY 的列
   • 高选择性的列（区分度高）
2. 不适合建索引的列：
   • 数据量小的表
   • 频繁更新的列
   • 区分度低的列（如性别）
   • TEXT/BLOB 等大字段（考虑前缀索引）
3. 复合索引设计原则：
   • 将选择性高的列放在前面
   • 考虑查询频率和排序需求
   • 遵循最左前缀原则