先说结论：
0、本次测试，未调优二者的参数，开箱起服务，直接测试的，部署架构一致。
1、在单表查询下，StarRocks 在部分场景下优于Doris，但是二次查询，二者不分伯仲。
2、在多表查询下，仅在一个场景下Doris速度逊于StarRocks ，大部分场景是Doris优于StarRocks的。
3、在cpu和内存的使用上，doris会比starrocks多吃1.5倍的资源。
压测试验：
1、基础环境
|
组件 |
cpu |
内存 |
Cpu架构 |
磁盘存储 |
|
Doris |
8C |
16G |
x86_64 |
492G |
|
8C |
16G |
x86_64 |
492G |
|
|
8C |
16G |
x86_64 |
492G |
|
|
8C |
16G |
x86_64 |
492G |
|
|
8C |
16G |
x86_64 |
492G |
|
|
StarRocks |
8C |
16G |
x86_64 |
492G |
|
8C |
16G |
x86_64 |
492G |
|
|
8C |
16G |
x86_64 |
492G |
|
|
8C |
16G |
x86_64 |
492G |
|
|
8C |
16G |
x86_64 |
492G |
2、版本
Doris-2.0.5 （2024 年 2 月 27 日 发 布）
StarRocks-3.2.3（2024 年 2 月 8 日 发 布）
StarRocks version 3.2 | StarRocks
3、压测工具
（1）下载doris的ssb-tools
使用doris-master\tools\ssb-tools下的压测脚本
（2）编译脚本
./build-ssb-dbgen.sh
（3）生成测试数据
./gen-ssb-data.sh -s 40
（4）修改配置信息
./conf/doris-cluster.conf
（5）创建表并导入数据
创建表：./create-ssb-tables.sh
导入数据：./load-ssb-data.sh
|
表名 |
行数 |
解释 |
|
lineorder |
2400万 |
SSB商品订单表 |
|
customer |
120万 |
SSB客户表 |
|
part |
120万 |
SSB 零部件表 |
|
supplier |
8万 |
SSB 供应商表 |
|
dates |
2556 |
日期表 |
|
lineorder_flat |
2400万 |
SSB打平后的宽表 |
4、查询测试
通过单表、多表的sql查询
（1）doris-2.0.5
doris单表：
|
序号 |
首次 |
多次 |
|
Q1.1 |
0.59 |
0.03 |
|
Q1.2 |
0.19 |
0.02 |
|
Q1.3 |
0.08 |
0.03 |
|
Q2.1 |
8.15 |
0.08 |
|
Q2.2 |
0.08 |
0.08 |
|
Q2.3 |
0.06 |
0.06 |
|
Q3.1 |
2.45 |
0.14 |
|
Q3.2 |
2.55 |
0.08 |
|
Q3.3 |
0.09 |
0.06 |
|
Q3.4 |
0.02 |
0.02 |
|
Q4.1 |
0.89 |
0.13 |
|
Q4.2 |
0.08 |
0.05 |
|
Q4.3 |
0.05 |
0.04 |
doris多表
|
序号 |
首次 |
多次 |
|
Q1.1 |
0.96 |
0.04 |
|
Q1.2 |
0.85 |
0.03 |
|
Q1.3 |
0.03 |
0.02 |
|
Q2.1 |
3.8 |
0.16 |
|
Q2.2 |
0.14 |
0.13 |
|
Q2.3 |
0.13 |
0.12 |
|
Q3.1 |
0.64 |
0.34 |
|
Q3.2 |
0.14 |
0.13 |
|
Q3.3 |
0.14 |
0.14 |
|
Q3.4 |
0.05 |
0.05 |
|
Q4.1 |
0.43 |
0.33 |
|
Q4.2 |
0.16 |
0.15 |
|
Q4.3 |
0.14 |
0.12 |
（2）starrocks-3.2.3
starrocks单表
|
序号 |
首次 |
多次 |
|
Q1.1 |
0.13 |
0.03 |
|
Q1.2 |
0.05 |
0.02 |
|
Q1.3 |
0.06 |
0.02 |
|
Q2.1 |
1.2 |
0.13 |
|
Q2.2 |
0.11 |
0.11 |
|
Q2.3 |
0.06 |
0.06 |
|
Q3.1 |
0.53 |
0.13 |
|
Q3.2 |
0.32 |
0.09 |
|
Q3.3 |
0.1 |
0.09 |
|
Q3.4 |
0.03 |
0.02 |
|
Q4.1 |
0.4 |
0.15 |
|
Q4.2 |
0.1 |
0.06 |
|
Q4.3 |
0.07 |
0.04 |
starrocks多表
|
首次 |
多次 |
|
|
Q1.1 |
0.34 |
0.04 |
|
Q1.2 |
0.32 |
0.03 |
|
Q1.3 |
0.27 |
0.03 |
|
Q2.1 |
2.46 |
0.26 |
|
Q2.2 |
0.24 |
0.21 |
|
Q2.3 |
0.43 |
0.19 |
|
Q3.1 |
0.49 |
0.32 |
|
Q3.2 |
0.26 |
0.22 |
|
Q3.3 |
0.17 |
0.16 |
|
Q3.4 |
0.07 |
0.05 |
|
Q4.1 |
0.52 |
0.44 |
|
Q4.2 |
0.2 |
0.18 |
|
Q4.3 |
0.15 |
0.13 |
5、对比：
（1）单表对比
|
序号 |
doris首次 |
doris多次 |
star首次 |
star多次 |
|
Q1.1 |
0.59 |
0.03 |
0.1 |
0.04 |
|
Q1.2 |
0.19 |
0.02 |
0.04 |
0.04 |
|
Q1.3 |
0.08 |
0.03 |
0.08 |
0.03 |
|
Q2.1 |
8.15 |
0.08 |
0.42 |
0.12 |
|
Q2.2 |
0.08 |
0.08 |
0.13 |
0.12 |
|
Q2.3 |
0.06 |
0.06 |
0.07 |
0.06 |
|
Q3.1 |
2.45 |
0.14 |
0.31 |
0.16 |
|
Q3.2 |
2.55 |
0.08 |
0.18 |
0.09 |
|
Q3.3 |
0.09 |
0.06 |
0.11 |
0.09 |
|
Q3.4 |
0.02 |
0.02 |
0.03 |
0.03 |
|
Q4.1 |
0.89 |
0.13 |
0.33 |
0.2 |
|
Q4.2 |
0.08 |
0.05 |
0.1 |
0.08 |
|
Q4.3 |
0.05 |
0.04 |
0.07 |
0.04 |
（2）多表对比
|
序号 |
doris首次 |
doris多次 |
star首次 |
star多次 |
|
Q1.1 |
0.96 |
0.04 |
0.1 |
0.05 |
|
Q1.2 |
0.85 |
0.03 |
0.08 |
0.04 |
|
Q1.3 |
0.03 |
0.02 |
0.04 |
0.04 |
|
Q2.1 |
3.8 |
0.16 |
0.41 |
0.26 |
|
Q2.2 |
0.14 |
0.13 |
0.22 |
0.2 |
|
Q2.3 |
0.13 |
0.12 |
0.21 |
0.19 |
|
Q3.1 |
0.64 |
0.34 |
0.38 |
0.32 |
|
Q3.2 |
0.14 |
0.13 |
0.26 |
0.22 |
|
Q3.3 |
0.14 |
0.14 |
0.38 |
0.16 |
|
Q3.4 |
0.05 |
0.05 |
0.1 |
0.06 |
|
Q4.1 |
0.43 |
0.33 |
0.49 |
0.42 |
|
Q4.2 |
0.16 |
0.15 |
0.22 |
0.18 |
|
Q4.3 |
0.14 |
0.12 |
0.15 |
0.13 |
6、cpu、内存使用情况
（1）starrocks
（2）doris
7、测试使用到的sql
- --Q1.1
- SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
- FROM lineorder_flat
- WHERE LO_ORDERDATE >= 19930101 and LO_ORDERDATE <= 19931231 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
- --Q1.2
- SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat
- WHERE LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19940131 AND LO_DISCOUNT
- BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;
- --Q1.3
- SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
- FROM lineorder_flat
- WHERE weekofyear(LO_ORDERDATE) = 6 AND LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19941231
- AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
- --Q2.1
- SELECT
- sum(LO_REVENUE),
- (LO_ORDERDATE DIV 10000) AS year,
- P_BRAND
- FROM lineorder_flat
- WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
- GROUP BY
- year,
- P_BRAND
- ORDER BY
- year,
- P_BRAND;
- --Q2.2
- SELECT
- sum(LO_REVENUE),
- (LO_ORDERDATE DIV 10000) AS year,
- P_BRAND
- FROM lineorder_flat
- WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
- GROUP BY
- year,
- P_BRAND
- ORDER BY
- year,
- P_BRAND;
- --Q2.3
- SELECT
- sum(LO_REVENUE),
- (LO_ORDERDATE DIV 10000) AS year,
- P_BRAND
- FROM lineorder_flat
- WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
- GROUP BY
- year,
- P_BRAND
- ORDER BY
- year,
- P_BRAND;
- --Q3.1
- SELECT
- C_NATION,
- S_NATION,
- (LO_ORDERDATE DIV 10000) AS year,
- sum(LO_REVENUE) AS revenue
- FROM lineorder_flat
- WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
- GROUP BY
- C_NATION,
- S_NATION,
- year
- ORDER BY
- year ASC,
- revenue DESC;
- --Q3.2
- SELECT
- C_CITY,
- S_CITY,
- (LO_ORDERDATE DIV 10000) AS year,
- sum(LO_REVENUE) AS revenue
- FROM lineorder_flat
- WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
- GROUP BY
- C_CITY,
- S_CITY,
- year
- ORDER BY
- year ASC,
- revenue DESC;
- --Q3.3
- SELECT
- C_CITY,
- S_CITY,
- (LO_ORDERDATE DIV 10000) AS year,
- sum(LO_REVENUE) AS revenue
- FROM lineorder_flat
- WHERE C_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND S_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
- GROUP BY
- C_CITY,
- S_CITY,
- year
- ORDER BY
- year ASC,
- revenue DESC;
- --Q3.4
- SELECT
- C_CITY,
- S_CITY,
- (LO_ORDERDATE DIV 10000) AS year,
- sum(LO_REVENUE) AS revenue
- FROM lineorder_flat
- WHERE C_CITY in ('UNITED KI1', 'UNITED KI5') AND S_CITY in ( 'UNITED KI1', 'UNITED KI5') AND LO_ORDERDATE >= 19971201 AND LO_ORDERDATE <= 19971231
- GROUP BY
- C_CITY,
- S_CITY,
- year
- ORDER BY
- year ASC,
- revenue DESC;
- --Q4.1
- SELECT
- (LO_ORDERDATE DIV 10000) AS year,
- C_NATION,
- sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
- FROM lineorder_flat
- WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR in ( 'MFGR#1' , 'MFGR#2')
- GROUP BY
- year,
- C_NATION
- ORDER BY
- year ASC,
- C_NATION ASC;
- --Q4.2
- SELECT
- (LO_ORDERDATE DIV 10000) AS year,
- S_NATION,
- P_CATEGORY,
- sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
- FROM lineorder_flat
- WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND P_MFGR in ( 'MFGR#1' , 'MFGR#2')
- GROUP BY
- year,
- S_NATION,
- P_CATEGORY
- ORDER BY
- year ASC,
- S_NATION ASC,
- P_CATEGORY ASC;
- --Q4.3
- SELECT
- (LO_ORDERDATE DIV 10000) AS year,
- S_CITY,
- P_BRAND,
- sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
- FROM lineorder_flat
- WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14'
- GROUP BY
- year,
- S_CITY,
- P_BRAND
- ORDER BY
- year ASC,
- S_CITY ASC,
- P_BRAND ASC;
- 多表查询
- --Q1.1
- select sum(lo_revenue) as revenue
- from lineorder join dates on lo_orderdate = d_datekey
- where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
- --Q1.2
- select sum(lo_revenue) as revenue
- from lineorder
- join dates on lo_orderdate = d_datekey
- where d_yearmonthnum = 199401
- and lo_discount between 4 and 6
- and lo_quantity between 26 and 35;
- --Q1.3
- select sum(lo_revenue) as revenue
- from lineorder
- join dates on lo_orderdate = d_datekey
- where d_weeknuminyear = 6 and d_year = 1994
- and lo_discount between 5 and 7
- and lo_quantity between 26 and 35;
- --Q2.1
- select sum(lo_revenue) as lo_revenue, d_year, p_brand
- from lineorder
- join dates on lo_orderdate = d_datekey
- join part on lo_partkey = p_partkey
- join supplier on lo_suppkey = s_suppkey
- where p_category = 'MFGR#12' and s_region = 'AMERICA'
- group by d_year, p_brand
- order by d_year, p_brand;
- --Q2.2
- select sum(lo_revenue) as lo_revenue, d_year, p_brand
- from lineorder
- join dates on lo_orderdate = d_datekey
- join part on lo_partkey = p_partkey
- join supplier on lo_suppkey = s_suppkey
- where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'
- group by d_year, p_brand
- order by d_year, p_brand;
- --Q2.3
- select sum(lo_revenue) as lo_revenue, d_year, p_brand
- from lineorder
- join dates on lo_orderdate = d_datekey
- join part on lo_partkey = p_partkey
- join supplier on lo_suppkey = s_suppkey
- where p_brand = 'MFGR#2239' and s_region = 'EUROPE'
- group by d_year, p_brand
- order by d_year, p_brand;
- --Q3.1
- select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- where c_region = 'ASIA' and s_region = 'ASIA'and d_year >= 1992 and d_year <= 1997
- group by c_nation, s_nation, d_year
- order by d_year asc, lo_revenue desc;
- --Q3.2
- select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES'
- and d_year >= 1992 and d_year <= 1997
- group by c_city, s_city, d_year
- order by d_year asc, lo_revenue desc;
- --Q3.3
- select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- where (c_city='UNITED KI1' or c_city='UNITED KI5')
- and (s_city='UNITED KI1' or s_city='UNITED KI5')
- and d_year >= 1992 and d_year <= 1997
- group by c_city, s_city, d_year
- order by d_year asc, lo_revenue desc;
- --Q3.4
- select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- where (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997'
- group by c_city, s_city, d_year
- order by d_year asc, lo_revenue desc;
- --Q4.1
- select d_year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- join part on lo_partkey = p_partkey
- where c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
- group by d_year, c_nation
- order by d_year, c_nation;
- --Q4.2
- select d_year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- join part on lo_partkey = p_partkey
- where c_region = 'AMERICA'and s_region = 'AMERICA'
- and (d_year = 1997 or d_year = 1998)
- and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
- group by d_year, s_nation, p_category
- order by d_year, s_nation, p_category;
- --Q4.3
- select d_year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit
- from lineorder
- join dates on lo_orderdate = d_datekey
- join customer on lo_custkey = c_custkey
- join supplier on lo_suppkey = s_suppkey
- join part on lo_partkey = p_partkey
- where c_region = 'AMERICA'and s_nation = 'UNITED STATES'
- and (d_year = 1997 or d_year = 1998)
- and p_category = 'MFGR#14'
- group by d_year, s_city, p_brand
- order by d_year, s_city, p_brand;
标签：StarRocks,join,revenue,压测,lo,year,LO,ORDERDATE,Doris From： https://www.cnblogs.com/huanghanyu/p/18186894