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

mysql>selecte.emp_no,e.first_name,d.dept_no,d.from_date,d.to_datefromemployeeseleftjoindept_empdone.emp_no=d.emp_nowheree.emp_no=32000ord.from_date='1996-11-24';58rowsinset(1.47sec)mysql>descselecte.emp_no,e.first_name,d.dept_no,d.from_date,d.to_datefromemployeeseleftjoindept_empdone.emp_no=d.emp_nowheree.emp_no=32000ord.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|Usingwhere|+----+-------------+-------+------------+------+----------------+---------+---------+--------------------+--------+----------+-------------+2rowsinset,1warning(0.00sec)

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

mysql>selecte.emp_no,e.first_name,d.dept_no,d.from_date,d.to_datefromemployeeseleftjoindept_empdone.emp_no=d.emp_nowheree.emp_no=32000->union->selecte.emp_no,e.first_name,d.dept_no,d.from_date,d.to_datefromemployeeseleftjoindept_empdone.emp_no=d.emp_nowhered.from_date='1996-11-24';58rowsinset(0.23sec)mysql>descselecte.emp_no,e.first_name,d.dept_no,d.from_date,d.to_datefromemployeeseleftjoindept_empdone.emp_no=d.emp_nowheree.emp_no=32000unionselecte.emp_no,e.first_name,d.dept_no,d.from_date,d.to_datefromemployeeseleftjoindept_empdone.emp_no=d.emp_nowhered.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|Usingwhere||2|UNION|e|NULL|eq_ref|PRIMARY|PRIMARY|4|employees.d.emp_no|1|100.00|NULL||NULL|UNIONRESULT|<union1,2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+--------+----------------+---------+---------+--------------------+--------+----------+-----------------+5rowsinset,1warning(0.00sec)