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

1、.：.；DTCC2021DM针对大数据量环境下分析型运用的支持方案大纲一个实践案例挑战和处理方案下一步任务规划DTCC2021DTCC2021一个实践案例案例简介DTCC2021 海量数据 基于已有硬件投资 单效力器节点 操作库和分析库合并 以查询分析为主，兼顾少量数据维护硬件与拓扑千兆交换机DTCC2021运用效力器数据汇总文本数据源文本 Excel数据数据清洗与入库数据库效力器P550Cpu x 4Mem 32GBP550Cpu x 4Mem 32GB源源16 X 1TB SASRAID 5文本数据源数据案例简介-数据DTCC2021 以常规数据为主，主要为数值、字符串、时间类型 日增长数

2、据量为约56G，3亿条元组 当前数据量3TB 最大单表为计费表，目前约150亿条记录 数据保管20年后归档为历史数据 在线数据规模将超越400TB典型业务流程DTCC2021 源数据清洗入库 分析统计型查询 第一步过滤的挑选条件不确定 试错式的查询分析过程，胜利后固化，普通包含20多个步骤 大规模的衔接查询、子查询、结合查询、数据分组与排序、临时结果集与暂时表等 复杂SQL不多，但IO非常大 日常数据维护 手工修正记录内容 批量删除 定期维护案例需求DTCC2021 关键在查询性能 第一个过滤步骤 挑选字段由用户随机定义，因此无法运用索引 普通会得到千万级别的结果集 大量的多表衔接查询 数据装

3、载性能 初始入库48亿条，近1T：限48小时，相当于3万条/s 后续每3天入库一次，9亿条，168G，限10小时内完成DTCC2021挑战-中心是性能原有产品难以支持分析型运用DTCC2021只支持行式存储查询优化器比较简陋虚拟机实现不尽合理物理存储设计有待优化日志系统过于复杂不能充分利用多机资源提升性能数据分片技术不完善于2021年开场新一代产品DM7的研制DTCC2021实验室原型技术积累阶段实现各类规范继续的技术积累5.6引入物理操作符,虚拟机6.0引入高级特性和oracle 兼容特性5DM72021稳定性及功能与开源系统有差距3DM5.64DM62021对DM4-DM6的技术总结交融列

4、存储与行存储基于向量数据的1DM1-DM32DM420042007执行内核原生的MVCCOLAP运用的支持1988-2003DM系统研制历程对于性能的了解DTCC2021运用系统的设计表达式计算优化器综合性能数据/控制权传送I/O效率并发/并行数据控制权传送-批量技术 DTCC2021 向量数据处置 在数据泵一次传送一批数据 减少控制转移的CPU损耗； 有利于批量的表达式计算传统的数据传送PROJECTFILTER一次只传送一条记录每个操作符一次只处置一行记录111控制权需求反复传送SCANDTCC2021向量式的数据传送PROJECT减少控制权限的反复传送提升CPU的有效利用率FILTER便

5、于表达式批量计算SCAN12N12NDTCC2021批量技术-数据入库DTCC2021 将系统的初始数据入库 原有BCP接口到达5000条/s，仍无法满足要求 改良： 在效力器端实现批量，减少执行流程中的控制跳转 效率提升倍批量技术-全表更新DTCC2021普通批量普通批量绑定针对大表更新的特定的批量绑定消息方案生成生成特定计划，减少执行流程单趟扫描一个ID进展更新，执行20万次ID进展排序，单趟扫描20万个ID并进展更新性能提升100倍以上，控制在2秒以内批量技术-LIKE谓词 select count(*) from orders whereo_comment not like%speci

