mysql> explain select a.dept_no,a.emp_no,a.from_date from dept_emp a left join dept_emp b on a.dept_no=b.dept_no and a.from_date < b.from_date where b.from_date is null;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | ref | dept_no | dept_no | 16 | slowtech.a.dept_no | 41376 | 19.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
方法3
mysql> select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
…
12 rows in set (1.57 sec)
mysql> desc select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 100.00 | Using where |
| 2 | DERIVED | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
2 rows in set, 2 warnings (0.00 sec)
从执行时间上看，
方法1的时间最短，在有复合索引(deptno, fromdate)的情况下，结果瞬间就出来了，即使在没有索引的情况下，也只消耗了0.75s。
方法2的时间最长，3个小时还是没出结果。同样的数据，同样的SQL，放到Oracle查，也消耗了87分49秒。
方法3的时间比较固定，无论是否存在索引，都维持在1.5s左右，比方法1的耗时要久。