当前位置:Gxlcms > mysql > SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第一篇)

SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第一篇)

时间:2021-07-01 10:21:17 帮助过:37人阅读

SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第一篇) 在第一篇里大家先搭建好测试环境,了解SQL Server中的执行引擎,了解一下表的信息,在第二篇再跟大家详细地说清楚索引 在第三篇里跟大家说一下统计信息。 因为篇幅比较长,所以暂时分成第一篇、

SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第一篇)

在第一篇里大家先搭建好测试环境,了解SQL Server中的执行引擎,了解一下表的信息,在第二篇再跟大家详细地说清楚索引

在第三篇里跟大家说一下统计信息。

因为篇幅比较长,所以暂时分成第一篇、第二篇、第三篇,不然大家看得很痛苦

---------------------------------------------------------------开始-----------------------------------------------------

先给出一篇文章的地址,都是博客园里的文章:

http://www.cnblogs.com/CareySon/archive/2012/05/23/CoreConceptOfExcutionEngine.html

《SQL Server中的执行引擎入门》,这篇文章主要讲了:执行引擎、执行计划、查询优化器

对执行引擎 、执行计划的了解或者掌握对SQLSERVER的索引查找数据方法是很有必要的,因为是环环相扣

如果阁下不对执行引擎 、执行计划先进行一下了解,下面的内容阁下会看得一头雾水

再看一下SQLSERVER为每个表记录了哪些信息 因为博客园的图片上传功能出了一些问题

我先给出图片链接

打开SSMS,点击表的+号,可以看到SQLSERVER一般记录了表的列信息、主键、约束、触发器、索引和统计信息

列信息、主键、约束、触发器就不说了,一般SQLSERVER查找目标数据都要使用到索引统计信息

--------------------------------------------搭建测试环境-----------------------------------------------

请确保阁下的计算机里有范例数据库AdventureWorks,并在数据库里建立两张新的规范表格

 1 USE [AdventureWorks]
 2 
 3 DROP TABLE [dbo].SalesOrderHeader_test
 4 GO
 5 DROP TABLE [dbo].SalesOrderDetail_test
 6 GO
 7 
 8 SELECT * INTO dbo.SalesOrderHeader_test
 9 FROM sales.[SalesOrderHeader]
10 GO
11 
12 SELECT * INTO [dbo].SalesOrderDetail_test
13 FROM sales.[SalesOrderDetail]
14 GO
15 
16 CREATE CLUSTERED INDEX SalesOrderHeader_test_CL
17 ON [dbo].SalesOrderHeader_test([SalesOrderID])
18 
19 GO
20 
21 CREATE INDEX SalesOrderDetail_test_NCL
22 ON [dbo].SalesOrderDetail_test([SalesOrderID])
23 GO

dbo.SalesOrderHeader_test:存放的是每一张订单的头信息,包括订单创建日期、客户编号、

合同编号、销售员编号等,每个订单都有一个单独的订单号在订单号这个字段上,有一个聚集索引

dbo.SalesOrderDetail_test:存放的是订单的详细内容。一张订单可以销售多个产品给同一个客户,

所以SalesOrderHeader_test和SalesOrderDetail_test是一对多的关系。每条详细内容包括他所属的订单编号,

他自己在表格里的唯一编号(SalesOrderDetailID)、产品编号、单价、销售数量等,在这里,先只在[SalesOrderID]

上建立一个非聚集索引

按照AdventureWorks里原先的数据,dbo.SalesOrderHeader_test里有3W多条订单信息,

dbo.SalesOrderDetail_test里有12W多条订单详细记录,基本上一条订单有3~5条详细记录,

这是一个正常的分布

下面再在dbo.SalesOrderHeader_test里加入9条订单记录,他们的编号是从75124到75132

这是9张特殊的订单,每张有12W多条详细记录。也就是说:dbo.SalesOrderDetail_test里会有

