详细分析Sql Server索引的创建、查询、删除等基本知识（附Demo）

前言

原先分析过Sql的基本知识，感兴趣也可阅读：

1. Mysql的两种存储引擎详细分析及区别（全）
2. Mysql优化高级篇（全）

1. 基本知识

索引是在数据库表上创建的数据结构，用于提高查询效率

SQL Server 支持几种类型的索引，主要包括：

• 聚集索引（Clustered Index）：数据表的实际数据按照索引顺序排列
  一个表只能有一个聚集索引，因为数据行的存储顺序只能有一种
  通常在主键列上创建
• 非聚集索引（Non-Clustered Index）：创建一个单独的索引结构，指向表中的数据行
  可以有多个非聚集索引

索引的主要作用是加速查询速度，但过多的索引可能会影响数据的插入、更新和删除操作的性能

2. 索引

2.1 创建

创建索引可以使用 CREATE INDEX 语句

以下是基于 [manong].[dbo].[yanjiuseng] 表的示例：

创建聚集索引（假设以 id 列为主键）：

CREATE CLUSTERED INDEX IX_yanjiuseng_id
ON [manong].[dbo].[yanjiuseng] (id);

创建非聚集索引（假设以 name 列为索引）：

CREATE NONCLUSTERED INDEX IX_yanjiuseng_name
ON [manong].[dbo].[yanjiuseng] (name);

创建复合索引（假设在 name 和 age 列上创建）：

CREATE NONCLUSTERED INDEX IX_yanjiuseng_name_age
ON [manong].[dbo].[yanjiuseng] (name, age);

2.2 删除

删除索引可以使用 DROP INDEX 语句：

删除非聚集索引：

DROP INDEX [manong].[dbo].[yanjiuseng].IX_yanjiuseng_name;

删除聚集索引（聚集索引通常在删除表时会自动删除，无需单独删除，但可以通过以下语句删除）：

DROP INDEX [manong].[dbo].[yanjiuseng].IX_yanjiuseng_id;

2.3 查询

单独查询表的索引信息：EXEC sp_helpindex '[manong].[dbo].[yanjiuseng]';

从表中也可得知为非聚集联合索引

如果想要查询是什么索引以及排的位置，可以使用如下方式：

SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ic.column_id AS ColumnID,
    c.name AS ColumnName
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.object_id = OBJECT_ID('[manong].[dbo].[yanjiuseng]');

截图如下：

查询指定索引所包含的列也可知道为联合索引

SELECT 
    i.name AS IndexName,
    c.name AS ColumnName,
    ic.index_column_id AS ColumnID
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.object_id = OBJECT_ID('[manong].[dbo].[yanjiuseng]')
    AND i.name = 'END_TIME_INDEX'
ORDER BY 
    ic.index_column_id;

如果结果返回一个列，则为单列索引

IndexName       | ColumnName | ColumnID
----------------|------------|----------
END_TIME_INDEX  | endTime    | 1

如果结果返回多个列，则为复合索引

IndexName       | ColumnName | ColumnID
----------------|------------|----------
END_TIME_INDEX  | endTime    | 1
END_TIME_INDEX  | status     | 2

再次展示另外一个表的聚集联合索引

3. 总结

• 索引的选择：选择适合的索引类型和列是关键
  频繁用于查询的列应建立索引，而频繁更新的列应谨慎建立索引
• 索引的维护：索引可能会随数据的增加而变得不再高效，定期对索引进行重建或重组可以保持其性能
• 性能考量：虽然索引提高了查询性能，但它们也会增加存储空间需求和数据操作的开销
  过多的索引会导致性能下降，因此应根据实际需求选择合适的索引