基于Teradata的SQL性能调优

1、基于的性能调优teradatasql宋轶（上海交通大学信息安全学院，上海 200030）摘要： 对 teradata 数据仓库的系统架构做简单介绍，尤其是针对影响 teradata 性能的底层因素进行必要的分析，并对实践过程中的遇到的常见问题进行总结和归纳，希望能给读者一 些启发和帮助。关键词： teradata； 数据仓库； 性能调节； 数据倾斜引 言teradata 作为数据仓库解决方案的领导者，给我 们提供一个处理海量数据的平台。 在拥有选择好一个 良好的数据仓库平台后，从前期的咨询、实施，到后期 的升级维护，都是极其重要的。 需要强调的是，数据仓teradata 的数据存储及分布ter

2、adata 的数据分布是整个系统范围内的数 据 分布，是跨 amp 的数据分布，是针对每一个实体表的 数据分布。 amp 是 teradata 数据库系统中具体数据 的持有者和直接操作者，整个 teradata 数据库系统中 的每一条数据都唯一关联到某一个 amp 上。 同时，对 于系统中的每个表，其数据都会依照哈希算法分布在 系统中的全部 amp 上，也就是说，在整个系统范围内 容的所有 amp 上，都有系统中的所有的实体表，只是 同一个表在不同的 amp 上实际存储 的数据 并 不 相 同。在进行 teradata 数据库的逻辑设计时，对于每一02库的维护，并不是单一靠硬件的升级来实现。容

3、易忽略的是，随着数据的不断积累，日常的 etl 过程和数据查询都对数据仓库的性能产生很大的影响，因此也 存在很大的优化空间。teradata 系统构架简介teradata teradata 在 整 体 上 是 按 shared nothing1架构体系进行组织的，能够高效并发操作。主要组件如图 1 所示。其系统的个表， 都要指定一个“主索引”（primary index，简称pi），teradata 数据库引擎会根 据 这个主索引的实际 值，同时根据系统的配置情况（主要是 amp 的个数），通过一种哈希算法，为每个表的每一条数据都生成一个“行哈希值”（row hash value），terad

4、ata 会根据这个行哈希值把数据分布到某一个确定的 amp 上。根据哈希算法， 对于不同的 primary index 值，可 能会计算出相同的行哈希值，这些具有相同的行哈希值的数据行就会被分布在同一个 amp 上。 这样，如果 哈希算法选择得当，则可以基本保证，同一个表中的数据在整个 teradata 并行系统的多个 amp 上的分布 是趋于均匀的（参见图 2）。teradata fi !"#$fi%"-.图 1 teradata 系统功能组件收稿日期：2009-05-25 修稿日期：2009-07-26作者简介：宋轶（1982-），男，湖南人，工程硕士,研究方向为数据仓

5、库1+234567, !/0 fi%()*teradata&ampampampampbtnet mtdpmoiØfl ¼fi³li³litdpØfl ¼fi计 算 方 法 ：eff = sum (cpu)/(sum (io) / 1000)该 等 式 给 出 了 每 1000 单 位 io 时 cpu 的 消 耗比，数值越小，表示性能越好。例如:查 询 一: 1283 cpu seconds, 1.09m ios ration of 6查 询 二: 2568 cpu seconds, 2.09m ios ration of 1

6、23.3 并发度（parallel efficiency）:是指某查询对整 个系统的影响计算方法：pe = (total cpu time / effective cpu time) * 100%系统花在执行该查询语句上的total cpu time：cpu 时间总和。effective cpu time： 系统中最忙的一个 amp 执行该语句所花的时间乘以系统 amp 总数。实际上，并发度和倾斜度密切相关。一般来说，倾 斜度越高，并发度就越低。 反之亦然。图 2 teradata 的数据分布机制对于一个 teradata 系统， 其哈希算法是确定的，而且是运行时不可变的，在系统的硬件不变的前

7、提下（amp 数不变），这就保证了对于同一个 primary index 值，所生成的行哈希值也是相同的。 因此，对于不同的 表，尤其是有主-外键关联关系的表，通过慎重地定义 primary index， 可以做到那些经常要进行关联 （ 往往 是主-外键关联） 的行会被分布到同一个 amp 上，这 大大缩短了数据库进行数据关联的时间，提高了 ter- adata 系统的性能。 反之，由于 pi 选择不当导致数据 在各 amp 上分布不均，就会影响系统性能。影响 teradata sql 性能的原因及解决方案在实践中，我们对影响 teradata 系统性能的原因 和解决方法，做了如下总结和归类。

