DB2 SQL 性能优化案例——索引对 SQL 性能的影响

男娘i 2022-04-08 14:38 671阅读 0赞

## DB2 SQL 性能优化案例一则 ##

SQL 语句优化贯穿于数据库类应用程序的整个生命周期，包括前期程序开发，产品测试以及后期生产维护。针对于不同类型的 SQL 性能问题有不同的优化方法。索引对于改善数据库 SQL 查询操作性能至关重要，如何选择合适的列以及正确的组合所选择的列创建索引对查询语句的性能有着极大的影响，本文将结合具体案例进行解释。

## 问题描述 ##

客户 A 业务核心数据库采用 DB2 UDB，业务部门报告其中一个模块响应缓慢，通过分析该业务模块代码可以定位为一条性能较差的 SQL 语句。

清单 1. 影响性能的 SQL 语句

db2fox@bivm:~/test> cat t1.sql
    select name,location,address from t1 where name=16123
    db2fox@bivm:~/test>

## 问题分析与解决 ##

**步骤一：分析该 SQL 语句的执行计划**

DB2 提供了能分析 SQL 执行计划的工具:db2expln，通过分析 SQL 执行计划我们将了解 DB2 优化器选择了什么样的“途径”来访问数据，执行计划的优劣将直接影响 SQL 的性能。

清单 2. 执行计划输出结果

db2fox@bivm:~/test> db2expln -database fox -i -g -stmtfile t1.sql -terminator ';' -output t1.exp
    db2fox@bivm:~/test> cat t1.exp
    DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool
    ******************** DYNAMIC ***************************************
    ==================== STATEMENT ==========================================
        Isolation Level = Cursor Stability
        Blocking = Block Unambiguous Cursors
        Query Optimization Class = 5
        Partition Parallel = No
        Intra-Partition Parallel = No
        SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
     "DB2FOX"
    Statement:
     select name, location, address 
     from t1 
     where name=16123
    Section Code Page = 1208
    Estimated Cost = 3517.214111 --此处我们可以看到行计划的 COST 值
    Estimated Cardinality = 3600.000000
    ( 2) Access Table Name = DB2FOX.T1 ID = 4,513
     | #Columns = 3
     | Skip Inserted Rows
     | Avoid Locking Committed Data
     | Currently Committed for Cursor Stability
     | May participate in Scan Sharing structures
     | Scan may start anywhere and wrap, for completion
     | Fast scan, for purposes of scan sharing management
     | Scan can be throttled in scan sharing management
     | Relation Scan
     | | Prefetch: Eligible
     | Lock Intents
     | | Table: Intent Share
     | | Row : Next Key Share
     | Sargable Predicate(s)
     | | #Predicates = 1
    ( 1) | | Return Data to Application
     | | | #Columns = 3
    ( 1) Return Data Completion
    End of section
    Optimizer Plan:
     Rows 
     Operator 
     (ID) 
     Cost 
     3600 
     RETURN 
     ( 1) 
     3517.21 
     | 
     3600 
     TBSCAN --> 该执行计划选择了全表扫描
     ( 2) 
     3517.21 
     | 
     90000 
     Table: 
     DB2FOX 
     T1

这是一条非常简单的 SQL 语句，其执行计划选择了“全表扫描”，一般情况下全表扫描的“代价”较高而执行效率较差，相对而言，使用索引的效率要高的多，但在一些特殊情况下“全表扫描”的效率要优于“使用索引”，影响优化器选择的因素有很多，包括：表的大小，查询结果集的大小，有无索引，I/O 预读等。

清单 3. 表的统计信息

db2fox@bivm:~/test> db2 "select count(*) from t1"
    1 
    -----------
     90000
     1 record(s) selected.
    fox@bivm:~/test> db2 "select substr(indname,1,10),substr(tabname,1,20),
    substr(colnames,1,20) from syscat.indexes where tabname='T1'"
    1 2 3 
    ---------- -------------------- --------------------
    I_T1 T1 +LOCATION+NAME 
    db2fox@bivm:~/test> db2 "select firstkeycard, first2keycard from syscat.indexes
    > where indname='I_T1'"
    FIRSTKEYCARD FIRST2KEYCARD 
    -------------------- ---------------------------------------------------
     3 -->重复值非常的多59093 -->重复值非常的少
     1 record(s) selected.
    db2fox@bivm:~/test> 
    db2fox@bivm:~/test> db2 "describe table t1"
     Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    NAME SYSIBM CHARACTER 40 0 Yes 
    LOCATION SYSIBM CHARACTER 50 0 Yes 
    ADDRESS SYSIBM VARCHAR 130 0 Yes 
     3 record(s) selected.
    db2fox@bivm:~/test>

T1 表上有一个名为“I\_T1”的索引，该表有大概 9 万条记录，而且 NAME 列的重复值非常的少，这种情况下影响业务性能的 SQL 语句非常适合使用索引，但当前的执行计划却选择了“全表扫描”！我们再仔细观察一下该 SQL 语句的原文：select name,location,address from t1 where name=16123 请注意 where 条件 name=16123 这是一个“数值”类型，而 t1 表中 NAME 列定义的是“字符”类型的，这可能是影响执行化选择的原因！

