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. 复合索引设计原则：

   • 将选择性高的列放在前面

   • 考虑查询频率和排序需求

   • 遵循最左前缀原则