《SQL语言艺术》读数笔记(2)—高效访问

• 前言

  ---

      SQL的基本原则不多，访问数据库时要实现什么目标以及如何实现会是本节的重要内容。

  
  

• 高效访问

  ---

  
  

1. 辨别查询

   ---

       易识别的语句有助于定位性能问题。消耗CPU资源多的进程能大概确定是由哪些SQL造成的，但要在系统中定位应用的哪部分提交这些SQL则困难得多。所以，程序和关键模型要添加注释，便于辨识查询在程序中的位置。Oracle的dbms_application_info 包提供了注释记录的功能。

   #例#古时的军装和军旗便于将军判断激战中部队位置。

   
   

2. 保持数据库连接的稳定

   ---

       应用允许多重连接会有很大的隐患。数据库连接是很消耗资源的操作，首先操作系统方面：①客户端与服务器建立握手协议；②客户端与远程服务器连接需要建立监听程序；③监听程序创建进程/线程来执行数据库核心程序或把客户请求传递给服务器进程。其次，数据库系统也有开销：①检查账户密码；②执行登录触发器，初始化存储过程和程序包；③为每次session建立新环境以追踪行为。因此，连接池等永久数据库连接技术对性能十分重要。

       另外，程序与数据库的交互也有开销，因为程序与DBMS核心有上下文切换(Linux在多任务间切换要保留CPU寄存器和程序计数器现场)，所以逐行处理指令会严重影响性能。如果*DBMS支持数据通过数组传递且数组接口时隐式的，应该毫不犹豫选用它并根据需要修改接口默认大小。

   #例#一个应用处理很多小文本，文本中每一行包含一个连接数据库加载数据(范例中使用insert 语句，直接加载技术会更快)的指令。仅连接数据库一次然后逐行插入数据比频繁连接中断速率提升约200倍；而再改进为多行数据(100行)批次插入以减少程序与数据库核心交互次数，性能会是最初的1200倍。

   
   

3. 关注实现策略

   ---

       着眼于结果规划解决方案，有时按照业务细节流程步骤写出来的SQL性能可能会很慢。

   #例#根据现有库存和各地订单统计要订购的原料数量。若把每个数据源的数据插入主表，对数据合并更新再删除无关数据，总共要20分钟；而from加上union子句的查询就能获得所有数据的聚合，耗时降至20秒。

   
   

4. 保持数据库Schema稳定

   ---

       不应在程序中使用DDL增删改数据库对象，除了分区和DBMS的临时表。因为DDL操作以核心数据库的数据字典为基础，任何对数据字典的操作都会引起全局锁，对系统影响很大。尚能接受的DDL有truncate，因为其清空表速度很快，不过注意其操作是不可回滚恢复的。

   
   

5. 直接操作实际数据

   ---

       建立临时表意味着以不太合理的方式存储更多信息，也反映“跳出业务流程细节解决问题”的能力不足。这种做法不太可取，原因有：①永久表可以设置复杂的存储选项；②DBMS在活动少时会收集统计信息，这时临时表可能为空，影响到后续的执行计划；③临时表的索引可能不是最优，且建表填入数据也多了开销。

       另，MySQL中永久表和临时表同名不会报错，要求建临时表要有好的习惯：①使用IF NOT EXIST；②命名加以tmp 区分。
   

   
   

6. 用SQL处理集合

   ---

       SQL基于集合处理数据(记录无序性决定的)，所以对于更新或删除操作，要先明确处理集合的规模以及处理操作的粒度。除非数据库难以修改，“一次大批量处理”要比“多次小块处理”要方便：①分隔多次处理需要占用过多空间保存原始数据；②修改失败后的回滚操作更复杂，需要确定失败发生的时间和位置。

       另，处理数据时应考虑事务失败所需的日志大小，以及数据修改的频率问题。若undo保存所需的数据量很大，应该将大规模的“月更新”改为“周更新”或“日更新”。

   
   

7. 包装SQL语句

   ---

       SQL擅长结构化查询，不擅长过程逻辑处理，所以尽量让其“短跑”。不要引入过程逻辑的原因有二：①数据库访问跨多个软件层以及网络访问，多条提取中间结果的语句意味着更多函数调用、更大带宽以及更长的等待时间；②单独一条SQL语句完成尽可能多的操作，维护优化的工作就能交由开销优化器(CBO)以及DBMS核心负责，而非由自己的程序负担。

   
   

8. 充分利用每次数据库访问

   ---

       若计划提取多段信息，即使这些信息“互不相关”，也应该在一次操作中完成提取。因为按计划顺序逐个字段提取会增加连接数据库的开销。

   #例#面向对象提倡为每个属性定义一个get，把关系数据库类比为OO编程(表等同于类，字段等同于属性，记录等同于对象)其实是不恰当的，因为其应用场景以及底层构造都不同。

   
   

