SQL Server索引如何使用时正确的?

数据库 SQL Server
本文主要向大家描述的是SQL Server索引的具体使用标准(Index Usage Criteria),下面就是文章的主要内容的详细描述。

此文章主要向大家探讨的是SQL Server索引的具体使用标准(Index Usage Criteria),在实际操作中我们大家为了其更为有效的决定创建哪些合适的SQL Server数据库索引,你必须决定这些索引实际中是否被SQL Server使用过。

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

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

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

  1. Sql代码   
  2. select stor_name from stores   
  3. where state = 'MD'   
  4. and zip = '21702'   
  5. select stor_name from stores   
  6. where state = 'MD' 
  7. and zip = '21702' 
  8. Sql代码   
  9. select stor_name from stores   
  10. where zip = '21702'   
  11. select stor_name from stores  
  12. where zip = '21702' 
  13.  

引用

注释:

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

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

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

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

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

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

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

引用

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

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

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

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

【编辑推荐】

  1. SQL Server磁带备份设备的性能优化过程
  2. SQL Server 2005商业智能功能浅析
  3. 优化SQL Server数据库的经验大盘点
  4. 创建SQL Server数据库更是实在
  5. SQL Server创建表所要用到的代码
责任编辑:佚名 来源: 中信出版社
相关推荐

2010-07-19 14:48:27

SQL Server索

2010-07-21 13:17:47

SQL Server表

2010-07-20 12:35:33

SQL Server索

2019-11-14 16:23:07

MySQL索引数据库

2010-07-20 08:56:37

SQL Server锁

2010-07-15 13:54:47

2010-07-07 13:39:33

SQL Server监

2010-07-08 16:52:31

SQL Server索

2010-06-29 17:52:02

SQL Server嵌

2011-03-18 14:54:52

SQL Server索引结构

2010-07-07 10:31:43

SQL Server数

2011-05-20 10:52:50

SQL Server 索引

2010-07-09 10:36:22

SQL Server

2010-07-19 11:35:05

2017-08-30 17:47:35

MySql索引

2010-07-07 09:47:04

SQL Server索

2022-09-22 22:51:32

Master数据库

2022-09-22 19:09:11

SQL Server数据库

2010-06-18 13:14:26

SQL Server

2010-07-20 13:02:03

SQL Server索
点赞
收藏

51CTO技术栈公众号