SQL索引主要可以按物理存储方式和逻辑功能两个核心维度进行分类，不同数据库系统（如MySQL、SQL Server、Oracle、PostgreSQL）在实现上各有特点，但基本分类框架相似。

一、按物理存储方式分类

1. 聚集索引（Clustered Index）

• 核心特性：数据行的物理顺序与索引顺序一致，表数据按索引键值排序存储
• 关键限制：每张表只能有一个聚集索引
• 工作原理：聚集索引的叶子节点直接存储完整的数据行，而非指向数据的指针
• 适用场景：主键查询、范围查询（如日期范围）
• 数据库差异：
  • MySQL：InnoDB的主键索引默认是聚集索引
  • SQL Server：需显式指定CLUSTERED关键字创建
  • Oracle：通过索引组织表(IOT)实现类似功能

2. 非聚集索引（Nonclustered Index）

• 核心特性：数据行的物理顺序与索引顺序不一致，索引独立于数据存储
• 关键特点：每张表可创建多个非聚集索引
• 工作原理：非聚集索引的叶子节点存储索引键值和指向数据行的指针（ROWID）
• 查询过程：先通过索引找到指针，再通过指针定位数据行（称为"回表"）
• 数据库差异：
  • MySQL：InnoDB的二级索引属于非聚集索引
  • SQL Server：默认创建的索引为非聚集索引
  • Oracle：B树索引默认为非聚集类型

SQL中存放数据的B树默认为B+树

---

二、按逻辑功能分类

1. 主键索引（Primary Key Index）

• 核心特性：唯一且非空，自动创建聚集索引（在大多数数据库中）
• 作用：保证数据行的唯一性和实体完整性
• 创建方式：定义主键约束时自动创建
• 性能特点：主键查询性能最高，无需回表操作

2. 唯一索引（Unique Index）

• 核心特性：确保索引列值唯一，但允许NULL值（可有多个NULL）
• 与主键区别：不强制非空，可存在多个唯一索引
• 适用场景：业务上必须唯一的字段（如订单号、身份证号）
• 性能特点：查询性能与主键索引接近，但不保证数据行顺序

CREATE UNIQUE INDEX idx_unique_email ON users(email);

3. 普通索引（Index/Key）

• 核心特性：最基本索引类型，无唯一性、非空等约束
• 适用场景：WHERE、JOIN、ORDER BY中频繁使用的非唯一字段
• 创建方式：显式使用CREATE INDEX或ALTER TABLE语句
• 性能特点：仅提升查询效率，不限制数据，是业务中最常用的索引类型

CREATE INDEX idx_customer_name ON customers(customer_name);

4. 联合索引（Composite Index）

• 核心特性：基于多个字段共同创建的索引
• 关键原则：遵循最左前缀匹配原则，索引按字段创建顺序排序
• 适用场景：多字段联合查询场景，优先使用联合索引而非多个单值索引
• 设计建议：单表联合索引数量建议控制在5个以内
• 示例：

-- 创建联合索引
CREATE INDEX idx_user_name_age ON users(name, age);
-- 有效查询
SELECT * FROM users WHERE name = 'Tom' AND age = 25;

5. 前缀索引（Prefix Index）

• 核心特性：对字符串字段的前N个字符创建索引，而非整个字段
• 优势：大幅减少索引存储空间，提升索引写入和查询效率
• 适用场景：长字符串字段（如VARCHAR(255)），且前N个字符选择性足够高
• 创建示例：

CREATE INDEX idx_email_prefix ON user(email(10)); -- 索引前10个字符

6. 全文索引（Full-text Index）

• 核心特性：针对大文本字段（TEXT、LONGTEXT）创建的索引
• 工作原理：基于分词技术实现，支持自然语言检索、布尔检索
• 适用场景：文章内容、商品描述、评论等大文本字段的关键词检索
• 数据库支持：
  • MySQL：InnoDB在5.6版本后支持
  • SQL Server：提供专门的全文索引类型
  • PostgreSQL：通过GIN索引支持全文检索

CREATE FULLTEXT INDEX idx_article_content ON articles(content);

7. 哈希索引（Hash Index）

• 核心特性：通过哈希表实现，提供O(1)复杂度的等值查询
• 优势：理论上比B-tree更快的等值查找（O(1) vs O(log n)）
• 局限：
  • 仅支持等值查询（=），不支持范围、排序、前缀匹配
  • 无法用于ORDER BY和DISTINCT优化
• 数据库支持：
  • SQL Server：内存优化表支持
  • PostgreSQL：10+版本支持WAL日志，具备崩溃恢复能力
  • MySQL：Memory引擎支持，InnoDB不支持

CREATE INDEX idx_hash_code USING HASH ON cache_table(resource_key);

8. 位图索引（Bitmap Index）

• 核心特性：用位图表示索引值的存在与否，每个位对应一个可能的值
• 适用场景：低基数列（Distinct值较少的列），如性别、状态等
• 优势：大幅减少存储空间，优化OLAP查询性能
• 局限：不适用于高并发事务环境（OLTP）
• 数据库支持：
  • Oracle：原生支持位图索引
  • PostgreSQL：通过扩展支持类似功能
  • MySQL：不直接支持，但可通过其他方式模拟

-- Oracle
CREATE BITMAP INDEX idx_gender ON patients(gender);

9. 函数索引（Function-based Index）

• 核心特性：基于函数或表达式结果创建的索引
• 适用场景：需要对字段进行函数处理的查询条件
• 创建示例：

-- Oracle/PostgreSQL
CREATE INDEX idx_upper_username ON users(UPPER(username));
-- MySQL 8.0+
CREATE INDEX idx_func_sub ON emp(substr(empno,1,2));

• 使用要点：查询必须使用相同的函数才能命中索引

10. 空间索引（Spatial Index）

• 核心特性：专为地理空间数据设计的索引
• 适用场景：空间数据的范围查询和空间关系查询
• 数据库支持：
  • SQL Server：提供专门的空间索引类型
  • PostgreSQL：通过GiST索引支持空间数据
  • MySQL：支持空间索引

CREATE SPATIAL INDEX idx_geom_location ON spatial_data(geom_column);

11. 列存储索引（Columnstore Index）

• 核心特性：按列而非按行存储数据，适合分析型查询
• 优势：
  • 与传统行存储相比，查询性能提升最多10倍
  • 数据压缩率提升最多7倍
• 适用场景：数据仓库工作负载，主要执行大容量加载和只读查询
• 数据库支持：SQL Server、Oracle、PostgreSQL均提供类似实现

-- 非聚集/聚集索引
CREATE NONCLUSTERED/CLUSTERED COLUMNSTORE INDEX IX_Columnstore ON TableName (Column1, Column2, Column3);

12. 过滤索引（Filtered Index）

• 核心特性：只对表中部分行进行索引的非聚集索引
• 优势：
  • 提升查询性能（针对特定数据子集）
  • 减少索引维护开销
  • 降低索引存储开销
• 创建示例：

-- SQL Server
CREATE INDEX idx_active_users ON users(status) WHERE status = 'active';

---

三、特殊索引概念

1. 覆盖索引（Covering Index）

• 核心概念：不是独立索引类型，而是索引优化的结果
• 工作原理：当查询的所有字段都包含在索引中时，无需回表查询数据
• 性能优势：避免回表操作，显著提升查询性能
• 实现方式：通过联合索引或包含列索引实现

CREATE INDEX idx_covering ON orders(order_id, status, total) INCLUDE (customer_id);

2. 包含列索引（Index with Included Columns）

• 核心特性：在非聚集索引中额外包含非键列
• 目的：实现覆盖索引效果，避免回表
• 数据库支持：SQL Server、PostgreSQL支持
• 创建示例：

CREATE INDEX idx_orders_user_id ON orders(user_id) INCLUDE (order_date, amount);

---

四、索引选择建议

1. 基本原则：索引不是越多越好，每个索引都有写入和存储成本
2. 选择性考量：优先在高选择性列（唯一值占比高）上创建索引
3. 查询模式匹配：根据实际查询模式设计索引，而非盲目创建
4. 联合索引优先：多字段查询时，优先使用联合索引而非多个单列索引
5. 避免冗余索引：已有联合索引idx_a_b(a,b)，无需再建idx_a(a)