一、使用union来取代where
in:
使用where or语句操作:
select * from city where or or >输出:
1 广州
3 深圳
4 惠州
explain 结果:
id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE city ALL PRIMARY NULL
NULL NULL
5 Using where
标准使用where in操作:
select * from city where id in (1,3,4)
输出:
1 广州
3 深圳
4 惠州
explain 结果:
id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE city ALL PRIMARY NULL
NULL NULL
5 Using where
使用union all操作:SELECT * FROM city where
union all SELECT * FROM city where union all SELECT * FROM city
where >输出:
1 广州
3 深圳
4 惠州
explain 结果:id select_type
table type possible_keys
key key_len ref rows Extra
1 PRIMARY city const
PRIMARY PRIMARY
4 const 1 2 UNION city const
PRIMARY PRIMARY
4 const 1 3 UNION city const
PRIMARY PRIMARY
4 const 1 NULL UNION RESULT
ALL NULL NULL NULL NULL
NULL
使用union all并且支持order by (因为union不支持order
by,使用以下方式则支持):
select * from (SELECT * FROM city where order by id asc) as t1
UNION ALL select * from (SELECT * FROM city where order by id desc)
as t2 UNION ALL select * from city where >1 广州
3 深圳
4 惠州
使用union
all并且对最后的结果集进行排序:(本SQL使用了filesort,性能有降低)
select * from (select * from (SELECT * FROM city where order by id
asc) as t1 UNION ALL select * from (SELECT * FROM city where order
by id desc) as t2 UNION ALL select * from city where as s1 order by
id desc
输出:
4 惠州
3 深圳
1 广州
二、Union 和 Union all 的差异:
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回
结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。
union all
只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
从效率上说,UNION ALL
要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用UNION ALL
查询对比:
select rand(1) union select rand(2) union select rand(3);
输出:
0.405403537121977
0.655586646549019
0.90576975597606
select rand(1) union select rand(1) union select rand(1);
输出:0.405403537121977
select rand(1) union all select rand(1) union all select
rand(1);
输出:
0.405403537121977
0.405403537121977
0.405403537121977