**步骤二：修改 SQL 原文**

将 SQL 原文中 where 条件部分加“引号”以使得“优化器”可以选择索引。

清单 4. 重新生成执行计划，验证优化效果

db2fox@bivm:~/test> cat t1.sql
    select name,location,address from t1 where name='16123' 
    db2fox@bivm:~/test> 
    db2fox@bivm:~/test> db2expln -database fox -i -g -stmtfile t1.sql -terminator ';' -output t1.exp
    DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool
    Output is available in "t1.exp".
    db2fox@bivm:~/test> cat t1.exp
    DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool
    ******************** DYNAMIC ***************************************
    ==================== STATEMENT ==========================================
        Isolation Level = Cursor Stability
        Blocking = Block Unambiguous Cursors
        Query Optimization Class = 5
        Partition Parallel = No
        Intra-Partition Parallel = No
        SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
     "DB2FOX"
    Statement:
     select name, location, address 
     from t1 
     where name='16123' 
    Section Code Page = 1208
    Estimated Cost = 132.665771 -->COST 值比优化前改善非常明显
    Estimated Cardinality = 2.596810
    ( 2) Access Table Name = DB2FOX.T1 ID = 4,513
     | Index Scan: Name = DB2FOX.I_T1 ID = 1
     | | Regular Index (Not Clustered)
     | | Index Columns:
     | | | 1: LOCATION (Ascending)
     | | | 2: NAME (Ascending)
     | #Columns = 2
     | Skip Inserted Rows
     | Avoid Locking Committed Data
     | Currently Committed for Cursor Stability
     | Evaluate Predicates Before Locking for Key
     | #Key Columns = 2
     | | Start Key: Inclusive Value
     | | | 1: [GAP Unconstrained]
     | | | 2: '16123 ...' 
     | | Stop Key: Inclusive Value
     | | | 1: [GAP Unconstrained]
     | | | 2: '16123 ...' 
     | Data Prefetch: Sequential(2), Readahead
     | Index Prefetch: Sequential(4), Readahead
     | Lock Intents
     | | Table: Intent Share
     | | Row : Next Key Share
     | Sargable Predicate(s)
    ( 1) | | Return Data to Application
     | | | #Columns = 3
    ( 1) Return Data Completion
    End of section
    Optimizer Plan:
     Rows 
     Operator 
     (ID) 
     Cost 
     2.59681 
     RETURN 
     ( 1) 
     132.666 
     | 
     2.59681 
     FETCH 
     ( 2) 
     132.666 
     / \
     2.59681 90000 
     IXSCAN Table: 
     ( 3) DB2FOX 
     115.093 T1 
     | 
     59093 
     Index: 
     DB2FOX -->已经使用了索引
     I_T1 
    db2fox@bivm:~/test>

重新执行该 SQL 语句验证其优化效果，可以看出该 SQL 已经有明显的改善，但依然没有满足业务期望。SQL 的性能很大程度上是与“索引”相关的, 正确的使用索引以及合理的设计“索引”是改善 SQL 性能的最主要手段，“索引”质量的高低也将直接影响 SQL 的性能好坏。

**步骤三：分析相关索引**

索引 I\_T1 是由 LOCATION 列和 NAME 列联合构成的“组合索引”，通常情况下“组合索引”的“引导列”（排在最左边的列）对查询语句中的 where 条件影响最大，而索引 I\_T1 的引导列为 LOCATION, 因此可以考虑新创建一个索引只有 NAME 列或者创建一个新的由 NAME 列为引导列的组合索引。

清单 5. 创建以 NAME 列为引导列的索引

db2fox@bivm:~> db2 "create index i_t1_name on t1(name)"
    DB20000I The SQL command completed successfully.
    db2fox@bivm:~> db2 "describe indexes for table t1"
    Index Index Unique Number of Index Index Null
    schema name rule columns type partitioning keys
    ------------------------------- ------------------- --------------
    DB2FOX I_T1 D 2 RELATIONAL DATA - Y 
    DB2FOX I_T1_NAME  D 1 RELATIONAL DATA - Y 
     2 record(s) selected.

清单 6. 重新生成执行计划，验证优化效果

