`
guoyiqi
  • 浏览: 1009419 次
社区版块
存档分类
最新评论

关于sql server建立索引需要注意的问题

 
阅读更多


人们在使用sql时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理oltp或决策支持系统dss)中表现得尤为明显。笔者在工作实践中发现,不良的sql往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后,其运行速度有了明显地提高!下面我将从这三个方面分别进行总结:  为了更直观地说明问题,所有实例中的sql运行时间均经过测试,不超过1秒的均表示为(< 1秒)。  测试环境--  主机:hp lh ii  主频:330mhz  内存:128兆  操作系统:operserver5.0.4  数据库:sybase11.0.3  一、不合理的索引设计  例:表record有620000行,试看在不同的索引下,下面几个 sql的运行情况:

  1.在date上建有一个非群集索引  select count(*) from record where date >'19991201' and date < '19991214'and amount >2000 (25秒)  select date,sum(amount) from record group by date(55秒)  select count(*) from record where date >'19990901' and place in ('bj','sh') (27秒)  分析:  date上有大量的重复值,在非群集索引下,数据在物理上随机存放在数据页上,在范围查找时,必须执行一次表扫描才能找到这一范围内的全部行。

 

  2.在date上的一个群集索引  select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(14秒)  select date,sum(amount) from record group by date(28秒)  select count(*) from record where date >'19990901' and place in ('bj','sh')(14秒)  分析:  在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。

 

  3.在place,date,amount上的组合索引  select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(26秒)  select date,sum(amount) from record group by date(27秒)  select count(*) from record where date >'19990901' and place in ('bj, 'sh')(< 1秒)  分析:  这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条sql没有引用place,因此也没有利用上索引;第三个sql使用了place,且引用的所有列都包含在组合索引中,形成了索引覆盖,所以它的速度是非常快的。

 

  4.在date,place,amount上的组合索引  select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1秒)  select date,sum(amount) from record group by date(11秒)  select count(*) from record where date >'19990901' and place in ('bj','sh')(< 1秒)  分析:  这是一个合理的组合索引。它将date作为前导列,使每个sql都可以利用索引,并且在第一和第三个sql中形成了索引覆盖,因而性能达到了最优。

 

  5.总结:  缺省情况下建立的索引是非群集索引,但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。一般来说:  ①.有大量重复值、且经常有范围查询  (between, >,< ,>=,< =)和order by、group by发生的列,可考虑建立群集索引;  ②.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;  ③.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。

 

  二、不充份的连接条件:  例:表card有7896行,在card_no上有一个非聚集索引,表account有191122行,在 account_no上有一个非聚集索引,试看在不同的表连接条件下,两个sql的执行情况:  select sum(a.amount) from account a,card b where a.card_no = b.card_no(20秒)  将sql改为:  select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no(< 1秒)  分析:  在第一个连接条件下,最佳查询方案是将account作外层表,card作内层表,利用card上的索引,其i/o次数可由以下公式估算为:  外层表account上的22541页+(外层表account的191122行*内层表card上对应外层表第一行所要查找的3页)=595907次i/o  在第二个连接条件下,最佳查询方案是将card作外层表,account作内层表,利用account上的索引,其i/o次数可由以下公式估算为:  外层表card上的1944页+(外层表card的7896行*内层表account上对应外层表每一行所要查找的4页)= 33528次i/o  可见,只有充份的连接条件,真正的最佳方案才会被执行。  总结:  1.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。  2.查看执行方案的方法-- 用set showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。

 

  三、不可优化的where子句  1.例:下列sql条件语句中的列都建有恰当的索引,但执行速度却非常慢:  select * from record where substring(card_no,1,4)='5378'(13秒)  select * from record where amount/30< 1000(11秒)  select * from record where convert(char(10),date,112)='19991201'(10秒)  分析:  where子句中对列的任何操作结果都是在sql运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被sql优化器优化,使用索引,避免表搜索,因此将sql重写成下面这样:  select * from record where card_no like '5378%'(< 1秒)  select * from record where amount < 1000*30(< 1秒)  select * from record where date= '1999/12/01' (< 1秒)你会发现sql明显快起来!

  2.例:表stuff有200000行,id_no上有非群集索引,请看下面这个sql:  select count(*) from stuff where id_no in('0','1')(23秒)  分析:  where条件中的'in'在逻辑上相当于'or',所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用id_no上的索引;但实际上(根据showplan),它却采用了"or策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用id_no上索引,并且完成时间还要受tempdb数据库性能的影响。

  实践证明,表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时间竟达到220秒!还不如将or子句分开:  select count(*) from stuff where id_no='0'  select count(*) from stuff where id_no='1'  得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3秒,在620000行下,时间也只有4秒。或者,用更好的方法,写一个简单的存储过程:  create proc count_stuff as  declare @a int  declare @b int  declare @c int  declare @d char(10)  begin  select @a=count(*) from stuff where id_no='0'  select @b=count(*) from stuff where id_no='1'  end  select @c=@a+@b  select @d=convert(char(10),@c)  print @d  直接算出结果,执行时间同上面一样快!  总结:  可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。  1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。  2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。  3.要善于使用存储过程,它使sql变得更加灵活和高效。  从以上这些例子可以看出,sql优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的i/o次数,尽量避免表搜索的发生。其实sql的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

分享到:
评论

相关推荐

    SQL SERVER建立索引.pdf

    ### SQL Server 建立索引相关知识点 #### 一、建立索引 索引是在数据库表中的数据列上创建的一种特殊的数据结构,其目的是为了加快数据检索速度。索引类似于书籍的目录,通过索引可以快速定位到所需的数据行,从而...

    SQL Server 2000完结篇系列之七:SQL Server 2000索引优化详解

    - 索引维护:索引会增加写操作的开销,频繁更新的列不宜建立索引。 - 存储空间:索引需要额外的存储空间,应权衡性能提升与存储成本。 四、索引优化策略 1. 唯一性:索引的唯一性有助于提高查询效率,避免冲突。 2....

    SQL Server 全文索引查询

    首先,要启用SQL Server的全文索引查询,你需要确保你的SQL Server实例支持全文搜索服务。这通常需要在安装SQL Server时选择并添加此功能。如果你已经安装了SQL Server但未包含全文搜索,可以通过重新安装或添加功能...

    SQL Server 索引结构及其使用(聚集索引与非聚集索引)

    非聚集索引的缺点是查询速度较慢,但可以在多个列上建立索引。 何时使用聚集索引或非聚集索引?下表总结了何时使用聚集索引或非聚集索引: | 动作描述 | 使用聚集索引 | 使用非聚集索引 | | --- | --- | --- | | ...

    优化SQL Server索引的小技巧

    在建立索引时,需要考虑数据类型和保存这些数据的 column。同时,也需要考虑数据库可能用到的查询类型以及使用的最为频繁的查询类型。对于 column 保存了高度相关的数据,并且常常被顺序访问时,最好使用 clustered ...

    关于SQL Server中索引使用及维护简介

    在SQL Server中,索引是提升数据库性能的关键因素,它们帮助快速定位数据,减少磁盘I/O操作,从而加速查询速度。本篇文章将探讨聚簇索引、非聚簇索引以及覆盖索引的使用和维护策略。 1. **聚簇索引**:聚簇索引决定...

    SQL Server 2000索引结构及使用方法

    因此,在建立索引时,需要考虑实际情况和需求,权衡索引的优缺点。 索引结构及使用方法是 SQL Server 2000 中的一个重要组成部分。只有充分理解聚集索引和非聚集索引的定义、区别和使用场景,才能更好地使用索引来...

    利用索引提高SQLServer数据处理效率

    在SQL Server数据库中,索引是提升数据处理效率的关键手段,尤其对于联机事务处理(OLTP)系统,快速的数据查询速度是系统性能的核心指标。本文主要探讨如何通过合理使用索引来优化SQL Server的性能。 首先,我们来...

    SQL Server 索引基础知识

    此外,对于经常用于连接操作的列,建立索引也有助于提高查询效率。 #### 七、AND 条件下的索引使用 - **AND 条件**:当查询条件包含多个 AND 连接的条件时,如果这些条件对应的列都有索引,则可以利用索引来快速...

    数据库SQLSERVER索引

    在本文中,我将说明如何用SQLServer的工具来优化数据库索引的使用,本文还涉及到有关索引的一般性知识。关于索引的常识影响到数据库性能的最大因素就是索引。由于该问题的复杂性,我只可能简单的谈谈这个问题,不过...

    SQL Server 2000数据库中如何重建索引

    如果直接删除并重新创建聚集索引,那么所有的非聚集索引都需要被删除并重新建立两次,因为它们依赖于聚集索引的键来定位数据行。但是,通过`DROP_EXISTING`,你可以避免这个昂贵的过程,因为它会更新现有索引,而...

    SQLServer视图及索引的创建及使用

    资源名称:SQL Server 视图及索引的创建及使用内容简介: 本文档主要讲述的是SQL Server 视图及索引的创建及使用;目的是通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除通过企业管理器。希望本文档会给...

    SQL Server 索引结构及其使用

    不过,在建立索引时应避免常见的误区。将主键自动设置为聚集索引是一种常见的错误做法。主键字段虽然是唯一的,但并不一定经常用于查询,因此将其设置为聚集索引可能不会显著提高查询效率。实际上,主键上的索引最好...

    sqlserver2005 创建分区表 分区索引 注意事宜 分区索引

    【SQL Server 2005 创建分区表与分区索引详解】 分区表和分区索引是SQL Server 2005为企业级数据管理和性能优化提供的重要特性,尤其适用于处理大量数据的大型表。分区能够将数据划分为独立的、易于管理的块,这有...

    lucene与sqlserver数据库实现索引的简单实例

    1. **数据抽取**:首先,我们需要从SQL Server数据库中获取需要建立索引的数据。这通常通过JDBC(Java Database Connectivity)驱动实现,编写Java代码连接数据库并执行查询,获取所需字段。 2. **预处理**:数据...

    SQLServer索引设计经验谈

    ### SQL Server索引设计经验谈 #### 摘要 本文旨在探讨Microsoft SQL Server中的索引设计技巧,尤其是如何通过合理的索引策略来优化查询性能。通过对聚集索引与非聚集索引的选择、索引列的排序方式、填充因子的...

    SQL Server的全文索引及优化.pdf

    为了使全文索引正常工作,首先需要启用SQL Server实例上的全文索引功能。此外,还需要建立全文目录,将需要全文索引的表注册到目录中,并对相应的表列创建全文索引。 创建全文索引时,可通过执行特定的存储过程来...

    SQL Server Native Client 10.0 驱动

    2. **支持新特性**:包括对SQL Server 2008引入的新特性如FILESTREAM、透明数据加密(TDE)、Change Data Capture (CDC) 和列存储索引的支持。 3. **连接池**:通过ODBC和OLE DB接口实现连接池管理,提高资源利用率...

    如何设置SQL Server数据库全文索引服务

    然而,需要注意的是,一旦全文索引建立,对表结构的修改可能会导致索引失效,所以在设计数据库时应充分考虑这一点。此外,全文索引需要额外的存储空间,以及管理和维护的成本,因此在启用全文索引前,应评估其对系统...

Global site tag (gtag.js) - Google Analytics