摘要:
对TPCH的Q20语句进行测试, 并与MySql8/innodb做对比
Q20语句
select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
ps_partkey in (
select
p_partkey
from
part
where
p_name like 'forest%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem,partsupp
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1994-01-01'
and l_shipdate < date '1994-01-01' + interval '1' year
)
)
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by
s_name;
STONEDB测试:
执行结果耗时:
mysql> select
-> s_name,
-> s_address
-> from
-> supplier,
-> nation
-> where
-> s_suppkey in (
-> select
-> ps_suppkey
-> from
-> partsupp
-> where
-> ps_partkey in (
-> select
-> p_partkey
-> from
-> part
-> where
-> p_name like 'forest%'
-> )
-> and ps_availqty > (
-> select
-> 0.5 * sum(l_quantity)
-> from
-> lineitem,partsupp
-> where
-> l_partkey = ps_partkey
-> and l_suppkey = ps_suppkey
-> and l_shipdate >= date '1994-01-01'
-> and l_shipdate < date '1994-01-01' + interval '1' year
-> )
-> )
-> and s_nationkey = n_nationkey
-> and n_name = 'CANADA'
-> order by
-> s_name;
Empty set (7.11 sec)
火焰图:
MySql8/Innodb测试:
执行结果耗时:
mysql> select
-> s_name,
-> s_address
-> from
-> supplier,
-> nation
-> where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
-> s_suppkey in (
-> select
-> ps_suppkey
-> from
-> partsupp
-> where
-> ps_partkey in (
-> select
-> p_partkey
-> from
-> part
-> where
-> p_name like 'forest%'
-> )
-> and ps_availqty > (
-> select
-> 0.5 * sum(l_quantity)
-> from
-> lineitem,partsupp
-> where
-> l_partkey = ps_partkey
-> and l_suppkey = ps_suppkey
-> and l_shipdate >= date '1994-01-01'
-> and l_shipdate < date '1994-01-01' + interval '1' year
-> )
-> )
-> and s_nationkey = n_nationkey
-> and n_name = 'CANADA'
-> order by
-> s_name;
Empty set (5 min 25.19 sec)
结论:
- stonedb耗时7秒
- MySql8耗时 5分钟25秒
- 查出的结果都为空
