数据库查询优化

数据库查询优化数据库查询优化张志胜[e-mail]0.0.引言引言数据库系统是管理信息系统的核心，数据库系统是管理信息系统的核心，基于数据库的联机事务处理（基于数据库的联机事务处理（OLTPOLTP）以及）以及联机分析处理联机分析处理(OLAP)(OLAP)是银行、企业、政府是银行、企业、政府等部门最为重要的计算机应用之一。从大等部门最为重要的计算机应用之一。从大多数系统的应用实例来看，查询操作在各多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大，因此种数据库操作中所占据的比重最大，因此如何优化数据库查询有着很重要的意义。如何优化数据库查询有着很重要的意义。优化数据库查询可以从以下几个方面进行：优化数据库查询可以从以下几个方面进行：一、后台数据库设计。一、后台数据库设计。二、二、SQLSQL语句优化。语句优化。三、前台实现的优化。三、前台实现的优化。1.1.后台数据库设计后台数据库设计一个好的数据库结构可以大大提高查一个好的数据库结构可以大大提高查询的效率。所以后台数据库结构的优化是询的效率。所以后台数据库结构的优化是数据库查询优化的基础。后台数据库结构数据库查询优化的基础。后台数据库结构的优化可从以下两个方面进行：的优化可从以下两个方面进行：1.11.1适当违反数据库设计范式适当违反数据库设计范式所谓违反数据库设计范式就是在建表所谓违反数据库设计范式就是在建表时，留冗余字段。这是由于在查询时，留冗余字段。这是由于在查询AA表信息表信息时，可能经常要关联到时，可能经常要关联到BB表中的信息。此表中的信息。此时，可以在时，可以在AA表中设立冗余字段存储表中设立冗余字段存储BB表中表中的相关信息。这样可以避免连接操作。的相关信息。这样可以避免连接操作。比如在比如在CDCD--ERPERP生产管理系统中，有一生产管理系统中，有一张记录产量的表字段如下：张记录产量的表字段如下：““产品名称产品名称””、、““扩散批号扩散批号””、、““实做数实做数””、、““次品数次品数””............。但用。但用户要求知道每种产品的封装形式。如果不户要求知道每种产品的封装形式。如果不加冗余字段，那必须用该表的加冗余字段，那必须用该表的““扩散批号扩散批号””字字段关联段关联““生产工作令生产工作令””基表，从而查出基表，从而查出““封装封装形式形式””。但。但““生产工作令生产工作令””基表的记录数很多基表的记录数很多所以查询速度很慢。在这种情况下，就要所以查询速度很慢。在这种情况下，就要在原来的表中加入在原来的表中加入““封装形式封装形式””这一冗余字这一冗余字段，以提高查询速度。如下图所示：段，以提高查询速度。如下图所示：产品名称扩散批号实做数次品数……XXXXXXXXXXXXXXXX……工作令编号扩散批号封装形式……XXXXXXXXXXXX……产品名称组装批号实做数次品数封装形式XXXXXXXXXXXXXXXXXXXX…………改进前改进后这就要求在对数据库进行添加和修改操作时这就要求在对数据库进行添加和修改操作时要保证数据一致性，这给开发前台程序带来了不要保证数据一致性，这给开发前台程序带来了不方便，且完全依靠前台程序，可靠性不能得到保方便，且完全依靠前台程序，可靠性不能得到保证。当然也可通过后台触发器来保证数据一致证。当然也可通过后台触发器来保证数据一致性，但触发器的使用往往会造成很多问题。例性，但触发器的使用往往会造成很多问题。例如，在如，在CDCD--ERPERP的财务系统中，财务发票是由主从的财务系统中，财务发票是由主从表进行存储的，主表存放表头信息，从表存放明表进行存储的，主表存放表头信息，从表存放明细栏信息。为了统计方便，在主表中增加了细栏信息。为了统计方便，在主表中增加了““总价总价””这一字段，其值为细表中相应记录价格之和。但这一字段，其值为细表中相应记录价格之和。但这就使得程序开发时要增加大量代码，要在增加、这就使得程序开发时要增加大量代码，要在增加、修改、删除从表记录时对冗余字段的修改。后来修改、删除从表记录时对冗余字段的修改。后来发现，主表的发现，主表的““总价总价””字段只在对发票进行汇总统字段只在对发票进行汇总统计时有用，且对查询效率的提高并不明显，所以计时有用，且对查询效率的提高并不明显，所以取消了取消了““总价总价””字段。字段。违反数据库设计范式的问题－－数据一致违反数据库设计范式的问题－－数据一致性的问题性的问题由上述两个例子可知，增加冗余字段有由上述两个例子可知，增加冗余字段有利于提高查询速度，但过多的冗余字段会利于提高查询速度，但过多的冗余字段会影响数据的一致性，所以在增加冗余字段影响数据的一致性，所以在增加冗余字段时要权衡利弊。时要权衡利弊。1.21.2合理使用索引合理使用索引索引（索引（INDEXINDEX）是与表和聚集相关的可）是与表和聚集相关的可选结构，它可以帮助用户更快速地存储数选结构，它可以帮助用户更快速地存储数据。索引逻辑地和物理地独立于相关表或据。索引逻辑地和物理地独立于相关表或聚集中的数据。使用索引可以加速数据的聚集中的数据。使用索引可以加速数据的存取，能够减少对数据文件的磁盘存取，能够减少对数据文件的磁盘I/OI/O，从，从而改善数据库的性能。索引是数据库中重而改善数据库的性能。索引是数据库中重要的数据结构，它的根本目的就是为了提要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都高查询效率。现在大多数的数据库产品都采用采用IBMIBM最先提出的最先提出的ISAMISAM索引结构。索引索引结构。索引的使用原则如下：的使用原则如下：„„在经常进行连接，但是没有指定为外键的在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由列上建立索引，而不经常连接的字段则由优化器自动生成索引。例如：在优化器自动生成索引。例如：在CDCD--ERPERP生生产管理系统中，经常要用来关联不同工序产管理系统中，经常要用来关联不同工序产量数据表的产量数据表的““扩散批号扩散批号””、、““组装批号组装批号””等字等字段，一般设为索引。段，一般设为索引。„„在频繁进行排序或分组（即进行在频繁进行排序或分组（即进行groupbygroupby或或orderbyorderby操作）的列上建立索引。操作）的列上建立索引。„„在条件表达式中经常用到的不同值较多的在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建列上建立检索，在不同值少的列上不要建立索引。例如在立索引。例如在CDCD--ERPERP人事系统员工信息人事系统员工信息表的表的““性别性别””列上只有列上只有““男男””与与““女女””两个不同两个不同值，因此就无必要建立索引。如果建立索值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降引不但不会提高查询效率，反而会严重降低更新速度。低更新速度。„„如果待排序的列有多个，可以在这些列上如果待排序的列有多个，可以在这些列上建立复合索引（建立复合索引（compoundindexcompoundindex）。例如：）。例如：在在CDCD--ERPERP生产管理系统的产量统计查询中生产管理系统的产量统计查询中经常要先按产品的经常要先按产品的““封装形式（封装形式（FZXSFZXS））””排排序，再按产品的序，再按产品的““产品名称（产品名称（CPMCCPMC））””排排序，即序，即““ORDERYBYFZXS,CPMCORDERYBYFZXS,CPMC””则此时，则此时，应把应把““FZXSFZXS””和和““CPMCCPMC””设为复合索引，而不设为复合索引，而不是分别设是分别设““FZXSFZXS””和和““CPMCCPMC””两个索引。两个索引。2.SQL2.SQL语句优化语句优化有了一个合理的后台数据库结构并不有了一个合理的后台数据库结构并不等于所有工作都已做完。还有大量的查询、等于所有工作都已做完。还有大量的查询、统计要通过统计要通过SQLSQL查询语句实现。在查询语句实现。在CDCD--ERPERP的实施过程中，查询统计速度慢始终是困的实施过程中，查询统计速度慢始终是困扰开发人员的一个问题。而速度问题大多扰开发人员的一个问题。而速度问题大多是由于不合理的是由于不合理的SQLSQL语句造成的。下面就是语句造成的。下面就是SQLSQL语句优化的一些经验。语句优化的一些经验。2.1SQL2.1SQL查询的五个阶段查询的五个阶段„„解析器解析器：它将：它将SQLSQL文本转换成语法树。这文本转换成语法树。这个阶段不查找系统目录中的任何信息，不个阶段不查找系统目录中的任何信息，不访问数据库；访问数据库；„„语义分析语义分析：本阶段分析解析器创建的语法：本阶段分析解析器创建的语法树，并产生用于查询的查询控制块和表达树，并产生用于查询的查询控制块和表达式树；式树；„„优化器优化器：优化器阶段使用语义分析构建的：优化器阶段使用语义分析构建的数据结构，以根据开销找到和制定优化查数据结构，以根据开销找到和制定优化查询计划；询计划；„„迭代器树构建迭代器树构建：第：第44阶段构建迭代器树，阶段构建迭代器树，它是用于并行查询执行的原型树。如果能它是用于并行查询执行的原型树。如果能并行运行查询，则可向迭代器树添加一个并行运行查询，则可向迭代器树添加一个交换节点，以便在查询执行时（第交换节点，以便在查询执行时（第55阶段）阶段）执行并行查询。执行并行查询。„„查询执行查询执行：最后一个阶段真正执行查询，：最后一个阶段真正执行查询，派生出辅助派生出辅助PDQPDQ线程以用于并行执行，并线程以用于并行执行，并扫描表和索引。扫描表和索引。2.22.2连接和联合的使用连接和联合的使用连接即连接即JoinJoin主要用于同时需要几张基表主要用于同时需要几张基表信息时的情况。信息时的情况。JoinJoin可以看成是把几张表的可以看成是把几张表的相关内容进行横向组合。相关内容进行横向组合。联合是指把几个相关的查询结果纵向联合是指把几个相关的查询结果纵向组合，每个查询结果的字段数量、类型和组合，每个查询结果的字段数量、类型和种类都是相同的。联合在数据库查询中是种类都是相同的。联合在数据库查询中是经常使用的。在经常使用的。在CDERPCDERP系统中联合的最典系统中联合的最典型使用就是，在统计查询时，求型使用就是，在统计查询时，求““总计总计””和和““小计小计””如下所示：如下所示：selectselect品种名品种名,,组装批号组装批号,,数量数量fromfrom(select(selectt.pzmt.pzm品种名品种名,,t.zzpht.zzph组装批号组装批号,,sum(t.slsum(t.sl))数数量量,'a'f1,'a'f1fromfromicsc_zcp_bakicsc_zcp_baktwheretwheret.fzxst.fzxs='SOP8'group='SOP8'groupbybyt.pzm,t.zzpht.pzm,t.zzphunionallunionallselectselectt.pzmt.pzm||'||'小计小计','',','',sum(t.sl),'asum(t.sl),'a'f1'f1fromfromicsc_zcp_bakicsc_zcp_baktwheretwheret.fzxst.fzxs='SOP8'group='SOP8'groupbybyt.pzmt.pzmunionallunionallselect'select'总计总计','',','',sum(t.sl),'bsum(t.sl),'b'f1'f1fromfromicsc_zcp_bakicsc_zcp_baktwheretwheret.fzxst.fzxs='SOP8')='SOP8')orderbyf1,orderbyf1,品种名品种名结果如下：结果如下：上述上述SQLSQL表