SQL Server 索引

时间：2023-03-09 16:38:51
SQL Server 索引

SQL Server 中数据存储的基本单位是页(Page)。数据库中的数据文件(.mdf 或 .ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。磁盘 I/O 操作在页级执行。也就是说，SQL Server 每次读取或写入数据的最少数据单位是数据页。

索引的用途

　　我们对数据查询及处理速度已成为衡量应用系统成败的标准，而采用索引来加快数据处理速度通常是最普遍采用的优化方法。

索引是什么

　　数据库中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在数据库中，数据库程序使用索引可以定位到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。

索引的利弊

　　查询执行的大部分开销是I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行了。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以我们要合理使用索引，及时更新去除次优索引。

索引的分类

　　  SQL SERVER中有多种索引类型。

　　按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“非聚集索引（非聚类索引，非簇集索引）”

　　按数据唯一性区分：“唯一索引”，“非唯一索引”

　　按键列个数区分：“单列索引”，“多列索引”。

　　[聚集索引](类似字典中的字母顺序查找)

　　聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序。像我们用到的汉语字典，就是一个聚集索引，比如要查“张”，我们自然而然就翻到字典的后面百十页。然后根据字母顺序跟查找出来。这里用到微软的平衡二叉树算法，即首先把书翻到大概二分之一的位置，如果要找的页码比该页的页码小，就把书向前翻到四分之一处，否则，就把书向后翻到四分之三的地方，依此类推，把书页续分成更小的部分，直至正确的页码。

　　由于聚集索引是给数据排序，不可能有多种排法，所以一个表只能建立一个聚集索引。科学统计建立这样的索引需要至少相当与该表120%的附加空间，用来存放该表的副本和索引中间页，但是他的性能几乎总是比其它索引要快。

　　由于在聚集索引下，数据在物理上是按序排列在数据页上的，重复值也排在一起，因而包含范围检查（bentween,<,><=,>=）或使用group by 或order by的查询时，一旦找到第一个键值的行，后面都将是连在一起，不必在进一步的搜索，避免啦大范围的扫描，可以大大提高查询速度。

　　[非聚集索引]（类似于字典中的偏旁部首查找）

　　SQL Server默认情况下建立的索引是非聚集索引，他不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。他像汉语字典中的根据‘偏旁部首’查找要找的字，即便对数据不排序，然而他拥有的目录更像是目录，对查取数据的效率也是具有的提升空间，而不需要全表扫描。

　　一个表可以拥有多个非聚集索引，每个非聚集索引根据索引列的不同提供不同的排序顺序。

填充因子

　　索引的一个特性，定义该索引每页上的可用空间量。FILLFACTOR（填充因子）适应以后表数据的扩展并减小了页拆分的可能性。填充因子是从0到100的百分比数值，设为100时表示将数据页填满。只有当不会对数据进行更改时(例如 只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的硬盘空间。填充因子指定不当，会降低数据库的读取性能，其降低量与填充因子设置值成反比。

索引SQL语法

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]

INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]

[[,]IGNORE_DUP_KEY]

[[,]DROP_EXISTING]

[[,]STATISTICS_NORECOMPUTE]

[[,]SORT_IN_TEMPDB]

]

[ ON filegroup ]

CREATE INDEX命令创建索引各参数说明如下：

UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。

CLUSTERED：用于指定创建的索引为聚集索引。

NONCLUSTERED：用于指定创建的索引为非聚集索引。

index_name：用于指定所创建的索引的名称。

table：用于指定创建索引的表的名称。

view：用于指定创建索引的视图的名称。

ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。

Column：用于指定被索引的列。

PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。

FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。

IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。

DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。

STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。

SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。

ON filegroup：用于指定存放索引的文件组。

　  例子：

--表bigdata创建一个名为idx_mobiel的非聚集索引，索引字段为mobiel

create index idx_mobiel

on bigdata(mobiel) 

--表bigdata创建一个名为idx_id的唯一聚集索引，索引字段为id

--要求成批插入数据时忽略重复值，不重新计算统计信息，填充因子为40

create unique clustered index idx_id

on bigdata(id)

with pad_index,

fillfactor=40,

ignore_dup_key,

statistics_norecompute

　　管理索引

Exec sp_helpindex BigData   --查看索引定义

Exec sp_rename 'BigData.idx_mobiel','idx_big_mobiel'  --将索引名由'idx_mobiel' 改为'idx_big_mobiel'

