DM针对大数据量环境下分析型应用的支持方案

1、DTCC2011DM 针对大数据量环境下分析型应用的支持方案DTCC2011大纲一个实际案例挑战和解决方案下一步工作规划DTCC2011一个实际案例案例简介DTCC2011海量数据 基于已有硬件投资 单服务器节点 操作库和分析库合并以查询分析为主，兼顾少量数据维护硬件与拓扑DTCC2011千兆交换机数据清洗与入库应用服务 器P550Cpu x 4 数据库 Mem 32GB 服务器P550Cpu x 4Mem 32GB16 X 1TB SASRAID 5案例简介 - 数据DTCC2011以常规数据为主，主要为数值、字符串、 时间类型日增长数据量为约 56G ，3 亿条元组 当前数据量 3TB 最

2、大单表为计费表，目前约 150 亿条记录 数据保存 20 年后归档为历史数据 在线数据规模将超过 400TBDTCC2011典型业务流程 源数据清洗入库 分析统计型查询 第一步过滤的筛选条件不确定 试错式的查询分析过程，成功后固化，一般包含 20 多个步骤 大规模的连接查询、子查询、联合查询、数据分组与排序、临 时结果集与临时表等 复杂SQL不多，但 IO 非常大 日常数据维护 手工修改记录内容 批量删除 定期维护DTCC2011案例需求关键在查询性能 第一个过滤步骤 筛选字段由用户随机定义，因此无法使用索引 一般会得到千万级别的结果集 大量的多表连接查询数据装载性能初始入库 48 亿条，近

3、1T ：限48 小时，相当于 3万条/s 后续每 3天入库一次， 9亿条， 168G ，限10 小时内完成原有产品难以支持分析型应用DTCC2011只支持行式存储 查询优化器比较简陋 虚拟机实现不尽合理 物理存储设计有待优化 日志系统过于复杂 不能充分利用多机资源提升性能 数据分片技术不完善于 2009 年开始新一代产品 DM7 的研制DM系统研1D制 TC历C2程01实验室原型 技术积累阶段 实现各类标准 稳定性及功能与开源系统有差距持续的技术积累5.6 引入物理操作符 ,虚拟机6.0 引入高级特性和 oracle 兼容特性43DM5.62007DM4DM620091DM1-DM320045

4、DM72011对 DM4-DM6 的技术总结融合列存储与行存储基于向量数据的执行内核原生的 MVCCOLAP 应用的支持19882003数据控制权传递 - 批量技术DTCC2011 向量数据处理 在数据泵一次传送一批数据 减少控制转移的 CPU 损耗； 有利于批量的表达式计算传统的数据D传TCC递2011PROJECTFILTER一次只传递一条记录 每个操作符一次只处理一行记录111控制权需要反复传递SCANPROJECTSCAN向量式的数据D传TC递C2011减少控制权限的反复传递提升CPU的有效利用率便于表达式批量计算批量技术 - 数据入库DTCC2011 将系统的初始数据入库 原有 BC

5、P 接口达到 5000 条/s ，仍无法满足要求 改进： 在服务器端实现批量，减少执行流程中的控制跳转 效率提升倍普通普通批量 绑定计划生成单趟扫描一个 ID 进行更新，执行 20 万次批量针对大表更 新的特定的 批量绑定消 息生成特定计 划，减少执 行流程ID 进行排序，单趟扫描 20 万个ID并进行更新批量技术 - 全表更新DTCC2011性能提升 100 倍以上，控制在 2秒以内DTCC2011批量技术 -LIKE 谓词 select count(*) from orders whereo_comment not like%special%requests%DBMS O3.311g:DB

