怀疑一切，但不否定一切

posts(21) comments(78) trackbacks(4)
  • 博客园
  • 联系
  • 订阅 订阅
  • 管理

与我联系

  • 发短消息

搜索

 

常用链接

  • 我的参与
  • 我的新闻
  • 最新评论
  • 我的标签

留言簿

  • 给我留言
  • 查看公开留言
  • 查看私人留言

随笔分类

  •  iBatis(1)
  •  Notification Service(1)
  •  SQL Server 性能优化(12)
  •  SQLServer2005小结(2)

随笔档案

  • 2008年8月 (2)
  • 2008年7月 (1)
  • 2008年6月 (5)
  • 2008年5月 (3)
  • 2008年4月 (4)
  • 2008年3月 (3)
  • 2007年11月 (1)
  • 2007年4月 (1)

最新评论

  • 1. re: 使用ad hoc的利弊
  • @Cheney Shue 多谢，那我不太明白sys.dm_exec_cached_plans里关于ad hoc的说明到是什么意思了？如果在.NET程序中去调用那些SQL语句，所得到的结果和在SSMS中...
  • --凉面
  • 2. re: 使用ad hoc的利弊
  • ad-hoc确实不是这样的。下面是adhoc的定义，从wiki上复制过来的：Ad hoc querying is a term in information science.Many applicat...
  • --Cheney Shue
  • 3. re: 使用ad hoc的利弊
  • 即席查询是能够进行参数嗅探的，而一般的卸载程序内的sql语句也不会变动大小写和空格啥的，都能被数据库嗅探到，而成为一个编译计划的。 最近发现一个写sql语句不太爽的地方就是不方便管理，在数据库内可以...
  • --PerfectDesign
  • 4. re: 使用ad hoc的利弊
  • 从SSMS命令窗口发送的SQL语句就是ad hoc？你用过ad hoc工具吗？
    文不对题，明明说的是使用参数绑定避免硬解析，跟ad hoc没关系。
  • --唐开礼
  • 5. re: 磁盘I/O的性能评估方法
  • 请教下I/O问题
    MSN: jxtu@msn.com
  • --_chill

阅读排行榜

  • 1. 数据库分页操作(2206)
  • 2. 为什么尽量避免使用触发器？(2168)
  • 3. 生成器工作内幕分析(1811)
  • 4. 为SQLSERVER打好地基－硬盘碎片和索引碎片(1720)
  • 5. 结果集大小如何影响并发性(1634)

评论排行榜

  • 1. 为什么尽量避免使用触发器？(17)
  • 2. 为SQLSERVER打好地基－硬盘碎片和索引碎片(16)
  • 3. 结果集大小如何影响并发性(13)
  • 4. 数据库分页操作(10)
  • 5. 如何编写高效的存储过程(8)

View Post

如何有效利用索引编写高效过程

USE AdventureWorks;
GO
--主键只用来保证数据，而聚集索引影响数据的逻辑排序
--下面的语句执行计划中并没有看到排序操作，证明数据是按SalesOrderID排过序的，而且是一种双向链表
SELECT TOP(10) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderID
SELECT TOP(10) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderID DESC

SELECT TOP(10) SalesOrderID FROM Sales.SalesOrderHeader ORDER BY SalesOrderID
SELECT TOP(10) SalesOrderID FROM Sales.SalesOrderHeader

--======================================================================
--查看表内数据是如何存储的
--======================================================================
SELECT object_name(p.object_id) as tablename,i.name as indexname,
    rows,a.type_desc as page_type_desc,
    total_pages as pages,first_page
FROM sys.partitions p
    JOIN sys.system_internals_allocation_units a
        ON p.partition_id = a.container_id
    JOIN sys.indexes i 
        ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE p.object_id=object_id(N'Sales.SalesOrderHeader')

