`
yanghuidang
  • 浏览: 946902 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

SQL Server数据库性能优化之索引篇

 
阅读更多

近期项目需要,做了一段时间的SQL Server性能优化,遇到了一些问题,也积累了一些经验,现总结一下,与君共享。SQL Server性能优化涉及到许多方面,如良好的系统和数据库设计,优质的SQL编写,合适的数据表索引设计,甚至各种硬件因素:网络性能、服务器的性能、操作系统的性能,甚至网卡、交换机等。这篇文章主要讲到如何改善索引,还将有另一篇讨论如何改善SQL语句。

首先需要强调一下,水能载舟,亦能覆舟。建立“适当”的索引是实现查询优化的首要前提。
当根据索引码的值搜索数据时,索引提供了对数据的快速访问。事实上,没有索引,数据库也能根据SELECT语句成功地检索到结果,但随着表变得越来越大,使用“适当”的索引的效果就越来越明显。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以,要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使数据库能得到高性能的发挥。

简述SQL Server的索引

SQL Server提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。
非聚集索引与课本中的索引类似。数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储,而表中的信息按另一种顺序存储(这可以由聚集索引规定)。如果在表中未创建聚集索引,则无法保证这些行具有任何特定的顺序。
更详细的介绍请参考MSDN上关于索引的介绍。http://msdn.microsoft.com/zh-cn/library/ms189271.aspx

使用SQL Server的索引

问题又来了,既然分了两种索引,何时何种情况用何种索引?那就看看下表吧。简单的说就是:对于小数目的不同值,或列经常被分组排序,或需要返回某范围内的数据时使用聚集索引;对于大数目的不同值,或列经常被分组排序,或列被频繁更新时使用非聚集索引。

使用聚集索引

使用非聚集索引

列经常被分组排序

返回某范围内的数据

不应

一个或极少不同值

不应

不应

小数目的不同值

不应

大数目的不同值

不应

频繁更新的列

不应

外键列

主键列

频繁修改索引列

不应


如何改善索引的一些经验:

1.索引首先要满足你的应用中最关键或者是被很多用户频繁执行的查询。

若某个查询每月仅执行一次,要考虑是否值得为其涉及表创建了索引。要知道在当月的其它时间数据库系统对该索引的维护开销是要超过满足该查询的表扫描的开销的。所以,好钢用在刀刃上,好索引用在关键频繁的查询上。

2.在经常进行连接,但是没有指定为外键的列上建立索引。

在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如下面的一条SQL,连接这两个表:tblA(id, c1, c2, …)和tblB(id, …),就需要分别在两个表的id字段上建立索引。

selecta.id,a.c1,a.c2,…fromtblAawhereexists(select1fromtblBbwhereb.id=a.id)

3.排序或分组对索引的影响

· 在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。如果待排序的列有多个,可以在这些列上建立复合索引。
· 索引中一定要包含所有的group by或order by操作列,且group by或order by操作列中列的次序一定要与索引中的次序相同。
· 简化或避免对大数据量表的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。
· 排序的列若来自不同的表,同样会在执行计划中引起一个排序的开销。为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。

4.非黑即白就别索引了

在条件表达式中经常用到的不同值较多的列上建立非聚集索引,在不同值很少的列上就不要建立索引了。比如在某表的“状态”列上只有“是”与“否”两个不同值,就没必要建了,因为此时表扫描来得更有效。若建了索引,不但查询效率没提高,反而严重降低了更新速度。

5.“索引覆盖”是怎样炼成的

对某个表tblA建了个聚集索引tblA_idx(c1, c2, c3)。这样实际上是建立了三个索引:(c1), (c1, c2), (c1, c2, c3)。

selectmin(c1)fromtblAwherec1>1--会触发ClusteredIndexSeek。
selectmin(c1)fromtblAwherec1>1andc2=2--会触发ClusteredIndexSeek。
selectmin(c1)fromtblAwherec1>1andc3<3--会触发ClusteredIndexScan。
selectmin(c1)fromtblAwherec2=2andc3<3--会触发ClusteredIndexScan。
selectmin(c1)fromtblAwherec1>1andc2=2andc3<3--会触发ClusteredIndexSeek,且形成了索引覆盖。

其中Clustered Index Scan的执行计划


其中Clustered Index Seek的执行计划


如此可见形成索引覆盖的必要性。

6.非聚集索引与精确查找的默契

对于某个表中的某个字段存在大数目的不同值时,为该字段建个非聚集索引会达到意想不到的效果。因为数据库系统在搜索数据值时,先对非聚集索引进行搜索,找到数据值在表中的位置,然后从该位置直接检索数据。因为索引包含描述查询所搜索的数据值在表中的精确位置的条目,这也是为什么非聚集索引是精确匹配查询的最佳方法。例如,在employee表为emp_id列建了非聚集索引,要搜索其雇员ID (emp_id) > 1000的所有人,SQL Server会在索引中直接跳到emp_id = 1000这样一个条目之后,列出匹配的emp_id列在表中的页和行,然后直接转到该页该行。

7.如果你是皮尔斯,SQL Server 的执行计划就是朗多

SQL Server 2005的Microsoft SQL Server Management Studio和Database Engine Tuning Advisor(DETA)是非常好的性能调试助手,可以使用它们对SQL语句调优,查看估计的执行计划开销,用DETA生成优化建议,采纳或参考索引优化部分。
需要注意的是,对于估计的执行计划,不要过于关注里面显示的开销比例,而实际上这个有时会误导。我在实际优化过程中就被发现,一个index scan的执行项开销只占25%,另一个键查找的开销占50%,而键查找部分根本没有可优化的,SEEK谓词就是ID=XXX这个建立在主键上的查找。而仔细分析可以看到,后者CPU开销0.00015,I/O开销0.0013。而前者呢,CPU开销1.4xxxx,I/O开销也远大于后者。因此,优化重点应该放在前者。
网上这类的文章很多,这里就不做赘述了。可以参考一篇较早的文章:SQL Server性能调优入门(图文版)

另外还有一篇不错的文章,共享在这里:探讨如何在有着1000万条数据的MS SQL SERVER数据库中实现快速的数据提取和数据分页


博文来源:http://www.blogjava.net/allen-zhe/archive/2010/07/23/326966.html

分享到:
评论

相关推荐

    SQLServer数据库性能优化

    【SQLServer数据库性能优化】 数据库性能优化是提升系统效率的关键环节,尤其是在SQL Server这样的大型企业级数据库管理系统中。本文主要探讨了如何从数据库设计和硬件系统两个层面来优化SQL Server的性能。 首先...

    SqlServer数据库性能优化详解

    ### SqlServer数据库性能优化详解 #### 一、性能优化的重要性 在现代企业的信息化建设中,数据库作为信息系统的核心组件,其性能直接影响着应用系统的整体表现。性能优化的目标是通过减少网络流量、磁盘I/O操作...

    SQL Server数据库性能优化.doc

    为了提高 SQL Server 数据库的性能,我们需要从多方面入手,包括数据库设计、硬件系统设计、查询优化、索引优化、锁机制优化等。 数据库设计是影响 SQL Server 数据库性能的关键因素。一个好的数据库设计可以提高...

    SQLServer数据库性能优化[借鉴].pdf

    SQL Server 数据库性能优化技巧 设计一个应用系统似乎并不难,但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库...只有通过结合实际情况,选择合适的方法和技术,才能实现系统的最优化性能。

    SQL SERVER数据库性能优化探讨.pdf

    "SQL SERVER数据库性能...SQL Server数据库性能优化是数据库管理员和开发人员的主要任务之一。通过各种手段和方法来提高 SQL Server 数据库的性能,可以提高应用程序的响应速度和吞吐量,提高用户体验和企业的竞争力。

    SQLSERVER数据库性能优化分析

    ### SQLSERVER数据库性能优化分析 #### 一、问题分析 **1.1 死锁** 在SQL Server数据库中,死锁是一种常见的性能问题。当多个事务互相等待对方释放资源时,就会形成死锁。例如,进程A锁定了资源B,进程B锁定了...

    SqlServer性能优化高效索引指南.pdf

    Sql Server性能优化高效索引指南是指在Sql Server数据库中,通过合理地设计和优化索引来提高数据库性能的一系列指南和最佳实践。本指南涵盖了索引的基本概念、索引的类型、索引的设计原则、索引的优化方法、索引的...

    SQLServer数据库优化之50种方法

    本文将根据给定的信息,详细阐述部分重要的SQL Server数据库优化方法,并对这些方法进行深入解读。 #### 1. 避免不必要的查询 - **概述**:避免执行那些实际上并不需要的查询操作,比如在编写存储过程或触发器时,...

    医院HIS系统SQLServer数据库性能优化.pdf

    "医院HIS系统SQLServer数据库性能...医院HIS系统SQLServer数据库性能优化是指通过调整规则、数据库服务器的配置优化、数据库索引优化、数据库查询优化、数据库备份和恢复优化等方面来提高数据库服务器的性能和可靠性。

    SQLServer数据库性能优化技术

    本文基于标题“SQLServer数据库性能优化技术”及其描述,深入探讨SQLServer数据库性能优化的关键技术和原则,旨在帮助读者理解和掌握如何提升SQLServer数据库的性能。 #### 数据库设计:性能优化的基石 1. **逻辑...

    SQLserver2008 数据库性能优化

    下面我们将从表设计优化、字段设计优化、索引优化、查询优化等方面对 SQL Server 2008 数据库性能优化进行系统的总结。 表设计优化 良好的表设计是数据库性能优化的基础。根据规范化的原则,表设计应当满足规范化...

    SQL Server 数据库技术---基础篇、数据库安全、SQL开发、数据库性能优化

    SQL Server 数据库技术---基础篇(T-SQL基础、数据库几本操作、SQL Server 2008新特性)、数据库安全(SQL Server 2008 安全数据...、数据库性能优化(数据库存储与索引、数据查询、事务处理、数据库系统调优 工具)

    SQL Server数据库性能优化.pdf

    本文主要围绕SQL Server数据库的性能优化问题进行了深入探讨,涵盖了影响数据库性能的关键因素,并提供了优化的原理和实际指导原则,以提升SQL Server数据库的性能。文章首先指出,数据库设计是计算机应用系统中非常...

    SQL Server数据库性能优化研究.pdf

    在当前信息科技高速发展的背景下,SQL Server数据库作为一种广泛使用的数据库系统,其性能优化成为确保计算机应用系统稳定运行的关键因素。在本文中,作者张春波与李晓会详细探讨了SQL Server数据库在实际运行过程中...

    浅析SQL Server数据库的性能优化方法.pdf

    SQL Server数据库是微软公司开发的一个关系型数据库管理系统,广泛应用于企业级的应用系统中,具备高性能、高可靠性和易用性的特点。随着计算机技术的快速发展,对数据库的性能要求越来越高,数据库性能优化已成为...

    SQL Server数据库性能优化方法.pdf

    - 文件组是SQL Server数据库性能优化的高级功能,它允许将数据表和索引组织成逻辑组,以此来优化数据的存储和访问。 - 文件组可以配合RAID技术使用,将文件组分布于RAID阵列的不同物理磁盘上,以进一步优化性能。 ...

    数据库设计对SQL Server数据库性能优化分析 (1).pdf

    以上是从文件标题、描述、标签和部分文中内容中提炼出的知识点,它们详细阐述了数据库设计在SQL Server数据库性能优化中的重要性,以及性能优化所涉及的各个方面。这些知识点对数据库管理员和开发人员来说是非常有...

    优化SQL Server数据库的经验总结

    SQL Server数据库优化是一个涉及多方面的复杂过程,需要综合考虑逻辑数据库设计、索引设计和查询设计等多个因素。通过对以上提到的关键点的应用和实践,可以有效地提升SQL Server数据库的整体性能。值得注意的是,...

    SqlServer性能优化高效索引指南

    SqlServer性能优化是数据库管理员和开发人员必须面对的常见任务,而高效索引则是提升SqlServer数据库性能的关键手段之一。索引是数据库表中的一种数据结构,用于加速查询操作,尤其是那些涉及WHERE子句、JOIN操作和...

Global site tag (gtag.js) - Google Analytics