网站描述怎么写比较好,免费的网站关键词查询工具,口碑好的网页制作公司,网络安全的重要性什么是Semi-Join半连接
Semi-Join半连接#xff0c;当外表在内表中找到匹配的记录之后#xff0c;Semi-Join会返回外表中的记录。但即使在内表中找到多条匹配的记录#xff0c;外表也只会返回已经存在于外表中的记录。而对于子查询#xff0c;外表的每个符合条件的元组都要…什么是Semi-Join半连接
Semi-Join半连接当外表在内表中找到匹配的记录之后Semi-Join会返回外表中的记录。但即使在内表中找到多条匹配的记录外表也只会返回已经存在于外表中的记录。而对于子查询外表的每个符合条件的元组都要执行一轮子查询效率比较低下。此时使用半连接操作优化子查询会减少查询次数提高查询性能。其主要思路是将子查询上拉到父查询中这样内表和外表是并列关系外表的每个符合条件的元组只需要在内表中找符合条件的元组即可所以效率会大大提高。 当参与等值JOIN的表达式存在有重复值时, 如果不需要找出该表其他字段的值(也就是仅使用JOIN字段/表达式), 那么JOIN时只需要查每个值的第一条, 然后就可以跳到下一个值. 在数据库中常常被用来优化 in, exists, not exists, any(), except 等操作(或者逻辑上成立的其他JOIN场景). 还有什么特别的joinPostgreSQL 与关系代数 (Equi-Join , Semi-Join , Anti-Join , Division) 并不是所有数据库都实现了所有场景的semi join, 例如 Oracle中的半连接MySQL也有半连接 如果未实现, 有什么方法可以模拟semi-join?递归/group by/distinct on/distinct Semi-Join 例子
准备测试数据
postgres# create table a (id int, info text, ts timestamp);
CREATE TABLE
postgres# create table b (like a);
CREATE TABLE
postgres# insert into a select id, md5(random()::text), now() from generate_series(0,1000000) as t(id);
INSERT 0 1000001 -- b表的100万行记录中b.id只有11个唯一值
postgres# insert into b select random()*10, md5(random()::text), now() from generate_series(0,1000000) as t(id);
INSERT 0 1000001 postgres# create index on a (id);
CREATE INDEX
postgres# create index on b (id);
CREATE INDEX未优化SQL
select a.* from a where exists (select 1 from b where a.idb.id); postgres# explain analyze select a.* from a where exists (select 1 from b where a.idb.id); QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost18436.17..18436.66 rows11 width45) (actual time226.590..226.598 rows11 loops1) Merge Cond: (a.id b.id) - Index Scan using a_id_idx on a (cost0.42..27366.04 rows1000001 width45) (actual time0.010..0.013 rows12 loops1) - Sort (cost18435.74..18435.77 rows11 width4) (actual time226.576..226.577 rows11 loops1) Sort Key: b.id Sort Method: quicksort Memory: 25kB - HashAggregate (cost18435.44..18435.55 rows11 width4) (actual time226.568..226.570 rows11 loops1) Group Key: b.id Batches: 1 Memory Usage: 24kB - Index Only Scan using b_id_idx on b (cost0.42..15935.44 rows1000001 width4) (actual time0.010..77.936 rows1000001 loops1) Heap Fetches: 0 Planning Time: 0.189 ms Execution Time: 226.630 ms
(13 rows)以上查询没有使用semi-join, 性能很一般.
由于b表的100万行记录中b.id只有11个唯一值, 可以使用semi-join进行加速.
用法参考: 《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》
使用递归模拟SEMI-JOIN, 只需要 0.171 ms 既可得出b表 11个值的结果.
with recursive tmp as ( select min(id) as id from b union all select (select min(b.id) from b where b.id tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null; id
---- 0 1 2 3 4 5 6 7 8 9 10
(11 rows)执行计划如下
postgres# explain analyze with recursive tmp as ( select min(id) as id from b union all select (select min(b.id) from b where b.id tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------- CTE Scan on tmp (cost50.07..52.09 rows100 width4) (actual time0.028..0.134 rows11 loops1) Filter: (id IS NOT NULL) Rows Removed by Filter: 1 CTE tmp - Recursive Union (cost0.44..50.07 rows101 width4) (actual time0.025..0.126 rows12 loops1) - Result (cost0.44..0.45 rows1 width4) (actual time0.024..0.025 rows1 loops1) InitPlan 3 (returns $1) - Limit (cost0.42..0.44 rows1 width4) (actual time0.021..0.022 rows1 loops1) - Index Only Scan using b_id_idx on b b_1 (cost0.42..18435.44 rows1000001 width4) (actual time0.020..0.020 rows1 loops1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 - WorkTable Scan on tmp tmp_1 (cost0.00..4.76 rows10 width4) (actual time0.007..0.007 rows1 loops12) Filter: (id IS NOT NULL) Rows Removed by Filter: 0 SubPlan 2 - Result (cost0.45..0.46 rows1 width4) (actual time0.007..0.007 rows1 loops11) InitPlan 1 (returns $3) - Limit (cost0.42..0.45 rows1 width4) (actual time0.006..0.006 rows1 loops11) - Index Only Scan using b_id_idx on b (cost0.42..6979.51 rows333334 width4) (actual time0.006..0.006 rows1 loops11) Index Cond: ((id IS NOT NULL) AND (id tmp_1.id)) Heap Fetches: 0 Planning Time: 0.177 ms Execution Time: 0.171 ms
(23 rows)使用递归模拟semi-join, SQL改写如下:
select a.* from a where exists (select 1 from b where a.idb.id); 改写成 select a.* from a where exists (select 1 from
(
with recursive tmp as ( select min(id) as id from b union all select (select min(b.id) from b where b.id tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null
) b where a.idb.id);改写后速度从226.630 ms 提升到 0.246 ms
postgres# explain analyze select a.* from a where exists (select 1 from
(
with recursive tmp as ( select min(id) as id from b union all select (select min(b.id) from b where b.id tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null
) b where a.idb.id); QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost53.76..318.49 rows100 width45) (actual time0.154..0.189 rows11 loops1) - HashAggregate (cost53.34..54.34 rows100 width4) (actual time0.144..0.149 rows11 loops1) Group Key: tmp.id Batches: 1 Memory Usage: 24kB - CTE Scan on tmp (cost50.07..52.09 rows100 width4) (actual time0.027..0.139 rows11 loops1) Filter: (id IS NOT NULL) Rows Removed by Filter: 1 CTE tmp - Recursive Union (cost0.44..50.07 rows101 width4) (actual time0.024..0.130 rows12 loops1) - Result (cost0.44..0.45 rows1 width4) (actual time0.023..0.024 rows1 loops1) InitPlan 3 (returns $1) - Limit (cost0.42..0.44 rows1 width4) (actual time0.020..0.021 rows1 loops1) - Index Only Scan using b_id_idx on b b_1 (cost0.42..18435.44 rows1000001 width4) (actual time0.019..0.019 rows1 loops1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 - WorkTable Scan on tmp tmp_1 (cost0.00..4.76 rows10 width4) (actual time0.008..0.008 rows1 loops12) Filter: (id IS NOT NULL) Rows Removed by Filter: 0 SubPlan 2 - Result (cost0.45..0.46 rows1 width4) (actual time0.007..0.007 rows1 loops11) InitPlan 1 (returns $3) - Limit (cost0.42..0.45 rows1 width4) (actual time0.006..0.006 rows1 loops11) - Index Only Scan using b_id_idx on b (cost0.42..6979.51 rows333334 width4) (actual time0.006..0.006 rows1 loops11) Index Cond: ((id IS NOT NULL) AND (id tmp_1.id)) Heap Fetches: 0 - Index Scan using a_id_idx on a (cost0.42..2.63 rows1 width45) (actual time0.003..0.003 rows1 loops11) Index Cond: (id tmp.id) Planning Time: 0.295 ms Execution Time: 0.246 ms
(29 rows) 文章转载自： http://www.morning.mbpzw.cn.gov.cn.mbpzw.cn http://www.morning.qngcq.cn.gov.cn.qngcq.cn http://www.morning.yqrgq.cn.gov.cn.yqrgq.cn http://www.morning.qxmys.cn.gov.cn.qxmys.cn http://www.morning.kqxwm.cn.gov.cn.kqxwm.cn http://www.morning.ldnrf.cn.gov.cn.ldnrf.cn http://www.morning.jycr.cn.gov.cn.jycr.cn http://www.morning.ggcjf.cn.gov.cn.ggcjf.cn http://www.morning.dtrcl.cn.gov.cn.dtrcl.cn http://www.morning.rrxgx.cn.gov.cn.rrxgx.cn http://www.morning.kqxng.cn.gov.cn.kqxng.cn http://www.morning.rbkgp.cn.gov.cn.rbkgp.cn http://www.morning.nbwyk.cn.gov.cn.nbwyk.cn http://www.morning.qhln.cn.gov.cn.qhln.cn http://www.morning.nbgfk.cn.gov.cn.nbgfk.cn http://www.morning.ljbm.cn.gov.cn.ljbm.cn http://www.morning.zffn.cn.gov.cn.zffn.cn http://www.morning.zycll.cn.gov.cn.zycll.cn http://www.morning.dtnyl.cn.gov.cn.dtnyl.cn http://www.morning.bpmnl.cn.gov.cn.bpmnl.cn http://www.morning.jwrcz.cn.gov.cn.jwrcz.cn http://www.morning.bbyqz.cn.gov.cn.bbyqz.cn http://www.morning.jwfkk.cn.gov.cn.jwfkk.cn http://www.morning.htpjl.cn.gov.cn.htpjl.cn http://www.morning.rhpy.cn.gov.cn.rhpy.cn http://www.morning.rdpps.cn.gov.cn.rdpps.cn http://www.morning.zfgh.cn.gov.cn.zfgh.cn http://www.morning.qpmwb.cn.gov.cn.qpmwb.cn http://www.morning.ntdzjx.com.gov.cn.ntdzjx.com http://www.morning.zfhzx.cn.gov.cn.zfhzx.cn http://www.morning.tbknh.cn.gov.cn.tbknh.cn http://www.morning.bfhfb.cn.gov.cn.bfhfb.cn http://www.morning.cjmmn.cn.gov.cn.cjmmn.cn http://www.morning.zxhpx.cn.gov.cn.zxhpx.cn http://www.morning.pwxkn.cn.gov.cn.pwxkn.cn http://www.morning.elsemon.com.gov.cn.elsemon.com http://www.morning.zfqdt.cn.gov.cn.zfqdt.cn http://www.morning.ltkms.cn.gov.cn.ltkms.cn http://www.morning.dqgbx.cn.gov.cn.dqgbx.cn http://www.morning.skmpj.cn.gov.cn.skmpj.cn http://www.morning.webpapua.com.gov.cn.webpapua.com http://www.morning.lfmwt.cn.gov.cn.lfmwt.cn http://www.morning.nywrm.cn.gov.cn.nywrm.cn http://www.morning.jhxtm.cn.gov.cn.jhxtm.cn http://www.morning.pflpb.cn.gov.cn.pflpb.cn http://www.morning.lbrrn.cn.gov.cn.lbrrn.cn http://www.morning.sqnxk.cn.gov.cn.sqnxk.cn http://www.morning.wdhlc.cn.gov.cn.wdhlc.cn http://www.morning.yqfdl.cn.gov.cn.yqfdl.cn http://www.morning.ctfwl.cn.gov.cn.ctfwl.cn http://www.morning.llllcc.com.gov.cn.llllcc.com http://www.morning.fkdts.cn.gov.cn.fkdts.cn http://www.morning.bntgy.cn.gov.cn.bntgy.cn http://www.morning.zylrk.cn.gov.cn.zylrk.cn http://www.morning.ljjmr.cn.gov.cn.ljjmr.cn http://www.morning.xqjz.cn.gov.cn.xqjz.cn http://www.morning.tlbdy.cn.gov.cn.tlbdy.cn http://www.morning.tqygx.cn.gov.cn.tqygx.cn http://www.morning.ndmbz.cn.gov.cn.ndmbz.cn http://www.morning.tqwcm.cn.gov.cn.tqwcm.cn http://www.morning.mglqf.cn.gov.cn.mglqf.cn http://www.morning.glswq.cn.gov.cn.glswq.cn http://www.morning.njfgl.cn.gov.cn.njfgl.cn http://www.morning.sbncr.cn.gov.cn.sbncr.cn http://www.morning.ai-wang.cn.gov.cn.ai-wang.cn http://www.morning.hqpyt.cn.gov.cn.hqpyt.cn http://www.morning.xjqrn.cn.gov.cn.xjqrn.cn http://www.morning.wjtxt.cn.gov.cn.wjtxt.cn http://www.morning.qsy39.cn.gov.cn.qsy39.cn http://www.morning.rnzwh.cn.gov.cn.rnzwh.cn http://www.morning.rxgnn.cn.gov.cn.rxgnn.cn http://www.morning.pswzc.cn.gov.cn.pswzc.cn http://www.morning.lhytw.cn.gov.cn.lhytw.cn http://www.morning.hjlwt.cn.gov.cn.hjlwt.cn http://www.morning.rttp.cn.gov.cn.rttp.cn http://www.morning.chfxz.cn.gov.cn.chfxz.cn http://www.morning.nrrzw.cn.gov.cn.nrrzw.cn http://www.morning.lpppg.cn.gov.cn.lpppg.cn http://www.morning.rbzht.cn.gov.cn.rbzht.cn http://www.morning.qdcpn.cn.gov.cn.qdcpn.cn
