十步优化SQL+Server中的数据访问

来源：互联网发布：淘宝一元秒杀网址编辑：程序博客网时间：2024/05/01 03:16

十步优化SQL Server中的数据访问

图 2 使用SQL Server管理工作台创建索引

　　第二步：创建适当的覆盖索引

　　假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引，假设ProductID列是一个高选中性列，那么任何在where子句中使用索引列(ProductID)的select查询都会更快，如果在外键上没有创建索引，将会发生全部扫描，但还有办法可以进一步提升查询性能。

　　假设Sales表有10,000行记录，下面的SQL语句选中400行(总行数的4%)：　

SELECT SalesDate, SalesPersonIDFROM Sales WHERE ProductID = 112

　　我们来看看这条SQL语句在SQL执行引擎中是如何执行的：

　　1)Sales表在ProductID列上有一个非聚集索引，因此它查找非聚集索引树找出ProductID=112的记录;

　　2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值，即SalesID);

　　3)针对每一个主键(这里是400)，SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

　　SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

　　在上面的步骤中，对ProductID = 112的每个主键记录(这里是400)，SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate，SalesPersonID)。

　　如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,，SalesPersonID)的值，SQL Server引擎可能不会执行上面的第3和4步，直接从非聚集索引树查找ProductID列速度还会快一些，直接从索引页读取这三列的数值。

　　幸运的是，有一种方法实现了这个功能，它被称为“覆盖索引”，在表列上创建覆盖索引时，需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子：　

CREATE INDEX NCLIX_Sales_ProductID--Index name

　　ON dbo.Sales(ProductID)--Column on which index is to be created

　　INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

　　应该在那些select查询中常使用到的列上创建覆盖索引，但覆盖索引中包括过多的列也不行，因为覆盖索引列的值是存储在内存中的，这样会消耗过多内存，引发性能下降。

　　创建覆盖索引时使用数据库调整顾问

　　我们知道，当SQL出问题时，SQL Server引擎中的优化器根据下列因素自动生成不同的查询计划：

　　1)数据量

　　2)统计数据

　　3)索引变化

　　4)TSQL中的参数值

　　5)服务器负载

　　这就意味着，对于特定的SQL，即使表和索引结构是一样的，但在生产服务器和在测试服务器上产生的执行计划可能会不一样，这也意味着在测试服务器上创建的索引可以提高应用程序的性能，但在生产服务器上创建同样的索引却未必会提高应用程序的性能。因为测试环境中的执行计划利用了新创建的索引，但在生产环境中执行计划可能不会利用新创建的索引(例如，一个非聚集索引列在生产环境中不是一个高选中性列，但在测试环境中可能就不一样)。

　　因此我们在创建索引时，要知道执行计划是否会真正利用它，但我们怎么才能知道呢?答案就是在测试服务器上模拟生产环境负载，然后创建合适的索引并进行测试，如果这样测试发现索引可以提高性能，那么它在生产环境也就更可能提高应用程序的性能了。

　　虽然要模拟一个真实的负载比较困难，但目前已经有很多工具可以帮助我们。

　　使用SQL profiler跟踪生产服务器，尽管不建议在生产环境中使用SQL profiler，但有时没有办法，要诊断性能问题关键所在，必须得用，在http://msdn.microsoft.com/en-us/library/ms181091.aspx有SQL profiler的使用方法。

　　使用SQL profiler创建的跟踪文件，在测试服务器上利用数据库调整顾问创建一个类似的负载，大多数时候，调整顾问会给出一些可以立即使用的索引建议，在http://msdn.microsoft.com/en-us/library/ms166575.aspx有调整顾问的详细介绍。

　　第三步：整理索引碎片

　　你可能已经创建好了索引，并且所有索引都在工作，但性能却仍然不好，那很可能是产生了索引碎片，你需要进行索引碎片整理。

　　什么是索引碎片?

　　由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。

　　有两种类型的索引碎片：内部碎片和外部碎片。

　　内部碎片：为了有效的利用内存，使内存产生更少的碎片，要对内存分页，内存以页为单位来使用，最后一页往往装不满，于是形成了内部碎片。

　　外部碎片：为了共享要分段，在段的换入换出时形成外部碎片，比如5K的段换出后，有一个4k的段进来放到原来5k的地方，于是形成1k的外部碎片。

　　如何知道是否发生了索引碎片?

　　执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行，用你的数据库名替换掉这里的AdventureWorks)：

SELECTobject_name(dt.object_id) Tablename,si.name

　　IndexName,dt.avg_fragmentation_in_percent AS

　　ExternalFragmentation,dt.avg_page_space_used_in_percent AS

　　InternalFragmentation

　　FROM

　　(

　　SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

　　FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'

　　)

　　WHERE index_id<> 0)AS dt INNER JOIN sys.indexes siON si.object_id=dt.object_id

　　AND si.index_id=dt.index_idAND dt.avg_fragmentation_in_percent>10

　　AND dt.avg_page_space_used_in_percent<75ORDER BY avg_fragmentation_in_percentDESC

　　执行后显示AdventureWorks数据库的索引碎片信息。

图 3 索引碎片信息

　　使用下面的规则分析结果，你就可以找出哪里发生了索引碎片：

　　1)ExternalFragmentation的值>10表示对应的索引发生了外部碎片;

　　2)InternalFragmentation的值<75表示对应的索引发生了内部碎片。

　　如何整理索引碎片?

　　有两种整理索引碎片的方法：

　　1)重组有碎片的索引：执行下面的命令

　　ALTER INDEX ALL ON TableName REORGANIZE

　　2)重建索引：执行下面的命令

　　ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)

　　也可以使用索引名代替这里的“ALL”关键字重组或重建单个索引，也可以使用SQL Server管理工作台进行索引碎片的整理。