Mysql union all+括号引起的性能问题

SQL如下(MYSQL版本为8.0.17):

select lastname from (select id,loginid,workcode,lastname from HrmResource union all select id,loginid,'' as workcode,lastname from HrmResourceManager)  a   where (id in (83109))；

mysql> explain select lastname from (select id,loginid,workcode,lastname from HrmResource union all select id,loginid,'' as workcode,lastname from HrmResourceManager)  a   where (id in (83109));
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
|  1 | PRIMARY     | <derived2>         | NULL       | ref  | <auto_key0>   | <auto_key0> | 5       | const |     10 |   100.00 | NULL  |
|  2 | DERIVED     | HrmResource        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 236351 |   100.00 | NULL  |
|  3 | UNION       | HrmResourceManager | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |      1 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

两表各自走了全表扫描,未使用ID列索引.

将过滤条件前置

select lastname from (select id,loginid,workcode,lastname from HrmResource where id=83109 union all select id,loginid,'' as workcode,lastname from HrmResourceManager where id=83109)  a

mysql> explain select lastname from (select id,loginid,workcode,lastname from HrmResource where id=83109 union all select id,loginid,'' as workcode,lastname from HrmResourceManager where id=83109)  a;
+----+-------------+--------------------+------------+-------+------------------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table              | partitions | type  | possible_keys          | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------------+------------+-------+------------------------+-----------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2>         | NULL       | ALL   | NULL                   | NULL            | NULL    | NULL  |    3 |   100.00 | NULL  |
|  2 | DERIVED     | HrmResource        | NULL       | const | PRIMARY,HRMRESOURCE_ID | PRIMARY         | 4       | const |    1 |   100.00 | NULL  |
|  3 | UNION       | HrmResourceManager | NULL       | ref   | IX_HRMRSCMGR_ID        | IX_HRMRSCMGR_ID | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------------+------------+-------+------------------------+-----------------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

此时为正确的执行计划.

Oracle 11G我们构建一个类似的SQL,在对比一下执行计划.

select /*+monitor*/ name from (

select deptno,ename name from scott.emp

union all

select deptno,dname name from scott.dept

) a where (deptno in(10));

SQL Monitoring Report

SQL Text
------------------------------
select /*+monitor*/ name from ( select deptno,ename name from scott.emp union all select deptno,dname name from scott.dept ) a where (deptno in(10))

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                
 Instance ID         :  1                              
 Session             :  SYSTEM (1190:29561)            
 SQL ID              :  306qn9tg7hz2x                  
 SQL Execution ID    :  16777216                       
 Execution Started   :  06/07/2023 16:10:28            
 First Refresh Time  :  06/07/2023 16:10:28            
 Last Refresh Time   :  06/07/2023 16:10:28            
 Duration            :  .001467s                       
 Module/Action       :  PL/SQL Developer/SQL ����- ��� 
 Service             :  sharedb                        
 Program             :  plsqldev.exe                   
 Fetch Calls         :  1                              

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.00 |    0.00 |     0.00 |     1 |      4 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=758530686)
===========================================================================================================================================
| Id |            Operation            |    Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                 |            | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
===========================================================================================================================================
|  0 | SELECT STATEMENT                |            |         |      |         1 |     +0 |     1 |        4 |          |                 |
|  1 |   VIEW                          |            |       6 |    3 |         1 |     +0 |     1 |        4 |          |                 |
|  2 |    UNION-ALL                    |            |         |      |         1 |     +0 |     1 |        4 |          |                 |
|  3 |     TABLE ACCESS BY INDEX ROWID | EMP        |       5 |    2 |         1 |     +0 |     1 |        3 |          |                 |
|  4 |      INDEX RANGE SCAN           | IDX_DEPTNO |       5 |    1 |         1 |     +0 |     1 |        3 |          |                 |
|  5 |     TABLE ACCESS BY INDEX ROWID | DEPT       |       1 |    1 |         1 |     +0 |     1 |        1 |          |                 |
|  6 |      INDEX UNIQUE SCAN          | PK_DEPT    |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
===========================================================================================================================================

Oracle查询优化器还是比较智能一点,自动做了谓词推进查询优化,生成了最佳的执行计划.