高效访问数据库—SQL语言技术

在我们编程的时候，涉及到数据库的操作，就效率而言，其实对我们一般的程序员来说，最重要的莫过于对数据库的访问了，如果你能保证尽量少的Connection，而在一次Connection中做尽量多的事情 ，就达到了效率的第一层了，第二层的话你就要尽量使用一句sql语句做更多的事情 ，再往上的话就得了解数据库的物理存储机制，其实我觉得对于一般的程序员而不是数据库管理员的话，了解第一第二层就行了，而实际上，现实生活中，达不到第二层的程序员大有人在，更有甚者，达不到第一层的也还是有不少人的。虽然现在orm框架很流行，但是了解sql其实还是蛮有益处的。
高效访问数据库这章是将通过哪些关键目标来达到高效访问数据库的目的。
这里我列举了几个我感兴趣的目标。

1、保持数据库连接稳定Stable Database Connections

其实就是讲的就是尽量减少数据库次数 的重要性。

例：

如果依次对每一行作连接/中断，结果是7.4行/秒;

连接一次，所有行逐个插入，结果是1681行/秒;

连接一次，以10行为一数组插入，结果是5914行/秒;

连接一次，以100行为一数组插入，结果是9190行/秒。

原因：(1)数据库连接时很“重”的操作，消耗资源很多。(2)你的程序(甚至包括存储过程)和数据库之间的交互也有开销。

也就是说要尽量减少数据库次数 ，而在一个连接中，也要减少程序与数据库的交互 ，程序和DBMS核心之间的上下文切换也有代价，如果DMBS支持数据通过数组传递，应毫不犹豫的使用它。

数据库连接和交互好似万里长城——长度越长，传递信息越耗时。

2、战略优先于战术Strategy Before Tactics

考 虑解决问题方案的细节之前，先站得远一些，把握大局。即要站在不同角度思考问题 。

3、用SQL处理集合 Set Processing in SQL

如果在存储过程中需要用游标，数据库很大的话，应该考虑的是执行该存储过程的频率 。也许以前是每月更新，考虑每周更新，设置可以是每日更新，而纠结于拆分sql语句并不是个好办法。

4、动态丰富的SQL语句 Action-Packed SQL Statements

可能在编写SQL时会有如下情况：用输入值从数据库中检查到一个或多个另外的数据值，然后借助循环或条件逻辑将一些语句组织起来，对数据库进行操作。这样做的话会出现多个SQL。

其实这样编写SQL是不必要的，许多负责操作往往可由一条SQL完成。如果用户提供了一些数据值，尽量不要将操作分解为多条提取中间结果的语句。不要在SQL中引入过程逻辑是因为

(1)数据库访问，总会跨多个软件层，甚至包括网络访问。

(2)在SQL中引入过程逻辑，意味着性能和维护问题将由你的程序承担。

尽可能多地把事情交给数据库优化器来承担 。

5、充分利用每次数据库访问 Profitable Database Accesses

如果需要多个字段的数据，千万不要逐个字段地提取，而应一次操作全部完成，与1类似。

在合理范围内，利用每次数据库访问完成尽量多的工作 。

6、接近DBMS核心

同样的结果，如果一个是通过数据库内置函数得到的，而另一个是通过自定义函数得到的，那显然第一种最快。

代码喜欢SQL内核——离核心越近，它就运行得越快。

7、只做必须做的Doing Only What Is required

select count(*) into counter where table_name where <certain_condition>

if(count>0)then

在大多数情况下,select count(*)完全不需要，如果要对多项记录进行操作，直接做即可。如果是为了更新或插入记录，也可以使用数据库的专用的SQL语句，如Oracle9i提供的Merge语句。

统计记录数极可能意味着重复全部搜索，因为它对相同数据处理了2次。

没必要编程实现那些数据库隐含实现的功能。

8、把逻辑放到查询中Program Logic into Queries

只要有可能，应尽量把条件逻辑放到SQL语句中，而不是SQL的宿主语言中。

9、一次完成多个更新 Mulitiple Updates at Once

update tbo_invoice_extractor

set pga_status=0 where pga_status in (1,3) and inv_type=0;

update tbo_invoice_extractor

set rd_status=0 where rd_status in (1,3) and inv_type=0;

改为

update tbo_invoice_extractor

set pga_status=(case pga_status when 1 then 0 when 3 then 0 else pga_status end),

rd_status = (case rd_status when 1 then 0 when 3 then 0 else rd_status end) where (pga_status in (1,3) or rd_status in (1,3)) and inv_type=0;

有可能的话，用一个语句处理多个更新；尽量减少对同一个表的重复访问

10、慎用自定义函数 Careful Use of User-Written Functions

优化器对自定义函数的代码无能为力。

11、SQL的进攻式编程Offensive Coding With SQL

尽量同时做几件事情 的进攻式编程有切实的优势。

例：进行一连串检查，每当其中一个检查所要求的条件不符时就产生异常。信用卡付款的处理中就涉及类似步骤。例如，检查所提交的客户身份和卡号是否有效，以及2者是否匹配；检查信用卡是否过期；检查当前的支付额是否超过了信用额度。通过了所有检查，支付才会继续。

select count(*) from customers where customer_id = provided_id;

select card_num,expiry_date,credit_limit from accounts where customer_id = provided_id

update accounts set balance = balance - purchased_ammount where card_num = provided_cardnum and custom_id = provided_id

改为：

update accounts set balance = balance - purchased_ammount

where balance >= purchased_ammount

and credit_limit >=purchased_amount

and espiry_date> today()

and card_num = provided_cardnum

and custom_id = provided_id

如果更新的行数为0，只需执行一个操作

select c.customer_id,a.card_num,a.expiry_date,a.creadit_limit,a.balance

from customers c

left outer join accounts a on a.customer_id = c.customer_id and a.card_num = provided_cardnum

where c.customer_id = provided_id

如果此查询没有数据，则可断定customer_id的值是错的；如果card_num是null，则可断定卡号是错的；等等，其实大多数情况下此查询并不需要。

这种方法可以称为乐观并发控制 ，乐观方法比悲观方法的吞吐量高很多。