如何最有效的编写 SQL

会员服务 ·

如何最有效的编写 SQL

2017 年 7 月 12 日 数据库开发

（点击上方公众号，可快速关注）

英文：Emrah Mete，翻译：Iteye-黑色巧克力

www.iteye.com/news/32540

如有好文章投稿，请点击 → 这里了解详情

译者注：解决数据库级（SQL）工作上的问题，应该采用的是SET方法（整体的）而不是过程式的方法。下面来看看作者为什么这么说。

编写有效的SQL查询是企业软件世界中最大的难题之一。

每个公司在数据库开发项目中所面临的最根本的问题，在于开发环境中实现的性能不能在生产环境中实现。一般来说，存在性能损失是因为生产环境中的数据量要大得多。

这些问题（运行缓慢的数据库操作）可能有各种各样的原因。本文将解释如何在编写查询时进行思考，如何思考是最基本的问题，也是解决此类问题的起点。

观察发现SQL开发人员常使用过程方法编写查询。事实上，这是很自然的，因为用程序方法解决问题是最方便的人类逻辑解决方案。另一个方面，几乎所有的SQL开发人员都在同时编写Java、c#或其他编程语言的代码。Java、C#等可以用来训练开发人员以一种程序化的方式来培养他们的思维方式，因为当使用这些语言开发应用程序时，会使用很多类似的东西，比如IF .. THEN .. ELSE，FOR .. LOOP，WHILE .. DO， CASE .. WHEN。当然，在这种情况下，当将业务规则应用到一组数据时，意味着每个记录都是单独处理的(逐行处理)。这个过程方法在Java、c#等语言中使用。虽然使用语言开发软件是一种正确的方法，但在编写数据库级(SQL)的查询时，却不会产生同样的效果。

下面用两种不同的方法来解决同一个示例问题，并将结果进行比较。看看CUSTOMERS表中对应的每个客户在SALES表中有多少条记录。

过程式方法如下:

SET AUTOTRACE ON

SELECT

      c.cust_id,

       (SELECT COUNT (*)

          FROM sh.sales s

         WHERE s.cust_id = c.cust_id)

          sa_count

  FROM SH.CUSTOMERS c;

Plan hash value: 881374884

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

    2454756  consistent gets

          0  physical reads

          0  redo size

     925474  bytes sent via SQL*Net to client

      41104  bytes received via SQL*Net from client

       3701  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      55500  rows processed

现在，采用基于SET的方法来编写查询。

SET AUTOTRACE ON

SELECT

        c.cust_id, COUNT (s.cust_id) jh_count

    FROM SH.CUSTOMERS c, sh.sales s

   WHERE c.cust_id = s.cust_id(+)

GROUP BY c.cust_id;

Plan hash value: 716053480

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        742  consistent gets

          0  physical reads

          0  redo size

     925474  bytes sent via SQL*Net to client

      41104  bytes received via SQL*Net from client

       3701  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      55500  rows processed

可以看到在两个查询的consistent gets数量之间的差异（当检查缓冲区缓存读到的块数据时）是巨大的。使用两种不同方法编写的查询在运行时导致不同时间。这种差别可以用性能来解释。

在另一个例子中，常见的习惯是在SQL语句中调用PL/SQL函数。作为过程式工作的例子，也是一种解决问题的方法。还有其他一些影响在SQL内调用PL/SQL代码性能的不利因素，但在本文中，不会提到性能问题。

下面编写查找客户表中每个客户的购买金额的代码。

过程方法:

在第一步中，创建一个PL/SQL函数来计算每个客户的总数，然后在代码和输出中调用这个函数。

CREATE OR REPLACE FUNCTION get_grand_total (

   p_cust_id_in IN SH.CUSTOMERS.CUST_ID%TYPE)

   RETURN NUMBER

IS

   r_grand_total   NUMBER;

BEGIN

   SELECT SUM (amount_sold)

     INTO r_grand_total

     FROM sh.sales

    WHERE cust_id = p_cust_id_in;

   RETURN r_grand_total;

END;

SET AUTOTRACE ON

SELECT cust_id,

            get_grand_total (cust_id) grand_total

FROM sh.customers;

Statistics

----------------------------------------------------------

      55503  recursive calls

          0  db block gets

    3066293  consistent gets

          0  physical reads

          0  redo size

     890447  bytes sent via SQL*Net to client

      41104  bytes received via SQL*Net from client

       3701  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      55500  rows processed

现在，采用基于SET的方法来编写查询。

SET AUTOTRACE ON

  SELECT c.cust_id, SUM (amount_sold)

    FROM SH.CUSTOMERS c, sh.sales s

   WHERE c.cust_id = s.cust_id(+)

GROUP BY c.cust_id;

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       1841  consistent gets

          0  physical reads

          0  redo size

     890452  bytes sent via SQL*Net to client

      41104  bytes received via SQL*Net from client

       3701  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      55500  rows processed