db2索引优化
在数据库应用程序开发期间，开发人员倾向于在表上定义大量索引，以保证每个查询能够良好地执行。当应用程序开发完成并且数据库投入到生产环境中之后，存在过多索引将导致数据库性能下降。大量的索引意味着数据库系统在执行 update、insert 和 delete (uid) 操作时要做更多的工作。另外，存在大量索引时，runstats 和 reorg 等常规维护活动的运行也显著变慢。因此， 要实现最佳的数据库性能，最关键的是确定哪些索引对查询执行是必要的，哪些是可以安全删除而且不影响查询运行时的。本文解释了几种方法，db2® for linux®, unix®, and windows® (db2 luw) 数据库管理员（dba）可以通过它们识别未使用和不常使用的索引。
db2 design advisor (db2advis)
db2 design advisor 是识别未使用索引的简单方法。您可以使用 design advisor 分析一组 sql 语句及其执行频率。分析完成之后，design advisor 返回以下信息：
额外的索引定义，它们通过减少查询的运行时间改进查询性能
db2 用于查询执行的现有索引的名称
db2 未用于查询执行的现有索引的名称
让我们看看基于 db2 测试数据库 sample 的 design advisor 使用场景。
首先，通过在命令行上执行 db2sampl 命令创建 sample 数据库，如清单 1 所示。
清单 1. 创建 sample 数据库
db2sampl
在调用 design advisor 之前，您必须运行 explain.ddl 脚本来创建它需要的 explain 表。您可以在 db2 安装的 sqllib/misc 子目录中找到该脚本。您仅需运行该脚本一次。清单 2 显示了运行该脚本所需的命令。
清单 2. 创建 explain 表
db2 "connect to sample"
db2 -tf "c:\program files\ibm\sqllib\misc\explain.ddl"
当调用 design advisor 时，您必须定义需要对其进行分析的 sql 语句组。您可以通过几种方法定义 sql 语句组。其中一种方法是提供一个包含需要分析的 sql 语句的文本文件。例如，清单 3 显示了一个名为 queries.sql 样例文件，它包含一组 sql 语句。
清单 3. db2 design advisor 输入文件 queries.sql
-- employee queries
--#set frequency 123
select lastname from employee where empno = '000010';
--#set frequency 456
select lastname from employee where workdept = 'a00';
-- department queries
--#set frequency 245
select deptname from department where deptno = 'a00';
--#set frequency 678
select deptname from department where mgrno = '000010';
-- project queries
--#set frequency 345
select projname from project where projno = 'op1000';
在创建 design advisor 需要分析的 sql 语句文本文件时，您需要考虑以下事项：
注释行以 -- 开始，design advisor 将忽略它们。
如果知道的话，您可以选择指定每个查询的大致执行频率。这能够帮助 design advisor 更好地估计创建新的索引能够对数据库性能有多大的改进。为此，在查询定义之前添加另外一个以 --#set frequency 开始的行（尽管这些行以 -- 开始，但不被看作是注释行）。
文本文件中的每个 sql 语句都必须以分号结束（;）。
sql 语句可能包含参数标记。可以存在参数标记，因为 design advisor 并没有实际执行 sql 语句，它仅为它们计算其他可行的访问计划。
对于所有没有完全限定的表和视图（不带有显式模式），调用方的授权 id 被用作默认的模式。在调用 design advisor 时，您也可以选择使用 -q 选项覆盖默认模式。
清单 4 显示了一个样例命令，您可以使用它调用 design advisor 和将 queries.sql 指定为包含需要分析的 sql 语句的输入文件。
清单 4. 使用输入文件 queries.sql 调用 db2 data studio
db2advis -d sample -i queries.sql -m i -l -1 -t 0 -o db2advis_file.txt
以上命令的选项包含一些含义：
-d，数据库名。
-i，包含需要分析的 sql 语句的输入文件。
-m，除了相关的索引之外，design advisor 还可以推荐 materialized query tables (mqts)、multidimensional clustering (mdc) 表和对分区表进行重新分区。在这个示例场景中，-m 的值为 i，这表示仅建议使用相关的索引（同时也是默认值）。
-l，新的索引或 mqt 的定义的大小限制。值 -1 表示没有大小限制。
-t，执行 design advisor 的运行时限制。值 0 表示没有时间限制。
-o，输出文件，用于写 design advisor 的建议的位置。
design advisor 创建的输出文件包含 3 个部分：建议的额外索引、建议的现有索引和未使用索引。对于这个场景，您主要对列出未使用索引的部分感兴趣。清单 5 显示了未使用索引部分的示例。
清单 5. db2 design advisor 输出中的未使用索引部分
-- unused existing indexes
-- ============================
-- drop index "fechner "."xdept3";
-- drop index "fechner "."xproj2";
-- ===========================
对于这个场景，design advisor 表明索引 xdept3 和 xproj2 未被使用。不过要记住，该建议仅基于 design advisor 分析的一组 sql 语句（包含在输入文件中的 sql 语句）。除了输入文件中的 sql 语句之外，还可能存在其他依赖于识别到的索引改善执行时间的语句。因此，在删除识别到的未使用索引之前，一定要小心检查和认真考虑依赖它们的其他 sql 语句。
包含 sql 语句的输入文件有一个代替办法，即让 design advisor 计算数据库的 sql 语句缓存的内容。为此，您需要在 db2advis 上使用 -g 选项，如清单 6 所示。
清单 6. 调用 db2 design advisor 计算 sql 语句缓存内容
db2advis -d sample -g -m i -l -1 -t 0 -o db2advis_sql_cache.txt
以这种方式调用 design advisor 比手动地创建包含 sql 语句的输入文件要省事得多。不过，您仍然要记住，分析得到的结果仅取决于 sql 语句缓存的当前内容。调用 data studio 时未驻留在缓存中的内容将未被分析。因此，当使用 sql 语句缓存作为输入时，应该在当天的不同时间点运行 design advisor，以计算更大范围的 sql 语句。
回页首
db2pd 实用程序
db2pd 实用程序通常用于问题诊断和监控，但也可用于查询关于数据库活动的信息。使用 db2pd 能够获得的信息之一是：自从数据库活动之后索引被访问的次数。清单 7 显示了如何使用 db2pd 实用程序为数据库中的所有表上的索引获取该信息。
清单 7. 使用 db2pd 实用程序查询表和索引的度量指标
db2pd -db sample -tcbstats all -file db2pd_tab_all.txt
以上调用 db2pd 的选项包含以下含义：
-db 数据库名
-tcbstats all 显示所有表和索引度量指标
-file 输出文件
如果您想要限制 db2pd 实用程序的输出，让它仅显示某些表及其索引，那么使用 -tcbstats 选项指定表空间 id 和表 id。为此，您首先需要在 syscat.tables 目录视图上执行 select 语句来确定表空间 id 和表 id，如清单 8 所示。
清单 8. 查询数据库目录以确定表的表空间 id 和表 id
db2 "select tbspaceid, tableid
from syscat.tables
where tabschema = 'fechner' and tabname = 'department'"
清单 9 显示了来自类似于以上的表空间 id 和表 id 查询的样例结果。
清单 9. 表空间 id 和表 id 查询的结果集
tbspaceid tableid
--------- -------
2 5
