【SQL进阶 索引分类】及聚集索引和二级索引的特点

SQL中的索引用于优化数据库查询性能，它们可以加快数据检索速度，但同时也会增加写操作（插入、更新、删除）的开销。以下是几种常见的索引分类：

1. 主键索引（Primary Key Index）：
   • 确保数据表中每一行数据的主键值都是唯一的。
   • 主键索引是一种特殊的唯一索引，不允许有空值。
2. 唯一索引（Unique Index）：
   • 保证索引列中的所有值都是不同的，不允许重复。
3. 普通索引（Regular Index）/单列索引（Single-Column Index）：
   • 用于单个列，可以提高针对该列的查询效率。
4. 复合索引（Composite Index）/多列索引（Multi-Column Index）：
   • 跨多个列，可以提高针对这些列组合的查询效率。
5. 全文索引（Full-Text Index）：
   • 用于大型文本数据的搜索，可以快速进行全文搜索查询。
6. 空间索引（Spatial Index）：
   • 用于地理空间数据类型，优化空间对象的查询。

现在，我们重点描述聚集索引和二级索引的原理及特点：

聚集索引（Clustered Index）：

聚集索引决定了表中数据的物理存储顺序。在聚集索引中，索引的键值和数据行是一起存储的。

• 原理：
  • 聚集索引就像一本书的目录，目录中的页码直接指向书页的物理位置。
  • 数据行在磁盘上的存储顺序与索引的顺序是一致的。
• 特点：
  • 一个表只能有一个聚集索引。
  • 聚集索引可以极大地提高数据检索效率，特别是对于范围查询。
  • 插入新行时，数据库会根据聚集索引的顺序将新行插入到正确的位置。
  • 更新聚集索引列可能会引起数据行的移动，这可能导致性能开销。

二级索引（Secondary Index）/非聚集索引（Non-Clustered Index）：

二级索引或非聚集索引不决定数据的物理存储顺序，它通常包含索引列和指向数据行的指针或行键。

• 原理：
  • 二级索引类似于书的索引，它提供了关键词到书页的映射，但不决定书页的物理顺序。
  • 查询时，数据库首先通过二级索引找到行键，然后使用行键到聚集索引中查找具体的数据行。
• 特点：
  • 一个表可以有多个二级索引。
  • 二级索引可以提高针对索引列的查询效率，但不会影响数据的物理存储顺序。
  • 对于非聚集索引的查询，可能需要两次查找：首先在二级索引中查找，然后在聚集索引或数据表中查找对应的数据行，这称为“索引跳跃”。
  • 由于不涉及数据行的移动，更新二级索引通常比更新聚集索引代价小。

在实际应用中，选择合适的索引类型对于优化查询性能和维护数据库效率至关重要。设计索引时需要考虑到查询模式、数据更新频率和系统资源等因素。