怎样为你的SQLServer表选择索引

时间：2022-12-16 04:34:17

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址


索引是提高SQLServer性能最重要的元素之一。一个写得好的查询除非通过查询里表上适当的索引发威，否则就成了病猫，尤其是表很大的时候。
索引的存在是为了加快数据选择，所以本文将着眼于为加快数据获取而选择最好的索引的方法。分为两个步骤完成。

  • 第一步：收集信息
  • 第二步：根据收集的信息采取行动

如果你不熟悉你的数据库、表间的关系、存储过程里的查询和嵌入式SQL是怎么写的，那么创建合适的索引将是一个挑战。你曾经工作过的公司有几个数据库有正确的ERD图和规范的编程风格呢？现实是时间很有限，结果导致SQLServer的性能低下。
如果你曾经被分配去优化数据库性能（这至少要有很高的威望才行），或者你主动去解决数据库潜在的性能问题，按照这些步骤将有助于你去调优表，就象它们有助于我一样。这些步骤适用于工程的任何阶段，即使眼前是最终期限。


第一步（收集信息）


与了解数据库和表结构的人进行交流。如果你已了解这点，很好。这对你接下来轻松进行工作是很重要的。
1)分辨键表：

  • 静态表（通常称为主表）
  • 高事务的表
  • 大量存储过程或嵌入式SQL里使用的表
  • 索引尺寸大于表数据尺寸的表。可以使用sp_spaceused后跟表名来列出表的大小。

  • 10    个或15个最大的表。看看以前年份的数据库是否有用或适用。这是为了找出作为产品一段时间后数据库里最大的表。这里有一个脚本也许对你发现大表有用。

2)找出最频繁使用的存储过程或查询并列出它们使用的所有的表。
3) 用事件探查器收集的有：

  • 产品服务器（如果可用的话）。在产品服务器的典型活动期间运行跟踪是值得的，将来分析用得着。
  • 测试服务器（如有一个可用的话）
  • 另外，可以从开发服务器上收集

建议为了后来的收集将收集的信息写成文档。
4)在分析收集的信息之前，在调优你的表时有几件事情要记住：

  • 为了查看查询的执行计划，在查询分析器里选中它们，然后从查询菜单里选择显示估计执行计划（Crlt+L）。如果你要看存储过程的查询计划，选择显示执行计划（Crlt+K）然后执行存储过程。也可以打开“Set Statistics IO on“命令。检查查询执行计划会花费一点时间。但如果你在开始之前真正了解了数据库和表你会发现其实很容易。
  • 你对聚集索引和非聚集索引是如何工作的需要有一个好的基础。你可以查看这个URL来了解该主题

SQLServer的首选索引类型


当你使用查询分析器来产生一个图形化的执行计划时，你会注意到SQLServer有几个不同的使用索引的方法。
1) Clustered Index Seek: 聚集索引搜索使用索引的搜索功能直接总聚集索引里获取行。大多数情况下，它们在SELECT语句里提供最好的性能。
在查询分析器，选择pubs数据库，输入下面的查询：
SELECT * FROM authors WHERE au_id LIKE'2%'
选中该查询然后按Crtl+L或者F5。在估计执行计划的标签里你可以看到下面的图：

怎样为你的SQLServer表选择索引

仔细看看上图的Argument部分。注意使用了聚集索引UPKCL_auidind来返回数据。
2) Index Seek: 索引搜索使用非聚集索引来获取数据，在某些方面就行聚集索引一样。这是因为所有数据的获取是从非聚集索引的页层取得的，而不是从任何数据页。你经常会在覆盖索引看到这种情况。
在查询分析器，选择pubs数据库，输入下面的查询：
SELECT title_id, title FROM titles WHERE title LIKE 't%'
选中该查询然后按Crtl+L或者F5。在估计执行计划的标签里你可以看到下面的图：

怎样为你的SQLServer表选择索引

