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查询优化器还是比较智能一点,自动做了谓词推进查询优化,生成了最佳的执行计划.
