ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 高性能 SQL

高性能 SQL

原创 Linux操作系统 作者:ArtCode 时间:2009-01-09 12:41:18 0 删除 编辑

简介

在过去一年半的时间里,在 标准 TPC-C 基准 方面有很多 DB2 for LUW 活动,这个基准常用于测试在线事务处理环境中的性能。其结果跨度很大,在具备通常配置的机器上,它可能是 非常小的数字,在那些家庭和工作中不多见的 特殊配置 上,又可能是非常大的数字。

虽然填充数据库模式的行的数量可能被调整到一定规模,但有一个地方仍然是相同的:SQL。在本文中,您将认识 TPC-C 基准的逻辑,理解 SQL 技术,并发现如何在实际的客户环境中使用 SQL 技术。

为了达到这个目的,作者 Serge Rielau 这样来组织这篇文章:首先介绍该基准的 DB 模式以及它的事务。接着,他将分析每个事务,并解释每个事务的 SQL 特性。到本文的最后,您应该对 TPC-C 基准以及 DB2 的高级 SQL 特性,包括它们背后的机理有更好的理解。







TPC-C: 基准简述

TPC-C 基准使用一小组事务和表来模拟一家分销企业,该企业有一个产品销售周期。每个事务的外部模式和语义都被 正式指定。为了简短地概述一下,并提供一个基本模式以供使用,下面给出了一个概要。 其中,表用 粗体标记,事务用 斜体 显示:

用于该基准的数据库主要由仓库(warehouses)、地区(districts)、产品(items)和客户(customers)组成。在每个 WAREHOUSE 中的 STOCK(库存)里有 100,000 个 ITEM。每个仓库为 10 个 DISTRICT 服务。每个地区为 3000 名 CUSTOMER 服务,这些客户 ORDER(订购)新产品。 每份 ORDER(订单)最多可以由 15 种不同产品组成,称作 ORDER_LINE(订购项)。在给订单发货之前,订单作为一个 NEW_ORDER 进行排队,仓库中每种产品的库存要进行调整……在 DELIVERY(发货)时,要向客户收费。在 PAYMENT(支付)时,该事务被存档在 HISTORY(历史)中,收入要记帐。

此外,客户可以查问他们的 ORDER STATUS(订单状态),仓库管理员可以查问订单上所订产品的 STOCK LEVEL(库存水平)。

为了更切合实际,客户容易忘记他们的客户 ID,因此必须能够按用户的姓氏(last name)查找客户 ID,但客户的姓可能有重复现象。而且,本地的仓库可能无法满足所有的订单,这要求从远处的仓库发货过来。

一个仓库所能处理的新订单的数量限制在每分钟 9 到 12.86 个事务之间。这意味着,为了达到 320 万 TpmC (NEW ORDER Transactions per minute TPC-C),至少需要借助 256,000 多个仓库。这意味着有 77 亿 注册客户。因此,除非您有足够的磁盘和一个大的保险丝,否则不要试。

在介绍 5 个事务之前,先看看各个表的 DDL。这些 DDL 都很明了,无需解释。


