Case：MySQL使用left join的时候or改写成union可以提高效率

(1)优化前：使用or的时候，SQL执行时间1.47s

mysql> select e.emp_no,e.first_name,d.dept_no,d.from_date,d.to_date from employees e left join dept_emp d on e.emp_no=d.emp_no where e.emp_no=32000 or d.from_date='1996-11-24';58 rows in set (1.47 sec)mysql> desc select e.emp_no,e.first_name,d.dept_no,d.from_date,d.to_date from employees e left join dept_emp d on e.emp_no=d.emp_no where e.emp_no=32000 or d.from_date='1996-11-24';+----+-------------+-------+------------+------+----------------+---------+---------+--------------------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys  | key     | key_len | ref                | rows   | filtered | Extra       |+----+-------------+-------+------------+------+----------------+---------+---------+--------------------+--------+----------+-------------+|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY        | NULL    | NULL    | NULL               | 299335 |   100.00 | NULL        ||  1 | SIMPLE      | d     | NULL       | ref  | PRIMARY,emp_no | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | Using where |+----+-------------+-------+------------+------+----------------+---------+---------+--------------------+--------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

(2)优化后：相同结果集，使用union的时候，SQL执行时间只需0.23s

mysql> select e.emp_no,e.first_name,d.dept_no,d.from_date,d.to_date from employees e left join dept_emp d on e.emp_no=d.emp_no where e.emp_no=32000       ->  union      -> select e.emp_no,e.first_name,d.dept_no,d.from_date,d.to_date from employees e left join dept_emp d on e.emp_no=d.emp_no where d.from_date='1996-11-24';58 rows in set (0.23 sec)mysql> desc select e.emp_no,e.first_name,d.dept_no,d.from_date,d.to_date from employees e left join dept_emp d on e.emp_no=d.emp_no where e.emp_no=32000 union  select e.emp_no,e.first_name,d.dept_no,d.from_date,d.to_date from employees e left join dept_emp d on e.emp_no=d.emp_no where d.from_date='1996-11-24';+----+--------------+------------+------------+--------+----------------+---------+---------+--------------------+--------+----------+-----------------+| id | select_type  | table      | partitions | type   | possible_keys  | key     | key_len | ref                | rows   | filtered | Extra           |+----+--------------+------------+------------+--------+----------------+---------+---------+--------------------+--------+----------+-----------------+|  1 | PRIMARY      | e          | NULL       | const  | PRIMARY        | PRIMARY | 4       | const              |      1 |   100.00 | NULL            ||  1 | PRIMARY      | d          | NULL       | ref    | PRIMARY,emp_no | PRIMARY | 4       | const              |      1 |   100.00 | NULL            ||  2 | UNION        | d          | NULL       | ALL    | PRIMARY,emp_no | NULL    | NULL    | NULL               | 331143 |    10.00 | Using where     ||  2 | UNION        | e          | NULL       | eq_ref | PRIMARY        | PRIMARY | 4       | employees.d.emp_no |      1 |   100.00 | NULL            || NULL | UNION RESULT |  | NULL       | ALL    | NULL           | NULL    | NULL    | NULL               |   NULL |     NULL | Using temporary |+----+--------------+------------+------------+--------+----------------+---------+---------+--------------------+--------+----------+-----------------+5 rows in set, 1 warning (0.00 sec)