90%的数据属于这9张订单。请用下面这段代码来得到模拟数据:

 1 USE [AdventureWorks]
 2 GO
 3 DECLARE @i INT
 4 SET @i=1
 5 WHILE @i<=9
 6 BEGIN
 7 INSERT INTO dbo.[SalesOrderHeader_test]
 8         ( [RevisionNumber] ,
 9           [OrderDate] ,
10           [DueDate] ,
11           [ShipDate] ,
12           [Status] ,
13           [OnlineOrderFlag] ,
14           [SalesOrderNumber] ,
15           [PurchaseOrderNumber] ,
16           [AccountNumber] ,
17           [CustomerID] ,
18           [ContactID] ,
19           [SalesPersonID] ,
20           [TerritoryID] ,
21           [BillToAddressID] ,
22           [ShipToAddressID] ,
23           [ShipMethodID] ,
24           [CreditCardID] ,
25           [CreditCardApprovalCode] ,
26           [CurrencyRateID] ,
27           [SubTotal] ,
28           [TaxAmt] ,
29           [Freight] ,
30           [TotalDue] ,
31           [Comment] ,
32           [rowguid] ,
33           [ModifiedDate]
34         )
35 SELECT 
36 [RevisionNumber],[OrderDate],[DueDate],
37 [ShipDate],[Status],[OnlineOrderFlag],[SalesOrderNumber],
38 [PurchaseOrderNumber],[AccountNumber],[CustomerID],
39 [ContactID],[SalesPersonID],[TerritoryID],
40 [BillToAddressID],[ShipToAddressID],[ShipMethodID],
41 [CreditCardID],[CreditCardApprovalCode],[CurrencyRateID],
42 [SubTotal],[TaxAmt],
43 [Freight],[TotalDue],[Comment],[rowguid],[ModifiedDate]
44 FROM [dbo].[SalesOrderHeader_test]
45 WHERE [SalesOrderID]=75123
46 
47 INSERT INTO [dbo].[SalesOrderDetail_test]
48         ( [SalesOrderID] ,
49           [CarrierTrackingNumber] ,
50           [OrderQty] ,
51           [ProductID] ,
52           [SpecialOfferID] ,
53           [UnitPrice] ,
54           [UnitPriceDiscount] ,
55           [LineTotal] ,
56           [rowguid] ,
57           [ModifiedDate]
58         )
59 SELECT 
60 75123+@i,[CarrierTrackingNumber],[OrderQty],
61 [ProductID],[SpecialOfferID],[UnitPrice],
62 [UnitPriceDiscount],[LineTotal],
63 [rowguid],GETDATE()
64 FROM sales.[SalesOrderDetail]
65 SET @i=@i+1
66 END
67 GO

在后面的内容中,将用这两张表做例子。

测试数据建立完毕了!

--------------------------------------------------索引与统计信息的介绍--------------------------------------------------------------

SQLSERVER有两类索引,聚集索引和非聚集索引。建立索引的主要目的,是按照预期的顺序排列数据,并且存储一部分冗余数据,

用来加快SQL找到数据的速度。一张几百万条甚至更大数据量的表,怎样从里面找到用户想要的数据呢?从头到尾找一遍肯定是最慢的办法

索引的功能类似于字典里的检字表。有了好的索引,表格再大,也能像查字典一样很快地找到用户需要的数据

除了需要索引的帮助,SQLSERVER在运行指令的时候,也要知道所涉及的表格每个有多大,预期每张表能够返回多少数据,

每一步的结果集会多大。知道了这些信息,才能够选择比较好的执行计划。可是SQLSERVER是一个计算机程序,他是怎麽知道这些信息的呢?

这要靠统计信息(statistics)来帮忙(上面已经给出了截图)。SQLSERVER会在每个索引上自动建立统计信息,也会根据运行指令的需要,

动态地创建一些统计信息。统计信息的准确度,会直接影响SQLSERVER完成指令的速度

第一篇完毕了,第二篇就是讲索引的内容了,敬请期待o(∩_∩)o

第二篇地址:SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第二篇)

第三篇地址:SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第三篇)

人气教程排行