SQLServer索引的优化

JournalofAnshanInstituteofI.&S.TechnologyVol.24No.4Aug.,2001

SQLServer索引的优化

苑卫国,迟呈英,钟　菊

1

2

1

(1.鞍山钢铁学院高等职业技术学院,辽宁鞍山　114002;2.鞍山钢铁学院计算机科学与工程学院,辽宁鞍山　114002)

摘　要:SQLServer优化技术对提高系统性能至关重要.为充分利用索引,优化表的查询机制,从而提高SQL

Server查询速度,本文从建立有用的索引减少表扫描IΠO次数,优化WHERE子句避免列操作,合理应用复合索

引选好第一索引列和使用聚集索引使数据在物理上按顺序排列四个方面加以阐述.

O;聚集索引;复合索引关键词:SQLSERVER查询优化器;表扫描;逻辑页IΠ

中图分类号:TP311113　文献标识码:A　文章编号:1000Ο1654(2001)04Ο0274Ο03　　信息技术高速发展的今天,管理信息系统已从传统的客户Π服务器(CΠS)模式,转向浏览器Π服务器(BΠS)模式.作为管理信息系统核心的数据库操作,已不能单纯地只要求结果的正确,更要强调运行性能的提高.　　本文将从优化SQLSERVER索引技术入手,探讨如何提高系统速度,从而提高系统性能.

1　测试环境

　　主机:AMD兼容机;主频:450;内存:65MB;操作系统:WIN2000;数据库:SQLSERVER710;数据表:TEACHER;结构:t-id,t-name,t-sex,t-age,t-dept,t-home,t-city,t-pay,t-worktime;记录个数:10000条.

2　表索引优化技术

211　建立有用的索引

　　有效的索引设计,对获得好的SQLSERVER性能是非常重要的.因为索引不看作是逻辑数据库设计的一部分,它们可以被删除、增加、修改而不影响数据库模式,只影响一个数据库应用的性能.　　SQLSERVER查询优化器是可以使用索引减少执行SQL语句要求的逻辑页IΠO数目.当没有索引可用时,SQLSERVER必须扫描整个表,读出表中每一个数据页;当有索引可用时,且查询优化器确定使用该索引会导致比表扫描更少的逻辑页IΠO和物理页IΠO,它就使用该索引.这样,就可以把可能由查询优化器使用的索引叫做有用索引.

　　一般认为,索引的有用性取决于数据的选择性,例如,给定使用数据表TEACHER的以下查询:　　SELECTt-id,t-name,t-sexFROMteacherWHEREt-name=‘张欣’　　则以下列上的一个索引对优器是有用的:　　①t-name;②t-name,t-sex.　　而下面这些列上的索引可能是没有用的:　　①t-home;②t-sex,t-name212　WHERE子句的优化

　　有三个查询,在相应的列上建有恰当的索引:　　SELECT3FROMteacherWHEREsubstring(t-id,1,4)=‘2104’[2145s]　　SELECT3FROMteacherWHEREt-payΠ12=1000[1189sΠ4270条记录]

收稿日期:2001-06-20.

作者简介:苑卫国(1970-),男,辽宁阜新人,实验师.

第4期　　　　　　　　　　　苑卫国,等:SQLServer索引的优化

　　SELECT3FROMteacherWHEREconvert(char(8),t-worktime,112)=‘19980901’[2107s]

・275・

　　分析以上三个查询,它们都有一个特点,即WHERE子句中对列的任何操作结果都是在SQL语句运行时逐列计算出来的,都进行了表的全部搜索,也即没有使用列上的索引.不妨把它们改成　　SELECT3FROMteacherWHEREt-id=LIKE‘2104%’[0103s]　　SELECT3FROMteacherWHEREt-pay=1000312[0137s]　　SELECT3FROMteacherWHEREt-worktime=‘1998Π09Π01’[0105s]

　　当开始查询编译时,计算结果(LIKE‘2104%’,1000312,1998Π09Π01)就可得出,这样经SQL查询优化器优化后,使用了索引,避免了表搜索,从而加快了查询速度.

　　当使用WHERE子句进行条件查询时,要充分利用索引,应避免任何形式对列的操作(如计算、数学函数、转换函数等等),也即避免了扫描整个表.查询时尽可能将必须的操作与索引列分列在操作符的两侧.

213　合理创建复合索引

　　在数据库操作过程中,经常同时存取多列,并且每列都有重复值时,将应用复合索引.

　　SQLSERVER系统中,对于包含多列的索引,SQLSERVER维护全部的索引列.如果复合索引的第一列有很差的选择,将严重影响其性能,并使某些SQL优化器可能不使用这个查询.下面是一个复合索引的例子,其索引列建在t-city,t-dept,t-worktime列上

　SELECTcount(3)FROMteacherWHEREt-dept=‘财务科’andt-worktime>=‘1996Π09Π01’[15167s]　SELECTt-deptsum(t-pay)FROMteacherGROUPBYt-dept[17151s]

(大连’)[3101s]　SELECTcount(3)FROMteacherWHEREt-dept=‘计算机系’andt-cityin‘‘,鞍山’

　　分析这个复合索引,其第一索引列为t-city,而第一和第二个SQL查询语句没有引用t-city,也即没有利用上索引,查询速度较慢.第三条SQL语句使用了第一个索引列t-city,且引用了包含在符合索引中的其它索引列,所以,查询速度较快.

　　根据上述三条SQL语句,修改这个复合索引,改为第一个索引列为t-dept,第二个索引列为t-work2time,第三个索引列为t-city,则有

