mysql in union_Case：MySQL使用in带子查询的时候，子查询最好不要使用union或union all...

MySQL使用in带子查询的时候，子查询不要使用union或union all

特别是当外部表比较大的时候，千万不要使用in和union搭配，因为子查询中一旦使用union，执行计划会出现dependent subquery这种情况，

在生产上我们有使用类似的情况，导致SQL执行效率很差，下面举例说明，为了生产安全隐私，以下举例用测试表演示，原理相通。

举例

(1) 使用in和union搭配的时候，s表作为外部表，全表扫描，有260w行，执行20多秒。mysql> select s.* from salaries s where s.emp_no in (select emp_no from employees e where e.first_name='Georgi' union all select emp_no from employees e where e.hire_date='1992-12-18');

2718 rows in set (21.14 sec)

mysql> desc select s.* from salaries s where s.emp_no in (select emp_no from employees e where e.first_name='Georgi' union all select emp_no from employees e where e.hire_date='1992-12-18');

+----+--------------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |

+----+--------------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

|  1 | PRIMARY            | s     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 2612229 |   100.00 | Using where |

|  2 | DEPENDENT SUBQUERY | e     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |       1 |    10.00 | Using where |

|  3 | DEPENDENT UNION    | e     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |       1 |    10.00 | Using where |

+----+--------------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

3 rows in set, 1 warning (0.00 sec)

(2)可以使用join来转化，再来看执行计划e表变成外表，s表使用PK检索，执行只要了0.32秒，效率大大提高。mysql> select s.* from salaries s join (select emp_no from employees e where e.first_name='Georgi' union all select emp_no from employees e where e.hire_date='1992-12-18')e on s.emp_no=e.emp_no;

2718 rows in set (0.32 sec)

mysql> desc select s.* from salaries s join (select emp_no from employees e where e.first_name='Georgi' union all select emp_no from employees e where e.hire_date='1992-12-18')e on s.emp_no=e.emp_no;

+----+-------------+------------+------------+------+----------------+---------+---------+----------+--------+----------+-------------+

| id | select_type | table      | partitions | type | possible_keys  | key     | key_len | ref      | rows   | filtered | Extra       |

+----+-------------+------------+------------+------+----------------+---------+---------+----------+--------+----------+-------------+

|  1 | PRIMARY     |  | NULL       | ALL  | NULL           | NULL    | NULL    | NULL     |  59866 |   100.00 | NULL        |

|  1 | PRIMARY     | s          | NULL       | ref  | PRIMARY,emp_no | PRIMARY | 4       | e.emp_no |      9 |   100.00 | NULL        |

|  2 | DERIVED     | e          | NULL       | ALL  | NULL           | NULL    | NULL    | NULL     | 299335 |    10.00 | Using where |

|  3 | UNION       | e          | NULL       | ALL  | NULL           | NULL    | NULL    | NULL     | 299335 |    10.00 | Using where |

+----+-------------+------------+------------+------+----------------+---------+---------+----------+--------+----------+-------------+

4 rows in set, 1 warning (0.00 sec)