基于索引的sql语句设计及优化

基于索引的sql语句设计及优化

0提高系统速度的方法及途径现在，信息技术在生产中得到了广泛应用，大量应用程序处理的数据相对较大。如何处理这些大量的数据，以及通过保障系统的快速响应，是系统设计中必须解决的最重要问题。影响系统速度的原因有多种多样,改善系统速度的方法及途径也是多种多样的,对于这些提高速度的方法及途径从本质上说大致可分为两大类:软件或硬件的改变,对于大部分系统而言,升级硬件意味着成本增加,通常客户无法承受这一点,因此常见改善系统性能的做法是从软件入手,通过改善软件结构、算法、语句效率等带来系统整体性能的提高。很多应用系统是通过SQL语句来操作数据库中的海量数据,使用精心设计的SQL语句或优化后的SQL语句能有效地改变系统运行速度。本文将从索引的角度分析如何设计及优化SQL语句以提高系统运行速度。1查询模式优化要对SQL语句进行优化,首先必须清楚SQL语句的执行过程,以从中得到一些可能的优化途径,SQL语句的执行过程一般分成4步:(1)语法分析:进行SQL语法分析,检查SQL语法是否正确。(2)优化分析:优化器采用基于规则的优化模式(RBO)或者是采用基于代价的优化模式(CBO)去选择效率最优的查询模式。(3)选择优化:优化器采用优化后的方案,产生SQL语句执行计划。(4)SQL语句执行引擎:按照该执行计划运行,并返回查询结果。在SQL语句的执行过程中,优化器决定了SQL语句执行的优化方案,这是SQL语句执行过程中的最重要的一步,以CBO优化方式为例说明优化过程:(1)SQL语句转换:把经过语法分析后的SQL语句转换成更合理的SQL语句。(2)计划生成:根据表、聚集和索引在数据字典中存放的特性统计信息以及根据访问路径、连接方式、连接顺序的不同组合,可产生不同的执行计划,并计算出执行每个计划的代价。其中访问路径对执行代价有很大的影响。(3)选择计划:比较各个计划,选择代价最低的形成优化方案并执行。根据以上SQL语句的执行过程,可以看出:SQL的优化关键在优化器选择的执行计划,执行计划的关键是访问路径的选择,而访问路径的选择是索引,利用好索引,是提高系统操作数据库速度的最重要因素之一。2建立聚族索引索引一般分聚族索引(Clusteredindex)和非聚族索引(Nonclusteredindex)两种。一个表只能有一个聚族索引,可有多个非聚族索引,使用聚族索引查询数据要比使用非聚族索引快。聚族索引和非聚族索引的选择对数据库的性能影响很大。聚族索引是一种数据表的物理顺序与索引顺序相同的索引,占用的空间相对较小,建立聚族索引的策略是:(1)在where子句中的列上建立聚族索引,包括where子句中的范围查询、模糊查询以及数据高度重复的列;(2)在join操作中连接的列上建立聚族索引;(3)在orderby和groupby子句中的列上建立聚族索引;(4)聚族索引不利于插入操作,所以不适用于insert语句。非聚族索引是一种数据表的物理顺序与索引顺序不相同的索引,与聚族索引相比,非聚族索引占用空间大,而且效率低,建立非聚族索引的策略是:(1)在where子句中的列上建立非聚族索引,包括范围查询、模糊查询、以及使用到的主键列和外键列,但前提条件是在没有建立聚族索引的情况下,并且适用于小范围查询(返回的结果集小于整表数据的20%);(2)在join操作中连接的主建列上建立非聚族索引;(3)在orderby和groupby子句中的主键列上建立非聚族索引。建立索引会提高数据检索速度,但不能盲目的建立很多索引,因为索引也有其弊端,一是建立索引要占有大量磁盘空间;二是增加了维护代价,因为在对表进行增加、删除和更新的时候,索引也需要动态的维护。3sql织物的不合理通过上面的分析可以看到利用合理的索引提高系统性能效果是明显的,但如果在程序中书写SQL语句不合理,就有可能会造成优化器不使用索引。在不使用索引后就采用全表扫描,不必要的全表扫描会造成不必要的数据处理,而且有时还会拖垮整个数据库。因此在编写SQL语句时应该清楚在哪些情况下不会使用已经建立的索引,这有助于提高SQL语句的性能。(1)选择索引方式如果采用通配符%在搜索词首出现,是不会使用需要搜索列的索引。比如:select*fromcustomerwherenamelike′%am%′(name列建立了索引)。name列的索引是不会被使用的,如果通配符出现在非词首,优化器是能够使用其索引。比如:select*fromcustomerwherenamelike′am%′(name列建立了索引)。这样就使用了name列的索引。(2)避免使用索引not操作符可以用来取反,但是一旦使用了not操作符就不会使用到索引。在使用与not操作符有同样效果的<>运算符的时候,同样不会使用到索引。在某些情况下,我们可以避免使用not或者<>。比如:select*fromcustomerwhereage<>40(age列建立了索引)。将以上的查询改成select*fromcustomerwhereage>40orage<40(age列建立了索引)。第二种查询方式比第一种查询方式会快一些,因为使用到了age列的索引。(3)生成索引vendoridor或者in子句查询的优化依赖于在这些子句中使用的表上的索引,如果or或者in子句查询中使用的列上有任一个列没有索引,则执行全表扫描。比如:selectnamefromcustomerswherecreditrating=1andvendorIDin(selectvendorIDfromcustomerswhereminorderoty>=20andaverageleadtime<16)(vendorID列上没有建立索引)。以上SQL语句执行全表扫描,效率较低。(4)索引有多列isnull或者isnotnull语句操作符是不会引用索引的,任何包含空值的列将不会被包含在索引中。即使索引有多列,但只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。比如:select*fromemployeewheresalaryisnotnull(salary列上建立了索引)。该语句是不会用到salary列上的索引。4书写sql句子综上所述,恰当地建立索引可使优化器优化SQL语句的执行,从而使访问的数