DB2索引优化
原创
©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任
问题描述
- 影响性能的 SQL 语句
问题分析与解决
步骤一：分析该 SQL 语句的执行计划DB2 提供了能分析 SQL 执行计划的工具:db2expln，通过分析 SQL 执行计划我们将了解 DB2 优化器选择了什么样的“途径”来访问数据，执行计划的优劣将直接影响 SQL 的性能。
[db2inst1@node01 tmp]$ db2expln -database testdb -i -g -stmtfile 2.ddl -terminator ';' -output 2.exp
DB2 Universal Database Version 11.1, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
Output is available in "2.exp".
[db2inst1@node01 tmp]$ cat 2.exp
DB2 Universal Database Version 11.1, 5622-044 (c) Copyright IBM Corp. 1991, 2017
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",
"DB2INST1"
Statement:
select name, id
from t2
where id=222
Section Code Page = 1208
Estimated Cost = 31314.082031
Estimated Cardinality = 3.292483
( 2) Access Table Name = DB2INST1.T2 ID = 5,4
| #Columns = 1
| 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 = 2
( 1) Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
3.29248
RETURN
( 1)
31314.1
|
3.29248
TBSCAN #####备注，这是全表扫描
( 2)
31314.1
|
6.36857e+06
Table:
DB2INST1
T2
使用db2advis
[db2inst1@node01 tmp]$ cat 2.ddl
select name,id from t2 where id=222;
[db2inst1@node01 tmp]$ db2advis -d testdb -i 2.ddl -t 5
Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2020-01-06-22.14.03.037809
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 93.247] MB
total disk space constrained to [ 125.535] MB
Trying variations of the solution set.
1 indexes in current solution
[31314.0000] timerons (without recommendations)
[ 14.0000] timerons (with current solution)
[99.96%] improvement
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 93.247MB
CREATE INDEX "DB2INST1"."IDX2001061414090" ON "DB2INST1"."T2"
("ID" ASC, "NAME" DESC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
COMMIT WORK ;
--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- ===========================
--
-- ====ADVISOR DETAILED XML OUTPUT=============
-- ==(Benefits do not include clustering recommendations)==
--
--<?xml version="1.0"?>
--<design-advisor>
--<index>
--<identifier>
--<name>IDX2001061414090</name>
--<schema>DB2INST1</schema>
--</identifier>
--<table><identifier>
--<name>T2</name>
--<schema>DB2INST1</schema>
--</identifier></table>
--<statementlist>0</statementlist>
--<benefit>31300.000000</benefit>
--<overhead>0.000000</overhead>
--<diskspace>93.247094</diskspace>
--</index>
--<statement>
--<statementnum>0</statementnum>
--<statementtext>
-- select name,id from t2 where id=222
--</statementtext>
--<objects>
--<identifier>
--<name>T2</name>
--<schema>DB2INST1</schema>
--</identifier>
--<identifier>
--<name>IDX2001061414090</name>
--<schema>DB2INST1</schema>
--</identifier>
--</objects>
--<benefit>31300.000000</benefit>
--<frequency>1</frequency>
--</statement>
--</design-advisor>
-- ====ADVISOR DETAILED XML OUTPUT=============
--
14 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.
显示需要创建索引
db2 "CREATE INDEX "DB2INST1"."IDX2001061414090" ON "DB2INST1"."T2"("ID" ASC, "NAME" DESC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS"
db2 "COMMIT WORK"走索引过后的执行计划
[db2inst1@node01 tmp]$ db2expln -database testdb -i -g -stmtfile 2.ddl -terminator ';' -output 2.exp
DB2 Universal Database Version 11.1, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
Output is available in "2.exp".
[db2inst1@node01 tmp]$ cat 2.exp
DB2 Universal Database Version 11.1, 5622-044 (c) Copyright IBM Corp. 1991, 2017
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",
"DB2INST1"
Statement:
select name, id
from t2
where id=222
Section Code Page = 1208
Estimated Cost = 13.542398
Estimated Cardinality = 3.292483
( 2) Access Table Name = DB2INST1.T2 ID = 5,4
| Index Scan: Name = DB2INST1.IDX2001061414090 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| | | 2: NAME (Descending)
| #Columns = 1
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | 1: 222
| | Stop Key: Inclusive Value
| | | 1: 222
| Index-Only Access
| Index Prefetch: Sequential(1), Readahead
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
( 1) | | Return Data to Application
| | | #Columns = 2
( 1) Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
3.29248
RETURN
( 1)
13.5424
|
3.29248
IXSCAN
( 2)
13.5424
|
1.93424e+06
Index:
DB2INST1
IDX2001061414090
从以上的执行计划中可以看到 COST 值从最初的31314.082031最终降低到13.542398，该 SQL 语句的性能提升非常明显。
索引设计原则
索引通常用于加速对表的访问。但是，逻辑数据设计也可以使用索引。例如，唯一索引不允许列中存在重复值的条目，从而保证了一个表中不会有两行相同的记录。还可以创建索引，以将一列中的值按升序或降序进行排序。
要点： 在创建索引时要记住，虽然它们可以提高查询性能，但会对写性能产生负面影响。出现此负面影响是因为对于数据库管理器写入表中的每行，它还必须更新任何受影响的索引。因此，只有在能够明显提高整体性能时，才应创建索引。
在创建索引时，还应考虑表结构和最常对这些表执行查询的类型。例如，频繁发出的查询的 WHERE 子句中出现的列很适合作为索引。但是，在较少运行的查询中，索引对 INSERT 和 UPDATE 语句的性能产生的负面影响可能超过所带来的好处。
同样，在经常运行的查询的 GROUP BY 子句中出现的列可能会从创建索引中获益，尤其在用于分组行的值的数目小于要分组的行数时。
在创建索引时, 也可以进行压缩。之后，您可以使用 ALTER INDEX 语句来修改索引，从而启用或禁用压缩功能。
要删除索引，可以使用 DROP INDEX 命令。
设计索引时的准则和注意事项
虽然构成一个索引键的列的顺序不会影响索引键的创建，但是当它决定是否使用索引时就可能影响优化器。例如，如果查询包含 ORDER BY col1,col2 子句，那么可以使用对 (col1,col2) 创建的索引，但对 (col2,col1) 创建的索引没什么帮助。同样，如果查询指定了条件，例如 where col1 >= 50 and col1 <= 100 或 where col1=74，那么对 (col1) 或 (col1,col2) 创建的索引将起作用，但基于 (col2,col1) 的索引的作用不大。
可以对特定的表定义任意数目的索引(最大数目为 32767)，这些索引能提高查询性能。
索引管理器必须在更新、删除和插入操作期间维护索引。为有很多更新内容的表创建大量索引可能减慢请求的处理速度。同样，大型索引键也会减慢处理请求的速度。因此，仅当频繁访问明显有利之时，才使用索引。
不是唯一索引键的一部分但要在该索引中存储或维护的列数据称为包含列。只能为唯一索引指定包含列。当用包含列创建索引时，仅对唯一键列进行排序并考虑其唯一性。使用包含列可以启用仅访问索引来进行数据检索，从而提高性能。
如果要建立索引的表是空的，那么仍会创建索引，但是在装入该表或插入行之前，不会建立任何索引条目。如果该表不为空，那么数据库管理器将在处理 CREATE INDEX 语句时创建索引条目。
索引会消耗磁盘空间。该磁盘空间大小取决于键列的长度和要建立索引的行数。随着插入到表中的数据增多，索引大小也会增加。因此，在规划数据库大小时，应考虑正在建立索引的数据量。
版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle
