在sql查询的where条件语句中,可以使用and和or实现逻辑的判断。如果where比较复杂的话,就会产生and 和 or的嵌套使用,写起来会很费力气,看起来就更是一头雾水了。
于是有人就想起了union,其实它是可以替代or的,反正就是把结果串联起来,貌似应该可以。而且,写起来更加容易,看起来也很清晰。但是不知道两个的性能如何呢?下面我就做一个比较,建立三张表,分别插入10万,100万和1000万的数据,每张表格都有8个字段,然后在三种数据量下,做三个字段的or和union,五个字段的or和union,然后通过查询时间比较一下他们的效率吧。
硬件环境:Q8200 4GB 内存
操作系统:Windows2003 R2
数据库:SQL SERVER 2005
代码
<!--<br/ /><br/ />Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ />http://www.CodeHighlighter.com/<br/ /><br/ />-->
create
database
test
go
use test
go
-- 建立测试表1
create table table1
(
col1 varchar ( 20 ),
col2 varchar ( 20 ),
col3 varchar ( 20 ),
col4 varchar ( 20 ),
col5 varchar ( 20 ),
col6 varchar ( 20 ),
col7 varchar ( 20 ),
col8 varchar ( 20 )
)
go
-- 插入10万数据
declare @i int
set @i = 1
while ( @i < 100000 )
begin
insert into table1 values ( ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' )
set @i = @i + 1
end
go
-- 建立测试表2
create table table2
(
col1 varchar ( 20 ),
col2 varchar ( 20 ),
col3 varchar ( 20 ),
col4 varchar ( 20 ),
col5 varchar ( 20 ),
col6 varchar ( 20 ),
col7 varchar ( 20 ),
col8 varchar ( 20 )
)
go
-- 插入100万数据
declare @i int
set @i = 1
while ( @i < 1000000 )
begin
insert into table2 values ( ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' )
set @i = @i + 1
end
go
-- 建立测试表3
create table table3
(
col1 varchar ( 20 ),
col2 varchar ( 20 ),
col3 varchar ( 20 ),
col4 varchar ( 20 ),
col5 varchar ( 20 ),
col6 varchar ( 20 ),
col7 varchar ( 20 ),
col8 varchar ( 20 )
)
go
-- 插入1000万数据
declare @i int
set @i = 1
while ( @i < 1000000 )
begin
insert into table3 values ( ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' )
set @i = @i + 1
end
go
-- 耗时4秒
select * from table1
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 '
go
-- 耗时11秒
select * from table1
where col1 = ' 123 '
union all
select * from table1
where col2 = ' 123 '
union all
select * from table1
where col3 = ' 123 '
go
-- 耗时4秒
select * from table1
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 ' or col4 = ' 123 ' or col5 = ' 123 '
go
-- 耗时19秒
select * from table1
where col1 = ' 123 '
union all
select * from table1
where col2 = ' 123 '
union all
select * from table1
where col3 = ' 123 '
union all
select * from table1
where col4 = ' 123 '
union all
select * from table1
where col5 = ' 123 '
go
-- 耗时37秒
select * from table2
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 '
go
-- 耗时1分53秒
select * from table2
where col1 = ' 123 '
union all
select * from table2
where col2 = ' 123 '
union all
select * from table2
where col3 = ' 123 '
go
-- 耗时38秒
select * from table2
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 ' or col4 = ' 123 ' or col5 = ' 123 '
go
-- 耗时2分24秒
select * from table2
where col1 = ' 123 '
union all
select * from table2
where col2 = ' 123 '
union all
select * from table2
where col3 = ' 123 '
union all
select * from table2
where col4 = ' 123 '
union all
select * from table2
where col5 = ' 123 '
go
drop table table1
drop table table2
drop table table3
drop database test
go
use test
go
-- 建立测试表1
create table table1
(
col1 varchar ( 20 ),
col2 varchar ( 20 ),
col3 varchar ( 20 ),
col4 varchar ( 20 ),
col5 varchar ( 20 ),
col6 varchar ( 20 ),
col7 varchar ( 20 ),
col8 varchar ( 20 )
)
go
-- 插入10万数据
declare @i int
set @i = 1
while ( @i < 100000 )
begin
insert into table1 values ( ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' )
set @i = @i + 1
end
go
-- 建立测试表2
create table table2
(
col1 varchar ( 20 ),
col2 varchar ( 20 ),
col3 varchar ( 20 ),
col4 varchar ( 20 ),
col5 varchar ( 20 ),
col6 varchar ( 20 ),
col7 varchar ( 20 ),
col8 varchar ( 20 )
)
go
-- 插入100万数据
declare @i int
set @i = 1
while ( @i < 1000000 )
begin
insert into table2 values ( ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' )
set @i = @i + 1
end
go
-- 建立测试表3
create table table3
(
col1 varchar ( 20 ),
col2 varchar ( 20 ),
col3 varchar ( 20 ),
col4 varchar ( 20 ),
col5 varchar ( 20 ),
col6 varchar ( 20 ),
col7 varchar ( 20 ),
col8 varchar ( 20 )
)
go
-- 插入1000万数据
declare @i int
set @i = 1
while ( @i < 1000000 )
begin
insert into table3 values ( ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' , ' 123 ' )
set @i = @i + 1
end
go
-- 耗时4秒
select * from table1
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 '
go
-- 耗时11秒
select * from table1
where col1 = ' 123 '
union all
select * from table1
where col2 = ' 123 '
union all
select * from table1
where col3 = ' 123 '
go
-- 耗时4秒
select * from table1
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 ' or col4 = ' 123 ' or col5 = ' 123 '
go
-- 耗时19秒
select * from table1
where col1 = ' 123 '
union all
select * from table1
where col2 = ' 123 '
union all
select * from table1
where col3 = ' 123 '
union all
select * from table1
where col4 = ' 123 '
union all
select * from table1
where col5 = ' 123 '
go
-- 耗时37秒
select * from table2
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 '
go
-- 耗时1分53秒
select * from table2
where col1 = ' 123 '
union all
select * from table2
where col2 = ' 123 '
union all
select * from table2
where col3 = ' 123 '
go
-- 耗时38秒
select * from table2
where col1 = ' 123 ' or col2 = ' 123 ' or col3 = ' 123 ' or col4 = ' 123 ' or col5 = ' 123 '
go
-- 耗时2分24秒
select * from table2
where col1 = ' 123 '
union all
select * from table2
where col2 = ' 123 '
union all
select * from table2
where col3 = ' 123 '
union all
select * from table2
where col4 = ' 123 '
union all
select * from table2
where col5 = ' 123 '
go
drop table table1
drop table table2
drop table table3
drop database test
从上面的可以看出来使用or和union连接where条件的话,数据10w和100w没有差距,只是在1000w的时候急速增大,但是同等数据量的话,使用or和union就表现了很大的差距,尽管union写起来和看起来都比较好理解。
结论:我想是因为每次使用union都会扫描一次表结构,or虽然难些难看,但是只扫描一次表结构,所以数据量上去的话,就会体现出来更大的优势。
结论仅供参考,欢迎大家一起讨论。
刚才看见一篇极限挑战—100万条数据导入SQL SERVER数据库仅用4秒 (附源码),好文章,转载一下地址。感谢作者的无私分享。