(1)优化前：使用or的时候，SQL执行时间1.47s
成都创新互联-专业网站定制、快速模板网站建设、高性价比建水网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式建水网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖建水地区。费用合理售后完善，十载实体公司更值得信赖。
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)
本文名称：Case：MySQL使用leftjoin的时候or改写成union可以提高效率
分享网址：
http://bzwzjz.com/article/gsoeso.html