8、（1）表连接和子查询（table join versus sub query）一般来说，使用表连接和子查询都能得到预期的 数据结果，但是有些情况下，比如说要把相对较少的一部分数据从一个大的数据集中排除掉时，使用左外连接会有更好的性能。 所以当 sql 语句中有（not in)/in/（not exists) 这样的语句时，如果满足上述情况，我 们应当将其改为左外连接或者是内连接。（2）“并”操作（union）和嵌套子查询以及排除语 句（not in）4teradata 性能指标一般来说， 判断一个 teradata 系统的性能好坏，我们都考虑如下指标。3.1 倾斜度（skew）：各并行单元上资

9、源消耗不均 匀计算方法：%max/agv skew = (max-avg)/max *1003同样， 实践证明，“并”操作相比嵌套子查询和not in 语句，有更好的性能，所以在条件允许的情况下，尽可能使用“并”操作。（3）避免使用 teradata 的内置函数“csum”teradata 提供了“csum” 函数， 当我们需要生成例如：avg = 1.8, max = 5, skew = 64%键值时，可能会使用 csum (1,1)这个函数。但是这个通俗点说， 就是 teradata 系统中某个 amp 承担了过重的计算任务， 而同时其他 amp 却空闲或者等 待着，这样就造成系统资源的分