　　SELECTcount(3)FROMteacherWHEREt-dept=‘财务科’andt-worktime>‘1996Π09Π01’[0134s]　　SELECTt-deptsum(t-pay)FROMtecaherGROUPBYt-dept[0154sΠ21条记录]

(大连’)[0141s]　　SELECTcount(3)FROMteacherWHEREt-dept=‘计算机系’andt-cityin‘‘,鞍山’

　　作为第一索引列的t-dept能被每个SQL语句所利用,且第二、第三索引列都包含在这个复合索引中,因而使查询速度加快,系统性能提高.

　　从上可知,在建立复合索引时,要尽量把使用最频繁的列,作为第一索引列.214　使用聚集索引

　　聚集索引是指行的物理顺序与行的索引顺序相同的索引.一个表只能有一个聚集索引.

　　非聚集索引是指定表的逻辑顺序的索引,行的物理顺序与索引顺序不尽相同.每个表可以有多个非聚集索引.

　　缺省情况下建立的索引是非聚集索引.在以下情况下考虑使用聚集索引:

　　(1)含有有限(不是很少)数目唯一值的列,如teacher表中含有确定数量唯一部门的t-dept的列,比如,沈阳、大连、鞍山.

　　(2)使用BETWEEN,〉〈,,〉=,<=这样运算符的返回值是一个范围的查询,例如:　　SELECT3FROMteacherWHEREt-worktimeBETWEEN‘1996Π09Π01’AND‘2000Π12Π31’　　(3)返回一个大的结果集的查询,例如:

　　　　　　　　　　　　　　鞍山钢铁学院学报　　　　　　　　　　　　　　第24卷・276・SELECT3FROMteacherWHEREt-worktime>=‘1994Π09Π01’

　　下面比较两种索引条件下的情况:　　(1)在t-dept上建立一个非聚集索引

　　SELECTcount(3)FROMteacherWHEREt-dept=‘财务科’andt-worktime>=‘1996Π09Π01’[0134s]　　SELECTt-deptsum(t-pay)FROMteacherGROUPBYt-detp[0154s]

(大连’)[0141s]　　SELECTcount(3)FROMteacherWHEREt-dept=‘计算机系’andt-cityin‘‘,鞍山’

　　在非聚集索引下,数据在物理上是随机存放的,当查询t-dept值时,由于有大量的重值,所以,必须扫描一次整个表,才能完成操作,这样查询速度将会很慢.　　(2)在t-dept上建立聚集索引.同样使用上面的例子

　　SELECTcount(3)FROMteacherWHEREt-dept=‘财务科’andt-worktime>‘1996Π09Π01’[0105s]　　SELECTt-deptsum(t-pay)FROMteacherGROUPBYt-detp[0101s](大连’)[0101s]　　SELECTcount(3)FROMteacherWHEREt-dept=‘计算机系’andt-cityin‘‘,鞍山’

　　由于使用了聚集索引,数据在物理上按顺序排列在数据页上,重复值也排在一起,即在t-dept列上,相同的值排列在一起.当查询t-dept列时,只要找到包含相同值的第一行和最末行,就可在这个范围内查找需要的值,避免了整个表的扫描,从而提高了查询速度.

3　结　论

　　表的优化查询机制,目的是帮助SQLSERVER提高速度,从上述例子中可以看出,在保证结果正确的前提下,要提高查询速度,充分利用索引,使用SQLSERVER优化器识别的语句,并减少表扫描IΠO次数,有效地避免整表的搜索.实际上,SQLSERVER优化技术是一个复杂的过程,这里只是体现了其中的表索引优化技术,更深入的应用(比如数据库的资源配置等)还需要在实践中不断地摸索和总结.参考文献:

[1]　MICHAELOtey,PAULCounte.SQLServer7Developer’sGuide[M].希望图书创作室译.北京:北京希望电子工业出版

社,1999.214-230.

[2]　PETKOVIC.SQLServer循序渐进教程[M].希望图书创作室译.北京:北京希望电子工业出版社,1999.126-137.[3]　微软公司.MicrosoftSQLServer7.0实现数据库设计[M].希望图书创作室译.北京:北京希望电子工业出版社,1999.

99-185,366-371.

[4]　李劲,谢兆阳.SQLServer7.0数据库设计与系统管理[M].北京:世界图书出版公司,2000.(10-1)-(10-19).

OptimizationofSQLServerIndex

YUANWei-guo,CHICheng-ying,ZHONGJu

1

2

1

(1.SchoolofHigherVocational,AnshanInstituteofI.&S.Technology,Anshan114002,China;

2.SchoolofComputerScienceandEngineering,AnshanInstituteofI.&S.Technology,Anshan114002,China)

Abstract:SQLServeroptimizingtechniqueisveryimportanttoimprovesystemperformance.Theproblemishowtomakefulluseofindex,tooptimizethequerymechanismoftable,soastoincreaseSQLserverqueryspeed.Thispa2perexpoundsfollowingfourpoints:first,establishusefulindextoreducepercenttimesoftablescan;secondly,opti2mizeWHEREclausestoavoidtheoperationofline;thirdly,makefulluseofmultiplexindextochoosethefirstindexline,andlast,makeuseofassembleindextoletthedatabelinedinphysicalorder.

KeyWords:optimizingwareofSQLserverquery;tablescan;logicpageIΠO;assembleindex;complexindex

(ReceivedJune20,2001)