mysql中or太多影响效率_优化mysql中where or和where in语
句的效率
⼀、使⽤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.405403537121977sql中union多表合并
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
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论