6、MS S 2005: 10DM7: 0.4orders : 1,500,000 记录c2p.2uG , 多次执行表达式计算 - 表达式结果重用DTCC2011 一个表达式出现多次 Select sum(2 * c1), sum(3 * (2 * c1) from t 只计算一次，结果缓存 v1 = 2 * c1 ； Select sum( v1), sum(3 * v1) from t 类似思路：中间结果重用 一个复杂查询在一条 sql 语句中使用多次的情况 将复杂查询提取，并将结果缓存，多次使用批量表达式D计TCC算2011for (i= 0; i 1001.80Q181.279.2122.

7、012.90Q191.929.065.624.17Q200.789.231000.79Q212.248.8833.015.49Q220.240.341001.16优化器 - 分析器流程DTCC2011智能优化器DTCC2011基于多趟分析的代价优化器 语义分析、代价优化过程分离 灵活的计划变换控制 基于时间单位 (ms) 的代价计算 解决统计信息的使用性问题 增加频率直方图 增加高度直方图的桶数查询优化：关系变换DTCC2011SFW 结构转换为关系树投影 (PROJECT)连接 (JOIN)半连接 (SEMI JOIN) 选择 (SELECT) 基本表 (BASE TABLE)Select

8、: ID , nameFrom : TWhere : ID = 10PROJECT (ID , name )SELECT(ID = 10)BASE _TABLE (T)SFW结构关系树查询优化：关系变换的关键 DTCC2011 消除子查询，“平坦”的关系树 子查询一律转化为半连接( SEMI JOIN )例： select from T1 where t1.id in (select ID from T2)PROJECTSEMIJOINT1T2查询优化：待选关系树的生成DTCC2011考虑三个因素 A. 确定的连接次序 B. 确定的卡特兰 2 叉树形状,特殊处理 C. 是否下放过滤条件 采用临

9、时结果减少重复计算 代价模型基本覆盖所有情况 对连接表的个数非常多的情况查询优化：统计信息DTCC2011记录数据分布情况，用于精确行数估计， 特别是数据分布不规则的情况，对基数及代价计算有重大影响频率直方图：不同值较少500450400350300250200150100500432124w_id = 0400238200167w_id = 1 w_id = 2w_id = 3w_id = 4w_id = 5300w_id = 6等高直方图：不同值较多4050400039503900385040324002399038003950396038883980I/O效率-融合列存储和行存储1D T

10、CC201列存储:数据按列存储 结合自适应压缩技术 与批量计算技术紧密结合 列存储优缺点 大幅提升扫描性能 适合批量装载与删除 不适合频繁的插入、删除和更新 融合列存储和行存储 提供按列存储选项 结合分区技术同时适应 OLAP和OLTP应用需求I/O 效率DTCC2011行存储优化简化物理记录格式 字段物理次序与逻辑次序分离 多 buffer 类型 常驻内存和常规方式淘汰 用户可以指定 批量读：预处理 支持垂直分区和水平分区DTCC2011提高并发度支持并行插入的物理数据存储 并行备份和恢复 分区技术及相应的并行查询操作符号典型场景一：大结果集DTCC2011场景描述某表T，31 个字段， 4

11、8 亿条记录 随机基于某字段筛选： SELECT * FROM T WHEREFLD1=753 查询符合条件的结果集达到千万条记录分析SQL 语句非常简单，没有更优的等效语句结果集筛选条件不确定，无法使用索引 服务器内存为 32G ，在扫描的过程中必然出现页面淘汰由于基础数据量大，因此即使命中率不高（ 0.2% ）， 也会生成 960 万条记录的结果集典型场景一：大结果集DTCC2011IO 效率从3个方向入手，提升全表扫描的 批量技术 降低结果集处理的时间消耗调整数据页读取策略典型场景一：大结果集DTCC2011返回结果集策略改进 优化前 根据通信块大小决定结果集分批次返回的数量 第一批结果

12、集返回后，自动完成后续结果集获取和返回 优化后 由应用设定第一批结果集的大小和返回的时机 当返回第一批结果集后，工作线程暂停 SQL 查询请求，直到下 一批结果集请求到来或开始新事务 效果 快速返回部分结果集，提高用户体验 避免自动返回所有结果集，降低服务器资源消耗典型场景一：大结果集DTCC2011调整数据读取策略 数据页（ page ）是数据读写的单位 优化前的全表扫描：按页读取，每次 IO 只扫描 一个页 优化后：一次扫描多个页，减少 IO 数量 测试：经过优化后，磁盘的吞吐量提升 1 倍典型场景二：大表连接DTCC2011场景描述 表T1 ，31 个字段， 5000W 条记录，数据类型

13、包括 int 、 varchar 、datetime 、Dec ；表T2,15 个字段， 500W 条记录， 数据类型包括 varchar 、 datetime 、 Dec ； SELECT T1.NAME, T2.TITLE FROM PERSON.PERSONT1, RESOURCES.EMPLOYEE T2 WHERET1.PERSONID = T2.PERSONID AND T1.SEX = M; 连接查询字段由最终用户临时指定，表上未建索引 结果集不大，但查询表数据量大，连接查询响应时间陡增典型场景二：大表连接DTCC2011分析 行存储特性：连接查询所连接的字段在数据页中的 存储非

14、连续，进行连接查询，需将所有数据页读到 内存， IO 消耗巨大； 连接匹配时，要对读入缓存中的所有页进行扫描。行存储：连接列分散在每个数据页中页1Cn+1Cn+1页NC1C2CnC1CmC1C2CnC1Cm典型场景二：大表连接DTCC2011优化方向：列存储按字段存储连接列被集中存储C1C1C1C2C2C3页1Cn+1CnC+m1Cn+1页N读入缓存中的数据页明显减少，系统 IO 下降典型场景二：大表连接 DTCC2011 优化方向：存储压缩 适用于列存储模式的压缩算法 初步压缩结果：采用本案例数据进行测试Float54% （压缩后大小 / 压缩前大小）Double33%Dec52%字符 56

15、%典型场景二：大表连接DTCC2011优化效果从17 小时降至 10 分钟以内int 、FROM典型场景三： 全表查询建表 DTCC2011场景描述 表 T,15 个字段， 500W 条记录，数据类型包括 varchar 、 datetime 、Dec ； 根据T进行查询建表： CREATE TABLE TT as SELECT * T;典型场景三： 全表查询建表 DTCC2011分析 大表进行查询建表时，需经过以下五个步骤初始 化目 标表全表扫描生成结果集插入结果事务提交 这个过程中可优化的操作有：查询与结果集的 生成和大量数据的插入操作典型场景三： 全表查询建表 DTCC2011直接B树操

16、作 避免结果集处理与数据插入 操作 直接复制根节点和叶子是在 内存中进行操作，速度更快优化效果对案例中的 T进行建表查询 优化前耗时约 35S 优化后耗时约 4S ，性能提升 9倍装载表数据到内存源表B树扫描复制B树典型场景四：重复表达式计算DTCC2011 场景描述 针对 500 万条记录的表进行如下查询 SELECT IDnum,sub(6,8,IDnum) as 生日,(now()-sub(6,8,IDnum) as 年龄 from 问题分析 表达式 sub(6,8,IDnum) 可重用典型场景四：重复表达式计算DTCC2011改进优化： 一个表达式出现多次，只计算一次 本例中性能提升

17、70% 。其他场景性能提升程度 取决于计算表达式的复杂度与数据量典型场景五： 并行查询插入 DTCC2011 场景描述 同结构的表 T1T10 ，每张表 500 万条记录，需要将 10 张表的所有数据合并到一个临时表 Ttmp 中 INSERT INTO Ttmp SELECT * FROM T1 INSERT INTO Ttmp SELECT * FROM T2。 应用的并行化并没有带来较大的提升 分析 Ttmp 成为瓶颈：原有的逻辑 Rowid 成为资源瓶颈 逻辑 Rowid ：不代表物理存储位置，更新、插入、重组 等操作代价降低，但 Rowid 需要通过临界资源获取 原有产品针对 OLT

18、P 业务场景， OLTP 事务以分散、短 小事务为主，原有的 RowID 机制不会成为突出瓶颈典型场景五： 并行查询插入 DTCC2011改进 物理 RowID ：代表记录的物理存储位置 多个工作线程进行插入操作，无需进入临界资 源获取 rowid ，每个工作线程自行生成 RowID 实现真正意义上的并发插入DTCC2011应用优化好的性能需要应用与数据库的配合实现 应用架构设计应站在系统全局考虑性能问题 应用与数据库应该取长补短数据存储 基于分区表进行数据划分应用的并行化 复杂事务分解为多个可并行的简单事务应用优化 - 手段DTCC2011保存第一步过滤结果集 利用视图减少中间结果集的保存

19、数据按月份分区 TOP查询减少不必要的全结果集应用优化 - 大表的全表扫描DTCC2011典型场景 5000 万无索引 TOP 查询： SELECT * FROM T6 WHERE NAME LIKE 张三 优化前：数据库服务器 CPU 满载而应用服务器没有 负载 在最坏情况下，将需要扫描整个表分析： 系统设计需要站在全局角度，充分考虑应用、 中间件、数据库之间的负载分配 充分利用已有的硬件应用优化 - 大表的全表扫描DTCC2011 改进： 数据进行分表和分区 DM 已实现的分区表并行查询操作符，提供了 分区表优化的支持 应用依据分表更改查询模块，从单线程改为 多线程 在应用服务器将各分表的

20、查询结果合并 效果： 按最坏情况测试，查询时间由原来的不可预 期，提升到 2 分钟内应用优化 - 数据清洗与入库DTCC2011最初方式： 基于 JDBC 驱动的数据迁移工具进行清洗和入库 操作 批量绑定 迁移工具的资源消耗随着迁移时间的持续增加， 导致迁移速度在运行 3 天后急剧下降 初始数据（ 1T ）入库时间达到 1个月，相当于 400 条/s应用优化 -数据清洗与入库DTCC2011问题分析： 超过 100 亿条记录，即使每 5000 条提交一次， 也有 2百万次的解析 -计划 -代价 -执行流程 大量的数据库 redo 与 undo 日志操作解决方案 利用批量 利用并行化充分发挥多

21、CPU 处理能力，增加 IO 的吞吐量 JDBC 方式转变为 JNI+ODBC实现动态编译型的 ETL 脚本引擎应用优化 -DM ETL 的技术改1D进 TCC201图 DMETL 内嵌 BCP应用优化-数据划分和并行化1DTCC201应用优化 -BCP DTCC2011 将清洗与入库分离 并行化清洗和装载入库 入库应用 BCP 方式 通过批量绑定减少了网络开销 服务器内部为 BCP 专门实现了” bcp_fast_insert 绕过SQL 处理流程，直接操作 B树叶子节点 不进行 Redo 与 Undo 不进行约束检查 对原有 BCP 也进行了服务器端的批量化处理最终效果：性能提升 100 倍，能够在 8 小时内完成海量数据备份的难题DTCC2011备份的效率问题 整库备份操作耗时太长备份粒度问题 需要灵活的针对整库、文件