大连网站开发公司排名,山西网络营销,ppt模板简约 淡雅 大气,徐州市建设局网站首页本文通过标准的TPC-H性能测试#xff0c;展示通过DuckDB分析只读实例将海量数据下的复杂分析查询性能大幅提升。 什么是DuckDB 分析型只读 DuckDB是一款专为嵌入式场景设计的单机OLAP#xff08;联机分析处理#xff09;数据库#xff0c;其核心架构能同时兼顾性能分析与…本文通过标准的TPC-H性能测试展示通过DuckDB分析只读实例将海量数据下的复杂分析查询性能大幅提升。什么是DuckDB 分析型只读DuckDB是一款专为嵌入式场景设计的单机OLAP联机分析处理数据库其核心架构能同时兼顾性能分析与事务处理高性能分析列式存储可以显著加速聚合分析类查询向量化执行引擎可高效处理批量数据。强大的事务支持具备完整的ACID事务能力可通过MVCC多版本并发控制 机制在单机环境下实现高效的并发读写操作。DuckDB的应用场景一体化事务处理与数据分析加速数据分析效能提升复杂分析性能加速慢SQL汇聚分析列存数据压缩数据归档RDS DuckDB分析实例以列式存储与向量化计算为核心实现了复杂分析查询性能百倍跃升。通过列式存储压缩技术显著降低存储成本为企业在海量数据规模场景下提供高性价比的实时分析能力提升企业数据驱动型决策效能。更多信息请参见 DuckDB分析实例。本文将基于业界标准的TPC-H基准测试通过一系列真实的查询场景直观地展示并验证DuckDB分析只读实例的复杂分析查询加速能力。准备工作实例规格本次测试使用了两套配置相同的RDS实例分别代表传统的行存主库和新型的列存分析引擎。RDS MySQL主实例对比组系列高可用独享规格内核版本MySQL 8.020251231规格mysql.x8.4xlarge.2c(32核256 GB)存储类型高性能云盘DuckDB分析只读实例测试组为上述主实例创建一个DuckDB分析只读实例用于处理分析查询。本测试中使用的配置如下系列高可用独享规格内核版本MySQL 8.020260204规格myduckro.x8.4xlarge.1c32核256 GB 存储类型高性能云盘说明创建完成后系统会自动在主实例和DuckDB分析只读实例之间建立数据同步链路。数据集测试采用了业界标准的TPC-H SF100基准数据集数据总量约100 GB。数据已预先导入主实例并自动同步至DuckDB分析只读实例。主要数据表规模如下表名称表中包含的数据行数LINEITEM600,037,902ORDERS150,000,000PARTSUPP80,000,000PART20,000,000CUSTOMER15,000,000SUPPLIER1,000,000NATION25REGION5操作流程单表查询单表扫描及过滤SELECT * FROM lineitem WHERE L_COMMENT aaaaaaaa AND L_COMMENT aaaaaaz;产品执行结果时间RDS MySQLEmpty set (4 min 28.63 sec)268 sRDS MySQL 分析型只读Empty set (0.50 sec)0.5 s单列聚合AGGSELECT SUM(L_DISCOUNT) from lineitem;产品执行结果时间RDS MySQL----------------- | SUM(L_DISCOUNT) | ----------------- | 30001636.44 | ----------------- 1 row in set (1 min 22.36 sec)82 sRDS MySQL 分析型只读----------------- | SUM(L_DISCOUNT) | ----------------- | 30001636.44 | ----------------- 1 row in set (0.08 sec)0.08 s分组聚合GROUP BYSELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE date 1998-12-01 - interval 90 day GROUP BY L_RETURNFLAG, L_LINESTATUS;产品执行结果时间RDS MySQL----------------- | AVG(L_DISCOUNT) | ----------------- | 0.049998 | | 0.050001 | | 0.050002 | | 0.049985 | ----------------- 4 rows in set (4 min 44.40 sec)284 sRDS MySQL 分析型只读---------------------- | AVG(L_DISCOUNT) | ---------------------- | 0.050002243530929025 | | 0.04998528433805397 | | 0.04999791831562552 | | 0.05000130433965413 | ---------------------- 4 rows in set (0.19 sec)0.19 s极限深翻页性能 (ORDER BYLIMIT)SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;产品执行结果时间RDS MySQL----------------------------- | L_ORDERKEY | SUM(L_QUANTITY) | ----------------------------- | 579181024 | 244.00 | | ... | ... | | 579185191 | 244.00 | ----------------------------- 100 rows in set (2 min 46.53 sec)166 sRDS MySQL 分析型只读----------------------------- | L_ORDERKEY | SUM(L_QUANTITY) | ----------------------------- | 482188741 | 244.00 | | ...| ... | | 143970337 | 244.00 | ----------------------------- 100 rows in set (5.57 sec)5.57 s多表查询及子查询多表关联 (JOIN) 性能SELECT COUNT(l3.L_DISCOUNT) FROM ( ( ( ( ( nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY n2.N_NATIONKEY ) STRAIGHT_JOIN supplier on n2.N_NATIONKEY supplier.S_NATIONKEY and S_SUPPKEY 2000 ) STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY supplier.S_SUPPKEY ) STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY l2.L_ORDERKEY and l1.L_LINENUMBER l2.L_LINENUMBER ) STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY l3.L_ORDERKEY and l2.L_LINENUMBER l3.L_LINENUMBER ) GROUP BY n1.N_NAME;产品执行结果时间RDS MySQL---------------------- | COUNT(l3.L_DISCOUNT) | ---------------------- | 56930| | ... | | 49995| ---------------------- 25 rows in set (1 min 21.86 sec)81 sRDS MySQL 分析型只读---------------------- | COUNT(l3.L_DISCOUNT) | ---------------------- | 56930| | ... | | 49995| ---------------------- 25 rows in set (0.67 sec)0.67 s关联子查询性能SELECT O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT FROM orders WHERE O_ORDERDATE 1995-01-01 AND O_ORDERDATE date_add(1995-01-01, interval 3 month) AND EXISTS ( SELECT * FROM lineitem WHERE L_ORDERKEY O_ORDERKEY AND L_COMMITDATE L_RECEIPTDATE ) GROUP BY O_ORDERPRIORITY ORDER BY O_ORDERPRIORITY;产品执行结果时间RDS MySQL------------------------------ | O_ORDERPRIORITY | ORDER_COUNT | ------------------------------ | 1-URGENT | 1028353 | | 2-HIGH | 1030059 | | 3-MEDIUM | 1028615 | | 4-NOT SPECIFIED | 1028496 | | 5-LOW | 1029615 | ------------------------------ 5 rows in set (38.12 sec)38 sRDS MySQL 分析型只读------------------------------ | O_ORDERPRIORITY | ORDER_COUNT | ------------------------------ | 1-URGENT | 1028353 | | 2-HIGH | 1030059 | | 3-MEDIUM | 1028615 | | 4-NOT SPECIFIED | 1028496 | | 5-LOW | 1029615 | ------------------------------ 5 rows in set (0.31 sec)0.31 s多表关联带子查询性能SELECT C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY) FROM ( SELECT * FROM orders WHERE O_ORDERKEY IN ( SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) 300 ) ) AS tmp, customer, lineitem WHERE C_CUSTKEY O_CUSTKEY AND O_ORDERKEY L_ORDERKEY GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE ORDER BY O_TOTALPRICE DESC, O_ORDERDATE;产品执行结果时间RDS MySQL--------------------------------------------------------------------------------------- | C_NAME | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) | --------------------------------------------------------------------------------------- | ... | ... | ... | ... | ... | ... | | Customer#000287677 | 287677 | 353124385 | 1993-06-11 | 353551.42 | 304.00 | | Customer#003777694 | 3777694 | 470363105 | 1997-04-06 | 349914.00 | 302.00 | | Customer#009446411 | 9446411 | 592379937 | 1995-12-29 | 343496.05 | 304.00 | --------------------------------------------------------------------------------------- 6398 rows in set (2 min 29.34 sec)149 sRDS MySQL 分析型只读--------------------------------------------------------------------------------------- | C_NAME | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) | --------------------------------------------------------------------------------------- | ... | ... | ... | ... | ... | ... | | Customer#000287677 | 287677 | 353124385 | 1993-06-11 | 353551.42 | 304.00 | | Customer#003777694 | 3777694 | 470363105 | 1997-04-06 | 349914.00 | 302.00 | | Customer#009446411 | 9446411 | 592379937 | 1995-12-29 | 343496.05 | 304.00 | --------------------------------------------------------------------------------------- 6398 rows in set (1.20 sec)1.20 s测试结论查询类型RDS MySQL主实例 (耗时)RDS DuckDB分析只读实例 (耗时)性能提升倍数单表扫描及过滤268s0.5s约536倍单列聚合AGG82s0.08s约1025倍分组聚合GROUP BY284s0.19s约1495倍极限深翻页166s5.57s约30倍多表关联 (JOIN)81s0.67s约121倍关联子查询38s0.31s约123倍多表关联带子查询149s1.20s约124倍测试结果表明RDS MySQL DuckDB分析只读实例在处理各类复杂分析查询时均展现出明显的性能优势。通过将分析负载转移至DuckDB分析只读实例可将原本分钟级的慢查询缩短至秒级甚至毫秒级实现近实时的交互式数据分析同时保障主实例的稳定性。更多信息免费试用企业用户与个人用户均可免费试用试用DuckDB分析实例。更多细节请参见 DuckDB 分析实例。「RDS DuckDB 实训营」火热进行中3月3日正式开营参营即享多重好礼学习内容【课程1】云数据库 RDS 产品能力介绍【课程2】RDS DuckDB 分析实例重点特性解析【免费试用】个人/企业用户均可领取 3个月 DuckDB 分析主实例的免费试用【实验】免费体验RDS DuckDB分析实例加速TP复杂SQL查询 点此立即报名参营更多性能测试基于标准TPC-H 的全面测试结果请参见 DuckDB分析只读实例概述。ClickHouse 官方维护的性能基准测试Benchmark仪表板直观地展示并对比各种主流分析型数据库OLAP在处理大规模数据集时的查询速度和性能表现。更多细节请参见 ClickBench。