9. 接近DBMS核心

   ---

       代码执行越接近DBMS核心，执行速度越快。如果使用函数，始终要首选DBMS自带的函数。某些数据库支持用C 等底层扩展功能，为了防止指针处理出错影响内存，DBMS核心会采用沙盒环境执行代码(出错也不会影响到数据)。*但处理大数据表的每一行时，不要用外部函数。

   #例#Oracle在外部函数和自身实现之间有一套通信机制，类似于数据库连接的方法。

   #例#书中提供了剔除字符串重复空格符的三种函数实现，并使用10000条随机记录进行测试(关闭头信息和屏幕显示，禁止输出确保测得的不是显示结果所花费的时间)。结果显示使用SQL函数的速度是PL/SQL存储过程的1.8倍(Instr()+Itrim())和2.2倍(replace())
   

   
   

10. 充分利用数据库的隐含功能

    ---

        数据库有许多隐含实现的功能，没必要编程重复实现。因为这可能存在重复处理。

    #例#开发者常用count(*)只为了测试“是否存在”，存在(counter>0)就进一步处理。其实可以在第一步操作就能确定并返回受影响的记录，不必统计记录数再全表扫描。例如通过API的mysql_affected_rows() 、系统变量(Transact-SQL的@@ROWCOUNT, PL/SQL的SQL%ROWCOUNT)、内嵌式SQL通讯区(SQLCA)的特殊字段以及JDBC的executeUpdate() 都能直接返回要处理的记录。此外，如果要更新或插入记录，使用数据库的专用语句(如Oracle 9i的MERGE)也比使用count检查键快得多。

    
    

11. SQL语句反映业务逻辑

    ---

        数据库系统有监控功能，可以查看当前正在执行的语句及其执行次数。执行语句的活动和业务活动的数量关系是否合理体现了SQL语句是否恰当。

    #例#业务逻辑有对应的“业务单元”——待处理的订单、待处理的请求、待结账的客户等。如果客户量一定，那么数据库初始化活动数量应该与之相当；如果查询customers 表的次数比正在处理的客户量多20倍，可能是查询对表的相同记录做了多余的访问，而不是一次获得所需信息。

    
    

12. 把逻辑放到查询中

    ---

        虽然SQL是非过程性语言，但也能实现一定的过程逻辑，且将宿主语言的过程逻辑内嵌到SQL中效率会更高。SQL不需要循环能力，因为它本质上是在操作集合，只需要执行条件逻辑的能力。条件逻辑的IF 能通过where 子句实现。ELSE (分组处理)的实现要复杂些。Oracle的case 能等效实现：

    CASE
    WHEN condition1 THEN <return sth>
      WHEN condition2 THEN <return sth else>
      ...
      ELSE <fall back on this value>
    END  
    

        数值或日期的比较使用Oracle 的greatest() 或least() 和MySQL的strcmp() 即可。*另，使用多重或条件insert 语句+merge 语句也能增加过程逻辑。

    
    

13. 一次完成多个更新

    ---

        *如果更新的是无关的记录，可以对一张表进行多次update；否则，应该合并成一个update 操作。比如同一个表的两个连续更新幸运的时候能走两次索引，否则起码要进行一次全表扫描。合并为一个update 几乎不会带来损失，还会减少重复访问。

    #例# 两次update
    

    UPDATE invoice
    SET pga_status = 0
    WHERE pga_status in (1,3)
      AND inv_type = 0;
    UPDATE invoice
    SET rd_status = 0
    WHERE rd_status in (1,3)
      AND inv_type = 0;
    

    #例# 合并update，使用了隐私的条件逻辑。*虽然可能重复更新相同内容的字段，但仍比多个update 快。

    UPDATE invoice
    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;      
    

    
    

14. 慎用自定义函数

    ---

        模块化编程是软开的传统，但数据库编程中，代码是开发者和数据库引擎的共享活动。使用自定义函数会带来许多问题：①开销优化器(CBO)不能优化自定义函数；②自定义函数会引入多次递归调用(放在SELECT 中，每返回一行数据就调用一次；放在WHERE 中，每次成功过滤都要调用一次)。

    #例#航班信息表与机场IATA代码参照表查找航班目的地城市信息的例子中，书中使用自定义函数隐含实现关联子查询是直接使用关联查询耗时的3倍(返回77行记录)。这在大型系统中会有更明显的差异，还会影响到支持并发的用户数量。
    

    
    

