SQL联合查询优化 用union all来代替union

Optimizing UNION
UNION has an interesting optimization that exists across a few different databases. It's obvious when you think about how it works. UNION gives you the rows from two tables that don't exist in the other. So implicitly, you are removing duplicates. To do this the MySQL database must return distinct rows, and thus must sort the data. Sorting, as we know is expensive, especially for large tables.

UNION ALL can very well be a big speedup for you. What if you already know that your data does not contain duplicates in either row, or what if you don't care about duplicates? In either case, UNION ALL is for you. Further, there may be other ways you can avoid the duplicates in your rows using some application logic, so you know that UNION ALL will provide the results you want, without the heavy overhead of sorting the data.

union和union all的差别就在于union会对数据做一个distanct的动作,而这个distanct动作的速度则取决于现有数据的数量,数量越大则时间也越慢。而对于几个数据集,要确保数据集之间的数据互相不重复,基本是O(n)的算法复杂度。

有了理论依据后,便动手更改SQL的结构,在确保数据逻辑上不会有重复情况出现后,将2个union都改成了union all,query的反应速度从1.7秒变成了300毫秒左右,耗费时间只有以前的17%。

UNION还有一个用处,我们在海量数据的查询中,如果使用select * from c_cons where cons_id in ('691339365','3387785','3387954');这样的查询语句,会引起全表扫描,可以使用UNION ALL来代替,如:

select * from c_cons where cons_id='691339365'
UNION ALL
select * from c_cons where cons_id='3387785'
UNION ALL
select * from c_cons where cons_id='3387954'
这样查询比使用in查询要快很多,它不会去进行全表扫描。


例2:

or语句(部分节选)

SELECT * FROM tablename where (cdp= 300 and inline=301) or (cdp= 301 and inline=301) or (cdp= 302 and inline=301) or (cdp= 303 and inline=301) or (cdp= 304 and inline=301) or (cdp= 305 and inline=301) or (cdp= 306 and inline=301) or (cdp= 307 and inline=301)

union all语句(部分节选)

SELECT * FROM tablename where (inline= 300 and cdp=300) union all SELECT * FROM tablename where (inline= 301 and cdp=300) union all SELECT * FROM tablename where (inline= 302 and cdp=300) union all SELECT * FROM tablename where (inline= 303 and cdp=300)

返回不规则的900条数据,前者用了60多秒,后者用了8秒左右。

------------------------------
用DB2测试,发现还是用IN的效率高于union all

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
根据引用中提到的优化方法,对于使用or的查询,在某些情况下可以使用union或者union all来代替以获得更好的效果。使用union或者union all的方式可以避免索引失效的问题,提高查询速度。 使用union或者union all的方式进行查询时,可以将原来的or条件拆分成多个子查询,并使用union或者union all将它们合并在一起。例如,将原来的查询语句SELECT id FROM A WHERE num =10 or num = 20可以改写为: SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num = 20 这样的查询可以更有效地利用索引,提高查询速度。根据引用和引用的描述,使用union all的方式可能会比使用union更快,但是不会去重。所以根据实际的查询需求,可以选择使用union或者union all。 总结起来,为了提高or查询的效率,可以尽量使用union或者union all来代替,将or条件拆分成多个子查询并合并起来。这样可以避免索引失效,并且根据需求选择使用union或者union all。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* [Mysql多表联查使用union all 代替or查询](https://blog.csdn.net/CSxiaoyuhan/article/details/108583308)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *3* [Sql优化总结](https://blog.csdn.net/qq_39134704/article/details/124594529)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值