SQL语句执行原理及性能优化

李强 杨岿 吴天吉

摘要：在关系数据库中，随着数据库数据量的不断增加，数据处理的速度几乎决定了应用程序的响应速度，许多应用程序因处理的数据量过大而造成机器死机的情况时有发生。因此，如何有效地提高SQL语句的执行效率，优化SQL语句的性能，越来越成为开发人员关心的重要课题。

关键词：SQL；执行原理；性能优化

中图分类号：TB

文献标识码：A

文章编号：1672-3198（2013）05-0186-02

1造成SQL语句性能不佳的主要原因

（1）程序开发人员对查询语句的效率重视程度不够，只关注查询结果的正确性，而忽视了查询语句的效率。对于海量数据而言，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，因此，开发一个应用系统不是简单地能实现其功能即可，而是要写出高质量的SQL语句，提高系统的可用性。

（2）开发人员在关注SQL语句效率的同时，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚，这些也是影响SQL语句性能的优化。

2SQL语句的执行原理

Select语句可以说是DBA和数据库开发者在工作中使用最多的语句，这条语句执行效率的高低直接影响到应用程序的可用性。我们以Oracle数据库为例，从Oracle数据库后台的运作机制来了解一条简单的Select语句的执行原理。这对SQL语句的优化起到至关重要的作用。

第一步：客户端把语句发给服务器端执行。当我们在数据层执行select语句时，应用程序客户端会把SQL语句发送给服务器端，让服务器端进程来处理这语句。

第二步：语句解析。

当客户端把SQL语句传送到服务器后，服务器进程会对该语句进行解析。语句解析是一个复杂的过程，主要流程如下：

（1）查询高速缓存。服务器进程在接到客户端传送过来的SQL语句时，不会直接去数据库查询，而是会先在数据库的高速缓存中去查找，是否存在相同语句的执行计划。如果存在，则服务器进程就会直接执行这个SQL语句，省去后续的工作。所以，采用高速数据缓存的话，可以提高SQL语句的查询效率。一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高，另一方面，也省去了语句解析等工作。

（2）语法检查。当在高速缓存中找不到对应的SQL语句时，则数据库服务器进程就会开始检查这条语句的语法合法性。如果服务器进程认为这条SQL语句不符合语法规则的时候，就会把这个错误信息，反馈给客户端。

（3）语义检查。若SQL语句符合语法上的定义的话，则服务器进程接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。如果表名与列名不准确的话，则数据库会就会反馈错误信息给客户端。

（4）获得对象解析锁。当语法、语义都正确后，系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性，防止我们在查询的过程中，其他用户对这个对象的结构发生改变。

（5）数据访问权限的核对。当语法、语义通过检查之后，服务器进程还会检查连接的用户是否有数据访问的权限。若用户不具有数据访问权限，则客户端就不能够取得这些数据。

（6）确定最佳执行计划。当语句与语法都没有问题，权限也匹配的话，服务器进程开始根据一定的规则，对这条语句进行优化。当服务器进程的优化器确定这条查询语句的最佳执行计划后，就会将这条SQL语句与执行计划保存到数据高速缓存。

第三步：语句执行。

SQL语句执行也分两种情况。若被选择行所在的数据块已经被读取到数据缓冲区的话，则服务器进程会直接把这个数据传递给客户端。若数据不在缓冲区中，则服务器进程将从数据库文件中查询相关数据，并把这些数据放入到数据缓冲区中。

第四步：提取数据。

当语句执行完成之后，查询到的数据还是在服务器进程中，还没有被传送到客户端的用户进程。所以，在服务器端的进程中，有一个专门负责数据提取的代码。他的作用就是把查询到的数据结果返回给用户端进程，从而完成整个查询动作。

3SQL优化的一般性原则

SQL优化的主要目标是减少服务器资源消耗（主要是磁盘IO）；设计优化上尽量依赖oracle的优化器，并为其提供条件，并创建合适的索引，注重列的可选择性；在编码优化上要合理利用索引，避免大表FULL TABLE SCAN，合理使用临时表，避免写过于复杂的SQL语句，在不影响业务的前提下减小事务的粒度。

4SQL语句性能优化技巧

（1）创建索引提高系统性能，但要避免在索引列上进行有关操作。

创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

但是，增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

在使用索引的同时，避免在索引列上使用NOT，IS NULL和IS NOT NULL等操作，避免在索引列上使用计算，函数或改变索引列类型等。使用这些操作时，SQL语句就会停止使用索引转而执行全表扫描。

（2）优化FROM表的选择和WHERE条件的语句顺序。ORACLE解析器按照从右到左的顺序处理FROM子句中的表名，按照自下而上的顺序解析WHERE子句。在FROM子句中包含多个表的情况下，写在最后的表（基础表driving table）将被最先处理，选择记录条数最少的表作为基础表可以大幅提升处理效率；同样WHERE子句中可以过滤最大数量记录的条件写在的末尾，性能会有本质的提升。

例1：表TABLE1有15000条记录，表TABLE2仅有2条记录，选择TABBLE2作为基础表，select count（*） from tab1，tab2执行时间0.92秒。选择TABLE1作为基础表select count（*） from tab2，tab1执行时间27.09秒。

例2：（低效，执行时间150.4秒）

SELECT COL FROM EMP E WHERE SAL>49850 AND JOB=‘MANAGER AND 26 < （SELECT COUNT（*） FROM EMP WHERE MGR = E.EMPNO）

（高效，执行时间9.9秒）

SELECT COL FROM EMP E WHERE 26 < （SELECT COUNT（*） FROM EMPWHERE MGR=E.EMPNO） AND SAL>49850 AND JOB=‘MANAGER

（3）使用高效的关键字替代低效的关键字。用EXISTS替代IN和DISTINCT，用Where子句替换HAVING子句等：

当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT或者IN，一般可以考虑用EXISTS替换，因为RDBMS核心模块将在EXISTS子查询的条件一旦满足后，立刻返回结果。HAVING子句只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

（4）避免使用耗费资源的操作：带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常，带有UNION，MINUS，INTERSECT的SQL语句都可以用其他方式重写。如果你的数据库的SORT_AREA_SIZE调配得好，使用UNION，MINUS，INTERSECT也是可以考虑的，毕竟它们的可读性很强。

通过以上分析可知，SQL语句的执行效率其实就是计算机时间和空间的一个平衡。节省了查询时间，提高了时间效率，就需要在空间方面给出牺牲；节约了系统空间，提高了空间效率，就需要在时间方面做出牺牲，因此在采取适当的数据策略时，应考虑SQL语句优化，使得有效的硬件资源性能得到最大化的发挥。

参考文献

[1]徐凤梅.关系数据库中SQL语言查询的优化策略[J].广西轻工业，2009，（5）：77.

[2]郭玉栋，左金平.SQL语言查询优化方案探究[J].晋中学院学报，2006（6）：19.