索引工作机制

SQL Server中的索引机制类似于书籍的目录系统，能够显著加速数据检索过程。虽然数据库在没有索引的情况下仍能正常运行，但合理的索引设计可以大幅提升查询效率，特别是在数据分析场景中效果更为明显。

要深入理解索引机制，需要掌握B+树结构、数据页组织、区段管理、填充因子设置、碎片处理以及文件组配置等相关概念。

索引的主要分类包括：

• 唯一约束索引：确保索引列中不存在重复值。若表中已存在重复数据，系统通常会阻止创建此类索引。当尝试插入会导致重复的记录时，数据库会拒绝该操作。
• 主键约束索引：为主键字段自动创建的特殊唯一索引。保证主键值的唯一性，并提供高效的数据访问路径。
• 聚簇索引：决定表中数据行的物理存储顺序与索引键值逻辑顺序一致。每张表只能有一个聚簇索引。以汉语词典为例，按拼音排序时，词条的排列顺序与页码顺序相对应。
• 非聚簇索引：索引键值的逻辑顺序与数据行物理存储顺序无关。一张表可创建多个非聚簇索引。

主键与索引的关系

在日常数据库开发中，主键和索引都是常见的概念，但两者存在本质区别：

• 主键本质上是一种特殊的索引，但并非所有索引都是主键
• 主键必须满足唯一性和非空约束，而普通索引仅在设置唯一性时才具备此特性
• 主键一旦设定通常不可更改，索引则可以根据需要灵活创建和删除
• 索引可以基于任意有序字段构建

索引操作实践

常用的索引管理操作包括创建、重建和删除：

-- 创建普通索引
CREATE NONCLUSTERED INDEX idx_name ON table_name(column_name)

-- 创建聚簇索引
CREATE CLUSTERED INDEX idx_name ON table_name(column_name)

-- 重建索引以优化性能
ALTER INDEX idx_name ON table_name REBUILD

-- 移除不再需要的索引
DROP INDEX idx_name ON table_name

特殊索引类型：

• XML索引：专门用于xml数据类型的列，对XML内容中的标签、数值和路径建立索引
• 空间索引：针对geometry或geography等空间数据类型的扩展索引

索引失效场景

索引失效指的是在特定查询条件下，数据库引擎选择不使用索引的情况：

1. 空值判断：使用IS NULL或IS NOT NULL条件时索引可能不生效

   -- 索引可能失效
   SELECT * FROM temp WHERE create_time IS NULL
   
   -- 索引正常使用
   SELECT * FROM temp WHERE create_time = '2023-06-01'
   

2. 模糊匹配：前缀通配符查询导致索引无法使用

   SELECT * FROM temp WHERE description LIKE '%关键词'

3. 不等值比较：使用!=操作符时索引可能被忽略

   SELECT * FROM temp WHERE amount != 0

4. 表达式计算：对索引列进行运算或函数调用

   -- 索引失效
   SELECT * FROM temp WHERE price * quantity > 100
   
   -- 索引失效
   SELECT * FROM temp WHERE YEAR(create_date) > 2020
   

5. 类型隐式转换：查询条件与列定义类型不匹配

   -- 隐式转换导致索引失效
   SELECT * FROM users WHERE birth_date = '2023-06-03'
   
   -- 显式转换确保类型一致
   SELECT * FROM users WHERE birth_date = CONVERT(datetime, '2023-06-03')
   

6. OR条件查询：复合条件可能导致索引不被使用

   -- 索引可能失效
   SELECT * FROM users WHERE name = '张三' OR name = '李四'
   
   -- 使用UNION优化
   SELECT * FROM users WHERE name = '张三' 
   UNION 
   SELECT * FROM users WHERE name = '李四'