清单 1. 创建 TPC-C 数据库表的 DDL
                
  1 
        CREATE TABLE WAREHOUSE
  2      (
  3        W_NAME      CHAR(10)   
        NOT NULL,
  4        W_STREET_1  CHAR(20)   
        NOT NULL,
  5        W_STREET_2  CHAR(20)   
        NOT NULL,
  6        W_CITY      CHAR(20)   
        NOT NULL,
  7        W_STATE     CHAR(2)    
        NOT NULL,
  8        W_ZIP       CHAR(9)    
        NOT NULL,
  9        W_TAX       INTEGER    
        NOT NULL,
 10        W_YTD       BIGINT     
        NOT NULL,
 11        W_ID        INTEGER    
        NOT NULL,
 12        
        PRIMARY KEY (W_ID)
 13      ); 
 14
 15 
        CREATE TABLE DISTRICT
 16      (
 17        D_NEXT_O_ID INTEGER         
        NOT NULL,
 18        D_TAX       INTEGER         
        NOT NULL,
 19        D_YTD       BIGINT          
        NOT NULL,
 20        D_NAME      CHAR(10)        
        NOT NULL,
 21        D_STREET_1  CHAR(20)        
        NOT NULL,
 22        D_STREET_2  CHAR(20)        
        NOT NULL,
 23        D_CITY      CHAR(20)        
        NOT NULL,
 24        D_STATE     CHAR(2)         
        NOT NULL,
 25        D_ZIP       CHAR(9)         
        NOT NULL,
 26        D_ID        SMALLINT        
        NOT NULL,
 27        D_W_ID      INTEGER         
        NOT NULL,
 28        
        PRIMARY KEY (D_ID, D_W_ID)
 29      );
 30
 31 
        CREATE TABLE ITEM
 32      (
 33        I_NAME          CHAR(24)    
        NOT NULL,
 34        I_PRICE         INTEGER     
        NOT NULL,
 35        I_DATA          VARCHAR(50) 
        NOT NULL,
 36        I_IM_ID         INTEGER     
        NOT NULL,
 37        I_ID            INTEGER     
        NOT NULL,
 38        
        PRIMARY KEY (I_ID)
 39      );
 40
 41 
        CREATE TABLE STOCK
 42      (
 43        S_REMOTE_CNT    INTEGER     
        NOT NULL,
 44        S_QUANTITY      INTEGER     
        NOT NULL,
 45        S_ORDER_CNT     INTEGER     
        NOT NULL,
 46        S_YTD           INTEGER     
        NOT NULL,
 47        S_DATA          VARCHAR(50) 
        NOT NULL,
 48        S_DIST_01       CHAR(24)    
        NOT NULL,
 49        S_DIST_02       CHAR(24)    
        NOT NULL,
 50        S_DIST_03       CHAR(24)    
        NOT NULL,
 51        S_DIST_04       CHAR(24)    
        NOT NULL,
 52        S_DIST_05       CHAR(24)    
        NOT NULL,
 53        S_DIST_06       CHAR(24)    
        NOT NULL,
 54        S_DIST_07       CHAR(24)    
        NOT NULL,
 55        S_DIST_08       CHAR(24)    
        NOT NULL,
 56        S_DIST_09       CHAR(24)    
        NOT NULL,
 57        S_DIST_10       CHAR(24)    
        NOT NULL,
 58        S_I_ID          INTEGER     
        NOT NULL,
 59        S_W_ID          INTEGER     
        NOT NULL,
 60        
        PRIMARY KEY (S_I_ID, S_W_ID)
 61      );
 62
 63 
        CREATE TABLE CUSTOMER
 64      (
 65        C_ID            INTEGER       
        NOT NULL,
 66        C_STATE         CHAR(2)       
        NOT NULL,
 67        C_ZIP           CHAR(9)       
        NOT NULL,
 68        C_PHONE         CHAR(16)      
        NOT NULL,
 69        C_SINCE         BIGINT        
        NOT NULL,
 70        C_CREDIT_LIM    BIGINT        
        NOT NULL,
 71        C_MIDDLE        CHAR(2)       
        NOT NULL,
 72        C_CREDIT        CHAR(2)       
        NOT NULL,
 73        C_DISCOUNT      INTEGER       
        NOT NULL,
 74        C_DATA          VARCHAR(500)  
        NOT NULL,
 75        C_LAST          VARCHAR(16)   
        NOT NULL,
 76        C_FIRST         VARCHAR(16)   
        NOT NULL,
 77        C_STREET_1      VARCHAR(20)   
        NOT NULL,
 78        C_STREET_2      VARCHAR(20)   
        NOT NULL,
 79        C_CITY          VARCHAR(20)   
        NOT NULL,
 80        C_D_ID          SMALLINT      
        NOT NULL,
 81        C_W_ID          INTEGER       
        NOT NULL,
 82        C_DELIVERY_CNT  INTEGER       
        NOT NULL,
 83        C_BALANCE       BIGINT        
        NOT NULL,
 84        C_YTD_PAYMENT   BIGINT        
        NOT NULL,
 85        C_PAYMENT_CNT   INTEGER       
        NOT NULL,
 86        
        PRIMARY KEY (C_ID, C_D_ID, C_W_ID)
 87      );
 88 
        CREATE INDEX CUST_IDXB 
 89   
        ON CUSTOMER (C_LAST, C_W_ID, C_D_ID, C_FIRST, C_ID);
 90
 91 
        CREATE TABLE HISTORY
 92      (
 93        H_C_ID          INTEGER     
        NOT NULL,
 94        H_C_D_ID        SMALLINT    
        NOT NULL,
 95        H_C_W_ID        INTEGER     
        NOT NULL,
 96        H_D_ID          SMALLINT    
        NOT NULL,
 97        H_W_ID          INTEGER     
        NOT NULL,
 98        H_DATE          BIGINT      
        NOT NULL,
 99        H_AMOUNT        INTEGER     
        NOT NULL,
100        H_DATA          CHAR(24)    
        NOT NULL
101      );
102
103 
        CREATE TABLE ORDERS
104      (
105        O_C_ID          INTEGER     
        NOT NULL,
106        O_ENTRY_D       BIGINT      
        NOT NULL,
107        O_CARRIER_ID    SMALLINT    
        NOT NULL,
108        O_OL_CNT        SMALLINT    
        NOT NULL,
109        O_ALL_LOCAL     SMALLINT    
        NOT NULL,
110        O_ID            INTEGER     
        NOT NULL,
111        O_W_ID          INTEGER     
        NOT NULL,
112        O_D_ID          SMALLINT    
        NOT NULL,
113        
        PRIMARY KEY (O_ID, O_W_ID, O_D_ID)
114      );
115 
        CREATE INDEX ORDR_IDXB 
116      
        ON ORDERS (O_C_ID, O_W_ID, O_D_ID, O_ID DESC);
117
118 
        CREATE TABLE ORDER_LINE
119      (
120        OL_DELIVERY_D    BIGINT     
        NOT NULL,
121        OL_AMOUNT        INTEGER    
        NOT NULL,
122        OL_I_ID          INTEGER    
        NOT NULL,
123        OL_SUPPLY_W_ID   INTEGER    
        NOT NULL,
124        OL_QUANTITY      SMALLINT   
        NOT NULL,
125        OL_DIST_INFO     CHAR(24)   
        NOT NULL,
126        OL_O_ID          INTEGER    
        NOT NULL,
127        OL_D_ID          SMALLINT   
        NOT NULL,
128        OL_W_ID          INTEGER    
        NOT NULL,
129        OL_NUMBER        SMALLINT   
        NOT NULL,
130        
        PRIMARY KEY (OL_O_ID, OL_W_ID, OL_D_ID, OL_NUMBER)
131      );
132
133 
        CREATE TABLE NEW_ORDER
134      (
135        NO_O_ID         INTEGER     
        NOT NULL,
136        NO_D_ID         SMALLINT    
        NOT NULL,
137        NO_W_ID         INTEGER     
        NOT NULL,
138        
        PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID)
139      );             
      







NEW ORDER 事务(本地)

NEW ORDER 事务处理来自一个客户的新订单。这里需要做各种不同的事情:

  1. 获取下一个订单 id 和客户所在地区的地区税率。
  2. 增加客户所在地区的下一个订单 id,以便用于随后的事务。
  3. 对于客户订购的每种产品:
    1. 从 ITEM 表检索产品的名称、价格和描述。
    2. 从 STOCK 表检索该产品的地区信息和剩下的库存水平。
    3. 将该产品的库存水平减去订购的数量。如果库存低于阈值,则应该订购货物(通过简单地增加库存值来实现)。
    4. 将获得的相关数据,包括总价格,插入到 ORDER_LINE 中。
  4. 将订单插入 ORDERS 和 NEW_ORDER 表。
  5. 从 CUSTOMER 检索客户名、折扣、信用信息。
  6. 从 WAREHOUSE 检索销售税。
  7. 考虑折扣和税,计算总价格。

