presto和doris查询对比

fct2001140269

已于 2022-12-07 11:28:34 修改

阅读量1.2k

点赞数

分类专栏： Doris 数据仓库 OLAP 文章标签：数据库 sql mysql

于 2022-12-07 11:26:38 首次发布

本文链接：https://blog.csdn.net/fct2001140269/article/details/128217397

版权

数据仓库同时被 3 个专栏收录

9 篇文章 1 订阅

订阅专栏

OLAP

4 篇文章 0 订阅

订阅专栏

Doris

1 篇文章 0 订阅

订阅专栏

本文对比了presto和doris在即席查询场景下的性能对比

1. count(*) 查询数据总条数

例子： select count(*) from ods_tb_device_point_data;

presto查询结果

presto:vasyslink_yag001> select count(*) from ods_tb_device_point_data;
   _col0    
------------
 2615538668 
(1 row)

Query 20220908_023044_00006_mb379, FINISHED, 3 nodes
Splits: 9,060 total, 9,060 done (100.00%)
12:47 [2.62B rows, 467GB] [3.41M rows/s, 623MB/s]

doris查询结果

MySQL [vasyslink_yag001]> select count(*) from ods_tb_device_point_data;
+------------+
| count(*)   |
+------------+
| 2615538668 |
+------------+
1 row in set (1 min 33.84 sec)

2. 根据时间做范围查询

select count(distinct device_id) from  ods_tb_device_point_data where acq_time_by_day > '20220612';

使用presto

presto:vasyslink_yag001> select count(distinct device_id) from ods_tb_device_point_data where acq_time_by_day > '2022-06-12';
 _col0 
-------
  5220 
(1 row)

Query 20220908_075740_00015_mb379, FINISHED, 3 nodes
Splits: 726 total, 726 done (100.00%)
0:26 [153M rows, 28GB] [5.91M rows/s, 1.08GB/s]

doris查询

MySQL [vasyslink_yag001]> select count(distinct device_id) from  ods_tb_device_point_data where acq_time_by_day > '20220612';
+-----------------------------+
| count(DISTINCT `device_id`) |
+-----------------------------+
|                        5220 |
+-----------------------------+
1 row in set (13.22 sec)

3.做join查询

MySQL [vasyslink_yag001]> select count(*) from  ods_tb_device_point_data aa  join ods_tb_device_point_data bb on aa.device_id = bb.device_id where aa.acq_time_by_day >= '20220907' and bb.acq_time_by_day >= '20220907';
+------------+
| count(*)   |
+------------+
| 7592514660 |
+------------+
1 row in set (1 min 10.66 sec)

presto查询


presto:vasyslink_yag001> select count(*) from  ods_tb_device_point_data aa  join ods_tb_device_point_data bb on aa.device_id = bb.device_id where aa.acq_time_by_day >= '2022-09-07' and bb.acq_time_by_day >= '2022-09-07';
   _col0    
------------
 7592514660 
(1 row)

Query 20220908_092647_00022_mb379, FINISHED, 3 nodes
Splits: 233 total, 233 done (100.00%)
0:13 [11M rows, 2.14GB] [881K rows/s, 175MB/s]

presto和doris的对比

	presto	doris	备注
count(*)	12:47min	1 min 33.84 sec	全量26.12亿条记录
count(DISTINCT `device_id`)	26s	13s	2022-06-12以后到现在的1.5亿条记录
join查询	13s	1 min 10.66 sec	最近两天的点位数据与自身join(9月7号，8号)