基于Teradata的SQL性能调优.doc

基于的性能调优TeradataSQL宋轶（上海交通大学信息安全学院，上海 200030）摘要： 对 Teradata 数据仓库的系统架构做简单介绍，尤其是针对影响 Teradata 性能的底层因素进行必要的分析，并对实践过程中的遇到的常见问题进行总结和归纳，希望能给读者一 些启发和帮助。关键词： Teradata； 数据仓库； 性能调节； 数据倾斜引 言Teradata 作为数据仓库解决方案的领导者，给我 们提供一个处理海量数据的平台。 在拥有选择好一个 良好的数据仓库平台后，从前期的咨询、实施，到后期 的升级维护，都是极其重要的。 需要强调的是，数据仓Teradata 的数据存储及分布Teradata 的数据分布是整个系统范围内的数 据 分布，是跨 AMP 的数据分布，是针对每一个实体表的 数据分布。 AMP 是 Teradata 数据库系统中具体数据 的持有者和直接操作者，整个 Teradata 数据库系统中 的每一条数据都唯一关联到某一个 AMP 上。 同时，对 于系统中的每个表，其数据都会依照哈希算法分布在 系统中的全部 AMP 上，也就是说，在整个系统范围内 容的所有 AMP 上，都有系统中的所有的实体表，只是 同一个表在不同的 AMP 上实际存储 的数据 并 不 相 同。在进行 Teradata 数据库的逻辑设计时，对于每一02库的维护，并不是单一靠硬件的升级来实现。容易忽略的是，随着数据的不断积累，日常的 ETL 过程和数据查询都对数据仓库的性能产生很大的影响，因此也 存在很大的优化空间。Teradata 系统构架简介Teradata Teradata 在 整 体 上 是 按 Shared Nothing1架构体系进行组织的，能够高效并发操作。主要组件如图 1 所示。其系统的个表， 都要指定一个“主索引”（Primary Index，简称PI），Teradata 数据库引擎会根 据 这个主索引的实际 值，同时根据系统的配置情况（主要是 AMP 的个数），通过一种哈希算法，为每个表的每一条数据都生成一个“行哈希值”（Row Hash Value），Teradata 会根据这个行哈希值把数据分布到某一个确定的 AMP 上。根据哈希算法， 对于不同的 Primary Index 值，可 能会计算出相同的行哈希值，这些具有相同的行哈希值的数据行就会被分布在同一个 AMP 上。 这样，如果 哈希算法选择得当，则可以基本保证，同一个表中的数据在整个 Teradata 并行系统的多个 AMP 上的分布 是趋于均匀的（参见图 2）。Teradata fi !#$fi%-.图 1 Teradata 系统功能组件收稿日期：2009-05-25 修稿日期：2009-07-26作者简介：宋轶（1982-），男，湖南人，工程硕士,研究方向为数据仓库1+234567, !/0 fi%()*Teradata&AMPAMPAMPBTNET MTDPMOIfl fiLILITDPfl 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 123.3 并发度（Parallel Efficiency）:是指某查询对整 个系统的影响计算方法：PE = (Total CPU Time / Effective CPU Time) * 100%系统花在执行该查询语句上的Total CPU Time：CPU 时间总和。Effective CPU Time： 系统中最忙的一个 AMP 执行该语句所花的时间乘以系统 AMP 总数。实际上，并发度和倾斜度密切相关。一般来说，倾 斜度越高，并发度就越低。 反之亦然。图 2 Teradata 的数据分布机制对于一个 Teradata 系统， 其哈希算法是确定的，而且是运行时不可变的，在系统的硬件不变的前提下（AMP 数不变），这就保证了对于同一个 Primary Index 值，所生成的行哈希值也是相同的。 因此，对于不同的 表，尤其是有主-外键关联关系的表，通过慎重地定义 Primary Index， 可以做到那些经常要进行关联 （ 往往 是主-外键关联） 的行会被分布到同一个 AMP 上，这 大大缩短了数据库进行数据关联的时间，提高了 Ter- adata 系统的性能。 反之，由于 PI 选择不当导致数据 在各 AMP 上分布不均，就会影响系统性能。影响 Teradata SQL 性能的原因及解决方案在实践中，我们对影响 Teradata 系统性能的原因 和解决方法，做了如下总结和归类。（1）表连接和子查询（Table Join versus Sub Query）一般来说，使用表连接和子查询都能得到预期的 数据结果，但是有些情况下，比如说要把相对较少的一部分数据从一个大的数据集中排除掉时，使用左外连接会有更好的性能。 所以当 SQL 语句中有（Not in)/in/（Not exists) 这样的语句时，如果满足上述情况，我 们应当将其改为左外连接或者是内连接。（2）“并”操作（Union）和嵌套子查询以及排除语 句（Not in）4Teradata 性能指标一般来说， 判断一个 Teradata 系统的性能好坏，我们都考虑如下指标。3.1 倾斜度（Skew）：各并行单元上资源消耗不均 匀计算方法：%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 却空闲或者等 待着，这样就造成系统资源的分配不合理，同时也大大影响了系统的整体性能。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+8%( (+99,:,(= 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 优化器不会去分析某个导出表，也不知 道导出表中包含有多少条数据，但是优化器会自动对临时表收集统计信息，并选择最优方案去执行查询语 句。 例如，当结果集很小时，优化器会临时表选择执行类型一致对性能是非常重要。 如果数据类型不一致，Teradata 首先会去进行数据类型的转换操作，从而会 极大地影响系统的性能。 例如下述例子就是属于数据 类型不一致Decimal (9,2), Decimal(9) 和 Float；Varchar 和 Char；ISO char 类型和 UTF Char 类型。万一数据类型不一致的列需要进行连接操作时，须显式地进行数据转换，这样能一定程度上减小对系 统性能的影响。（5） 正确地使用 SQL 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 Table）同样， 如果能将复杂 SQL 语句从逻辑上分割成 更小的 SQL 语句，避免使用导出表，也能保证系统性能。 主要原因就是 Teradata 的查询优化器并不能准确 地判断导出表的大小，从而可能对执行顺序和方法产生影响，导致性能低下。（7）正确地选择主索引（Primary Index）如果一张表中的数据倾斜度很高，基本上是由于这样会同时在所 有 的“duplicated data to all amps”，AMP 上进行连接操作，因此有更好的性能；而对于导出表，则只是选择执行“joined using a merge join”。（11）先做聚合操作，然后再连接先做聚合操作， 特别是当和某一维度表连接时，这样操作会非常高效的压缩数据，优化器会选择执行“duplicated data to all amps”操作，从而减小倾斜度。（12）左连接运算时，在空值上导致倾斜在一条查询语句中有多个左连接运算时，Tera-data 会去分步执行。 这样就可能造成后面表在和先前 结果集进行连接运算时，列在空值或者其他某一值上倾斜。 解决方法是用其他能均匀分布的数值来代替空PI 选择得不恰当（PI 决定了数据的分布）。在这种情值。举例说明：修改前：TableALEFT OUTER JOIN TableBON TableA.ITEM_ID=TableB.ITEM_ID LEFT OUTER JOIN 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）收集统计信息（Collect Statistics）Collect Statistics 语句在 Teradata 中是常见的并且 非常重要的一条语句。 该语句能让 Teradata 知道参与连接操作的列的数据分布情况。 如果统计信息不正确，可能会导致错误的执行顺序。 根据经验法则，一个表中 有超过 5%的数据被更新，则需要重新收集统计信息。在 Teradata V2R5+版本中，下述命令能在 Expla-修改后：TableALEFT OUTER JOIN TableBON TableA.ITEM_ID=TableB.ITEM_ID LEFT OUTER JOIN TableCON coalesce (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 条， 一条包含默认数据，另一条不含默认数据；在条件允许的情况下，直接过滤掉导致倾斜的 数据。结 语由于篇幅有限，本文仅对 Teradata 架构以及性能 调优进行了简单介绍介绍，可以帮助读者对相关内容 作初步了解。5参考文献1（美）沙沙等著. 孟小峰等译. 数据库性能调优：原理与技 术，20042A Min Tjoa 著. Data Warehousing and Knowledge Discov- ery，20053Teradata Solution Technical Overview. /t/WorkArea/DownloadAsset.aspx?id=4650SQL Performance Tuning Based 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 work, 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 Network Technology course in Highe r Vocati