drop index BigData.idx_big_mobiel  --删除bigdata表中的idx_big_mobiel索引

dbcc showcontig(bigdata,idx_mobiel) --检查bigdata表中索引idx_mobiel的碎片信息

dbcc indexdefrag(Test,bigdata,idx_mobiel)  --整理test数据库中bigdata表的索引idx_mobiel上的碎片

update statistics bigdata  --更新bigdata表中的全部索引的统计信息

索引的设计原理

　　对于一张表来说索引的有无和建立什么样的索引，要取决与where字句和Join表达式中。

　　一般来说建立索引的原则包括以下内容：

  • 系统一般会给主键字段自动建立聚集索引。
  • 有大量重复值且经常有范围查询和排序、分组的列，或者经常频繁访问的列，考虑建立聚集索引。
  • 在一个经常做插入操作的表中建立索引，应使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在表为只读表，填充因子可设为100。
  • 在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。

索引优化实例

建测试表

CREATE TABLE T_UserInfo

(

Userid varchar(20), UserName varchar(20),

RegTime datetime, Tel varchar(20),

)

插入100W数据

DECLARE @I INT

DECLARE @ENDID INT

SELECT @I = 1

SELECT @ENDID = 1000000 --在此处更改要插入的数据，重新插入之前要删掉所有数据

WHILE @I <= @ENDID

BEGIN

INSERT INTO T_UserInfo

SELECT 'ABCDE'+CAST(@I AS VARCHAR(20))+'EF','李'+CAST(@I AS VARCHAR(20)),

GETDATE(),''+CAST(@I AS VARCHAR(20))

SELECT @I = @I + 1

END

情况一：无建立索引查询

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT * FROM T_UserInfo AS tui WHERE tui.UserName='李10000'

SET STATISTICS PROFILE OFF

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

结果为：

SQL Server 索引

情况二：创建聚集索引后查询

建聚集索引

CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (UserName)

查询

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT * FROM T_UserInfo AS tui WHERE tui.UserName='李10000'

SET STATISTICS PROFILE OFF

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

结果为：

SQL Server 索引

情况三：创建非聚集索引后查询

创建非聚集索引

CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (UserName)

查询

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT * FROM T_UserInfo AS tui WHERE tui.UserName='李10000'

SET STATISTICS PROFILE OFF

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

结果为：

SQL Server 索引

总结：

实现同一查询功能的SQL写法可能会有多种，如果判断哪种最优化，如果仅仅是从时间上来测，会受很多外界因素的影响，而我们明白了MSSQL如何去执行，通过IO逻辑读、通过查看图示的查询计划、通过其优化后而执行的SQL语句，才是优化SQL的真正途径。

提醒：数据量的多少有时会影响MSSQL对同一种查询写法语句的执行计划，这一点在非聚集索引上特别明显，还有就是在多CPU与单CPU下，在多用户并发情况下，同一写法的查询语句执行计划会有所不同，这个就需要大家有机会去试验。

查看SQL语句执行时间

程序优化过程中，往往需要分析所写的SQL语句是否已经优化过了，服务器的响应时间有多快，这个时候就需要用到SQL的STATISTICS状态值来查看了。

通过设置STATISTICS我们可以查看执行SQL时的系统情况。选项有PROFILE，IO ，TIME。

　　【介绍】

　　SET STATISTICS PROFILE ON：显示分析、编译和执行查询所需的时间（以毫秒为单位）。 
　　SET STATISTICS IO ON：报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。 
　　SET STATISTICS TIME ON：显示每个查询执行后的结果集，代表查询执行的配置文件。

　　【使用方法】

　　SET STATISTICS PROFILE ON 
　　SET STATISTICS IO ON 
　　SET STATISTICS TIME ON 
　　 --你的SQL脚本开始--
　　SELECT [TestCase] FROM [TestCaseSelect] 
　　--你的SQL脚本结束--
　　SET STATISTICS PROFILE OFF 
　　SET STATISTICS IO OFF 
　　SET STATISTICS TIME OFF

　　【执行效果】

　　SQL Server 索引

通过手工添加语句，计算执行时间来查看执行语句花费了的时间，以判断该条SQL语句的效率如何：

　　declare @d datetime
　　set @d=getdate()
　　--你的SQL脚本开始--
　　SELECT [TestCase] FROM [TestCaseSelect] 
　　--你的SQL脚本结束--
　　select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

相关文章