在上图的Argument部分，注意非聚集索引titleind用来获取数据。
3) Bookmark Lookup: 书签查找使用非聚集索引去选择数据。它开始在非聚集索引的页节点进行索引搜索以从数据页找到数据的位置，然后直接从数据页获取必要的数据。非聚集索引的页节点包含指向数据页上实际数据的行定位器。
在查询分析器，选择pubs数据库，输入下面的查询：
SELECT * FROM titles WHERE title LIKE 't%'
选中该查询然后按Crtl+L或者F5。在估计执行计划的标签里你可以看到下面的图：

怎样为你的SQLServer表选择索引

在Index Seek （索引搜索）的Argument部分，注意使用的是非聚集索引titlecind，但是一旦从非聚集索引的叶页里找到数据页的位置，然后就会执行Bookmark Lookup（书签查找）。此外，当查询优化器为了获取数据而不得不从数据页查找时就要使用Bookmark Lookup。在Bookmark Lookup的Argument部分，注意Bookmark Lookup使用的名称是"Bmk1000"。这个名称是查询优化器字段分配的。
4) Scans: 扫描（表扫描、索引扫描和聚集索引扫描）通常是不利的，除非表有很少的行并且查询优化器认为表扫描胜过使用可用的索引。在你的执行计划里观察一下扫描。
在查询分析器，选择pubs数据库，输入下面的查询：
SELECT * FROM employee WHERE hire_date > '1992-08-01'
选中该查询然后按Crtl+L或者F5。在估计执行计划的标签里你可以看到下面的图：

怎样为你的SQLServer表选择索引

注意这里执行了一个聚集索引扫描，这意味着不得不检查聚集索引里的每一行以完成查询的需求。
既然我们了解了一些怎样阅读查询执行计划的基础知识，那让我们来看看一些另外的信息，你会发现在为使用正确的索引而分析查询时是有用的：

  • 如果你在查询分析器里同时创建了多个查询或一个存储过程执行计划，那么你可以比较每个查询或存储过程的成本看看哪一个更有效。这对比较相同查询或存储过程的不同版本是有用的。
  • 如果表上不存在聚集索引并且在创建主键约束时没有指定非聚集索引，主键约束就会自动创建聚集索引。
  • 非聚集索引存储聚集索引的键作为它们的行定位器。通过创建覆盖索引（稍后解释），这个开销是值得的。使用覆盖索引需要小心。
  • 表的大小既包含表的数据也包含表上任何索引的大小。
  • 在表上添加太多的索引会增加表的整个索引的大小，常常会降低性能。
  • 为每一个表总是添加聚集索引，除非有正确的原因不支持那样做，象表只有很少的行。
  • 在查询执行计划里的探索表明避免扫描对SELECT语句的性能来说是有利的。
  • 表的大小（这里指行数）也是查询优化器在决定最好的查询计划时主要的因素。
  • 在很多查询计划里索引排序扮演了重要的角色。例如，在pubs数据库里的表authors，一个非聚集索引是按照先按au_lname排序后按au_fname排序来定义的。
    好的查询A
    SELECT * FROM authors WHERE au_lname like 'r%'
    这里使用书签查找和索引搜索。
    好的查询B
    select * FROM authors WHERE au_lname LIKE 'r%' AND au_fname like ‘a’
    这里使用书签查找和索引搜索。

    不那么好的查询C
    SELECT * FROM authors WHERE au_fname LIKE ‘a’
    这里使用聚集索引扫描。

 

  • SQLServer2000（以前的版本没有）提供了在索引里指定升序还是降序排序的功能。这对那些使用ORDER BY DESC子句的查询有用。
  • 为了在所有的存储过程代码里找到特定的单词（例如表名），你可以使用下面的代码。例如你可以使用它来找到使用一个表的所有存储过程。
    SELECT DISTINCT a.name AS SPName FROM syscomments b, sysobjects a
    WHERE b.text LIKE '%authors%' AND a.id=b.id AND a.type='P'

