PolarDB MySQL 的 Cloud-Native HTAP實時數(shù)據(jù)分析技術(shù)

云原生新引擎技術(shù)演講人：葵暢阿里云數(shù)據(jù)庫高級技術(shù)專家云原生新引擎技術(shù)演講人：葵暢阿里云數(shù)據(jù)庫高級技術(shù)專家PPolarDB關(guān)于我?2013年加入阿里巴巴集團?PolarDB列存引擎，X-Engine引擎內(nèi)核開發(fā)?PolarDBMySQL云原生數(shù)據(jù)庫?PolarDB(IMCI)列存引擎?PolarDB(X-Engine)高壓縮引擎PolarDB最佳實踐Application安全保護點只讀實例1只讀實例2只讀實例3數(shù)據(jù)庫計算節(jié)點儲確保主從數(shù)據(jù)庫毫秒級延遲、并提供超高I/O吞吐能力，通寫分離，高并發(fā)下性能是自建開源數(shù)據(jù)庫的5-6倍；Application安全保護點只讀實例1只讀實例2只讀實例3數(shù)據(jù)庫計算節(jié)點儲確保主從數(shù)據(jù)庫毫秒級延遲、并提供超高I/O吞吐能力，通寫分離，高并發(fā)下性能是自建開源數(shù)據(jù)庫的5-6倍；倍提升大表并行查詢性能向量化技術(shù)，復(fù)雜查詢提供百倍的加速比高壓縮節(jié)點，提供3到10倍的壓縮比PolarStore數(shù)據(jù)存儲節(jié)點數(shù)據(jù)存儲節(jié)點數(shù)據(jù)存儲節(jié)點aaacccPolarDBMySQL云原生數(shù)據(jù)庫?最大存儲容量100T，最高超1000核CPU?可擴展到16個計算節(jié)點(一寫多讀)或者32個計算節(jié)點(多主)?支持Serverless秒級伸縮?支持臨時升級，彈多久，付多久的錢，高性價比應(yīng)對業(yè)務(wù)高峰智能代理PolarProxy數(shù)據(jù)3副本，多可用區(qū)部署?主節(jié)點故障后10秒內(nèi)切換到任一只讀節(jié)點；級備份，可按時間點快速恢復(fù)數(shù)據(jù)；容災(zāi)，全方位、多維度確保RPO=0?PolarDBMySQL云原生數(shù)據(jù)庫?PolarDB(IMCI)列存引擎?PolarDB(X-Engine)高壓縮引擎PolarDB最佳實踐PolarDBMySQL的客戶訴求:HTAP同步鏈路PolarDB(MySQL)ClickHouse/DorisDB?只訪問必要數(shù)據(jù)?單指令多數(shù)據(jù)?只訪問必要數(shù)據(jù)?單指令多數(shù)據(jù)PolarDBHTAP：WhyPolarDBNeedColumnStore記錄按列拆分存儲壓縮存儲SIMDSIMD?壓縮數(shù)據(jù)及減少IO量?壓縮數(shù)據(jù)計算IO加速IO加速OLAPOLTPOLAPOLTPPolarDBIMCI(In-Memory-Column-Index)：一體化HTAP數(shù)據(jù)庫DB PolarDBIMCI：一體化HTAP數(shù)據(jù)庫Lredolog步數(shù)據(jù)，數(shù)據(jù)同步延MySQL提升百倍性能，與專用數(shù)倉性能相當(dāng)ColumnBasedPlanPlanColumnBasedPlanPlan?串?/PQ/列存混合調(diào)度執(zhí)?算??向量化執(zhí)?加速SIMD?事務(wù)級別的?列?致性?列存物理復(fù)制及?寫多讀onEngineJoinwBasedPolarDBIMCI:核心架構(gòu)SQLParserSQLParsertionEngineColumnIndexAccesstionEngineColumnIndexAccessAPIJoinhedulertionhedulerhedulerhedulerHashJoinessionFrameworkSIMDSIMDSIMDIMCIForTableIMCIForTableNCIOptimizeforParallelIMCIPlanOptimizeforParallelRow-BasePlanrserSerialRow-BaseExecutionIMCIExecutionOptimizeforParallelIMCIPlanOptimizeforParallelRow-BasePlanrserSerialRow-BaseExecutionIMCIExecutionBasePlanelIMCIPlanSerialRow-BasePlanIMCIQueryEngine:HybridExecution??存并??列存并?基于代價的執(zhí)?計劃選擇?整體代價較低時選擇?存?遍歷執(zhí)?計劃樹以探測IMCI兼容性Plan存執(zhí)?OptimizeforSerialRow-BasePlanseExecution不seExecutionIMCIQueryupBytupBytupBytOINOINIMCIQueryupBytupBytupBytOINOINOINupBytOINTableScanSELECTcustomers.cust_first_name,customers.cust_last_name,MAX(QUANTITY_SOLD),AVG(QUANTITY_SOLD)FROMsales,customersWHEREsales.cust_id=customers.cust_idGROUPBYcustomers.cust_first_name,customers.cust_last_name;hProbeHashTable0codematerializevalueayloadABCTab2Mod1PartitionExpressionHashTable1codematerializevalueayloadMod1odABCoddhProbeHashTable0codematerializevalueayloadABCTab2Mod1PartitionExpressionHashTable1codematerializevalueayloadMod1odABCodddHashTable2codematerializevalueayloadherByddod…dHashTable7codematerializevalueayloadodHashBuilddd?HashProbePipeline-memoryrecycle?HashProbePipeline-memoryrecycleandreducepeakmemoryNohashtablemerge?HashBuildSkew–Scheduler&ConcurrentHashTableExpression- C IMCIQueryEngine:VectorizedExpression- C Expression:(a+b)>?表達式遞歸消除提升CPU計算效率 Vectorized& abThroughput(billion/s)IMCIQueryEngine:SIMDbasedExpressionThroughput(billion/s)ScalarfunctionThroughput550504.543.53.54.543.53.525210.50.250.220.220ADDADD8bit16bit32bit64bit使用SIMD(AVX512)指令可以獲得近10倍的加速效果IMCIStorage:實現(xiàn)為索引的列存PolarDBInnoDBTable列存展現(xiàn)方式C1C2C3C4C5PKPK11172522lDDL對存量數(shù)據(jù)新增列索引lParallelScan主表l不阻塞OLTP的DML操作31213516223245262732Part.1Part.2Part.3Part.11172522lDDL對存量數(shù)據(jù)新增列索引lParallelScan主表l不阻塞OLTP的DML操作31213516223245262732Part.1Part.2Part.3Part.4Part.5Part.6293021819891056758DeleteRow(lock/delete)RowlockupdateUnlockRecordsTransactionCommitTransactionl基于物理日志復(fù)制方式同步到列存節(jié)點l更新操作以事務(wù)粒度應(yīng)用到列存l與行存事務(wù)共享相同的并發(fā)控制機制l與DeleteRow(lock/delete)RowlockupdateUnlockRecordsTransactionCommitTransactionl基于物理日志復(fù)制方式同步到列存節(jié)點l更新操作以事務(wù)粒度應(yīng)用到列存l與行存事務(wù)共享相同的并發(fā)控制機制l與行存共用相同提交事務(wù)編號RowlockupdateInsertRow(lock/insert)UnlockRecordsTransactionitTrxBufferertrowdeleterowertrowTrxBufferertrowInsertInsertRow(lock/insert)DeleteDeleteRow(lock/delete)CommiCommitTransactionTrxBufferertrowdeleterowertrowdeleterowColumnEncodeCompressColumnEncodeCompressColumnEncodeCompressIMCIStorage:列存數(shù)據(jù)組織管理ColumnEncodeCompressColumnEncodeCompressColumnEncodeCompressl更新操作以事務(wù)粒度應(yīng)用到列存l64K行為一個RowGrouplRowGroup內(nèi)拆分按列存儲l列DataPack會進行壓縮C1C2C3C4C5基于MIN/MAX過濾基于MIN/MAX過濾列存數(shù)據(jù)塊維護MIN/MAX/SUM等統(tǒng)計信息基于統(tǒng)計信息實現(xiàn)大塊數(shù)據(jù)的Pruning單個pack按數(shù)據(jù)min/max等分若干分區(qū)使用bitmap記錄對應(yīng)分區(qū)是否有數(shù)據(jù)命中單個pack中借助多個hash函數(shù)構(gòu)建bitmap優(yōu)化等值/in等精確命中查詢join操作時，通過運行時結(jié)果集信息傳遞，減少無效IO和計算Insert/DeleteBitmapDPN3DPN1DPN3DPN1DPNDPN2……查詢延時(秒)0ClickHouse4.449.263.591.81725.9480.24751.17112.64759.165.2443.7181.72452.6951.4580.8741.2336.1348.4556.3721.87901.449PolarDBIMCIPerformance:TPCH100GPKClickHouse查詢延時(秒)0ClickHouse4.449.263.591.81725.9480.24751.17112.64759.165.2443.7181.72452.6951.4580.8741.2336.1348.4556.3721.87901.449TPC-H100GBMySQL行存VSColumnIndexVSClickHouse140000080060040000QQ1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11Q12Q13Q14Q15Q16Q17Q18Q19Q20Q21Q22MySQL行索引1290.611.8244.9259.61231.18217.42410.5957.52415.11139.7330.67245.19718.266.66123.7937.5454.34240.2818.6246.91253.2717.08PolarDBColumnIndex4.20.582.541.691.990.423.473.7813.244.433.131.218.340.822.83