mysql where 优化_优化mysql中where or和where in语句的效率

一、使用union来取代where in：

使用where or语句操作：

select * from city where id = 1 or id = 3 or id = 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

标准使用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 id = 1 union all SELECT * FROM city where id = 3 union all SELECT * FROM city

where id = 4

输出：

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 id = 1 order by id asc) as t1 UNION ALL select * from (SELECT * FROM city where id = 3 order by id desc) as t2 UNION ALL select * from city where id = 4

1 广州

3 深圳

4 惠州

使用union all并且对最后的结果集进行排序：(本SQL使用了filesort，性能有降低)

select * from (select * from (SELECT * FROM city where id = 1 order by id asc) as t1 UNION ALL select * from (SELECT * FROM city where id = 3 order by id desc) as t2 UNION ALL select * from city where id = 4) 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