大数据量表中如何有效使用非聚集索引及如何避免使用LEFT JOIN

今天在查看一些过程，发现了点问题，和大家一起讨论一下：

--================================================================

一、如何有效利用索引，查询顾客编号＞的所有订单信息

--================================================================

--代替非聚焦索引扫描的方法

--此时因选择度过低而无法有效利用索引查找而选择表扫描

USE AdventureWorks;

GO

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue

FROM Sales.SalesOrderHeader

WHERE CustomerID>16000

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

--首先在非聚焦索引叶级中扫描获得最大及最小的订单号，由于此时表扫描只是在叶级所以I/O操作会明显减少

DECLARE @min int,

       @max int

SELECT @min=MIN(SalesOrderID),@max=MAX(SalesOrderID)

FROM Sales.SalesOrderHeader

WHERE CustomerID>16000

--使用聚焦索引进一步限制查询的条件，从而进行聚焦索引查找，找到合适的订单后再根据CustomerID过滤记录

SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue

FROM Sales.SalesOrderHeader

WHERE SalesOrderID BETWEEN @min AND @max

    AND CustomerID>16000

--==========================================================================================

--代替LEFT JOIN的方法，此方法在右表中对应左表记录少的情况下会显著提高查询性能

--如果左表数据在右表中基本都存在，则使用LEFT会更有效率

--为了防止在查询中出现LEFT操作，在设计数据库时应尽量避免关联表中不一致的情况出现

--比如产品的类别和具体产品的对应关系中，可在类别加入一个无类别的记录以对应不知是何类别的产品

--==========================================================================================

USE AdventureWorks;

GO

--1、查询所有客户的总订单数量

SELECT C.CustomerID,COALESCE(SUM(OrderQty),0) AS Quantity

FROM Sales.Customer C LEFT JOIN           

    Sales.SalesOrderHeader O ON C.CustomerID=O.CustomerID

    LEFT JOIN Sales.SalesOrderDetail D ON O.SalesOrderID=D.SalesOrderID

GROUP BY C.CustomerID

GO

DBCC FLUSHPROCINDB(8);

GO

DBCC DROPCLEANBUFFERS;

GO

--2、替代的查询方式

DECLARE @CustomerInfo table
(
 CustomerID int   not null,
 Quantity smallint not null
)

INSERT INTO @CustomerInfo
SELECT CustomerID,0
FROM Sales.Customer;

WITH SumQuantity AS
(
 SELECT O.CustomerID,COALESCE(SUM(OrderQty),0) AS Quantity
 FROM Sales.SalesOrderHeader O
 JOIN Sales.SalesOrderDetail D ON O.SalesOrderID=D.SalesOrderID
 GROUP BY O.CustomerID
)
UPDATE @CustomerInfo
SET C.Quantity=S.Quantity
FROM @CustomerInfo C JOIN SumQuantity S
 ON S.CustomerID=C.CustomerID;

SELECT * FROM @CustomerInfo;
GO

在对表进行联结时有三种方式：嵌套循环、合并联结及哈稀联结，所占用的资源依次增加，出现哈稀联结的情况是在所联结的字段上没有创建索引所导致的。下面是使用LEFT JOIN 时的查询计划，可以看到有一个哈稀联结，正是因为SalesOrderDetail与SalesOrderHeader联结的结果没有相应的索引，因此在与Customer表进行联结时出现了哈稀联结。

但在使用替代的方法后，没有了哈稀及对SalesOrderDetail与SalesOrderHeader表的扫描，因此查询成本比使用LEFT要提高9倍左右。因为过程中使用了表变更，会增加一些额外的I/O操作，如果在高速磁盘中，查询应该快于使用LEFT。
--==========================================================================================

--三、让存储过程正确的选择查询计划

--说来说去都是如何有效的使用非聚集索引的问题，下面的过程选择大于日期参数的所有订单信息

--但是因为选择度的问题，可能会因为缓存计划而误导了SQL查询优化器，因此请提示它正确选择

--如果你觉得对某参数的选择范围变化很大，而另一些参数的变化很小，请使用的新的语句级编译提示

--以减少对所有语句都重新编译带来的开销

--==========================================================================================

USE Northwind

GO

CREATE PROC dbo.usp_GetOrders

  @odate AS DATETIME

AS

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE OrderDate >= @odate

--OPTION(RECOMPILE);

GO

--选择度高的查询

EXEC dbo.usp_GetOrders '19980506';

--选择度低的查询

EXEC dbo.usp_GetOrders '19960101';

SELECT cacheobjtype, objtype, usecounts, sql

FROM sys.syscacheobjects

WHERE sql NOT LIKE '%cache%'

  AND sql LIKE '%usp_GetOrders%';

如果不加OPTION(RECOMPILE)，执行第一条语句时因选择度高使用非聚集索引查找可提高查询性能，因为这时有了缓存计划，在执行第二条语句是直接使用了缓存的计划，但因为选择度低，使用表扫描反而比索引查找更有效率。所以像这种参数对选择数据量有很大影响时，请使用查询提示。

 

http://www.cnblogs.com/tom-fu/archive/2008/03/07/1095060.html