技术开发 频道

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

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

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

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

SELECT SalesDate, SalesPersonID FROM 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有调整顾问的详细介绍。

2
相关文章