mysql union是并行执行吗

今天有人问in一堆条件的sql如何优化。这个很自然就想到用union来代替in来提高效率，网上很多例子也是这么说的

 http://hi.baidu.com/dereky/blog/item/382c2df536c0532cbc310929.html 

 

可是我在本机做实验为什么有相反地结果呢...

EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel'
EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel'

这条语句执行结果481条，执行时间为0.35s

1 PRIMARY employees ALL 300141 Using where

2 UNION employees ALL 300141 Using where

UNION RESULT <union1,2> ALL

explain SELECT * FROM employees WHERE employees.first_name IN ('Georgi','Bezalel')
explain SELECT * FROM employees WHERE employees.first_name IN ('Georgi','Bezalel')

1 SIMPLE employees ALL 300141 Using where

explain SELECT * FROM employees WHERE employees.first_name ='Georgi' or employees.first_name='Bezalel'
explain SELECT * FROM employees WHERE employees.first_name ='Georgi' or employees.first_name='Bezalel'

难道是网上的说法有误？难道和索引有关？在firstname上建立了一个索引

重新执行

union的执行执行计划如下，执行时间为0.004s

1 PRIMARY employees ref index_firstname index_firstname 44 const 253 Using where

2 UNION employees ref index_firstname index_firstname 44 const 228 Using where

UNION RESULT <union1,2> ALL

in的执行计划如下，执行时间也为0.004s

1 SIMPLE employees range index_firstname index_firstname 44 481 Using where

or的执行计划如下，执行时间也为0.004s

1 SIMPLE employees range index_firstname index_firstname 44 481 Using where