这是很大的工作量。不过,DB2 使用 SQL 语句来处理所有这些工作。其原理如下:

首先,DB2 处理 DISTRICT 表。这里需要返回数据,并执行更新。经验告诉我们,这需要 2 条 SQL 语句,并且 UPDATE 应该在 SELECT 语句之前执行;否则,当并发增多时,可能会发生死锁。

但是,DB2 支持一种新的 SQL 特性,这种特性正处在标准化的过程中。该特性允许访问触发器中所谓的 过渡表(transition table)OLD TABLE 过渡表保存了受影响的行在被 UPDATE 或 DELETE 语句处理之前的初始状态。 NEW TABLE 过渡表保存处理了 INSERT 或 UPDATE 语句之后受影响的行。这是 AFTER 触发器被激发之前的状态。懂得 Microsoft 或 Sybase 的用户可能知道这两个表,在 Microsoft 或 Sybase 中,这两个表的表名是 DELETEDINSERTED

DB2 所做的就是允许 UPDATE、DELETE 和 INSERT 出现在选择语句的 FROM 子句中,并允许用户选择应该从哪个过渡表进行选择:


清单 2. 使用过渡表
                
1  
        SELECT D_TAX, D_NEXT_O_ID  
2     
        INTO :dist_tax , :next_o_id
3     
        FROM OLD TABLE ( 
        UPDATE DISTRICT
4                       
        SET  D_NEXT_O_ID = D_NEXT_O_ID + 1
5                       
        WHERE D_W_ID = :w_id 
6                         
        AND D_ID = :d_id 
7                     ) 
        AS OT
      

通过查看 清单 3 中所示的优化器计划,可以很容易看出这种逻辑的优点:


清单 3. 使用过渡表的访问计划
                
                    Rows 
                   RETURN 
                   (   1) 
                    Cost 
                     I/O 
                     |
                      1 
                   UPDATE 
                   (   2) 
                   25.7261 
                      2 
                  /---+--\
                1          26 
             FETCH   TABLE: SRIELAU  
             (   3)     DISTRICT 
             12.872 
                1 
           /----+---\
         1            26 
      IXSCAN    TABLE: SRIELAU  
      (   4)       DISTRICT 
     0.0175755 
         0 
        |
        26 
  INDEX: SYSIBM   
 SQL0410231029415
      

这个组合的计划在结构上与单独的 UPDATE 语句几乎一样。TPC-C 规范将下一个订单 id 的存储放在 DISTRICT 表中。在客户环境中,可以很容易地转而使用一个 SEQUENCE 事务,以避免锁在一起。

现在,看看这三条 SQL 语句中的第二条 SQL 语句:


清单 4. 第二条 SQL 语句
                
 1  
        WITH DATA AS (  
        SELECT  O_ID , D_ID , W_ID , OL_NUMBER , I_ID
 2                        , W_ID AS I_SUPPLY_W_ID
 3                        , 0 AS OL_DELIVERY_D
 4                        , I_QTY
 5                        , ( I_PRICE * I_QTY ) 
        AS TOTAL_PRICE
 6                        , OL_DIST_INFO , I_PRICE, I_NAME, I_DATA, S_DATA
 7                        , S_QUANTITY
 8                     
        FROM ( 
        SELECT   :next_o_id as O_ID
 9                                   , :w_id AS W_ID
10                                   , :d_id as D_ID
11                                   , OL_NUMBER  , I_ID , I_QTY
12                              
        FROM  TABLE( 
        VALUES
13                                                (  1 , :id0  , :ol_quantity0  )
14                                              , (  2 , :id1  , :ol_quantity1  )
15                                              , (  3 , :id2  , :ol_quantity2  )
16                                              , (  4 , :id3  , :ol_quantity3  )
17                                              , (  5 , :id4  , :ol_quantity4  )
18                                              , (  6 , :id5  , :ol_quantity5  )
19                                              , (  7 , :id6  , :ol_quantity6  )
20                                              , (  8 , :id7  , :ol_quantity7  )
21                                              , (  9 , :id8  , :ol_quantity8  )
22                                              , ( 10 , :id9  , :ol_quantity9  )
23                                              , ( 11 , :id10 , :ol_quantity10 )
24                                              , ( 12 , :id11 , :ol_quantity11 )
25                                              , ( 13 , :id12 , :ol_quantity12 )
26                                              , ( 14 , :id13 , :ol_quantity13 )
27                                              , ( 15 , :id14 , :ol_quantity14 )
28                                         ) 
        AS X ( OL_NUMBER , I_ID  , I_QTY )
29                          ) 
        AS ITEMLIST
30                        , 
        TABLE( NEW_OL_LOCAL( I_ID , I_QTY , W_ID
31                                             , O_ID , D_ID  , SMALLINT(OL_NUMBER)
32                                             )
33                               ) 
        AS NEW_OL_LOCAL
34                     
        WHERE NEW_OL_LOCAL.I_PRICE 
        IS NOT NULL
33               )
36  
        SELECT I_PRICE , I_NAME , I_DATA , OL_DIST_INFO , S_DATA , S_QUANTITY
37    
        FROM NEW TABLE ( 
        INSERT INTO ORDER_LINE
38                       (   OL_O_ID       , OL_D_ID     , OL_W_ID      
39                         , OL_NUMBER     , OL_I_ID     , OL_SUPPLY_W_ID 
40                         , OL_DELIVERY_D , OL_QUANTITY , OL_AMOUNT 
41                         , OL_DIST_INFO
42                       )
43                       
        INCLUDE (   I_PRICE    INTEGER
44                                 , I_NAME     CHAR(24)
45                                 , I_DATA     VARCHAR(50)
46                                 , S_DATA     VARCHAR(50)
47                                 , S_QUANTITY SMALLINT )
48                        
        SELECT   O_ID          , D_ID    , W_ID           
49                               , OL_NUMBER     , I_ID    , I_SUPPLY_W_ID 
60                               , OL_DELIVERY_D , I_QTY   , TOTAL_PRICE 
61                               , OL_DIST_INFO  , I_PRICE , I_NAME
62                               , I_DATA        , S_DATA  , S_QUANTITY
63                        
        FROM DATA
64                    ) 
        AS INS
      

