Case：MySQL使用in帶子查詢的時候，子查詢最好不要使用union或union all

MySQL使用in帶子查詢的時候，子查詢不要使用union或union all

特別是當(dāng)外部表比較大的時候，千萬不要使用in和union搭配，因為子查詢中一旦使用union，執(zhí)行計劃會出現(xiàn)dependent subquery這種情況，

在生產(chǎn)上我們有使用類似的情況，導(dǎo)致SQL執(zhí)行效率很差，下面舉例說明，為了生產(chǎn)安全隱私，以下舉例用測試表演示，原理相通。

舉例

(1) 使用in和union搭配的時候，s表作為外部表，全表掃描，有260w行，執(zhí)行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來轉(zhuǎn)化，再來看執(zhí)行計劃e表變成外表，s表使用PK檢索，執(zhí)行只要了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     | <derived2> | 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)