感觉性能差不多啊。但是注意执行计划中的type，ref要好于range哦(ref为非唯一性索引扫描，range为索引范围扫描）

突然感觉好像和网上说的差不多了，但是第一个语句走了两个ref扫描 会不会效率比走一次range的扫描低啊。

要不我再试试主键，这个是唯一的，会不会和网上的效果一直呢？

EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 UNION ALL SELECT * FROM employees WHERE employees.EMP_NO=101100
EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 UNION ALL SELECT * FROM employees WHERE employees.EMP_NO=101100

union的执行计划如下

1 PRIMARY employees const PRIMARY PRIMARY 4 const 1

2 UNION employees const PRIMARY PRIMARY 4 const 1

UNION RESULT <union1,2> ALL

EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO IN (100001 ,101100)
EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO IN (100001 ,101100)

in的执行计划如下

1 SIMPLE employees range PRIMARY PRIMARY 4 2 Using where

EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 OR emp_no=101100
EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 OR emp_no=101100

or的执行计划如下

1 SIMPLE employees range PRIMARY PRIMARY 4 2 Using where

感觉结果和第二个实验还是差不多。但是const的效率要比range高些。

写到这里楼主真的崩溃了，难道是和我的数据库引擎有关？我用的是大众的InnoDB啊。。。。

/**

* 20120607 in和exists

*/

这里我补充一下exist的用法，有人说exists可以优化in的查询。exists的用法和in不一样，要和子表进行关联，而且关联时要用到索引，所以加快了查询速度。exists作为where条件时，先对where条件前的主查询进行查询然后用主查询的结果一个一个代入exists的查询进行判断，如果为真则输出。

网上有种说法，exists的效率并不一定要比in的高，子查询表大的用exists，子查询表小的用in。对于not in和not exists，not in 内外表都要进行全表扫描，而not exists的子查询仍然可以用索引。所以not exists的查询要比not in的高。

总结一下，经过一晚上的实验，并没有得到网上实验的结果，unionall的效率要高于in和or。在字段没有索引的情况下，union ALL的效率绝对没有in和all的高，如果有两个匹配值，union All 要扫2次全表。如果字段是有索引的，那么unionall貌似要比in和or看起来高效一些，但是在我的实验环境下共30W的数据查询时间没有多大区别

/**

*20120607 大量数据测试

*/

感谢 fzxu_05的建议。我这次用100个数据来测试，效果更明显的证明了in的效率要比union高

上语句：

select * from employees where first_name ='Georgi' union
select * from employees where first_name ='Bezalel' union
select * from employees where first_name ='Parto' union
select * from employees where first_name ='Chirstian' union
select * from employees where first_name ='Kyoichi' union
select * from employees where first_name ='Anneke' union
select * from employees where first_name ='Tzvetan' union
select * from employees where first_name ='Saniya' union
select * from employees where first_name ='Sumant' union
select * from employees where first_name ='Duangkaew' union
select * from employees where first_name ='Mary' union
select * from employees where first_name ='Patricio' union
select * from employees where first_name ='Eberhardt' union
select * from employees where first_name ='Berni' union
select * from employees where first_name ='Guoxiang' union
select * from employees where first_name ='Kazuhito' union
select * from employees where first_name ='Cristinel' union
select * from employees where first_name ='Kazuhide' union
select * from employees where first_name ='Lillian' union
select * from employees where first_name ='Mayuko' union
select * from employees where first_name ='Ramzi' union
select * from employees where first_name ='Shahaf' union
select * from employees where first_name ='Bojan' union
select * from employees where first_name ='Suzette' union
select * from employees where first_name ='Prasadram' union
select * from employees where first_name ='Yongqiao' union
select * from employees where first_name ='Divier' union
select * from employees where first_name ='Domenick' union
select * from employees where first_name ='Otmar' union
select * from employees where first_name ='Elvis' union
select * from employees where first_name ='Karsten' union
select * from employees where first_name ='Jeong' union
select * from employees where first_name ='Arif' union
select * from employees where first_name ='Bader' union
select * from employees where first_name ='Alain' union
select * from employees where first_name ='Adamantios' union
select * from employees where first_name ='Pradeep' union
select * from employees where first_name ='Huan' union
select * from employees where first_name ='Alejandro' union
select * from employees where first_name ='Weiyi' union
select * from employees where first_name ='Uri' union
select * from employees where first_name ='Magy' union
select * from employees where first_name ='Yishay' union
select * from employees where first_name ='Mingsen' union
select * from employees where first_name ='Moss' union
select * from employees where first_name ='Lucien' union
select * from employees where first_name ='Zvonko' union
select * from employees where first_name ='Florian' union
select * from employees where first_name ='Basil' union
select * from employees where first_name ='Yinghua' union
select * from employees where first_name ='Hidefumi' union
select * from employees where first_name ='Heping' union
select * from employees where first_name ='Sanjiv' union
select * from employees where first_name ='Mayumi' union
select * from employees where first_name ='Georgy' union
select * from employees where first_name ='Brendon' union
select * from employees where first_name ='Ebbe' union
select * from employees where first_name ='Berhard' union
select * from employees where first_name ='Breannda' union
select * from employees where first_name ='Tse' union
select * from employees where first_name ='Anoosh' union
select * from employees where first_name ='Gino' union
select * from employees where first_name ='Udi' union
select * from employees where first_name ='Satosi' union
select * from employees where first_name ='Kwee' union
select * from employees where first_name ='Claudi' union
select * from employees where first_name ='Charlene' union
select * from employees where first_name ='Margareta' union
select * from employees where first_name ='Reuven' union
select * from employees where first_name ='Hisao' union
select * from employees where first_name ='Hironoby' union
select * from employees where first_name ='Shir' union
select * from employees where first_name ='Mokhtar' union
select * from employees where first_name ='Gao' union
select * from employees where first_name ='Erez' union
select * from employees where first_name ='Mona' union
select * from employees where first_name ='Danel' union
select * from employees where first_name ='Kshitij' union
select * from employees where first_name ='Premal' union
select * from employees where first_name ='Zhongwei' union
select * from employees where first_name ='Parviz' union
select * from employees where first_name ='Vishv' union
select * from employees where first_name ='Tuval' union
select * from employees where first_name ='Kenroku' union
select * from employees where first_name ='Somnath' union
select * from employees where first_name ='Xinglin' union
select * from employees where first_name ='Jungsoon' union
select * from employees where first_name ='Sudharsan' union
select * from employees where first_name ='Kendra' union
select * from employees where first_name ='Amabile' union
select * from employees where first_name ='Valdiodio' union
select * from employees where first_name ='Sailaja' union
select * from employees where first_name ='Arumugam' union
select * from employees where first_name ='Hilari' union
select * from employees where first_name ='Jayson' union
select * from employees where first_name ='Remzi' union
select * from employees where first_name ='Sreekrishna' union
select * from employees where first_name ='Valter' union
select * from employees where first_name ='Hironobu' union
select * from employees where first_name ='Perla'
select * from employees where first_name ='Georgi' union
select * from employees where first_name ='Bezalel' union
select * from employees where first_name ='Parto' union
select * from employees where first_name ='Chirstian' union
select * from employees where first_name ='Kyoichi' union
select * from employees where first_name ='Anneke' union
select * from employees where first_name ='Tzvetan' union
select * from employees where first_name ='Saniya' union
select * from employees where first_name ='Sumant' union
select * from employees where first_name ='Duangkaew' union
select * from employees where first_name ='Mary' union
select * from employees where first_name ='Patricio' union
select * from employees where first_name ='Eberhardt' union
select * from employees where first_name ='Berni' union
select * from employees where first_name ='Guoxiang' union
select * from employees where first_name ='Kazuhito' union
select * from employees where first_name ='Cristinel' union
select * from employees where first_name ='Kazuhide' union
select * from employees where first_name ='Lillian' union
select * from employees where first_name ='Mayuko' union
select * from employees where first_name ='Ramzi' union
select * from employees where first_name ='Shahaf' union
select * from employees where first_name ='Bojan' union
select * from employees where first_name ='Suzette' union
select * from employees where first_name ='Prasadram' union
select * from employees where first_name ='Yongqiao' union
select * from employees where first_name ='Divier' union
select * from employees where first_name ='Domenick' union
select * from employees where first_name ='Otmar' union
select * from employees where first_name ='Elvis' union
select * from employees where first_name ='Karsten' union
select * from employees where first_name ='Jeong' union
select * from employees where first_name ='Arif' union
select * from employees where first_name ='Bader' union
select * from employees where first_name ='Alain' union
select * from employees where first_name ='Adamantios' union
select * from employees where first_name ='Pradeep' union
select * from employees where first_name ='Huan' union
select * from employees where first_name ='Alejandro' union
select * from employees where first_name ='Weiyi' union
select * from employees where first_name ='Uri' union
select * from employees where first_name ='Magy' union
select * from employees where first_name ='Yishay' union
select * from employees where first_name ='Mingsen' union
select * from employees where first_name ='Moss' union
select * from employees where first_name ='Lucien' union
select * from employees where first_name ='Zvonko' union
select * from employees where first_name ='Florian' union
select * from employees where first_name ='Basil' union
select * from employees where first_name ='Yinghua' union
select * from employees where first_name ='Hidefumi' union
select * from employees where first_name ='Heping' union
select * from employees where first_name ='Sanjiv' union
select * from employees where first_name ='Mayumi' union
select * from employees where first_name ='Georgy' union
select * from employees where first_name ='Brendon' union
select * from employees where first_name ='Ebbe' union
select * from employees where first_name ='Berhard' union
select * from employees where first_name ='Breannda' union
select * from employees where first_name ='Tse' union
select * from employees where first_name ='Anoosh' union
select * from employees where first_name ='Gino' union
select * from employees where first_name ='Udi' union
select * from employees where first_name ='Satosi' union
select * from employees where first_name ='Kwee' union
select * from employees where first_name ='Claudi' union
select * from employees where first_name ='Charlene' union
select * from employees where first_name ='Margareta' union
select * from employees where first_name ='Reuven' union
select * from employees where first_name ='Hisao' union
select * from employees where first_name ='Hironoby' union
select * from employees where first_name ='Shir' union
select * from employees where first_name ='Mokhtar' union
select * from employees where first_name ='Gao' union
select * from employees where first_name ='Erez' union
select * from employees where first_name ='Mona' union
select * from employees where first_name ='Danel' union
select * from employees where first_name ='Kshitij' union
select * from employees where first_name ='Premal' union
select * from employees where first_name ='Zhongwei' union
select * from employees where first_name ='Parviz' union
select * from employees where first_name ='Vishv' union
select * from employees where first_name ='Tuval' union
select * from employees where first_name ='Kenroku' union
select * from employees where first_name ='Somnath' union
select * from employees where first_name ='Xinglin' union
select * from employees where first_name ='Jungsoon' union
select * from employees where first_name ='Sudharsan' union
select * from employees where first_name ='Kendra' union
select * from employees where first_name ='Amabile' union
select * from employees where first_name ='Valdiodio' union
select * from employees where first_name ='Sailaja' union
select * from employees where first_name ='Arumugam' union
select * from employees where first_name ='Hilari' union
select * from employees where first_name ='Jayson' union
select * from employees where first_name ='Remzi' union
select * from employees where first_name ='Sreekrishna' union
select * from employees where first_name ='Valter' union
select * from employees where first_name ='Hironobu' union
select * from employees where first_name ='Perla'

下面是in的语句

select * from employees where first_name in ('Georgi', 'Bezalel', 'Parto', 'Chirstian', 'Kyoichi', 'Anneke', 'Tzvetan', 'Saniya', 'Sumant', 
'Duangkaew', 'Mary', 'Patricio', 'Eberhardt', 'Berni', 'Guoxiang', 'Kazuhito', 'Cristinel', 'Kazuhide', 'Lillian', 'Mayuko', 'Ramzi', 'Shahaf',
 'Bojan', 'Suzette', 'Prasadram', 'Yongqiao', 'Divier', 'Domenick', 'Otmar', 'Elvis', 'Karsten', 'Jeong', 'Arif', 'Bader', 'Alain', 'Adamantios',
 'Pradeep', 'Huan', 'Alejandro', 'Weiyi', 'Uri', 'Magy', 'Yishay', 'Mingsen', 'Moss', 'Lucien', 'Zvonko', 'Florian', 'Basil', 'Yinghua', 'Hidefumi', 
'Heping', 'Sanjiv', 'Mayumi', 'Georgy', 'Brendon', 'Ebbe', 'Berhard', 'Breannda', 'Tse', 'Anoosh', 'Gino', 'Udi', 'Satosi', 'Kwee', 'Claudi', 
'Charlene', 'Margareta', 'Reuven', 'Hisao', 'Hironoby', 'Shir', 'Mokhtar', 'Gao', 'Erez', 'Mona', 'Danel', 'Kshitij', 'Premal', 'Zhongwei',
 'Parviz', 'Vishv', 'Tuval', 'Kenroku', 'Somnath', 'Xinglin', 'Jungsoon', 'Sudharsan', 'Kendra', 'Amabile', 'Valdiodio', 'Sailaja', 'Arumugam',
 'Hilari', 'Jayson', 'Remzi', 'Sreekrishna', 'Valter', 'Hironobu', 'Perla')
select * from employees where first_name in ('Georgi', 'Bezalel', 'Parto', 'Chirstian', 'Kyoichi', 'Anneke', 'Tzvetan', 'Saniya', 'Sumant', 
'Duangkaew', 'Mary', 'Patricio', 'Eberhardt', 'Berni', 'Guoxiang', 'Kazuhito', 'Cristinel', 'Kazuhide', 'Lillian', 'Mayuko', 'Ramzi', 'Shahaf',
 'Bojan', 'Suzette', 'Prasadram', 'Yongqiao', 'Divier', 'Domenick', 'Otmar', 'Elvis', 'Karsten', 'Jeong', 'Arif', 'Bader', 'Alain', 'Adamantios',
 'Pradeep', 'Huan', 'Alejandro', 'Weiyi', 'Uri', 'Magy', 'Yishay', 'Mingsen', 'Moss', 'Lucien', 'Zvonko', 'Florian', 'Basil', 'Yinghua', 'Hidefumi', 
'Heping', 'Sanjiv', 'Mayumi', 'Georgy', 'Brendon', 'Ebbe', 'Berhard', 'Breannda', 'Tse', 'Anoosh', 'Gino', 'Udi', 'Satosi', 'Kwee', 'Claudi', 
'Charlene', 'Margareta', 'Reuven', 'Hisao', 'Hironoby', 'Shir', 'Mokhtar', 'Gao', 'Erez', 'Mona', 'Danel', 'Kshitij', 'Premal', 'Zhongwei',
 'Parviz', 'Vishv', 'Tuval', 'Kenroku', 'Somnath', 'Xinglin', 'Jungsoon', 'Sudharsan', 'Kendra', 'Amabile', 'Valdiodio', 'Sailaja', 'Arumugam',
 'Hilari', 'Jayson', 'Remzi', 'Sreekrishna', 'Valter', 'Hironobu', 'Perla')

/**

* 2012.06.26 

*/

mysql优化器对in子查询优化时存在一个问题，mysql优化器对于in语句的优化的lazy的，对于in子句，如果不是现实的列表定义，如in('a','b','c')，那么in子句都会别转换成exists的相关子查询（非独立子查询，相关子查询要比独立子查询性能低）

对于相关子查询，有时可以通过派生表来进行重写，以避免子查询和外部子查询的多次比较操作。