一、什么是索引
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;
五、索引使用
-
确保查询使用索引: 通过查看查询计划(
SET SHOWPLAN_XML ON
),确认查询是否使用了预期的索引。 -
避免全表扫描: 尽量设计查询语句,使其能够利用索引,避免不必要的全表扫描。
-
使用索引列作为查询条件: 在
WHERE
、JOIN
、ORDER BY
和GROUP BY
子句中使用索引列。 -
考虑索引的选择性: 选择性高的索引(即不同值较多的列)通常更有效。
-
使用索引覆盖: 如果一个查询只需要从索引中检索数据,而不是表数据,那么可以使用索引覆盖来提高性能。
-
定期维护索引: 使用
DBCC CHECKDB
检查索引的完整性,使用DBCC INDEXDEFRAG
和DBCC SHOWCONTIG
检查索引的碎片化,并适时重建或重组索引。 -
删除不必要的索引: 如果索引不再被查询使用,或者其维护成本高于其带来的性能提升,考虑删除它们。
-
使用数据库引擎优化顾问: 数据库引擎优化顾问(DEOA)可以分析查询并推荐索引操作。
-
自动化索引维护: 使用 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、更新统计信息:定期更新统计信息,确保查询优化器能够选择最佳查询计划。