这个查询搜索代码里有表authors的所有存储过程。注意这个查询也列出一些不必要的存储过程，例如那些在注释里使用该表的存储过程。（译者注：其实可以用系统存储过程sp_MSdependencies来显示存储过程中使用的对象。）


第二步：一旦收集完必要的信息后所要做的


对键表采取的行动
对于静态表（很少更改的表），可以添加的索引数量是不受限制的。正如前面所述，太多的索引会降低高事务表的性能，但这条不适合那些数据不更改的表。可能仅考虑的一点是磁盘空间。为了最小化磁盘I/O以获得更好的性能，在静态表上设置所有索引的填充因子为100。
对于高事务的表来说，尽量限制索引的数量。总是记住非聚集索引包含了聚集索引键。因此，为了保持小的尺寸，限制聚集索引上的列数量。事务繁忙的表的任何索引都必须有很高的理由才能创建。为了避免潜在的页拆分小心为这些索引选择填充因子（通常在80到90％之间）。
对于在存储过程或嵌入式SQL里使用的表来说，由于它们频繁地被调用，所以这些表在整个应用程序生命周期里扮演了重要的角色。所以要求对它们特别的注意。重要的是看看查询里的表是怎样的方式访问的以便消除扫描并把它们转化为搜索。使用命令Set Statistics IO on查看逻辑I/O可以帮助你查看哪个查询访问了最多的数据。磁盘逻辑I/O越少越好。要小心选择聚集索引。依赖表的处理方式，选择一个较高的填充因子。
对于那些索引尺寸大于数据尺寸的表来说意味着表有大量的索引，所以检查索引并确保它们的存在是有用的合理的。
对于前10或15个最大的表来说，记住这样的事实，当为这类型的表创建索引时，它们的索引会很大。也检查一下这些表是静态还是非静态的，这有助于决定什么列需要索引。
对于最频繁调用的存储过程或嵌入式SQL来说，看看查询计划和逻辑I/O页的使用。
SQL事件探查器是一个很好的工具。它跟踪SQLServer里执行的调用的任何给定的时间、执行时长、I/O读、登录用户、执行的SQL语句等等。它也能用做调试工具。对于分辨运行慢的查询来说分析跟踪是重要的。你可以设置duration>100ms来查看那些执行超过100毫秒的查询。
使用覆盖索引加使用聚集索引作为行定位器的非聚集索引
一个可以利用的事实是非聚集索引存储聚集索引键作为它们的行定位器。这意味着在查询的SELECT列表、WHERE子句和JOIN条件里引用的所有列上有索引的话，这个非聚集索引可以用做聚集索引。
示例1
在NorthWind数据库的表Orders里，在列ShippedDate上有一个非聚集索引。
试着运行下面的语句：
SELECT ShippedDate, shipcity FROM orders WHERE ShippedDate > '8/6/1996'
查询计划会使用一个聚集索引扫描。
现在在ShippedDate上的非聚集索引里添加列shipcity。
CREATE INDEX [ShippedDate] ON [dbo].[Orders] ([ShippedDate], [ShipCity]) WITH DROP_EXISTING
现在重新运行查询。这时查询计划使用了索引搜索。
这个神奇的事情是因为在SELECT和WHERE子句里的所有列（ShippedDate和ShipCity）都作为了索引的一部分。
示例2
在pubs数据库的表Titles里，为下面的查询检查执行计划：
SELECT title_id, title FROM titles WHERE title LIKE 't%'
注意执行计划显示一个索引搜索，而不是书签查找（你经常会在非聚集索引上发现）。这是因为Title列上的非聚集索引包含了聚集索引键Title_ID，并且这个SELECT语句仅在SELECT和WHERE子句里有Title_ID和Title字段。


现在艰难的部分才开始


通过执行这里列出的简单的步骤，在任何时候你都可以得到有助于你提升数据库性能的有用的信息。在你进行调优工作之前有数据之后，这些小的投入会带来大的效果。

标签：

相关文章