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>
回页首
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 原文中 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 命令。
注:都应该按重复值最少到重复值最多的顺序对索引键中的列进行排序。此排序提供最佳性能。
回页首
本案例中通过修改了两 SQL 原文并重新设计了一个索引达到了优化目的,满足了业务要求,当数据库出现性能问题时,通过现象分析其本质,最终找到优化的具体方法。数据库优化是一个系统化的过程,有时无法一蹴而就,需要循序渐进。深刻的理解数据库的运行机制和原理是迅速判断性能问题的基础。