tpch数据集的 sql优化案例

数据集：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;

优化：

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

优化后

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',
'1993-04-03', '1993-04-04', '1993-04-05', '1993-04-06', '1993-04-07', '1993-04-
08',
'1993-04-09', '1993-04-10', '1993-04-11', '1993-04-12', '1993-04-13', '1993-04-
14',
'1993-04-15', '1993-04-16', '1993-04-17', '1993-04-18', '1993-04-19', '1993-04-
20',
'1993-04-21', '1993-04-22', '1993-04-23', '1993-04-24', '1993-04-25', '1993-04-
26','1993-04-27', '1993-04-28', '1993-04-29', '1993-04-30', '1993-05-01', '1993-05-
02',
'1993-05-03', '1993-05-04', '1993-05-05', '1993-05-06', '1993-05-07', '1993-05-
08',
'1993-05-09', '1993-05-10', '1993-05-11', '1993-05-12', '1993-05-13', '1993-05-
14',
'1993-05-15', '1993-05-16', '1993-05-17', '1993-05-18', '1993-05-19', '1993-05-
20',
'1993-05-21', '1993-05-22', '1993-05-23', '1993-05-24', '1993-05-25', '1993-05-
26',
'1993-05-27', '1993-05-28', '1993-05-29', '1993-05-30', '1993-05-31', '1993-06-
01',
'1993-06-02', '1993-06-03', '1993-06-04', '1993-06-05', '1993-06-06', '1993-06-
07',
'1993-06-08', '1993-06-09', '1993-06-10', '1993-06-11', '1993-06-12', '1993-06-13',
'1993-06-14', '1993-06-15'
)
and o_orderkey IN (
	SELECT l_orderkey
	FROM lineitem
	WHERE l_commitdate < l_receiptdate
	AND L_LINENUMBER = 1
	AND L_ORDERKEY < 400000
)
GROUP BY o_orderpriority
LIMIT 5;

-- 原sql
select o_orderdate, o_clerk, o_shippriority,count(1) sum
from customer,orders
where
c_mktsegment = 'AUTOMOBILE'
and c_custkey = o_custkey
and o_orderdate - interval '10' day < date '1995-03-06'
and C_ACCTBAL > 711.56
group by o_orderdate, o_clerk, C_NATIONKEY
having C_NATIONKEY < 10
order by o_orderdate, o_clerk, o_shippriority
limit 10;

优化：

1. 范围查询使用等值查询，计算使用值替换，避免类型问题不命中索引
2. 建立联合索引o_orderdate_cleak_ship_index优化group by 和order by

优化后

SELECT
o_orderdate,
o_clerk,
o_shippriority,
COUNT(*) AS sum
FROM
orders force index (o_orderdate_cleak_ship_index),
customer
WHERE
o_custkey = c_custkey
AND c_mktsegment = 'AUTOMOBILE'
AND o_orderdate < DATE '1995-03-16'
AND C_ACCTBAL > 711.56
AND C_NATIONKEY < 10
GROUP BY
o_orderdate, o_clerk, o_shippriority
ORDER BY
o_orderdate, o_clerk, o_shippriority
LIMIT 10;

select ord.o_custkey, ord.o_orderpriority, count(1) sum
from orders ord
where (o_custkey, ord.O_ORDERDATE) in (
	select
	o_custkey,
	min(o_orderdate)
	from
	orders
	where O_ORDERKEY < 4000000
	group by
	o_custkey,
	o_orderdate
)
and o_orderdate like '1993%'
group by ord.o_custkey, ord.O_ORDERPRIORITY
order by ord.o_custkey limit 5;

1. like查询加索引优化，注：like 前缀是可命中索引的
2. 强制索引优化group by

SELECT
ord.o_custkey,
ord.o_orderpriority,
COUNT(ord.o_custkey) AS SUM
FROM
orders ord FORCE INDEX (idx_o_custkey_O_ORDERPRIORITY_o_orderdate)
WHERE
ord.O_ORDERKEY < 4000000
AND ord.o_orderdate >= '1993-01-01'
AND ord.o_orderdate <= '1993-12-31'
GROUP BY
ord.o_custkey,
ord.O_ORDERPRIORITY LIMIT 5;

-- 原sql
SELECT
orders.o_totalprice,
orders.o_orderpriority,
orders.o_orderdate
FROM
orders
LEFT JOIN customer ON orders.O_CUSTKEY = customer.C_CUSTKEY
WHERE
substring(orders.O_ORDERDATE, 1, 7) = '1994-04'
order by
o_totalprice desc,
o_orderdate,
o_orderdate
LIMIT
300000, 10;

1. 使用索引覆盖优化子查询，优化limit
2. 强制使用索引

-- 优化后
SELECT
o1.o_totalprice,
o1.o_orderpriority,
o1.o_orderdate
FROM
orders o1,
(
	SELECT
	o2.o_orderkey
	FROM
	orders o2 FORCE INDEX (IND_O_DATEANDPRICEANDPRIORITY)
	WHERE
	o2.o_orderdate BETWEEN DATE '1994-4-1'
	AND DATE '1994-4-30'
	ORDER BY
	o2.o_totalprice DESC
	LIMIT
	300000, 10
) o3
WHERE
o1.o_orderkey = o3.o_orderkey;