SQL Server索引的正确使用标准是啥样?

数据库 SQL Server
以下的文章主要描述的是SQL Server索引的正确使用标准(Index Usage Criteria),以及对其实际应用代码的描述。

文章主要描述的是SQL Server索引的正确使用标准(Index Usage Criteria),在实际操作中为了有确定性的决定哪些SQL Server索引应该创建,我们一定要决定这些索引实际中是否被SQL Server使用过。

如果一个索引不能被有效使用,在修改数据时,那只会浪费空间和增加不必要的负担。

需要记住的主要标准是:如果至少是索引的***列没有被包含在一个有效的搜索参数(search argument SARG)或join子句中,那么SQL Server 就不会使用索引进行更有效地书签查找(bookmark lookup)。为创建复合SQL Server索引,选择列的顺序时牢记住这一点,想想下面的在store表中的索引:

Create index nc1_stores on stores (city, state, zip)

 

下面的每一个查询将会用到索引,因为它们包含了SQL Server索引的***列city,其为一个SARG:

 

Sql代码

 

  1. select stor_name from stores   
  2. where city = 'Frederick'   
  3. and state = 'MD'   
  4. and zip = '21702'   
  5. select stor_name from stores   
  6. where city = 'Frederick' 
  7. and state = 'MD' 
  8. and zip = '21702' 
  9. Sql代码   
  10. select stor_name from stores   
  11. where city = 'Frederick'   
  12. and state = 'MD'   
  13. select stor_name from stores  
  14. where city = 'Frederick' 
  15. and state = 'MD' 
  16. Sql代码   
  17. select stor_name from stores   
  18. where city = 'Frederick'   
  19. and zip = '21702'   
  20. select stor_name from stores  
  21. where city = 'Frederick' 
  22. and zip = '21702' 

然而,下面的查询不会用到索引而进行书签查找,因为它们没指定city列为一个SARG:

Sql代码

 

  1. select stor_name from stores   
  2. where state = 'MD'   
  3. and zip = '21702'   
  4. select stor_name from stores   
  5. where state = 'MD' 
  6. and zip = '21702' 

Sql代码

  1. select stor_name from stores   
  2. where zip = '21702'   
  3. select stor_name from stores  
  4. where zip = '21702' 

引用

 

注释:

 

对于前面提到的***两个查询,如果你显示执行计划(execution plan)信息,你可能发现,查询实际上使用了nc1_store索引来检索了结果集(resultset)。如果再仔细看,你会发现查询没有使用索引最有效地方式——它使用了索引扫描(index scan),而不是索引查找(index seek)。

有关查询存取方法(query aceess method)的更多信息,可参见第35章“Understanding Query Optimization”,在该章中将讲述索引查找。

在索引查找(Index seek)中,SQL Server 沿着索引树(index tree)从根级(root level)向下进行索引键值匹配搜索,直到搜索到指定的行,然后使用存储在SQL Server索引键值中的书签值(bookmark value)直接从数据页中检索匹配的数据行(这个书签值可以是行标识符(RID),或者聚集索引的键值)。

对一个索引扫描(Index scan),SQL Server搜索索引树中所有叶级(leaf level)中的行来进行可能匹配的查找。如果发现满足匹配的行,然后利用书签检索数据行。

尽管两者都使用了索引,从I/O代价角度来讲,索引扫描比索引查找的代价要高,但比表扫描(Table scan)要略微要小些。然而,本章学习设计索引的目的是为了使用索引查找,所以当我谈到使用索引时,指的是索引查找。

 

为了得到可能列的书签查询,你可能想到的一个容易的方法是在表中所有列上都创建索引,这样任何类型的查询都可以使用索引了。这种策略可能在某些支持ad hoc queries(随意的查询)的只读的DSS(决策支持系统)环境下是合适的,但是这样也存在问题,因为仍然会造成有许多索引不被使用。

 

正如你在本章的Index selection节看到的,不会仅仅因为在某列创建了索引,优化器就总会使用该列的索引,例如,当该列的选择性不够时(not selective enough),就不会使用该列的索引。另外,在一张大表(large table)上创建太多索引会占据数据库中的大量空间,增加了备份的要求时间。前面也提到过,在一个OLTP(在线联机处理)系统上,太多的索引会给数据的插入、修改、删除操作带来大量的额外负担,造成性能上的不利影响。

引用

 

建议:(每张表4-5个索引)

我曾经常犯的一个设计错误是在OLTP环境下定义了太多的索引。许多情况下,有些SQL Server索引是冗余的或者是优化器在处理查询时就根本没有考虑。结果,这些索引导致空间的浪费和增加了修改数据时的不必要负担。

在这一点上有一个案例,有个客户在一个表上创建了8个索引,其中4个索引都是在同一列上,该列的键值唯一(unique key),在索引中该列都是***个索引列。对表的查询和修改操作,该列都包含在where 子句中。结果只有4个的其中1个索引曾被用到过。

希望在本章结束后,你将会理解为什么所有这些SQL Server索引不是必须的,并且能重新认识和决定在哪些列上创建索引将会收益,而哪些列上应避免创建索引。

【编辑推荐】

  1. SQL Server数据库在安装时的注意事项
  2. SQL Server 2005数据库安装实例演示
  3. SQL Server 2000全文检索的使用方案描述
  4. SQL Server数据库与identity列
  5. SQL Server 实用操作的代码演示

 

责任编辑:佚名 来源: 清华大学出版社
相关推荐

2010-07-07 10:25:00

SQL Server索

2010-07-07 09:47:04

SQL Server索

2010-07-21 13:17:47

SQL Server表

2010-07-20 12:35:33

SQL Server索

2010-06-29 17:52:02

SQL Server嵌

2010-07-15 13:54:47

2010-07-08 16:52:31

SQL Server索

2010-07-07 13:39:33

SQL Server监

2010-07-07 10:31:43

SQL Server数

2011-05-20 10:52:50

SQL Server 索引

2011-03-18 14:54:52

SQL Server索引结构

2010-07-19 11:35:05

2010-07-09 10:36:22

SQL Server

2010-07-14 10:43:00

SQL Server

2010-06-18 13:14:26

SQL Server

2010-10-20 11:07:21

sql server全

2010-07-20 12:53:50

SQL Server索

2010-07-20 13:02:03

SQL Server索

2010-07-23 10:36:43

SQL Server内

2009-06-08 09:22:07

数据访问优化SQL Server
点赞
收藏

51CTO技术栈公众号