这是一条 重量级 SQL 语句。接下来我们逐步来分析这个查询:

  1. ITEMLIST 由输入参数组合而成。它包括产品以及每种产品的数量,这是在一个 VALUES 子句中收集到的。对于列表中的所有产品,订单 id、地区和仓库是不变的。
  2. 通过使用一个关联连接(correlated join), NEW_OL_LOCAL SQL 表函数逐个处理列表中的每种产品。很快您就可以看到,该函数返回产品的价格、某些元数据和库存信息。
  3. 对于不能找到的产品(价格为 NULL),可以将其过滤掉。将产生的表称作 DATA
  4. DATA 中的每种产品插入到 ORDER_LINE 中。
  5. 将每种产品的价格、名称、元数据和库存信息返回给用户。

这里有两件有趣的事情。

首先, NEW_OL _LOCAL 表函数实际上一次只返回一行。由于这一层关系,它操作起来更像是一个 用户定义的关系操作符

另外,正如您后面将会看到的, NEW_OL _LOCAL 表函数实际上是写一个表。这里您看到的是一个连接,内表在其中执行数据库修改。为允许这一点,并使数据库保持一致的状态,DB2 需要确信连接的内表和外表没有冲突;或者,DB2 需要外加一个称作 dam 的临时表,以确保在这个表函数开始处理之前,连接的外表被完全导出。由于这个 SQL 表函数是以 内联 SQL PL 编写的,这是 DB2 用于 SQL PL 的宏语言,因此 DB2 可以看穿这个函数,并得出结论,认为该事务在行为上没有问题,这种情况下不需要 dam。

提到连接的内表和外表之间各种可能的冲突之后,还应该提到的是,在这个函数与 INSERT 操作之间也可能发生类似的问题。为了避免太多的复杂性,DB2 强加了一条简单的规则:除非嵌入在相关的函数里面,否则 UPDATE、DELETE 或 INSERT ( 数据更改操作)不应该出现在连接中。而且, 数据更改操作 也不应该出现在嵌套查询中。

相反, 数据更改操作 应该限制在公共表表达式(CTE,也叫做“WITH 子句”)的顶层 SELECT 或最上面的 SELECT 中。在上述例子中,整个过程是这样的: DATA 执行一个 数据更改操作,并作为 CTE 放在 WITH 子句中。 现在它可以为 INSERT 操作提供数据了。

为什么要有这些规则?让 数据更改操作 留在 WITH 子句中,实际上便强加了一个非常自然的顺序,这样可用于解决冲突。

最后,在这个事务中还暴露了有关 数据更改操作 的另一个更新颖的特性: 那就是 INCLUDE-子句,像任何其他 数据更改语句 一样,INSERT 可以使用这个子句。这个子句允许通过附加额外的列来 扩展 OLD TABLE 或 NEW TABLE 过渡表。这些列只是通过 INSERT 操作搭载的,并且上面的 SELECT 可以访问这些列。在这个例子中,产品价格、名称、数据以及有关库存的数据不是 ORDER_LINE 表的一部分。这些列只是暂时传递、用以返回给用户的。

为什么会这么混乱呢?为什么不从 DATA 选择两次 —— 一次用于 INSERT,一次用于最后的 SELECT? 答案很简单:就像 SORT 一样,TEMP 是一个“4 个字母的单词”。

如果没有看 NEW_OL_LOCAL 表函数,那么对 NEW ORDER 事务的探索就不算完:


清单 5. NEW_OL_LOCAL 表函数
                
 1 
        CREATE FUNCTION NEW_OL_LOCAL(   I_ID      INTEGER
 2                               , I_QTY     SMALLINT
 3                               , W_ID      INTEGER
 4                               , O_ID      INTEGER
 5                               , D_ID      SMALLINT
 6                               , OL_NUMBER SMALLINT
 7                             )
 8 
        RETURNS TABLE(   I_PRICE       INTEGER
 9                , I_NAME        CHAR(24)
 0                , I_DATA        VARCHAR(50)
11                , OL_DIST_INFO  CHAR(24)
12                , S_DATA        VARCHAR(50)
13                , S_QUANTITY    SMALLINT
14              )
15 
        SPECIFIC NEW_OL_LOCAL
16 
        MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL
17 
18 VAR: 
        BEGIN ATOMIC
19   
        DECLARE I_PRICE       INTEGER      ;
20   
        DECLARE I_NAME        CHAR(24)     ;
21   
        DECLARE I_DATA        VARCHAR(50)  ;
22   
        DECLARE OL_DIST_INFO  CHAR(24)     ;
23   
        DECLARE S_DATA        VARCHAR(50)  ;
24   
        DECLARE S_QUANTITY    SMALLINT     ;
25
26   
        SET  ( I_PRICE , I_NAME , I_DATA )
27     =  ( 
        SELECT 
28                   I_PRICE
29                 , I_NAME
30                 , I_DATA
31 
32            
        FROM ITEM
33           
        WHERE ITEM.I_ID = NEW_OL_LOCAL.I_ID
34       )
35     ;
36     
        SET ( OL_DIST_INFO , S_DATA , S_QUANTITY )