db2fox@bivm:~/test> db2expln -database fox -i -g -stmtfile t1.sql -terminator ';' -output t1.exp
    DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool
    Output is available in "t1.exp".
    db2fox@bivm:~/test> cat t1.exp
    DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool
    ******************** DYNAMIC ***************************************
    ==================== STATEMENT ==========================================
        Isolation Level = Cursor Stability
        Blocking = Block Unambiguous Cursors
        Query Optimization Class = 5
        Partition Parallel = No
        Intra-Partition Parallel = No
        SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
     "DB2FOX"
    Statement:
     select name, location, address 
     from t1 
     where name='16123' 
    Section Code Page = 1208
    Estimated Cost = 27.005688 -->COST 值比优化前改善非常明显
    Estimated Cardinality = 2.898831
    ( 2) Access Table Name = DB2FOX.T1 ID = 4,513
     | Index Scan: Name = DB2FOX.I_T1_NAME ID = 2
     | | Regular Index (Not Clustered)
     | | Index Columns:
     | | | 1: NAME (Ascending)
     | #Columns = 2
     | Skip Inserted Rows
     | Avoid Locking Committed Data
     | Currently Committed for Cursor Stability
     | Evaluate Predicates Before Locking for Key
     | #Key Columns = 1
     | | Start Key: Inclusive Value
     | | | 1: '16123 ...' 
     | | Stop Key: Inclusive Value
     | | | 1: '16123 ...' 
     | Data Prefetch: Sequential(1), Readahead
     | Index Prefetch: Sequential(1), Readahead
     | Lock Intents
     | | Table: Intent Share
     | | Row : Next Key Share
     | Sargable Predicate(s)
    ( 1) | | Return Data to Application
     | | | #Columns = 3
    ( 1) Return Data Completion
    End of section
    Optimizer Plan:
     Rows 
     Operator 
     (ID) 
     Cost 
     2.89883 
     RETURN 
     ( 1) 
     27.0057 
     | 
     2.89883 
     FETCH 
     ( 2) 
     27.0057 
     / \
     2.89883 90000 
     IXSCAN Table: 
     ( 3) DB2FOX 
     13.5494 T1 
     | 
     30731 
     Index: 
     DB2FOX 
     I_T1_NAME -->优化器选择新创建的索引
    db2fox@bivm:~/test>

从以上的执行计划中可以看到 COST 值从最初的**3517.214111**最终降低到**27.005688**，该 SQL 语句的性能提升非常明显。

## 索引设计原则 ##

索引通常用于加速对表的访问。但是，逻辑数据设计也可以使用索引。例如，唯一索引不允许列中存在重复值的条目，从而保证了一个表中不会有两行相同的记录。还可以创建索引，以将一列中的值按升序或降序进行排序。

**要点：** 在创建索引时要记住，虽然它们可以提高查询性能，但会对写性能产生负面影响。出现此负面影响是因为对于数据库管理器写入表中的每行，它还必须更新任何受影响的索引。因此，只有在能够明显提高整体性能时，才应创建索引。

在创建索引时，还应考虑表结构和最常对这些表执行查询的类型。例如，频繁发出的查询的 WHERE 子句中出现的列很适合作为索引。但是，在较少运行的查询中，索引对 INSERT 和 UPDATE 语句的性能产生的负面影响可能超过所带来的好处。

同样，在经常运行的查询的 GROUP BY 子句中出现的列可能会从创建索引中获益，尤其在用于分组行的值的数目小于要分组的行数时。

在创建索引时, 也可以进行压缩。之后，您可以使用 ALTER INDEX 语句来修改索引，从而启用或禁用压缩功能。

要删除索引，可以使用 DROP INDEX 命令。

**设计索引时的准则和注意事项**

1.  虽然构成一个索引键的列的顺序不会影响索引键的创建，但是当它决定是否使用索引时就可能影响优化器。例如，如果查询包含 ORDER BY col1,col2 子句，那么可以使用对 (col1,col2) 创建的索引，但对 (col2,col1) 创建的索引没什么帮助。同样，如果查询指定了条件，例如 where col1 >= 50 and col1 <= 100 或 where col1=74，那么对 (col1) 或 (col1,col2) 创建的索引将起作用，但基于 (col2,col1) 的索引的作用不大。
2.  可以对特定的表定义任意数目的索引（最大数目为 32767），这些索引能提高查询性能。
3.  索引管理器必须在更新、删除和插入操作期间维护索引。为有很多更新内容的表创建大量索引可能减慢请求的处理速度。同样，大型索引键也会减慢处理请求的速度。因此，仅当频繁访问明显有利之时，才使用索引。
4.  不是唯一索引键的一部分但要在该索引中存储或维护的列数据称为包含列。只能为唯一索引指定包含列。当用包含列创建索引时，仅对唯一键列进行排序并考虑其唯一性。使用包含列可以启用仅访问索引来进行数据检索，从而提高性能。
5.  如果要建立索引的表是空的，那么仍会创建索引，但是在装入该表或插入行之前，不会建立任何索引条目。如果该表不为空，那么数据库管理器将在处理 CREATE INDEX 语句时创建索引条目。
6.  索引会消耗磁盘空间。该磁盘空间大小取决于键列的长度和要建立索引的行数。随着插入到表中的数据增多，索引大小也会增加。因此，在规划数据库大小时，应考虑正在建立索引的数据量。

**注：** 都应该按重复值最少到重复值最多的顺序对索引键中的列进行排序。此排序提供最佳性能。

## 结束语 ##

本案例中通过修改了两 SQL 原文并重新设计了一个索引达到了优化目的，满足了业务要求，当数据库出现性能问题时，通过现象分析其本质，最终找到优化的具体方法。数据库优化是一个系统化的过程，有时无法一蹴而就，需要循序渐进。深刻的理解数据库的运行机制和原理是迅速判断性能问题的基础。