开发/数据库

  |  手机版

收藏网站

投稿QQ:1745232315

IT专家网,汇聚专业声音 解析IT潮流 IT专家网,汇聚专业声音 解析IT潮流

网络

专家视点在现场环球瞭望
IT专家网 > 开发/数据库

如何编写有效的SQL查询?有哪些方法?

作者:ITeye出处:论坛2018-09-07 16:48

如何编写有效的SQL查询,这是一个很大的难题之一。今天,小编就要为大家带来相关知识。

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

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

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

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

过程式方法如下: 

代码 
  1. SET AUTOTRACE ON  
  2. SELECT   
  3.       c.cust_id,  
  4.        (SELECT COUNT (*)  
  5.           FROM sh.sales s  
  6.          WHERE s.cust_id = c.cust_id)  
  7.           sa_count  
  8.   FROM SH.CUSTOMERS c;  
  9. Plan hash value: 881374884  
  10. Statistics  
  11. ----------------------------------------------------------  
  12.           0  recursive calls  
  13.           0  db block gets  
  14.     2454756  consistent gets  
  15.           0  physical reads  
  16.           0  redo size  
  17.      925474  bytes sent via SQL*Net to client  
  18.       41104  bytes received via SQL*Net from client  
  19.        3701  SQL*Net roundtrips to/from client  
  20.           0  sorts (memory)  
  21.           0  sorts (disk)  
  22.       55500  rows processed  


现在,采用基于SET的方法来编写查询。 
代码 
  1. SET AUTOTRACE ON  
  2. SELECT   
  3.         c.cust_id, COUNT (s.cust_id) jh_count  
  4.     FROM SH.CUSTOMERS c, sh.sales s  
  5.    WHERE c.cust_id = s.cust_id(+)  
  6. GROUP BY c.cust_id;  
  7. Plan hash value: 716053480  
  8. Statistics  
  9. ----------------------------------------------------------  
  10.           1  recursive calls  
  11.           0  db block gets  
  12.         742  consistent gets  
  13.           0  physical reads  
  14.           0  redo size  
  15.      925474  bytes sent via SQL*Net to client  
  16.       41104  bytes received via SQL*Net from client  
  17.        3701  SQL*Net roundtrips to/from client  
  18.           0  sorts (memory)  
  19.           0  sorts (disk)  
  20.       55500  rows processed  


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

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

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

过程方法: 

在第一步中,创建一个PL/SQL函数来计算每个客户的总数,然后在代码和输出中调用这个函数。 
代码 
  1. CREATE OR REPLACE FUNCTION get_grand_total (  
  2.    p_cust_id_in IN SH.CUSTOMERS.CUST_ID%TYPE)  
  3.    RETURN NUMBER  
  4. IS  
  5.    r_grand_total   NUMBER;  
  6. BEGIN  
  7.    SELECT SUM (amount_sold)  
  8.      INTO r_grand_total  
  9.      FROM sh.sales  
  10.     WHERE cust_id = p_cust_id_in;  
  11.    RETURN r_grand_total;  
  12. END;  
  13. SET AUTOTRACE ON  
  14. SELECT cust_id,   
  15.             get_grand_total (cust_id) grand_total   
  16. FROM sh.customers;  
  17. Statistics  
  18. ----------------------------------------------------------  
  19.       55503  recursive calls  
  20.           0  db block gets  
  21.     3066293  consistent gets  
  22.           0  physical reads  
  23.           0  redo size  
  24.      890447  bytes sent via SQL*Net to client  
  25.       41104  bytes received via SQL*Net from client  
  26.        3701  SQL*Net roundtrips to/from client  
  27.           0  sorts (memory)  
  28.           0  sorts (disk)  
  29.       55500  rows processed  



现在,采用基于SET的方法来编写查询。 
代码 
  1. SET AUTOTRACE ON  
  2.   SELECT c.cust_id, SUM (amount_sold)  
  3.     FROM SH.CUSTOMERS c, sh.sales s  
  4.    WHERE c.cust_id = s.cust_id(+)  
  5. GROUP BY c.cust_id;  
  6. Statistics  
  7. ----------------------------------------------------------  
  8.           1  recursive calls  
  9.           0  db block gets  
  10.        1841  consistent gets  
  11.           0  physical reads  
  12.           0  redo size  
  13.      890452  bytes sent via SQL*Net to client  
  14.       41104  bytes received via SQL*Net from client  
  15.        3701  SQL*Net roundtrips to/from client  
  16.           0  sorts (memory)  
  17.           0  sorts (disk)  
  18.       55500  rows processed  


在本例中,通过查看consistent GETS和递归调用输出,我们可以看到相同的情况。 

我们的查询也是生成更高效的数据库操作的第一步,它考虑的是批处理,而不是逐行思考。在进行数据库操作时,批处理的方法会让你在一天结束时消耗更少的资源,从而提高工作效率。

希望以上知识能够对你有所帮助。

相关文章

关键词:SQL查询,SQL

责任编辑:林音子

网警备案