37       = ( 
        SELECT   OL_DIST_INFO 
38                  , S_DATA 
39                 , S_QUANTITY
40             
        FROM NEW TABLE ( 
        UPDATE STOCK
41                                
        INCLUDE (  OL_DIST_INFO CHAR( 24 )  ) 
42                                
        SET S_QUANTITY = 
        CASE WHEN S_QUANTITY - NEW_OL_LOCAL.I_QTY >= 10
43                                                      
        THEN S_QUANTITY - NEW_OL_LOCAL.I_QTY
44                                                      
        ELSE S_QUANTITY - NEW_OL_LOCAL.I_QTY + 91
45                                                 
        END
46                                  , S_ORDER_CNT  = S_ORDER_CNT + 1
47                                  , S_YTD        = S_YTD + NEW_OL_LOCAL.I_QTY
48                                  , OL_DIST_INFO = 
        CASE D_ID 
        WHEN  1 
        THEN S_DIST_01
49                                                             
        WHEN  2 
        THEN S_DIST_02
50                                                             
        WHEN  3 
        THEN S_DIST_03
51                                                             
        WHEN  4 
        THEN S_DIST_04
52                                                             
        WHEN  5 
        THEN S_DIST_05
53                                                             
        WHEN  6 
        THEN S_DIST_06
54                                                             
        WHEN  7 
        THEN S_DIST_07
55                                                             
        WHEN  8 
        THEN S_DIST_08
56                                                             
        WHEN  9 
        THEN S_DIST_09
57                                                             
        WHEN 10 
        THEN S_DIST_10
58                                                   
        END
59                                 
        WHERE S_I_ID = NEW_OL_LOCAL.I_ID
60                                   
        AND S_W_ID = NEW_OL_LOCAL.W_ID  
61                           ) 
        AS U
62        )
63    ;
64    
        RETURN VALUES(   VAR.I_PRICE
65                   , VAR.I_NAME
66                   , VAR.I_DATA
67                   , VAR.OL_DIST_INFO
68                   , VAR.S_DATA
69                   , VAR.S_QUANTITY
70                 )
71    ;
72 
        END
            

这个函数实现上述步骤中的步骤 2。该函数为一个订购项(order line)检索产品信息,并执行必要的库存更新。注意,这个函数被定义为 MODIFIES SQL DATA。这个子句允许 SQL 表函数包含 UPDATE、DELETE、INSERT 和 MERGE 语句。还应注意,这里使用了 INCLUDE 子句,这一次是为了放弃 OL_DIST_INFO 列。这个列来自所存储产品的特定地区信息。最后,注意在 RETURN 语句中使用了单行的 VALUES 子句,以便将最后的结果以单行的表的形式返回。

为了从这个复杂的 SQL 语句中赚取性能,DB2 选择一个非常精妙的查询计划:


清单 6. 第二条 SQL 语句的访问计划
                
                          Rows 
                         RETURN 
                         (   1) 
                          Cost 
                           I/O 
                           |
                          14.4 
                         TBSCAN 
                         (   2) 
                         26.2997 
                         2.02765 
                           |
                          14.4 
                         SORT   
                         (   3) 
                         26.2978 
                         2.02765 
                           |
                          14.4 
                         INSERT 
                         (   4) 
                         26.2922 
                         2.02765 
                        /---+---\
                    14.4          44 
                   FILTER   TABLE: SRIELAU  
                   (   5)     ORDER_LINE 
                   13.4359 
                   1.02765 
                     |
                     15 
                   NLJOIN 
                   (   6) 
                   13.4334 
                   1.02765 
           /----------+---------\
        15                         1 
      TBSCAN                    NLJOIN 
      (   7)                    (   8) 
    0.000201927                 13.2461 
         0                      1.02765 
        |               /----------+----------\
        15            1                          1 
 TABFNC: SYSIBM    NLJOIN                     TBSCAN 
      GENROW       (   9)                     (  18) 
                   13.246                  4.48727e-005 
                   1.02765                       0 
           /----------+---------\               |
         1                         1             1 
      TBSCAN                    UNION    TABFNC: SYSIBM   
      (  10)                    (  11)        GENROW 
   4.48727e-005                 13.2457 
         0                      1.02765 
        |               /----------+----------\
         1          0.96                         1 
 TABFNC: SYSIBM    FETCH                      UPDATE 
      GENROW       (  13)                     (  15) 
                   12.8727                   0.371624 
                      1                      0.027648 
                 /----+---\                  /---+---\
               1            36         0.013824         9 
            IXSCAN    TABLE: SRIELAU    FETCH    TABLE: SRIELAU  
            (  14)         ITEM         (  16)        STOCK 
           0.0182935                   0.193765 
               0                       0.013824 
              |                       /----+---\
              36                0.013824          9 
        INDEX: SYSIBM            IXSCAN    TABLE: SRIELAU  
       SQL0410231029418          (  17)         STOCK 
                                0.0157303 
                                    0 
                                   |
                                    9 
                             INDEX: SYSIBM   
                            SQL0410231029421 
      

这是一个好计划 —— 但是它与前面的查询有什么关系呢?下面对该计划中的各项作一番介绍:

  • FETCH(13) 表示函数中的第一条 SET 语句,它从 ITEM 表中进行选择。
  • UPDATE(15) 显然是 STOCK 表的 UPDATE,包括将其派生出来的 SET 语句。
  • UNION(11) 不如它所声明的那么完整。在 内联 SQL PL 的上下文中,UNION 用于以一种连续的方式派生出语句。因此,这个 UNION 就相当于函数体 BEGIN ATOMIC...END。
  • TBSCAN(10) 初始化函数中的本地变量。这是 DECLARE 语句链。 FETCH(13)UPDATE(15) 实际上是读取 NLJOIN(9) 的外表的值,并赋上新值,覆盖本地变量的默认值。
  • TBSCAN(18) 表示这个 SQL 表函数中的 RETURN 语句。
  • 我们看一下 NLJOIN(8),它表示整个函数,从中可以看到一个按三种方法排序的连接:
    1. 初始化本地变量。
    2. 执行函数体。
    3. 返回结果表。
  • TBSCAN(7) 是该连接的外表,它为函数提供产品列表。
  • FILTER(5) 抛弃 I_PRICE IS NULL 的不合适的产品。
  • INSERT(4) 显然是到 ORDER_LINE 的 INSERT。
  • SORT(3) 有点特殊。因为这个查询是一个游标,因此 DB2 正面临一个窘境。该查询通过更新 STOCK 和 ORDER_LINE,而更改数据库的状态。然而,我们无法保证用户将真正取表中的所有行,从而使这些更改通过。因此,DB2 需要 dam 结果集,以确保当游标被打开时整个查询已经完成。出于性能的原因,这里为常规临时表上的 dam 选择一个没有排序键的 SORT。这些 常用的(do-at-open) 语义实际上有一个非常好的副作用。通过使用 cursor with hold,可以执行复杂的数据库操作,并在打开后提交这些操作。然后就可以直接读取和分析结果集,而不必持有任何锁。

