近来,在项目后期,应客户要求,对项目涉及的SQL进行了优化,颇有一些感触,现记录如下。
SQL的好坏,体现在速度上,可能是几倍甚至几百倍的差别,而在项目开始之初,就应该对用户的数据量有一个大概的了解,并将写出的SQL实地运行一下,看是否能够满足要求。如果只是能用就好,而项目即将结束之时,再回来改变SQL这无疑是一个巨大的风险。
SQL的书写应该遵循下面几个注意点。
1.在使用count时,不要使用count *这样的语句,因为这里的*迟早是要被解析替换的,而如果这个表的列数太多的话,只是解析这个表的字段就会耗掉很大一部分时间。我自己曾经试过查询5000条数据,表的连接稍加复杂,用select *比select 1要多耗费1秒左右的时间。对于数据库来说,1秒实在是相当可观的一个数字。
2.判断一条记录在一个表中是否存在,倾向于使用not exists()而不是not in()。这里面效率的提升也是相当明显的。
3.关于nested loop与hash join。根据网上的说法,hashJoin这种连接方式适合于两个表做连接,且这两个表的数据量相差不大,而这两个表又实在很是巨大的情况下。而nested Loop则是两张表的数据量相差很大的情况下使用的。我们原来的SQL,在100条记录的时候,执行在毫秒级内就可以完成。但是数据量上升到在几十万条数据中查找5000条数据的时候,时间就激增到了20秒,对于Web应用而言,这是难以让人接受的等待。查看执行计划的cost,并不高,但所有表的连接方式都是通过nestedLoop来实现的。后来,我们将where部分的所有连接都移动到select部分采用子查询的方式来实现(这主要是针对于不会影响到查询记录数的一些左连接),而会影响到查询记录数的一些连接则通过exist(子查询)的方式来改写。这样,再查看执行计划,虽然cost会增加,但执行时间却提高到了1秒之内。针对这种现象,我也看了一些文档,因为时间问题也未曾深刻理解,只是知道通过hints是可以改变表的连接方式,尝试无果,只能使用上面复杂的方式将项目中所有SQL重写!
上面提到的内容只是在Oracle的前提下的进行写的,其它的没有试过。另外,为了能写出更加高效的SQL,有必要对Oracle的相关文档进行一番研究,理解它的优化目标,执行路径等的相关内容。