SQL优化技术交流.ppt

NCR-DW2_ Teradata Corp. Xu Feng 2008年10月 SQL运行很慢？ 系统负载过大，登陆查询的用户过多 相关表被锁 Database lock、Table lock Utility lock，如Arcmain、Tpump 数据库内部操作 Crashdumps Mini-Cylpacks/Packdisk 查询运行太偏或IO过大 SQL编写不规范 Primary Index选择不当 错误的执行路径 ETL SQL 优化过程 选取目标 收集信息 调优之前必须收集的两类信息： 表定义（特别关注PI或PPI） 表大小 表定义可以通过Show Query命令展现，可以一次解析出所有的视图、物理表； 表大小可以通过以下SQL获取： Explain分析 执行命令Explain Query，观察当前优化器所选择的执行路径 什么是合理的执行路径？ 大表不会发生all-AMPs Duplicated 大表应尽量避免all-AMPs Redistributed，尽可能直接引用或Built locally 小表应尽量all-AMPs Duplicated 或 all-AMPs Redistributed 在多表进行关联的时候，最佳的情况是第一次关联就使Spool结果集缩至最小，应避免大表与大表先发生关联 若关联的一方记录集极小（100条），尽可能采取product join 分区表首先进行Partition Elimination，尽可能避免all-Partitions retrieve 不良的执行路径意味着长耗时和资源浪费，需要进行优化 优化手段 整体逻辑 避免大进大出 代码共用 建立临时表 PDM设计 调整PI 使用PPI 使用SI 其他设计(multiset,no fallback) 调整SQL SQL写法不优化，忽略了Teradata的机理与特性 收集统计 使优化器获得足够的信息，因此能提供更有效的执行路径 流程优化 避免作业拥挤 优化案例之整体逻辑 通过优化算法，减少对大表的读写操作 避免大进大出 在做增量比对或全量比对的时候，尽量减少DEL/INS的记录数 Case study【T03_AGMT_ACCU_H 1:42:29 - 0:28:08】 代码共用 在做多步并插的时候，如果各步关联的表完全一致，可以考虑将多步合并为一步 在做多步并插的时候，如果各步关联的大表一致，可考虑建立临时表 Case study 【CTIT 1:22:47 - 0:30:39】 如果多个作业存在相同的大查询，可以考虑将该查询作为单独的作业 使用临时表 在Teradata数据库中，对空表操作不计Transient journal，推荐多用临时表 Volatile table使用Spool space，并且local to session，推荐使用volatile table 当调整PI和增加统计都无法改变执行路径的时候，可以考虑通过临时表强制更改执行路径 Case study 【T_PFS_TRADE_ACC 0:59:49 - 0:00:10】 优化案例之PDM设计 为避免在多表Join过程中，大表做Redistribute操作，必须根据实际情况调整PI 调整PI是优化执行路径最有效的手段 如果对大表的读取经常带有过滤条件（如时间序列、代码序列），建议改用PPI Partition Elimination能极大提高查询速度 PPI在某些查询中会减慢关联速度，必须均衡考虑 在某些特定条件下，通过SI可以极大提高查询速度 使用SI在某些查询中可以提高速度，但在其他查询中可能减慢速度，必须均衡考虑 使用SI必须对相关字段做统计，否则优化器不会采纳 物理表尽量采用multiset table和no fallback 用insert select语句时，尽量保证源表和目标表的PI字段类型一致、长度一致，比如避免char(20)和varchar(20) 优化案例之调整SQL Multiple Insert/select ? Multi-Statement Insert/Select Insert/Select with Union/Union all ? Multi-Statement Insert/Select 记录排重 – 使用Qualify Row_Number() (Partitioned by …Order by…) 关联条件必须遵循PI的设置 Case study 【JSACCOUNT 2:16:32 - 0:09:34】 关联条件中尽量避免不必要的trim、coalesce、substr、cast操作 使用trim等函数在多数情况下会使该表redistributed 关联条件中尽量少用or