10、配不合理，同时也大大影响了系统的整体性能。3.2 cpu/io 效率比（efficiency ratio）teradata 系统的 io 能力很高，所以相对于 io 操 作，我们更关心 cpu 的消耗量。函数倾斜度很高，因为它只在一个 amp 上进行操作。处于性能考虑，强烈推荐使用如下替代方法：sum(1) over (rows unbounded preceding)该方法是在所有的 amp 上运行的，起数据倾斜。因此不会引（4）确保参与连接操作数据列的数据类型是一致的-./01t1 ¼2 2 -2 $56 +,(+,(78+8%( (+,(78+8%( (9+,:9,(9,(78

11、+8%( (+99,:<,(<<,: ø $ $ (48 bits) ¼ 2 $kÇ 034 !"# ( $);%& ,; $%& )%& +%& )*+%& (在连接操作或者 where 语句中，保持列的数据nation 中显示统计信息：diagnostic helpstats on for session;（9）ppi 过滤正 确 地 设 置 和 使 用 ppi （partitioned primary in-dex），能使极大地提升查询速度 ， 但是 teradata 优化 器只识别常量类型

12、的 ppi 过滤。 比如说某一日期类型的列（created_dt）被设置为了 ppi，在下列情况中，where created_dt >= date 2006-10-23 interval 23，teradata 优化器可以识别 ppi 过滤； where created_dt >= current_date interval23 month，teradata 优化器不能识别 ppi 过滤；where created_dt >= date 2006-10-23 23，teradata 优化器不能识别 ppi 过滤。（10）导出表和临时表teradata 优化器不会去分析某个导

13、出表，也不知 道导出表中包含有多少条数据，但是优化器会自动对临时表收集统计信息，并选择最优方案去执行查询语 句。 例如，当结果集很小时，优化器会临时表选择执行类型一致对性能是非常重要。 如果数据类型不一致，teradata 首先会去进行数据类型的转换操作，从而会 极大地影响系统的性能。 例如下述例子就是属于数据 类型不一致decimal (9,2), decimal(9) 和 float；varchar 和 char；iso char 类型和 utf char 类型。万一数据类型不一致的列需要进行连接操作时，须显式地进行数据转换，这样能一定程度上减小对系 统性能的影响。（5） 正确地使用 sq

14、l where 语句中的“ 不等于”操作“不等于”操作包括 in, not in, like, between,>, <, >=,<= 等，正确地使用上述操作符也能够提升系统性能。例如下述 sql，先前是：where a = b = c and a is like apple%进行优化后（对所有相关列都显示的设置不等条 件），系统性能也得到了提升。 改为：where a is like apple% , b is like apple% , c is likeapple% and a = b = c（6）从逻辑上将 sql 语句分段来避免使用导出表（derived ta

15、ble）同样， 如果能将复杂 sql 语句从逻辑上分割成 更小的 sql 语句，避免使用导出表，也能保证系统性能。 主要原因就是 teradata 的查询优化器并不能准确 地判断导出表的大小，从而可能对执行顺序和方法产生影响，导致性能低下。（7）正确地选择主索引（primary index）如果一张表中的数据倾斜度很高，基本上是由于这样会同时在所 有 的“duplicated data to all amps”，amp 上进行连接操作，因此有更好的性能；而对于导出表，则只是选择执行“joined using a merge join”。（11）先做聚合操作，然后再连接先做聚合操作， 特别是当和

16、某一维度表连接时，这样操作会非常高效的压缩数据，优化器会选择执行“duplicated data to all amps”操作，从而减小倾斜度。（12）左连接运算时，在空值上导致倾斜在一条查询语句中有多个左连接运算时，tera-data 会去分步执行。 这样就可能造成后面表在和先前 结果集进行连接运算时，列在空值或者其他某一值上倾斜。 解决方法是用其他能均匀分布的数值来代替空pi 选择得不恰当（pi 决定了数据的分布）。在这种情值。举例说明：修改前：tablealeft outer join tablebon tablea.item_id=tableb.item_id left outer j

17、oin tablecon tableb.order_id=tablec.order_idand tablec.order_status=3况下，数据库管理员（dba）通常建议更改 pi 并重建表，有时为了性能需要，还要增加 nusi （non-uniquesecondary index）。当一张表中存在 nupi（non-unique primary in-dex），那么 teradata 的 rdbms 就不会强制检查该列的唯一性。 为了避免数据倾斜，同样一个 nupi 值的记录 数要尽量少（经验证明，不要超过 100 条），这样就能保证数据能在物理上能均匀地分布在各个 amp 上。（8）收

18、集统计信息（collect statistics）collect statistics 语句在 teradata 中是常见的并且 非常重要的一条语句。 该语句能让 teradata 知道参与连接操作的列的数据分布情况。 如果统计信息不正确，可能会导致错误的执行顺序。 根据经验法则，一个表中 有超过 5%的数据被更新，则需要重新收集统计信息。在 teradata v2r5+版本中，下述命令能在 expla-修改后：tablealeft outer join tablebon tablea.item_id=tableb.item_id left outer join tablecon coales

19、ce (tableb.order_id, -tablea.item_id） =tablec.order_idand tablec.order_status=3只需要用 coalesce 函数将 tableb.order_id 上的空值转换为 -tablea.item_id，这样就能使空值均 匀分布到每个 amp 上，从而减小数据倾斜。（13）默认值倾斜在 数 据 表 中 ， 有些列可能有 默 认 值 （-999, null, 等等），当在这些列上进行连接运算时，也有可 能会造成性能很差。 有两种解决方案：将查询语句拆分成 2 条， 一条包含默认数据，另一条不含默认数据；在条件允许的情况下，直接

20、过滤掉导致倾斜的 数据。结 语由于篇幅有限，本文仅对 teradata 架构以及性能 调优进行了简单介绍介绍，可以帮助读者对相关内容 作初步了解。5参考文献1（美）沙沙等著. 孟小峰等译. 数据库性能调优：原理与技 术，20042a min tjoa 著. data warehousing and knowledge discov- ery，20053teradata solution technical overview. /t/workarea/downloadasset.aspx?id=4650sql performance tuning ba

21、sed on teradatasong yi（college of information security, shanghai jiaotong university, shanghai 200030）abstract： presents the architecture of teradata data warehousing system, the insights of data distri-bution of teradata system, summarizes the best practice for performance tuning during my daily wo

22、rk, and hopes it may help others.keywords： teradata; data warehouse; performance tuning; data skew（上接第 80 页）application of project teaching method in computernetwork technology course in higher vocational collegeshe jun1，wu li-chun2(1. ningxia vocational college of finance & economics, yinchuan 7500021；2. computer teaching and research section, ningxia medical university, yinchuan 750004）abstract： computer