15. 进攻式编程

    ---

        编程一般建议进行防御式编程(code defensively)，即检查确认所有参数的合法性再进行处理。而数据库编程采用进攻式编程(code offensively)，假设最可能的结果，尽量同时做多件事情，不是必要情况不采用异常捕捉处理。

    
    

    #例#付款处理涉及一系列检查：所提交客户和卡号是否匹配；信用卡是否过期；当前支付额是否超过信用额度等。防御式编程的流程是先进行存在性检验，再检查字段的值。

    --检查客户存在
    SELECT count(*)
    FROM customers
    WHERE customer_id = provided_id
    --检查客户条件是否达标
    SELECT card_num, expiry_date, credit_limit
    FROM accounts
    WHERE customer_id = provided_id
    

             进攻式编程在可能性基础上判定检查客户存在性无意义(customer_id 出错不会有记录返回)。其次，达标条件可通过关联后的值是否为null 进一步判定。

    --直接返回达标账户
    UPDATE accounts
    SET balance = balance - purchased_amount
    WHERE balance >= purchased_amount
      AND credit_limit >= purchased_amout
      AND expiry_date > today()
      AND customer_id = provided_id
      AND card_num = provided_cardnum
    --无返回数据再判定是否数据异常，如card_num是null则卡号错了
    SELECT c.customer_id, a.card_num, a.expiry_date, 
      a.credit_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     
    

    
    

16. 精明使用异常

    ---

        数据库编程中，不是所有异常都使用同样的处理方式--“好”异常应该先抛出优化处理；

    “坏”异常在真正发生时再抛出。

    首先要避免使用异常，预测异常并提前处理。

    因为异常会迫使开发者使用过程式逻辑，往往需要一次一行地处理，效率很低。

    #例#重复键排查问题，每引入一个键都要检查是否违反了唯一性约束。合并公司设定员工邮箱标准格式为<Name>，最多12字符，空格引号用下划线代替的场景中，就有唯一性问题。employee_old 的3000条记录中有33个潜在重复项，使用*异常处理(剔除问题记录，再执行INSERT...SELECT )将耗时18s：
    

    DECLARE
      v_counter varchar2(12);
      b_ok      boolean;
      n_counter number;
      cursor c is SELECT emp_num, emp_name, emp_firstname
                  FROM employees_old;
    BEGIN
      FOR rec in c
      LOOP
        BEGIN
          INSERT INTO employees(emp_num, emp_name, emp_firstname, emp_email)
          VALUES (rec.emp_num, rec.emp_name, rec.emp_firstname,
            substr(substr(rec._emp_firstname, 1, 1)
            || translate(rec.emp_name, '''', '_ _'), 1, 12));
        EXCEPTION
          WHEN dup_val_on_index THEN
            b_ok := FALSE;
            n_counter := 1;
            BEGIN
              v_counter := ltrim(to_char(n_counter));
          INSERT INTO employees(emp_num, emp_name, emp_firstname, emp_email)
          VALUES(rec.emp_num, rec.emp_name, rec.emp_firstname, 
                 substr(substr(rec.emp_firstname, 1, 1)
                   || translate(rec.emp_name, '''', '_ _'), 1,
                   12-length(v_counter)) || v_counter);
          b_ok := TRUE;
        EXCEPTION
          WHEN dup_val_on_index THEN
            n_countser := n_counter + 1;
          END;
        END;
      END LOOP;
    END;
    

    避免执行异常的方式，先按重复名字员工更具年龄添加邮箱编号，耗时11s，速度提升了40%：

    INSERT INTO employees(emp_num, emp_firstname, emp_name, emp_email)
    SELECT emp_num, emp_firstname, emp_name,
      decode(rn, 1, emp_email, 
             substr(emp_email, 1, 12-length(ltrim(to_char(rn))))
                   || ltrim(to_char(rn)))
    FROM (SELECT emp_num, emp_firstname, emp_name,
          substr(substr(emp_firstname, 1, 1)
                || translate (emp_name, '''', '_ _'), 1, 12)
                order by emp_num) rn
          FROM employees_old)                        
    

    
    

• 总结

  ---

      明确SQL是声明式语言，擅长做什么而非怎么做。

  实现查询时应该优先使用数据库提供的内建函数以及隐含功能。

  编写数据库程序尽量做到：

  ①采用进攻式编程，面向最可能的情况执行；

  ②预测异常出现的情况，通过数据库的监测功能关注业务逻辑与程序执行情况是否相符，提前优化问题避免逐一捕捉异常；

  ③尽可能多地在一条语句中完成多部操作，减少连接访问数据库以及全表扫描的次数；

  ④部分情况下可以将宿主语言的过程逻辑放在SQL中实现；

  ⑤慎用CBO难以优化的自定义函数。

  
  

  
  

引用内容：

1.《SQL语言艺术》

2.封面--Google 图片