如何正确区分MySQL索引类型？

MySQL中索引的实现与存储引擎密切相关，不同引擎对索引类型的支持存在差异性。通常可以从三个层面来划分索引类别：物理存储结构（如聚簇与非聚簇索引）、逻辑约束类型（如主键、唯一、普通索引）以及实际应用特性（如全文索引、空间索引等）。这种分类方式体现了索引在数据组织、约束保障和功能特性上的多维特征。

一、物理存储结构

MySQL索引按物理存储结构分为B-树索引和哈希索引两类：

1. B-树索引（BTREE索引）

采用多层级树形结构存储，包含根节点（索引树的顶层节点，唯一入口）、分支节点（指向下层节点或叶子节点，用于路由定位）和叶子节点（直接指向数据表中的行记录，且节点间通过指针串联形成有序链表）。叶子节点直接指向数据行并形成有序链表，支持范围查询、全键值匹配、前缀查询和排序操作，但必须遵循最左前缀原则，具体表现为：

1.1必须遵循最左前缀原则：

1.2查询必须从索引最左列开始

1.3不能跳过索引中的任何列

1.4范围查询后的列无法用于索引过滤

2. 哈希索引（HASH索引）

适用于大多数需要范围查询和排序的场景，是InnoDB、MyISAM等引擎的默认索引类型。

通过散列算法将索引值转换为哈希码直接定位数据，仅支持等值查询（=、IN），不支持范围查询、排序或部分列匹配。查询效率接近O(1)，但仅适用于精确匹配场景。目前只有MEMORY/HEAP存储引擎原生支持，且是MEMORY引擎的默认索引类型。

核心区别：B-树索引功能全面适合通用事务处理，哈希索引仅适用于内存表的高频精确匹配场景。

二、逻辑约束类型

从逻辑约束角度，MySQL索引可分为以下五类，各类索引通过特定的约束条件保证数据的完整性和查询效率：

1. 普通索引（INDEX）

约束特性：无任何数据约束，仅用于加速查询

允许值：可包含重复值和NULL值

创建关键字：INDEX 或 KEY

适用场景：常规查询优化，无唯一性要求字段

CREATE INDEX index_name ON table_name(column_name);

2. 唯一索引（UNIQUE）

约束特性：保证数据唯一性，禁止重复值

允许值：允许NULL值（但仅能出现一次）

创建关键字：UNIQUE

适用场景：防止数据重复，如身份证号、邮箱等

CREATE UNIQUE INDEX index_name ON table_name(column_name);

3. 主键索引（PRIMARY KEY）

约束特性：特殊的唯一索引，要求非空且唯一

允许值：禁止NULL值和重复值

创建关键字：PRIMARY KEY

特殊要求：每表只能有一个，自动聚簇索引（InnoDB）

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4. 空间索引（SPATIAL）

约束特性：针对空间数据类型的地理数据索引

允许值：禁止NULL值（字段必须为NOT NULL）

创建关键字：SPATIAL

引擎限制：仅MyISAM引擎支持

数据类型：仅适用于GEOMETRY等空间类型

CREATE SPATIAL INDEX index_name ON table_name(column_name);

5. 全文索引（FULLTEXT）

约束特性：专用于文本内容的关键词搜索

允许值：允许重复值和NULL值

创建关键字：FULLTEXT

引擎限制：MyISAM和InnoDB（5.6+）支持

数据类型：仅适用于CHAR、VARCHAR、TEXT类型

CREATE FULLTEXT INDEX index_name ON table_name(column_name);

三、实际应用类型

从实际应用角度，MySQL索引可分为单列索引和多列索引两类，分别适用于不同的查询场景和性能优化需求：

1. 单列索引（Single-Column Index）

定义：仅基于表中单个字段建立的索引

索引类型：可以是普通索引、唯一索引、主键索引或全文索引

适用场景：

1.1查询条件中仅涉及单个字段的过滤

1.2需要快速排序或分组的单字段操作

1.3数据重复度较低且查询频次较高的字段

优势：创建和维护简单、查询优化器容易选择使用、适合精确匹配查询。

CREATE INDEX index_addr ON tb_student(address(4));   -- 创建单列前缀索引（只索引前4个字符）

2. 多列索引（复合索引/组合索引）

定义：基于表中多个字段联合建立的索引

索引结构：按照定义时的字段顺序组织索引键值

核心特性：最左前缀匹配原则

查询必须从索引最左字段开始

可以匹配索引的前缀子集（如索引(a,b,c)可支持a、a,b查询）

不能跳过中间字段进行匹配

适用场景：

2.1多条件联合查询（WHERE条件包含多个字段）

2.2多字段排序或分组（ORDER BY/GROUP BY多个字段）

2.3覆盖索引查询（索引包含所有查询字段）

优势：减少索引数量，降低存储开销、支持多条件查询的高效执行、可实现覆盖索引，避免回表操作。

CREATE INDEX index_na ON tb_student(name, address);   -- 创建name和address字段的组合索引