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 | <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)亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