6、al%requests%DTCC2021DBMS O 11g:3.3DBMS S 2005: 10DM7:0.4orders : 1,500,000记录cpu 2.2G,多次执行DTCC2021 一个表达式出现多次 Select sum(2 * c1), sum(3 * (2 * c1) from t 只计算一次，结果缓存 v1 = 2 * c1； Select sum(v1), sum(3 * v1) from t 类似思绪：中间结果重用 一个复杂查询在一条sql语句中运用多次的情况 将复杂查询提取，并将结果缓存，多次运用表达式计算-表达式结果重用批量表达式计算for (i = 0; i 1

7、001.80Q181.279.2122.012.90Q191.929.065.624.17Q200.789.231000.79Q212.248.8833.015.49Q220.240.341001.16TPCHDM7DBMSO11PGSQL8.3DBMSS2005Q11.3149.0916.0112.87Q20.160.0460.190.14Q30.8621.619.302.78Q40.989.030.800.68Q51.49.054.611.58Q60.7892.720.96Q71.6111.7319.542.35Q82.30.282.972.01Q931.6118.015.45Q101.3

8、69.165.832.23Q110.1944.670.550.46TPC-H /SF=1对比测试(S)优化器-分析器流程DTCC2021SQL脚本语法分析语法树语义分析SFW构造关系代数变换关系树代价优化优化了的关系树物理方案生成执行方案智能优化器 基于多趟分析的代价优化器 语义分析、代价优化过程分别 灵敏的方案变换控制 基于时间单位(ms)的代价计算 处理统计信息的运用性问题 添加频率直方图 添加高度直方图的桶数DTCC2021查询优化：关系变换DTCC2021 SFW构造转换为关系树Select : ID , nameFrom : TSFW构造投影(PROJECT)衔接(JOIN)半衔接(

9、SEMI JOIN)选择(SELECT)根本表(BASE TABLE)Where : ID = 10PROJECT(ID , name )SELECT(ID = 10)BASE _TABLE (T)关系树查询优化：关系变换的关键DTCC2021 消除子查询，“平坦的关系树 子查询一概转化为半衔接SEMI JOIN例：select from T1 where t1.id in (select ID from T2)PROJECTSEMIJOINT1T2查询优化：待选关系树的生成DTCC2021 思索三个要素 A.确定的衔接次序 B.确定的卡特兰2叉树外形 C.能否下放过滤条件 采用暂时结果减少反

10、复计算 代价模型根本覆盖一切情况 对衔接表的个数非常多的情况,特殊处置查询优化：统计信息DTCC2021 记录数据分布情况，用于准确行数估计，特别是数据分布不规那么的情况，对基数及代价计算有艰苦影响 频率直方图：不同值较少500450400350300250400200238432300200150100500124167w_id = 0w_id = 1w_id = 2w_id = 3w_id = 4w_id = 5w_id = 6 等高直方图：不同值较多4050400040023990403239803950390038503800395039603888DTCC2021 列存储: 数据按列

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

12、录 随机基于某字段挑选：SELECT * FROM T WHEREFLD1=753 查询符合条件的结果集到达千万条记录 分析SQL语句非常简单，没有更优的等效语句结果集挑选条件不确定，无法运用索引效力器内存为32G，在扫描的过程中必然出现页面淘汰由于根底数据量大，因此即使命中率不高0.2%，也会生成960万条记录的结果集典型场景一：大结果集DTCC2021从3个方向入手，提升全表扫描的IO效率 批量技术 降低结果集处置的时间耗费 调整数据页读取战略典型场景一：大结果集DTCC2021 前往结果集战略改良 优化前 根据通讯块大小决议结果集分批次前往的数量 第一批结果集前往后，自动完成后续结果集获

13、取和前往 优化后 由运用设定第一批结果集的大小和前往的时机 当前往第一批结果集后，任务线程暂停SQL查询恳求，直到下一批结果集恳求到来或开场新事务 效果 快速前往部分结果集，提高用户体验 防止自动前往一切结果集，降低效力器资源耗费典型场景一：大结果集DTCC2021 调整数据读取战略 数据页page是数据读写的单位 优化前的全表扫描：按页读取，每次IO只扫描一个页 优化后：一次扫描多个页，减少IO数量 测试：经过优化后，磁盘的吞吐量提升1倍典型场景二：大表衔接DTCC2021 场景描画 表T1，31个字段，5000W条记录，数据类型包括int、varchar、datetime、Dec；表T2,

14、15个字段，500W条记录，数据类型包括varchar、datetime、Dec； SELECT T1.NAME, T2.TITLE FROM PERSON.PERSONT1, RESOURCES.EMPLOYEE T2 WHERET1.PERSONID = T2.PERSONID AND T1.SEX = M; 衔接查询字段由最终用户暂时指定，表上未建索引 结果集不大，但查询表数据量大，衔接查询呼应时间陡增典型场景二：大表衔接DTCC2021 分析 行存储特性：衔接查询所衔接的字段在数据页中的存储非延续，进展衔接查询，需将一切数据页读到内存，IO耗费宏大； 衔接匹配时，要对读入缓存中的一切页

15、进展扫描。 行存储：衔接列分散在每个数据页中Cn+1页1Cn+1页NC1C2CnC1CmC1C2CnC1Cm典型场景二：大表衔接DTCC2021 优化方向：列存储按字段存储衔接列被集中存储Cn+1 Cn+1页1Cn+1页N读入缓存中的数据页明显减少，系统IO下降C1C1C1C2C2C3Cm典型场景二：大表衔接 优化方向：存储紧缩 适用于列存储方式的紧缩算法 初步紧缩结果：DTCC2021采用本案例数据进展测试Float 54%紧缩后大小/紧缩前大小Double 33%Dec 52%字符56%典型场景二：大表衔接 优化效果从17小时降至10分钟以内DTCC2021典型场景三：全表查询建表DTCC

16、2021 场景描画 表T,15个字段，500W条记录，数据类型包括int、varchar、datetime、Dec ； 根据T进展查询建表：CREATE TABLE TT as SELECT * FROMT;典型场景三：全表查询建表DTCC2021 分析 大表进展查询建表时，需经过以下五个步骤初始化目标表全表扫描生成结果集插入结果事务提交 这个过程中可优化的操作有：查询与结果集的生成和大量数据的插入操作典型场景三：全表查询建表DTCC2021 直接B树操作 防止结果集处置与数据插入操作 直接复制根节点和叶子是在内存中进展操作，速度更快 优化效果对案例中的T进展建表查询 优化前耗时约35S 优化

17、后耗时约4S，性能提升9倍装载表数据到内存源表B树扫描复制B树典型场景四：反复表达式计算DTCC2021 场景描画 针对500万条记录的表进展如下查询 SELECT IDnum,sub(6,8,IDnum) as 生日,(now()-sub(6,8,IDnum) as 年龄 from 问题分析 表达式sub(6,8,IDnum) 可重用典型场景四：反复表达式计算DTCC2021 改良优化： 一个表达式出现多次，只计算一次 本例中性能提升70%。其他场景性能提升程度取决于计算表达式的复杂度与数据量典型场景五：并行查询插入DTCC2021 场景描画 同构造的表T1T10，每张表500万条记录，需求

18、将10张表的一切数据合并到一个暂时表Ttmp中 INSERT INTO Ttmp SELECT * FROM T1 INSERT INTO Ttmp SELECT * FROM T2。 运用的并行化并没有带来较大的提升 分析 Ttmp成为瓶颈：原有的逻辑Rowid成为资源瓶颈 逻辑Rowid：不代表物理存储位置，更新、插入、重组等操作代价降低，但Rowid需求经过临界资源获取 原有产品针对OLTP业务场景，OLTP事务以分散、短小事务为主，原有的RowID机制不会成为突出瓶颈典型场景五：并行查询插入DTCC2021 改良 物理RowID：代表记录的物理存储位置 多个任务线程进展插入操作，无需进

19、入临界资源获取rowid，每个任务线程自行生成RowID 实现真正意义上的并发插入运用优化DTCC2021 好的性能需求运用与数据库的配合实现 运用架构设计应站在系统全局思索性能问题 运用与数据库应该取长补短 数据存储 基于分区表进展数据划分 运用的并行化 复杂事务分解为多个可并行的简单事务运用优化-手段DTCC2021保管第一步过滤结果集利用视图减少中间结果集的保管数据按月份分区TOP查询减少不用要的全结果集运用优化-大表的全表扫描 DTCC2021 典型场景 5000万无索引TOP查询：SELECT * FROM T6WHERE NAME LIKE 张三 优化前：数据库效力器CPU满载而运

20、用效力器没有负载 在最坏情况下，将需求扫描整个表 分析： 系统设计需求站在全局角度，充分思索运用、中间件、数据库之间的负载分配 充分利用已有的硬件运用优化-大表的全表扫描 DTCC2021 改良： 数据进展分表和分区 DM已实现的分区表并行查询操作符，提供了分区表优化的支持 运用根据分表更改查询模块，从单线程改为多线程 在运用效力器将各分表的查询结果合并 效果： 按最坏情况测试，查询时间由原来的不可预期，提升到2分钟内运用优化-数据清洗与入库 DTCC2021 最初方式： 基于JDBC驱动的数据迁移工具进展清洗和入库操作 批量绑定 迁移工具的资源耗费随着迁移时间的继续添加，导致迁移速度在运转3

21、天后急剧下降 初始数据1T入库时间到达1个月，相当于400条/s运用优化-数据清洗与入库 DTCC2021 问题分析： 超越100亿条记录，即使每5000条提交一次，也有2百万次的解析-方案-代价-执行流程 大量的数据库redo与undo日志操作 处理方案 利用批量 利用并行化充分发扬多CPU处置才干，添加IO的吞吐量 JDBC方式转变为JNI+ODBC 实现动态编译型的ETL脚本引擎DTCC2021图 DMETL 内嵌BCP运用优化-DM ETL的技术改良DTCC2021运用优化-数据划分和并行化运用优化-BCPDTCC2021 将清洗与入库分别 并行化清洗和装载入库 入库运用BCP方式 经过批量绑定减少了网络开销 效力器内部为BCP专门实现了bcp_fast_insert方法 绕过SQL处置流程，直接操作B树叶子节点 不进展Redo与Undo 不进展约束检查 对原有BCP也进展了效力器端的批量化处置最终效果：性能提升100倍，可以在8小时内完成海量数据备