对于那些对内联 SQL PL 的内部原理真正感兴趣的读者,可以在本文 参考资料 一节中给出的参考资料中发现更详细的信息。

NEW_ORDER 事务中的第三条语句,也即最后一条语句是:


清单 7. 第三条 SQL 语句
                
 1 
        SELECT W_TAX, C_DISCOUNT, C_LAST, C_CREDIT 
 2   
        INTO :ware_tax, :c_discount, :c_last, :c_credit
 3   
        FROM TABLE ( NEW_WH (   :next_o_id
 4                         , :w_id 
 5                         , :d_id 
 6                         , :c_id 
 7                         , :o_entry_d 
 8                         , :inputItemCount 
 9                         , :allLocal 
10                       )
11               ) 
        AS NEW_WH_TABLE 
      


清单 8. NEW_WH 函数
                
 1 
        CREATE FUNCTION NEW_WH (   O_ID        INTEGER
 2                          , W_ID        INTEGER
 3                          , D_ID        SMALLINT
 4                          , C_ID        INTEGER
 5                          , O_ENTRY_D   BIGINT
 6                          , O_OL_CNT    SMALLINT
 7                          , O_ALL_LOCAL SMALLINT
 8                        )
 9 
        RETURNS TABLE (   W_TAX       INTEGER
10                 , C_DISCOUNT  INTEGER
11                 , C_LAST      VARCHAR(16)
12                 , C_CREDIT    CHAR(2)
13               )
14 
        SPECIFIC NEW_WH
15 
        MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL
16 VAR: 
        BEGIN ATOMIC
17   
        DECLARE C_DISCOUNT  INTEGER ;
18   
        DECLARE C_LAST      VARCHAR(16) ;
19   
        DECLARE C_CREDIT    CHAR(2) ;
20   
        DECLARE W_TAX       INTEGER ;
21
22   
        INSERT 
23     
        INTO NEW_ORDER ( NO_O_ID, NO_D_ID, NO_W_ID )
24             
        VALUES (   O_ID
25                      , D_ID
26                      , W_ID
27                    ) 
28   ;
29   
        INSERT 
30     
        INTO ORDERS (   O_C_ID      , O_ENTRY_D , O_CARRIER_ID , O_OL_CNT
31                   , O_ALL_LOCAL , O_ID      , O_W_ID       , O_D_ID   )
32          
        VALUES (   C_ID        , O_ENTRY_D , 0    , O_OL_CNT
33                   , O_ALL_LOCAL , O_ID      , W_ID , D_ID             ) 
34   ;
35   
        SET ( C_DISCOUNT, C_LAST, C_CREDIT ) 
36    =  ( 
        SELECT  C_DISCOUNT, C_LAST, C_CREDIT 
37           
        FROM  CUSTOMER 
38          
        WHERE  C_ID = NEW_WH.C_ID 
39            
        AND  C_W_ID = W_ID
40            
        AND  C_D_ID = D_ID
41       )
42   ;
43   
        SET W_TAX 
44     =    ( 
        SELECT W_TAX
45              
        FROM WAREHOUSE
46             
        WHERE W_ID = NEW_WH.W_ID 
47          ) 
48   ;
49   
        RETURN VALUES ( W_TAX , C_DISCOUNT , C_LAST , C_CREDIT ) ;
50 
        END
            

您可以看到, NEW_WH 函数中的语句相互之间是独立的。那么,为什么还要这么麻烦地将这些语句封装成一个函数呢?将这四条语句合并在一起的原因是,减少与 SQL 语句的调用相关的代码路径(codepath)。在一个常规的客户环境中,能够进行封装而又于性能无损,这种能力是很好的。DB2 在这里证明,好的编码风格和基准程序不一定要不一致。

相应的计划如下:


清单 9. 第三条 SQL 语句的访问计划
第三条 SQL 语句的访问计划

在这里可以看到 UNION 如何派生出这四条语句。还应注意的是,在顶部没有 SORT,因为该语句是一种 SELECT INTO,而不是游标。

本文将不谈论处理远程仓库的 NEW_ORDER 事务。这种事务的结构与这里所述的事务很类似,因此没有什么新东西需要了解。







DELIVERY 事务

DELIVERY 是一个非常简单的事务:

  1. 送货者从 NEW_ORDER 队列中找到时间最久的订单 id。
  2. 将该订单从队列中删除。
  3. 从存储在 ORDER 中的订单检索客户 id。
  4. 从 ORDER_LINE 计算应付总额。注意,在正常的情况下,总数决不会存储在订单本身当中。
  5. 通过设置送货日期,将订购项标记为已发货。
  6. 通过设置送货日期,将订单标记为已发货。
  7. 更新客户的结余和发货计数。
  8. 返回订单 id。
如果队列为空,则什么也不做,只返回 NULL。


清单 10. Delivery 表函数
                
 1 
        CREATE FUNCTION DEL(   W_ID           INTEGER 
 2                      , D_ID           SMALLINT
 3                      , CARRIER_ID     SMALLINT
 4                      , DELIVERY_D     BIGINT
 5                    )
 6 
        RETURNS TABLE ( O_ID INTEGER )
 7 
        SPECIFIC DELIVERY
 8 
        MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL
 9 VAR: 
        BEGIN ATOMIC
10   
        DECLARE O_ID   INTEGER ;
11   
        DECLARE C_ID   INTEGER ;
12   
        DECLARE AMOUNT INTEGER ;
