SQL Server 数据库中的索引

留言

其他数据库也类似

索引的简介：

索引分为聚集索引和非聚集索引，索引类似于一本书的目录，在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间 。

优点

  • 提高查询性能：通过减少数据扫描的行数来加快查询速度。
  • 强制唯一性：确保数据唯一性，避免重复。

缺点

  • 增加存储空间：索引需要额外的存储空间。
  • 增加维护成本：插入、更新和删除操作需要额外的索引维护工作。

总结

  • 索引建少了，用 WHERE 子句找数据效率低，不利于查找数据。
  • 索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER 除了要更新数据表本身，还要连带立即更新所有的相关索引。
  • 过多的索引也会浪费硬盘空间。

索引的分类：

  • 唯一索引
    • 确保索引列中的所有值都是唯一的。
    • 可以是聚集索引或非聚集索引。
  • 主键索引
    • 当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。
  • 全文索引
    • 用于快速搜索大文本字段中的字词。
    • 支持复杂查询，例如短语搜索、词根形式搜索和近义词搜索。
  • 过滤索引（Filtered Index）
    • 基于表中满足特定条件的子集创建。
    • 有助于提高查询性能并减少存储开销。
  • XML 索引
    • 专用于存储和查询 XML 数据类型的索引。
    • 包括主 XML 索引和辅助 XML 索引。

何时使用索引

  • 高频查询列
    • 某列经常被用作查询条件，为该列创建索引可以加快查询速度。
  • 排序和分组
    • 某列经常被用于排序(ORDER BY)或分组(例如 GROUP BY)，在该列上创建索引可以提高性能。
  • 联接（JOIN）操作：
    • 在需要经常进行联接操作的表的键列上创建索引可以提高联接操作的效率。
  • 唯一性约束：
    • 如果某列需要确保唯一性(如:订单号)，创建唯一索引可以避免重复数据。
  • 全文搜索：
    • 如果需要在大文本字段中进行快速搜索，创建全文索引可以显著提高搜索性能。

注意事项

  • 频繁更新的列：避免在频繁更新的列上创建索引，因为每次更新都会增加索引维护的开销。
  • 小表：对于数据量较小的表，索引可能不会显著提高性能，反而会增加存储开销和维护负担。
  • 平衡：在创建索引时需要平衡查询性能和索引维护成本，定期监控和优化索引以确保最佳性能。

如何管理索引

  • NONCLUSTERED 非聚集索引关键字
  • CLUSTERED 聚集索引关键字
  • UNIQUE 唯一索引关键字

创建非聚集索引

1
2
CREATE NONCLUSTERED INDEX 索引名称
ON 表名 (列名);

创建聚集索引

1
2
CREATE CLUSTERED INDEX 索引名称
ON 表名 (列名);

创建唯一索引

1
2
CREATE UNIQUE INDEX 索引名称
ON 表名 (列名);

删除索引

1
DROP INDEX 索引名称 ON 表名;

注意

  • 如果索引是由主键约束创建的，不能直接删除该索引。需要先删除主键约束。
  • 如果索引是由唯一约束创建的，需要先删除唯一约束。
  • 如果索引当前正在被其他会话或进程锁定或阻塞，删除操作可能无法进行，直到锁定解除。

删除主键约束及其索引

1
2
ALTER TABLE 表名
DROP CONSTRAINT 主键约束名称;

删除唯一约束及其索引

1
2
ALTER TABLE TableName
DROP CONSTRAINT 唯一约束名称;

删除主键约束和唯一约束的时候都会顺带删除其索引数据

重建索引

1
2
ALTER INDEX 索引名称
ON 表名 REBUILD;

禁用索引

1
2
ALTER INDEX 索引名称
ON 表名 DISABLE;