DB2之SQL优化浅析

SQL优化方法
• 有效利用执行计划 3.在执行计划的输出结果中也可以看到执行计划树：
一般来说计划树 层次越少，效率 越高；原始表尽 量位于底层
SQL优化方法
• 有效利用执行计划 4.在QUEST中的结果：
SQL优化方法
• 定期更新表统计信息 1.更新表统计信息 Runstats的作用是重新更新数据库系统对表的统计信息， 这些信息在数据库生成执行计划时将被使用 runstats on table schema.tab_name [with distribution and detailed indexes all] 2.整理表存储碎片 如果一个表的数据量变化很多，与操作系统的文件系统一 样就会产生存储碎片，需要重新收集数据碎片和分布数据， 从而提高数据访问效率 reorg table tab_name 另外可以通过reorgchk on table…来检查碎片是否需要 整理
SQL优化-几点建议（一）
1、合理设置PARTITIONING KEY，分区键尽量选择值分布均 匀的字段 2、合理创建索引，提高检索效率,索引字段最好是离散值分 布均匀 3、大数据量表要合理做runstats，插入数据后做一次，创建 完索引后再做一次runstats RUNSTATS ON TABLE cqcrm.dw_product_dt WITH DISTRIBUTION AND DETAILED INDEXES ALL 4、少用delete、update语句，用insert的方式代替 5、表关联要使用表的别名约束字段，减少SQL解析时间， 也避免引起字段歧义 6、避免使用select * from tab，在select语句中明确指定 要检索的列，INSERT时亦如此，要列出相关字段
我们的应用几乎全都是SQL
SQL优化的意义
数据库资源是有限的，任何一条SQL语句的执 行都需要消耗一定的数据库资源（CPU、MEM、 I/O），良好的DB2 SQL语句可以被数据库重复使 用从而减少分析时间、改善一个系统的性能、对提 高数据库内存区的命中率、减少I/O访问等有着非常 重要的意义。
优化的目的： 1、提升执行效率 2、减轻数据库开销
SQL优化方法
• 有效利用执行计划 1.通过查看sql执行计划，可以得到该sql的成本消耗，对sql 的优化可以成为重要的参考信息 db2expln -d db_name -u user_name passwd -t -f do.sql -g -z ';‘ 其中 -t 表示显示结果在终端上 -f 表示分析的sql命令文件 -g 表示输入执行计划的图形 -z 表示sql命令文件中各个sql之间的分隔符 查看sql执行计划 2.在执行计划的输出结果中重点查看执行消耗指标： Estimated Cost = 9118.881836
SQL优化-几点建议（三）
13、清空表使用Alter table activate not logged initially with empty table,不要使用delete 14、有时候用表关联比exists或者not exists更高效
SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’); （更高效） SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A’ ;
1.建表时必须明确指明表空间/索引表空间 2.建表时必须指明分区键partitioning key，注意分区键的选择应当尽量使 用值域大、值分布均匀的字段，比如phone_no等 Create table tab_name( col1 int, col2 varchar(10) ) partitioning key(col1) in tbs_store index in tbs_idx not logged initially 3.可使用如下语句察看表数据是否分布均匀： select nodenumber(phone_no), count(*) from tmp1_vgrp_inet_call group by nodenumber(phone_no)
SQL优化-几点建议（二）
7、用EXISTS替代IN、用NOT EXISTS替代NOT IN;（视情况而 定，对于大集合用exists，小集合使用in） 8、避免在索引列上做函数运算，例如：WHERE SAL*12>25000或者WHERE SUM(SAL)>25000 9、对于固定参数，用IN来替代OR： WHERE LOC_ID=10 OR LOC_ID=15 OR LOC_ID=20可以用WHERE LOC_ID IN (10,15,20) 10、避免在索引列上使用IS NULL和IS NOT NULL; 11、用UNION-ALL替代UNION,UNION-ALL不排重 12、避免在索引列上使用<>或者！=，用>并且<代替，例 如:WHERE SAL>10 and SAL<10
• 分步执行
1、insert into TAB_XXX SELECT PHONE_NO,SUM(CALL_DURATION_M) DURATION FROM DW_CALL_CDR_200910 WHERE CALLTYPE_ID='01' AND (TIME(START_DATETIME)>='22:00:00' OR TIME(START_DATETIME)<='07:59:59') AND ROAMTYPE_ID='0' AND TOLLTYPE_ID='0' and system_type<>'vn' AND CHAT_TYPE='000' GROUP BY PHONE_NO HAVING SUM(CALL_DURATION_M)>10 2、create index idx_phone on TAB_XXX (phone_no) 3、select a.phone_no,a. DURATION from TAB_XXX a left join (SELECT PHONE_NO FROM DW_HJH_USER WHERE OP_DATE='20091129') b on a.phone_no=b.phone_no where b.phone_no is null 查询数据量为：第一次查询1亿5千万次。第二次查询能使用到索引，查询效率极高，以上3步的处理时 间应该在30分钟左右
SQL优化方法
• 合理的使用索引 1. 基于效率问题来考虑使用索引，索引字段的选择应当是在 where子句中的关联字段 2.大量数据插入表时，如果该表已经建立了索引，效率会降 低（但不会太明显） 3.索引的创建也会增加sql的总体执行时间，因此需要平衡总 体的耗时，比如如果该表会多次使用，则倾向建立索引 4.如果表本身就需要全表扫描，则可以不建立索引 5.当发现某些sql执行时间很长时，应当考虑索引的使用 6.在关联字段一般为联合字段的情况下应当使用复合索引， 否则更倾向于各个关联字段建立各自的索引
典型SQL案例（一）
• 对比分析
xxx_179511 20万左右的数据量 ods_dcustmsgneБайду номын сангаас_yyyymm 1000万左右的量
update xxx_179511 a set a.qx=(select substr(b.belong_code,3,2) from xxx_deal.ods_dcustmsgnew_yyyymmdd b where a.id_no=b.id_no and b.belong_code like ‘xx%');
DB2之SQL优化浅析
白德全 西南区中心团队&青海PSO
目录
为什么要做SQL优化
SQL优化建议
典型SQL案例
SQL语句对数据库的影响
1、SQL语句是操作数据库的唯一途径，任何应用程 序对数据库的操作最终都会归结为SQL语句对数据 库的操作 2、SQL语句的执行效率对数据库系统的性能起到了 决定性作用 3、据统计SQL语句几乎消耗了70%~90%的数据库 资源 4、SQL语句的不同写法在性能上的差异可能很大
15、将复杂的表关联语句，拆分为多步执行，简化SQL语句， 减轻数据库开销 16、大数据量插入某个临时表，应该激活Not logged模式 以便不记录日志 alter table tab_name activate not logged initally
目录
为什么要做SQL优化
SQL优化建议
典型SQL案例
SQL优化方法
• 合理的使用表连接 1. 数据量小的表作为左表（当然在业务逻辑允许的情况下） 2. 将使用条件筛选的表作为左表 3. 具有索引选择操作的表作为左表，否则每扫描左表中的一 行，就要扫描一遍整个表 4. 重复记录少的表更趋向于作左表 5. 外关联时，on后面仅仅存放关联条件，对于关联表内部的 条件应当放在where子句中
典型SQL案例（二）
• 对比分析
SELECT DISTINCT PHONE_NO,SUM(CALL_DURATION_M) DURATION FROM DW_CALL_CDR_200910 WHERE CALLTYPE_ID='01' AND (TIME(START_DATETIME)>='22:00:00' OR TIME(START_DATETIME)<='07:59:59') AND ROAMTYPE_ID='0' AND TOLLTYPE_ID='0' and system_type<>'vn' AND PHONE_NO NOT IN (SELECT PHONE_NO FROM DW_HJH_USER WHERE OP_DATE='20091129') AND CHAT_TYPE='000' GROUP BY PHONE_NO HAVING SUM(CALL_DURATION_M)>10 查询数据量为：300多亿次左右，处理时间在6~24小时
目录
为什么要做SQL优化
SQL优化建议
典型SQL案例
SQL优化的原则
1、尽量减少数据库查询次数，减少对数据资源的请 求次数 2、尽量使用索引，避免全表扫描，提升检索效率 3、尽量避免多表关联以及子查询的使用，一切从简
目标：减少SQL复杂度，提高效率，只要够快，咋
走都行~~
SQL优化方法
• 合理的设置分区键
典型SQL案例（三）
• 对比分析：
Select a.* from ods_acct_shoulditem_200911 a where a.id_no not in (select id_no from ods_acct_shoulditem_200910) 查询数据量为：若ods_acct_shoulditem_yyyymm 为一亿条，则该查询需要执 行的次数为1亿 X 1亿次 采用LEFT JOIN 方式： Select a.* from ods_acct_shoulditem_200911 a left join ods_acct_shoulditem_200910 on a.id_no=b.id_no Where b.id_no is null 查询数据量为：该查询只需要查询1亿次 第一次个查询时间将是第二个查询时间的N（N>10）倍 总结：not in 的SQL语句执行过程 ：A not in ( select * from B) 执行的次数为 A * B 次。 left join 执行过程： A left join B on A.x=B.x where B.x is null 执行次数为A 次
10分钟左右都出不来
典型SQL案例（一）
• 对比分析（优化后）
xxx_179511 20万左右的数据量 ods_dcustmsgnew_yyyymm 1000万左右的量
以下方法就是几秒钟：
1. create table xxx_179511_1 like xxx_179511 in tbs_app_xxx partitioning key (id_no); 2. create index idx_xxx_179511 on xxx_179511 (id_no); 3. insert into xxx_179511_1 select a.id_no,a.phone_no,a.login_no,a.district_name,a.town_name,a.op_ti me,a.begin_time,a.end_time,substr(b.belong_code,3,2) from xxx_179511 a left join xxx_deal.ods_dcustmsgnew_yyyymmdd b on a.id_no=b.id_no; 4. drop table xxx_179511