--查看所含的pages字段最多的则为聚集索引叶级，其余的为别的索引页
--0xA01400000100得知第一页为5280，此为聚集索引叶的第一页
DBCC TRACEON(3604)
GO
DBCC PAGE(AdventureWorks,1,5280,3)--查看第4～7字节的值是按照SalesOrderID从小到大排列的
GO
--查看IX_SalesOrderHeader_CustomerID索引的第一页的第一个值是否为1
--0x105200000100得到第一页为21008
DBCC PAGE(AdventureWorks,1,21008,1)
GO


--======================================================================
--行长度的限制8060
--======================================================================
USE tempdb;
GO
IF OBJECT_ID(N'bigrows',N'U') IS NOT NULL
    DROP TABLE dbo.bigrows
GO
CREATE TABLE dbo.bigrows
(
    a char(3000),
    b char(3000),
    c char(2000),
    d char(60)
)
GO
INSERT INTO dbo.bigrows
    SELECT REPLICATE('a',3000),REPLICATE('b',3000),
            REPLICATE('c',2000),REPLICATE('d',6)
--2005使用行溢出数据突破了変长列的限制，但这样会增加I/O操作。
IF OBJECT_ID(N'bigrows',N'U') IS NOT NULL
    DROP TABLE dbo.bigrows
GO
CREATE TABLE dbo.bigrows
(
    a varchar(3000),
    b varchar(3000),
    c varchar(3000),
    d varchar(3000)
)
GO
INSERT INTO dbo.bigrows
    SELECT REPLICATE('a',3000),REPLICATE('b',3000),
            REPLICATE('c',3000),REPLICATE('d',3000)
SELECT object_name(object_id) as name,
    partition_id,partition_number as pnum,rows,
    allocation_unit_id as au_id,type_desc as page_type,total_pages as pages
FROM sys.partitions p    
    JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
WHERE object_id = object_id(N'dbo.bigrows');
--======================================================================
--非聚集索引通过聚集索引进行查找
--======================================================================
--SQL2005新增用于查看表中各索引的统计的动态函数
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), OBJECT_ID(N'Sales.SalesOrderHeader'), NULL, NULL , 'DETAILED');
--索引的深度
SELECT INDEXPROPERTY(object_ID('Sales.SalesOrderHeader'),N'PK_SalesOrderHeader_SalesOrderID','IndexDepth')
SELECT INDEXPROPERTY(object_ID('Sales.SalesOrderHeader'),N'AK_SalesOrderHeader_SalesOrderNumber','IndexDepth')
--聚集索引查找，因为索引的深度为3，所以此时逻辑读为3
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID=43659
--非聚集索引通过聚集索引进行查找(逻辑读有5次，因为聚集为3，非聚集为2)
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderNumber=N'SO45283'

--======================================================================
--复合索引测试
--======================================================================
BEGIN TRANSACTION
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
    (
    CustomerID,
    CreditCardID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

UPDATE STATISTICS Sales.SalesOrderHeader IX_SalesOrderHeader_CustomerID
--查看此索引的统计信息
DBCC SHOW_STATISTICS('Sales.SalesOrderHeader',IX_SalesOrderHeader_CustomerID)

SELECT COUNT(*) --EQ_ROWS等于上限值的个数
FROM Sales.SalesOrderHeader
WHERE CustomerID=1

SELECT COUNT(*) --RANGE_ROWS此范围内不包括边界的记录数
FROM Sales.SalesOrderHeader
WHERE CustomerID>1 AND CustomerID<19

SELECT  COUNT(DISTINCT CustomerID)--DISTINCT_RANGE_ROWS 此范围内不包括边界的不同记录数
FROM Sales.SalesOrderHeader
WHERE CustomerID>1 AND CustomerID<19

SELECT  1.0*COUNT(*)/COUNT(DISTINCT CustomerID)--AVG_RANGE_ROWS 数据分布程度
FROM Sales.SalesOrderHeader
WHERE CustomerID>1 AND CustomerID<19
--查看Sales.SalesOrderHeader表中索引信息
SELECT * FROM 
sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), OBJECT_ID(N'Sales.SalesOrderHeader'), NULL, NULL , 'DETAILED');
--IX_SalesOrderHeader_CustomerID的索引编号为5深度为2，叶级有71个页面，根叶1页
SELECT QUOTENAME(name)
FROM sys.indexes
WHERE  object_id = 722101613 AND index_id = 5;

DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
--非聚集索引查找+键值查找(逻辑读为8，非聚集索引查找2页加每个值一次聚集索引查找)
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID=19965
--非聚集索引扫描+键值查找(因此使用复合索引除第一个字段外，不会进行索引查找)
SELECT * FROM Sales.SalesOrderHeader WHERE CreditCardID=15466
--覆盖索引查找(逻辑读为2，说明只在非聚集索引中查找到了所需的数据)
SELECT SalesOrderID,CreditCardID FROM Sales.SalesOrderHeader
WHERE CustomerID=19965

--作为查询条件的字段会自动创建统计
SELECT * FROM Production.Location WHERE ModifiedDate>'20070101'
--所有自动创建的统计
SELECT m.name+'.'+object_name(s.object_id) as [table],s.name as [statistics],c.name as [column]
FROM sys.stats s
    join sys.stats_columns sc
        on s.stats_id = sc.stats_id and s.object_id = sc.object_id
    join sys.columns c 
        on sc.column_id = c.column_id and c.object_id = sc.object_id
    join sys.objects o 
        on s.object_id = o.object_id
    join sys.schemas m
        on m.schema_id = o.schema_id
where s.auto_created=1 and o.type='U'

SELECT object_name(id),name,statblob--统计信息二进制大型对象(BLOB)，仅内部使用，返回NULL.
FROM sys.sysindexes

--SELECT * FROM sys.stats_columns
--====================================================================================
--选择度过低而无法有效利用索引查找而选择表扫描，查询2004-7-01之后的订单
--====================================================================================
USE AdventureWorks;
GO
BEGIN TRANSACTION
GO
DROP INDEX IX_OrderDate ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_OrderDate ON Sales.SalesOrderHeader
    (
    OrderDate
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
--分别使用2004-7-01低选择度与2004-7-26高选择度比较两种查询的性能差异
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate>'2004-7-01'

--首先在非聚焦索引叶级中扫描获得最大及最小的订单号，由于此时表扫描只是在叶级所以I/O操作会明显减少
DECLARE @min int,
        @max int
SELECT @min=MIN(SalesOrderID),@max=MAX(SalesOrderID) 
FROM Sales.SalesOrderHeader
WHERE OrderDate>'2004-7-01'
--使用聚焦索引
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN @min AND @max
GO
--================================================
--什么是查询参数规范SARGS
--================================================
--1、不要对字段做运算
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID+1=2
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID=1
--2、不要对字段使用函数(数学函数、日期函数、字符串函数等)
SELECT * FROM Sales.SalesOrderHeader WHERE ABS(SalesOrderID-44659)<1
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID<44660 AND SalesOrderID>44658
--3、不要使用负向查询(NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN)
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty!=1
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty>1 OR OrderQty<1
--4、小心使用OR(虽然有聚集索引但是因Status无索引可用只能进行表扫描)
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID=43659 OR [Status]=1

--================================================================================
--三种联结(嵌套循环、合并联结、哈稀联结)，所占用的资源依次增加，SQL优先选用嵌套循环
--================================================================================
--1、嵌套循环(外表、内表)，记录少的表作为外表，在内表中根据每个联结字段的值进行
--循环，只有在外表数据量较少时使用
SELECT *
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader O
    ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 43665
--2、合并联结的算法前提是字段已排序
SELECT *
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader O
    ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 54000
--3、哈稀联结(在要联结的字段上无可用索引时使用此连接算法)
IF OBJECT_ID(N'SumPrice',N'U') IS NOT NULL
    DROP TABLE SumPrice
GO
SELECT O.SalesOrderID,YEAR(O.OrderDate)+MONTH(O.OrderDate) AS Date,CAST(SUM(D.OrderQty*D.UnitPrice*(1-D.UnitPriceDiscount)) AS decimal(18,2)) AS Price
INTO SumPrice
FROM 
    Sales.SalesOrderDetail D 
        JOIN 
    Sales.SalesOrderHeader O
        ON D.SalesOrderID=O.SalesOrderID
GROUP BY O.SalesOrderID,O.OrderDate
GO
SELECT O.SalesOrderID,O.
FROM SumPrice D JOIN Sales.SalesOrderHeader O
    ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 54000

--==========================================================================================
--代替LEFT JOIN的方法
--为了防止在查询中出现LEFT操作，在设计数据库时应尽量避免关联表中不一致的情况出现
--比如产品的类别和具体产品的对应关系中，可在类别加入一个无类别的记录以对应不知是何类别的产品
--==========================================================================================
BEGIN TRANSACTION
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
    (
    CustomerID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
DBCC FLUSHPROCINDB(8);
GO
DBCC DROPCLEANBUFFERS;
GO
--1、查询所有客户的总订单数量
SELECT C.CustomerID,COALESCE(SUM(OrderQty),0) AS Quantity
FROM Sales.Customer C LEFT JOIN            
    (    Sales.SalesOrderHeader O 
        JOIN Sales.SalesOrderDetail D ON O.SalesOrderID=D.SalesOrderID
    ) ON C.CustomerID=O.CustomerID
GROUP BY C.CustomerID
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

--==========================================================================================
--控制表之间的联结
--==========================================================================================
USE Northwind;
GO
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON O.CustomerID = C.CustomerID
  LEFT OUTER JOIN dbo.[Order Details] AS OD
    ON OD.OrderID = O.OrderID
  LEFT OUTER JOIN dbo.Products AS P
    ON P.ProductID = OD.ProductID
  LEFT OUTER JOIN dbo.Suppliers AS S
    ON S.SupplierID = P.SupplierID
--OPTION(FORCE ORDER);
--上面的查询过多的使用外联结，可使用下面的方式替换
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Orders AS O
  JOIN dbo.[Order Details] AS OD
    ON OD.OrderID = O.OrderID
  JOIN dbo.Products AS P
    ON P.ProductID = OD.ProductID
  JOIN dbo.Suppliers AS S
    ON S.SupplierID = P.SupplierID
  RIGHT OUTER JOIN dbo.Customers AS C
    ON O.CustomerID = C.CustomerID;

SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Customers AS C
  LEFT OUTER JOIN
    (dbo.Orders AS O
       JOIN dbo.[Order Details] AS OD
         ON OD.OrderID = O.OrderID
       JOIN dbo.Products AS P
         ON P.ProductID = OD.ProductID
       JOIN dbo.Suppliers AS S
         ON S.SupplierID = P.SupplierID)
      ON O.CustomerID = C.CustomerID;


--============================================================
--存储过程相关
--============================================================
USE Northwind;
GO
--***********************************************************
--1、计划缓存重用造成的无效查询计划
--造成上述问题的原因是因为SQLServer为了节省编译的开销会优先考虑使用缓存的计划
--2000下只能使用过程级重新编译，2005下提供了语句级重新编译的特性。从而避免了编译
--整个过程的开销
IF OBJECT_ID(N'dbo.usp_GetOrders',N'P') IS NOT NULL
  DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
  @odate AS DATETIME
AS

SELECT c.CustomerID, c.CompanyName,
    o.EmployeeID, o.OrderID, o.OrderDate
FROM dbo.Orders o
    join dbo.Customers c
        on o.CustomerID = c.CustomerID
WHERE OrderDate >= @odate;
--OPTION(RECOMPILE);
GO
--选择度高的测试
EXEC dbo.usp_GetOrders '19980506';
--选择度低的测试
EXEC dbo.usp_GetOrders '19960101';
--查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
  AND sql LIKE '%usp_GetOrders%';
--清除缓存计划
DBCC FREEPROCCACHE;
GO
--***********************************************************
--2、参数嗅探测
--以当前日期插入一条记录，高选择度查询
INSERT INTO dbo.Orders(OrderDate, CustomerID, EmployeeID) VALUES(GETDATE(), N'ALFKI', 1);
GO
ALTER PROC dbo.usp_GetOrders
  @d AS INT = 0
AS
DECLARE