Sql Server索引的创建及优化

一、什么是索引

　　SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间 。

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

二、索引原理

SQL Server中的索引是一种数据结构，用于提高数据检索效率。索引的存储结构通常是基于B树（Balanced Tree），它是一种平衡多叉树，用于快速查找、插入和删除操作。B树的设计初衷是为了减少对磁盘的扫描次数，通过将B树的根节点存入内存，经过几次查找就可以定位到数据所在的页，从而提高性能。

聚集索引（Clustered Index）决定了表中数据的物理存储顺序，类似于书本的目录，它允许每张表只能有一个。非聚集索引（Nonclustered Index）则类似于书的索引页，它不重新组织表中的数据，而是在索引结构中为每一行存储索引列值，并用一个指针指向数据所在的页面。一个表可以拥有多个非聚集索引。

三、索引的存储机制

首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，如果我们将字典的汉字随即打乱，没有前面的按照拼音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多底，大家可以想象。

聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。

四、索引创建

        1、创建聚集索引： 聚集索引决定了表中数据的物理存储顺序。一个表只能有一个聚集索引。

CREATE CLUSTERED INDEX idx_name ON table_name (column1, column2, ...);

        2、创建非聚集索引： 非聚集索引不改变表中数据的物理存储顺序，一个表可以有多个非聚集索引。

CREATE NONCLUSTERED INDEX idx_name ON table_name (column1, column2, ...);

        3、创建唯一索引： 唯一索引保证了在索引列中没有重复的值。

CREATE UNIQUE INDEX idx_name ON table_name (column1, column2, ...);

        4、创建索引时包含额外列： 使用 INCLUDE 子句可以在索引中包含额外的列，这些列不需要是索引键的一部分。

CREATE INDEX idx_name ON table_name (column1)
INCLUDE (column2, column3, ...);

        5、使用填充因子： 填充因子可以指定索引页的填充程度，以减少未来的页分裂。

CREATE INDEX idx_name ON table_name (column1)
WITH FILLFACTOR = 80;

        6、创建索引时忽略重复键： 使用 IGNORE_DUP_KEY 选项可以在插入重复键时不抛出错误。

CREATE UNIQUE INDEX idx_name ON table_name (column1)
WITH IGNORE_DUP_KEY;

五、索引使用

1. 确保查询使用索引： 通过查看查询计划（SET SHOWPLAN_XML ON），确认查询是否使用了预期的索引。

2. 避免全表扫描： 尽量设计查询语句，使其能够利用索引，避免不必要的全表扫描。

3. 使用索引列作为查询条件： 在 WHERE、JOIN、ORDER BY 和 GROUP BY 子句中使用索引列。

4. 考虑索引的选择性： 选择性高的索引（即不同值较多的列）通常更有效。

5. 使用索引覆盖： 如果一个查询只需要从索引中检索数据，而不是表数据，那么可以使用索引覆盖来提高性能。

6. 定期维护索引： 使用 DBCC CHECKDB 检查索引的完整性，使用 DBCC INDEXDEFRAG 和 DBCC SHOWCONTIG 检查索引的碎片化，并适时重建或重组索引。

7. 删除不必要的索引： 如果索引不再被查询使用，或者其维护成本高于其带来的性能提升，考虑删除它们。

8. 使用数据库引擎优化顾问： 数据库引擎优化顾问（DEOA）可以分析查询并推荐索引操作。

9. 自动化索引维护： 使用 SQL Server Agent 作业自动执行索引维护任务。

六、索引优化

1、索引覆盖：创建只包含查询所需的列的索引，这样可以避免回表操作，提高查询效率。

2、包含列：在索引中包含额外的列，以避免额外的I/O操作，这在查询中需要多列但只有部分列在索引中时特别有用。

3、索引压缩：使用ALTER INDEX命令重建索引，并通过设置PAD_INDEX、SORT_IN_TEMPDB等选项来减少索引的大小，提高索引性能。

4、索引重组与重建：定期对索引进行重组（ALTER INDEX REORGANIZE）和重建（ALTER INDEX REBUILD），以减少索引碎片化，提高查询性能。

5、监控索引碎片化：使用系统视图查询索引碎片化信息，并根据需要进行优化。

6、删除无用索引：删除那些不再使用或很少使用的索引，释放资源并提高性能。

7、更新统计信息：定期更新统计信息，确保查询优化器能够选择最佳查询计划。