tpch数据集的 sql优化案例

最新推荐文章于 2023-08-11 14:24:54 发布

bigShong

最新推荐文章于 2023-08-11 14:24:54 发布

阅读量461

点赞数 1

分类专栏： mysql 数据库文章标签： mysql

本文链接：https://blog.csdn.net/Houson_c/article/details/115606721

版权

数据集：tpch 2.18 scale factor20，数据量大约1.2亿
在这里插入图片描述
1.


select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-03-16'
and o_orderdate < date '1993-03-16' + interval '3' month
and exists (
	select
	*
	from
	lineitem
	where
	l_orderkey = o_orderkey
	and l_commitdate < l_receiptdate
	and L_ORDERKEY < 400000
	and L_LINENUMBER < 2
)
group by
o_orderpriority order by o_orderpriority limit 5;

优化：

强制使用索引，不适用引擎优化建议
范围查询变成等值查询，即将<>换成in
建立索引create index o_orderdate_index on orders(o_orderdate)
使用索引覆盖优化子查询

优化后


SELECT o_orderpriority, COUNT(1) AS order_count
FROM orders force index (o_orderdate_index)
WHERE o_orderdate IN (
'1993-03-16', '1993-03-17', '1993-03-18', '1993-03-19', '1993-03-20', '1993-03-
21',
'1993-03-22', '1993-03-23', '1993-03-24', '1993-03-25', '1993-03-26', '1993-03-
27',
'1993-03-28', '1993-03-29', '1993-03-30', '1993-03-31', '1993-04-01', '1993-04-
02'