13
14   /* Delete the order from new order table */
15   
        SET VAR.O_ID = ( 
        SELECT NO_O_ID
16                      
        FROM OLD TABLE ( 
        DELETE
17                                         
        FROM ( 
        SELECT NO_O_ID
18                                                  
        FROM NEW_ORDER
19                                                 
        WHERE NO_W_ID = DEL.W_ID
20                                                   
        AND NO_D_ID = DEL.D_ID
21                                                 
        ORDER BY NO_O_ID 
        ASC
22                                                 
        FETCH FIRST 1 
        ROW ONLY
23                                              ) 
        AS NEW_ORDER
24                                     ) 
        AS D
25                  )
26   ;
27   /* Update the order as delivered and retrieve the customer id */
28   
        SET VAR.C_ID = ( 
        SELECT O_C_ID
29                      
        FROM OLD TABLE ( 
        UPDATE ORDERS
30                                          
        SET O_CARRIER_ID = DEL.CARRIER_ID
31                                        
        WHERE O_W_ID = DEL.W_ID
32                                          
        AND O_D_ID = DEL.D_ID
33                                          
        AND O_ID   = VAR.O_ID
34                                     ) 
        AS U
35                  ) 
36   ;
37   
        SET VAR.AMOUNT = ( 
        SELECT SUM( OL_AMOUNT )
38                        
        FROM OLD TABLE ( 
        UPDATE ORDER_LINE
39                                            
        SET OL_DELIVERY_D = DEL.DELIVERY_D
40                                          
        WHERE OL_W_ID = DEL.W_ID
41                                            
        AND OL_D_ID = DEL.D_ID
42                                            
        AND OL_O_ID = VAR.O_ID
43                                       ) 
        AS U
44                    ) 
45   ;
46   /* Charge the customer */
47   
        UPDATE CUSTOMER
48      
        SET   C_BALANCE      = C_BALANCE + VAR.AMOUNT
49          , C_DELIVERY_CNT = C_DELIVERY_CNT + 1
50    
        WHERE C_W_ID = DEL.W_ID
51      
        AND C_D_ID = DEL.D_ID
52      
        AND C_ID   = VAR.C_ID  
53   ;
54   /* Return the order id to the caller (or NULL) */
55   
        RETURN VALUES VAR.O_ID ;
56 
        END
            

就像在 NEW_ORDER 事务中一样,所有的步骤被收集到一个 SQL 表函数中,以节省代码路径。这里执行的没有 8 个步骤,而只有 5 条子语句。怎么回事?

  1. 在 SQL 中,处理队列上一个元素的传统方法是在第一步取这个元素。然后,在第二步中,从表中删除这个元素。这种方法的主要问题在于,除非打开 cursor-for-update,否则直接进行 select into 时,将不能充分地锁定这一行以便阻止另一个送货者尝试为相同的订单送货。而且,不管是否为游标,都需要执行两条语句。同样,将 数据更新操作(在这里是 DELETE)放入到 FROM 子句中被证明是有用的。然而,还需要更多的东西。为了发现“时间最久的”订单,必须执行一个 MIN() 函数。这个函数不能出现在 DELETE 语句的 WHERE 子句中,而只能出现在查询中。DB2 的 SQL 语言是高度正交的,这一事实有助于以一种优雅的方式解决问题。首先,DB2 允许对查询的 DELETE 具有等同于对视图(实际上也是查询)的 DELETE 的语义。其次,DB2 允许对一个已排序的查询进行 DELETE。在前面相当优雅的 DELETE 操作中,“ORDER BY O_ID FETCH FIRST ROW ONLY”将提供时间最久的订单 id 结果。
  2. 检索客户 id 和更新订单被合成一条语句。
  3. 通过更新 ORDER_LINES 中的 delivery 列,从各个订购项计算订单总值被合在一起。同样,由于 DB2 对 SQL 语言的选择的正交性,这样是可行的。因为 UPDATE 在 FROM 子句中,所以很容易使用标准 SUM() 来聚集最终结果。
而执行发货的真正语句却十分简单:


清单 11. 执行发货的 SQL 语句
                
1 
        SELECT O_ID 
2   
        INTO :no_o_id :no_o_id_indicator
3   
        FROM TABLE (  DEL( :w_id , :d_id , :o_carrier_id , :ol_delivery_d ) ) 
        AS T
      

同样,下面的计划展示了由于对 SQL 语句的有效使用,这个事务变得多么地紧凑:


清单 12. Delivery 访问计划
访问计划

同样,我们快速看一下这个计划的关键特性:

  • DELETE(6) 是来自队列的 POP。DB2 承认它只需一个 index fetch。 当然,如果有一个 SORT,这条语句也能工作,但那将是一个不好的计划。
  • GRPBY(11)UPDATE(12) 之上计算 SUM(OL_AMOUNT)。
到目前为止,我们一直假设,作为一名勤恳的读者,应该对所有其他操作都是熟悉的。







PAYMENT 事务

PAYMENT 事务有两种版本。对于那些提供了客户 id 的客户,使用第一种版本。对于不记得客户 ID,而只提供了姓氏的客户,使用第二种版本。这里只讨论第二种版本,因为其中提出了第一种版本中所没有的挑战。

在支付事务(按姓氏)中,必须发生以下步骤:

  1. 检索地区的名称和地址。
  2. 根据姓氏发现客户的客户 id。如果在该地区有多个同姓的客户,则正确的客户应该是根据客户的名字得来的“中间”客户。
  3. 检索客户的个人信息。
  4. 增加该地区至今为止的收入。
  5. 增加仓库至今为止的收入。
  6. 增加客户的支付额,如果客户信用不佳,则还应包括额外的数据。
  7. 将这次的支付记录到历史中。

与前面的事务一样,这里的大部分逻辑被封装到一个名为 PAY_C_LAST() 的表函数中。


