重新描述下问题:
-----------------------查询一----------------------
select .... from A
left join B
on A.id=B.id-----------------------查询二----------------------
select .... from A
left join (
(select .... from B)B1 union all (select ... from C)C1 on B1.id=C1.id
)B2
on A.id=B2.id数据量:A=4百万条,B=5百万条,C=10条
这两个句子执行效率差别非常大:
查询一:15秒左右
查询二:3分种左右请高手指点 有没有好的优化方法?
-----------------------查询一----------------------
select .... from A
left join B
on A.id=B.id-----------------------查询二----------------------
select .... from A
left join (
(select .... from B)B1 union all (select ... from C)C1 on B1.id=C1.id
)B2
on A.id=B2.id数据量:A=4百万条,B=5百万条,C=10条
这两个句子执行效率差别非常大:
查询一:15秒左右
查询二:3分种左右请高手指点 有没有好的优化方法?
-----------------------查询一----------------------
select .... from A
left join B
on A.id=B.id-----------------------查询二----------------------
select .... from A
left join (
(select .... from B) union all (select ... from C)
)B2
on A.id=B2.id数据量:A=4百万条,B=5百万条,C=10条
这两个句子执行效率差别非常大:
查询一:15秒左右
查询二:3分种左右请高手指点 有没有好的优化方法?
left join b on
...
left join c on
...
干嘛union all
P.S 回复的时候引用一下 否则收不到消息
left join B
on a.id=b.id
union
select ... from a
left join c
on a.id=c.id
你可以先建一个分区表 customers_t 语句如下:
CREATE TABLE customers_t(
id INT PRIMARY KEY ,
name VARCHAR (20),
dept VARCHAR (10)
)
PARTITION BY LIST (dept)
(
PARTITION dept1 VALUES ('dept2') TABLESPACE xxx,
PARTITION dept2 VALUES ('dept2') TABLESPACE xxx
);
然后执行 insert into customers_t select * from customers;
rename customers to customers_old;
rename customers_t to customers;
查询的时候这样写语句
select * from customers PARTITION (dept1);
上面所有语句用到的dept1 dept2 是dept字段的具体值,注意分区名后面的dept1不用加单引号,分区名规定必须字母开头且不能包含-