--12章 SQL语句调优 P357
--懒惰的肥兔的文章 索引include的使用：http://www.cnblogs.com/lzrabbit/archive/2012/07/03/2549558.html
--当确定了应用性能问题可以归结到某一个，或几个消耗资源的语句后
--对这些语句进行调优就是DBA或者开发者的职责了
--语句调优是和数据库打交道的必备基本功之一，也是相对来讲比较单纯和有意思的话题
--单纯：基本上可以排除操作系统，网络，应用程序等因素
--有意思：语句的编译和运行背后有一整套数据访问和管理理论，而且很多东西是关系型数据库
--所共有的，可以拿他当一门手艺来研究
--inside microsoft sql server2005 (query tuning and optimization)专门讲
--语句调优
--当你面对一个“有问题”的语句时，应该怎麽分析他的问题所在，最后达到优化语句的目的呢？
--首先要想一想，“有问题”的语句“问题”究竟在哪里？也就是说，你要优化的目标是什么？
--常见的需求有：
--1、语句需要访问大量的数据页面，造成内存压力，磁盘繁忙等
--对于这类问题，所关心的是为什麽语句执行要访问这麽多数据页面？是语句的结果集本身
--就比较大；还是SQL没有办法有效地seek，而是像大炮打苍蝇一样从大量的原始数据里找出
--需要返回的结果；还是因为数据页面有很多碎片，导致SQL读了很多页面，但是每个页面
--里的数据量不多。这些都是考虑因素
--2、在内存没有压力的前提下（语句所访问的页面都事先缓存在内存里），语句运行的时间
--还是很长
--语句的运行时间一般会主要花在这3步上：语句编译、语句执行、结果集返回。结果集返回
--的速度和SQL自身没有太大关系，所以一般不会在语句调优的时候来考虑。语句调优
--时要搞清楚编译和执行各花了多少时间，哪一段时间有优化空间，以及怎样优化
--3、单个语句执行时间可以接受，但是其CPU使用量比较大，多个语句并发执行会造成SQL
--CPU高
--有些语句单句执行可能一两秒就能执行完毕，对用户来讲还在可接受范围。但是他的
--CPU时间可能也是在一两秒，甚至更长。如果同时有十几个用户在跑同样的语句，SQL
--就会满负荷了。语句的CPU时间也分编译阶段和执行阶段。优化者要先搞清楚这两个阶段
--各用了多少CPU资源。然后，再看有没有优化降低CPU使用量的可能
--4、语句单独执行看不出有大问题，但是并发执行就容易遇到阻塞或死锁
--这个也是语句调优的一个重要人物。很多语句执行速度很快，使用资源量SQL也能够承受
--但是就是容易引起阻塞或死锁。这种现象往往是由于应用在某个表或者索引上的
--并发度特别高，而问题语句申请的锁数量比较大造成的。当然有时候可以使用
--Query Hint（查询提示 WITH(ROWLOCK)）来强制SQL使用粒度比较小的锁。但是这往往不是最好的解决办法
--也可能解决不了问题。最理想的方法，是通过调整语句运行的方式，引导他
--申请尽可能少的、粒度尽可能小的锁。这里也要做语句调优
--做这些调优的时候，首先要对目标语句做估算，看看他优化的空间有多大。有些语句本身比较简单
--可以通过调整索引的方法迅速提高性能，这样的调优是很值得做的。有些语句非常复杂，或者
--返回的结果集很大，通过调整SQL这里的设置，提供性能的空间往往不大。这时候要考虑
--语句本身是不是能够换一种方法实现。很多时候改一下语句，把一条大的语句拆分为若干条
--小的语句，或者去掉一些不必要的逻辑，会达到事半功倍的效果
--在谈论如何做语句调优的具体方法之前，必须先来介绍一些最必需的背景知识。不了解
--这些知识，做语句调优就只能基本靠猜。所需要的背景知识主要包括：
--（1）理解索引
--（2）统计信息
--（3）编译和重编译
--（4）读懂语句执行计划
--为了说明问题方便，请先在AdventureWorks数据库里建立两张新的规范表格
USE [AdventureWorks]
DROP TABLE [dbo].SalesOrderHeader_test
GO
DROP TABLE [dbo].SalesOrderDetail_test
GO
SELECT * INTO dbo.SalesOrderHeader_test
FROM sales.[SalesOrderHeader]
GO
SELECT * INTO [dbo].SalesOrderDetail_test
FROM sales.[SalesOrderDetail]
GO
CREATE CLUSTERED INDEX SalesOrderHeader_test_CL
ON [dbo].SalesOrderHeader_test([SalesOrderID])
GO
CREATE INDEX SalesOrderDetail_test_NCL
ON [dbo].SalesOrderDetail_test([SalesOrderID])
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张订单。请用下面这段代码来得到模拟数据：
USE [AdventureWorks]
GO
DECLARE @i INT
SET @i=1
WHILE @i<=9
BEGIN
INSERT INTO dbo.[SalesOrderHeader_test]
( [RevisionNumber] ,
[OrderDate] ,
[DueDate] ,
[ShipDate] ,
[Status] ,
[OnlineOrderFlag] ,
[SalesOrderNumber] ,
[PurchaseOrderNumber] ,
[AccountNumber] ,
[CustomerID] ,
[ContactID] ,
[SalesPersonID] ,
[TerritoryID] ,
[BillToAddressID] ,
[ShipToAddressID] ,
[ShipMethodID] ,
[CreditCardID] ,
[CreditCardApprovalCode] ,
[CurrencyRateID] ,
[SubTotal] ,
[TaxAmt] ,
[Freight] ,
[TotalDue] ,
[Comment] ,
[rowguid] ,
[ModifiedDate]
)
SELECT
[RevisionNumber],[OrderDate],[DueDate],
[ShipDate],[Status],[OnlineOrderFlag],[SalesOrderNumber],
[PurchaseOrderNumber],[AccountNumber],[CustomerID],
[ContactID],[SalesPersonID],[TerritoryID],
[BillToAddressID],[ShipToAddressID],[ShipMethodID],
[CreditCardID],[CreditCardApprovalCode],[CurrencyRateID],
[SubTotal],[TaxAmt],
[Freight],[TotalDue],[Comment],[rowguid],[ModifiedDate]
FROM [dbo].[SalesOrderHeader_test]
WHERE [SalesOrderID]=75123
INSERT INTO [dbo].[SalesOrderDetail_test]
( [SalesOrderID] ,
[CarrierTrackingNumber] ,
[OrderQty] ,
[ProductID] ,
[SpecialOfferID] ,
[UnitPrice] ,
[UnitPriceDiscount] ,
[LineTotal] ,
[rowguid] ,
[ModifiedDate]
)
SELECT
75123+@i,[CarrierTrackingNumber],[OrderQty],
[ProductID],[SpecialOfferID],[UnitPrice],
[UnitPriceDiscount],[LineTotal],
[rowguid],GETDATE()
FROM sales.[SalesOrderDetail]
SET @i=@i+1
END
GO
--在后面的内容中，将用这两张表做例子。 还会为[SalesOrderDetail_test]建立这两个索引
--现在请不要创建他们
CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])
GO
CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO
--------------------------------------------索引与统计信息------------------------------------------------------
--SQL有两类索引，聚集索引和非聚集索引。建立索引的主要目的，是按照预期的顺序排列
--数据，并且存储一部分冗余数据，用来加快SQL找到数据的速度。一张几百万条甚至更大
--数据量的表，怎样从里面找到用户想要的数据呢？从头到尾找一遍肯定是最慢的办法
--索引的功能类似于字典里的检字表。有了好的索引，表格再大，也能像字典一样很快
--地找到用户需要的数据
--除了需要索引的帮助，SQL在运行指令的时候，也要知道所涉及的表格每个有多大，
--预期每张表能够返回多少数据，每一步的结果集会多大。知道了这些信息，才能
--够选择比较好的执行计划。可是SQL是一个计算机程序，他是怎麽知道这些信息的呢？
--这要靠统计信息（statistics）来帮忙。SQL会在每个索引上自动建立统计信息，
--也会根据运行指令的需要，动态地创建一些统计信息。统计信息的准确度，会直接
--影响SQL完成指令的速度
--在这一节，介绍SQL是怎麽通过索引来找到目标数据的，统计信息里到底存放了哪些数据
--以及SQL是怎麽维护他们的
--索引上的数据检索方法
--如果一张表上没有聚集索引，数据将会以随机的顺序存放在表格里。以
--[SalesOrderDetail_test]为例子。他的上面没有聚集索引，只有一个在SalesOrderID
--上的非聚集索引。所以表格的每一行记录，不会按照任何顺序，而是随意地存放在
--Hash里
--这时候如果用户要找所有单价大于200的销售详细记录，要运行的语句会是
SET STATISTICS PROFILE ON
USE [AdventureWorks]
GO
SELECT
[SalesOrderDetailID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [UnitPrice]>200
--由于表格在UnitPrice上没有索引，所以SQL不得不对这个表格从头到尾扫描一遍
--把所有UnitPrice的值大于200的记录一个一个挑出来
--从执行计划里可以清楚地看出来SQL这里做了一个表扫描。在后面会详细介绍
--如何得到和分析执行计划
--如果这个表格上有聚集索引，事情会怎样呢？还是以刚才那张表做例子，先给他
--在值是唯一的字段SalesOrderDetailID上建立一个聚集索引，这样所有的数据都会
--按照聚集索引的顺序存储
USE [AdventureWorks]
GO
CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])
GO
--可惜的是,查询条件UnitPrice上没有索引,所以SQL还是要把所有记录都扫描一遍
--和刚才有区别的是,执行计划里的表扫描变成了聚集索引扫描.因为在有聚集索引的表格上
--数据是直接存放在索引的最底层的,所以要扫描整个表格里的数据,就要把整个聚集索引
--扫描一遍。在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描
--没有什么差别。并不是说这里有了“Index”这个字样，就说明执行计划比表扫描的有多大进步
--当然反过来讲，如果看到“Table Scan”的字样，就说明这个表格上没有聚集索引
--现在在UnitPrice上面建立一个非聚集索引，看看情况会有什么变化
USE [AdventureWorks]
GO
CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO
--在非聚集索引里，会为每条记录存储一份非聚集索引索引键的值和一份聚集索引索引键的值
--【在没有聚集索引的表格里，是RID值指向数据页面，有聚集索引的话指向聚集索引的键(在不使用include时)】
--所以在这里，每条记录都会有一份[UnitPrice]和[SalesOrderDetailID]记录，按照[UnitPrice]
--的顺序存放
--再跑刚才那个查询，你会看到这次SQL不用扫描整个表了。根据新建的索引，他直接找到了符合记录的值
--执行计划：索引查找
--但是光用建立在[UnitPrice]上的索引不能告诉我们其他字段的值。如果在刚才那个查询里再增加几个
--字段返回，SQL就要先在非聚集索引上找到所有[UnitPrice]大于200的记录，然后再根据[SalesOrderDetailID]
--的值找到存储在聚集索引上的详细数据。这个过程可以称为“bookmark loolup”
SET STATISTICS PROFILE ON
USE [AdventureWorks]
GO
SELECT [SalesOrderID],[SalesOrderDetailID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test] WITH (INDEX (salesorderdetail_test_ncl_price))
WHERE [UnitPrice]>200
--select * 使用了12秒
--select 三个字段使用了5秒
--在SQL2005以后，bookmark lookup的动作用一个嵌套循环来完成。所以在执行计划里，可以看到
--SQL先seek了非聚集索引salesorderdetail_test_ncl_price，然后用clustered index seek
--把需要的行找出来。这里的嵌套循环其实就是bookmark lookup
-----------------------------------总结--------------------------------------------------------
--总结一下，在SQL里根据找寻目标的不同和方法不同，有下面几种情况
--SQL寻找数据的方法
--结构 Scan Seek
--堆（没有聚集索引的表格数据页） Table Scan 无
--聚集索引 Clustered Index Scan Clustered Index Seek
--非聚集索引 Index Scan Index Seek
--注意：非聚集索引的情况：（如果表格没有聚集索引的话数据还是存放在堆结构的表数据页，只是
--在非聚集索引里，会为每条记录存储一份非聚集索引索引键的值和一份聚集索引索引键的值
--（在没有聚集索引的表格里，是RID值指向数据页面，有聚集索引的话指向聚集索引的键）
--为什麽不叫：NONCLUSTERED Index Scan？NONCLUSTERED Index Seek？
--前面说了SQL只有两种索引：聚集索引和非聚集索引
--下面这两句话是一样的都是建立非聚集索引：
CREATE NONCLUSTERED INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO
CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO
--所以就没有必要叫NONCLUSTERED Index Scan和NONCLUSTERED Index Seek了
--总结---------------------------------------------------------------------------------------------
--如果在执行计划里看到这些动作，就应该能知道SQL正在对哪种对象在做什么样的操作。
--表扫描表明正在处理的表格没有聚集索引，SQL正在扫描整张表。聚集索引扫描表明
--SQL正在扫描一张有聚集索引的表，但是也是整张表扫描。Index Scan表明SQL正在扫描
--一个非聚集索引。由于非聚集索引上一般只会有一小部分字段，所以这里虽然也是扫描
--但是代价会比整表扫描会少很多，因为数据不存放在索引里面。
--Clustered Index Seek和Index Seek说明SQL正在利用索引结果检索目标数据。如果结果
--集只占表格总数据量的一小部分并且结果集的字段都包含在索引里，Seek会比Scan便宜很多，
--索引就起到了提高性能的作用，否则就会引起bookmark lookup
--where 后面（筛选的字段）决定你建索引的时候要建的字段的范围CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])
--where前面（返回的字段）决定你的表数据的查找速度，根据聚集索引来返回其他字段的值，没有的话到数据页面里去找字段值
--聚集索引扫描：where后面的字段是没有建立索引【包括非聚集索引（表格上要有非聚集索引）和聚集索引】的字段 但是表格上有聚集索引 不管表格上有没有非聚集索引
--非聚集索引扫描：where后面的字段是没有建立索引（非聚集索引）的字段 但是表格上有非聚集索引但没有聚集索引
--表扫描：where后面的字段是没有建立索引的字段 并且表格上没有聚集索引和非聚集索引
--书签查找就是：where后面的字段建立了索引（不管是聚集索引还是非聚集索引），但是where前面返回的字段中有些没有建立索引
--（不管是聚集索引还是非聚集索引）
--补充：使用 CREATE INDEX 语句的 INCLUDE 子句，可以将索引键定义为 (Title, Revision)，将 FileName 定义为非键列。
--这样，索引键大小将为 110 个字节 (55 * 2)，并且索引仍将包含所需的所有列。下面的语句就创建了这样的索引。
USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
--一般很少人会在建索引的时候在多个字段上建立索引不管是聚集索引还是非聚集索引，因为会影响效率特别插入记录的时候
--CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice]。。。N个表字段)
--GO
--可以把经常被返回的列放到索引的include里面去，在不增加索引键大小的情况下尽可能覆盖尽可能多的列，
--这样当遇到某些查询，没有用到组合索引的先导列，但又感觉不值得为其建立索引时，若此查询用到得字段被组合索引实现了索引覆盖，
--可以进行非聚集索引扫描完成查询（当非聚集索引实现了索引覆盖时，进行非聚集索引扫描有着比聚集索引扫描更好的效率）。
--但是使用INCLUDE也有注意事项的，详细的可以看一下这篇文章
--SQL Server 索引include（具有包含性列的索引）
--http://ldbjakyo.iteye.com/blog/1546405
--个人意见：如果可以为聚集索引指定包含性列，那么非聚集索引就没有什么用了
--我测试了一下结果不能为聚集索引指定包含性列，应该是因为聚集索引把数据
--都存放到索引页（上面提到在有聚集索引的表格上数据是直接存放在索引的最底层的）
--所以这些包含性索引列不能随意添加，因为添加了会改变数据的存放顺序
--而非聚集索引就没有这个问题
--数据库索引过多的坏处 2024-1-16
--影响数据库写入和更新速度，当insert语句和update语句和delete语句， 需要同时写入到多个索引或者更新多个索引
--过多的索引也会影响优化器运行时间，并且不合适的索引会误导优化器，选择错误的索引，所以索引并不是越多越好。