清单 13. 表函数 PAY_C_LAST
                
  1 
        CREATE FUNCTION PAY_C_LAST(  W_ID INTEGER
  2                            , D_ID SMALLINT
  3                            , C_W_ID INTEGER
  4                            , C_D_ID SMALLINT
  5                            , C_LAST VARCHAR(16)
  6                            , H_DATE BIGINT
  7                            , H_AMOUNT BIGINT  
  8                            , BAD_CREDIT_PREFIX VARCHAR(34) 
  9                           )
 10 
        RETURNS TABLE(   W_STREET_1 CHAR(20)
 11                , W_STREET_2 CHAR(20)
 12                , W_CITY CHAR(20)
 13                , W_STATE CHAR(2)
 14                , W_ZIP CHAR(9)
 15                , D_STREET_1 CHAR(20)
 16                , D_STREET_2 CHAR(20)
 17                , D_CITY CHAR(20)
 11                , D_STATE CHAR(2)
 19                , D_ZIP CHAR(9)
 20                , C_ID INTEGER
 21                , C_FIRST VARCHAR(16)
 22                , C_MIDDLE CHAR(2)
 23                , C_STREET_1 VARCHAR(20)
 24                , C_STREET_2 VARCHAR(20)
 25                , C_CITY VARCHAR(20)
 26                , C_STATE CHAR(2)
 27                , C_ZIP CHAR(9)
 28                , C_PHONE CHAR(16)
 29                , C_SINCE BIGINT
 30                , C_CREDIT CHAR(2)
 31                , C_CREDIT_LIM BIGINT
 32                , C_DISCOUNT INTEGER
 33                , C_BALANCE BIGINT
 34                , C_DATA CHAR(200)   
 35              )
 36 
        SPECIFIC PAY_C_ID 
        INHERIT ISOLATION LEVEL WITH LOCK REQUEST
 37 
        MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL
 38 VAR: 
        BEGIN ATOMIC
 39   
        DECLARE  W_NAME CHAR(10) ;
 40   
        DECLARE  D_NAME CHAR(10) ;
 41   
        DECLARE  W_STREET_1 CHAR(20) ;
 42   
        DECLARE  W_STREET_2 CHAR(20) ;
 43   
        DECLARE  W_CITY CHAR(20) ;
 44   
        DECLARE  W_STATE CHAR(2) ;
 45   
        DECLARE  W_ZIP CHAR(9) ;
 46   
        DECLARE  D_STREET_1 CHAR(20) ;
 47   
        DECLARE  D_STREET_2 CHAR(20) ;
 48   
        DECLARE  D_CITY CHAR(20) ;
 49   
        DECLARE  D_STATE CHAR(2) ;
 50   
        DECLARE  D_ZIP CHAR(9) ;
 51   
        DECLARE  C_ID INTEGER ;
 52   
        DECLARE  C_FIRST VARCHAR(16) ;
 53   
        DECLARE  C_MIDDLE CHAR(2) ;
 54   
        DECLARE  C_STREET_1 VARCHAR(20) ;
 55   
        DECLARE  C_STREET_2 VARCHAR(20) ;
 56   
        DECLARE  C_CITY VARCHAR(20) ;
 57   
        DECLARE  C_STATE CHAR(2) ;
 58   
        DECLARE  C_ZIP CHAR(9) ;
 59   
        DECLARE  C_PHONE CHAR(16) ;
 60   
        DECLARE  C_SINCE BIGINT ;
 61   
        DECLARE  C_CREDIT CHAR(2) ;
 62   
        DECLARE  C_CREDIT_LIM BIGINT ;
 63   
        DECLARE  C_DISCOUNT INTEGER ;
 64   
        DECLARE  C_BALANCE BIGINT ;
 65   
        DECLARE  C_DATA CHAR(200) ;
 66
 67   /* Update District and retrieve its data */
 68   
        SET ( D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP )
 69     =  ( 
        SELECT   D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
 70            
        FROM OLD TABLE ( 
        UPDATE DISTRICT
 71                                
        SET D_YTD = D_YTD + PAY_C_ID.H_AMOUNT
 72                              
        WHERE D_W_ID = PAY_C_ID.W_ID
 73                                
        AND D_ID   = PAY_C_ID.D_ID
 74                           ) 
        AS U
 75        )
 76   ;
 77   /* Determine the C_ID */
 78   
        SET ( C_ID )
 79     = ( 
        SELECT C_ID
 80           
        FROM ( 
        SELECT   C_ID 
 81                         , COUNT(*) OVER() 
        AS COUNT
 82                         , ROWNUMBER() OVER (
        ORDER BY C_FIRST) 
        AS NUM
 83                    
        FROM CUSTOMER
 84                    
        WHERE C_LAST = PAY_C_LAST.C_LAST
 85                      
        AND C_W_ID = PAY_C_LAST.C_W_ID
 86                      
        AND C_D_ID = PAY_C_LAST.C_D_ID 
 87                ) 
        AS T
 88          
        WHERE NUM = (COUNT + 1) / 2
 89       )    
 90   ;
 91   /* Update the customer */
 92   
        SET (   C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
 93         , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM
 94         , C_DISCOUNT, C_BALANCE, C_DATA )
 95     = ( 
        SELECT   C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
 96                , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT
 97                , C_CREDIT_LIM , C_DISCOUNT, C_BALANCE 
 98                , 
        CASE WHEN C_CREDIT = 'BC' 
 99                       
        THEN SUBSTR(C_DATA, 1, 200) 
        END AS C_DATA
100           
        FROM NEW TABLE ( 
        UPDATE CUSTOMER
101                               
        SET   C_BALANCE     = C_BALANCE - PAY_C_ID.H_AMOUNT
102                                   , C_YTD_PAYMENT = C_YTD_PAYMENT + PAY_C_ID.H_AMOUNT
103                                   , C_PAYMENT_CNT = C_PAYMENT_CNT + 1
104                                   , C_DATA = 
        CASE WHEN C_CREDIT = 'BC'
105                                                   
        THEN    BAD_CREDIT_PREFIX
106                                                        || SUBSTR( C_DATA, 1, 466 )
107                                                   
        ELSE C_DATA
108                                              
        END
109                             
        WHERE C_W_ID = PAY_C_ID.C_W_ID
110                               
        AND C_D_ID = PAY_C_ID.C_D_ID
111                               
        AND C_ID   = PAY_C_ID.C_ID
112                          ) 
        AS U
113       )
114   ;
115   /* Update the warehouse */
116   
        SET ( W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE,  W_ZIP )
117     = ( 
        SELECT W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE,  W_ZIP
118           
                                                            

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15082138/viewspace-534786/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论