这是创建的索引：
mysql> show index from salaries;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| salaries | 0 | PRIMARY | 1 | emp_no | A | 295694 | NULL | NULL | | BTREE | | |
| salaries | 0 | PRIMARY | 2 | from_date | A | 2838426 | NULL | NULL | | BTREE | | |
| salaries | 1 | salary | 1 | salary | A | 73927 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
这是按照网上的sql优化和没有优化的结果，为何优化的效率比没有优化还要低？
mysql> select * from salaries a join (select emp_no,from_date from salaries order by from_date limit 2000000,20) b on a.emp_no=b.emp_no and a.from_date=b.from_date;
+--------+--------+------------+------------+--------+------------+
| emp_no | salary | from_date | to_date | emp_no | from_date |
+--------+--------+------------+------------+--------+------------+
| 49095 | 52441 | 1999-03-29 | 2000-03-28 | 49095 | 1999-03-29 |
| 49534 | 106829 | 1999-03-29 | 2000-03-28 | 49534 | 1999-03-29 |
| 49661 | 88589 | 1999-03-29 | 2000-03-28 | 49661 | 1999-03-29 |
| 47516 | 89305 | 1999-03-29 | 2000-03-28 | 47516 | 1999-03-29 |
| 47520 | 48996 | 1999-03-29 | 2000-03-28 | 47520 | 1999-03-29 |
| 48129 | 65100 | 1999-03-29 | 2000-03-28 | 48129 | 1999-03-29 |
| 42510 | 43998 | 1999-03-29 | 2000-03-28 | 42510 | 1999-03-29 |
| 43186 | 52203 | 1999-03-29 | 2000-03-28 | 43186 | 1999-03-29 |
| 51434 | 58226 | 1999-03-29 | 2000-03-28 | 51434 | 1999-03-29 |
| 45635 | 53164 | 1999-03-29 | 2000-03-28 | 45635 | 1999-03-29 |
| 45823 | 65169 | 1999-03-29 | 2000-03-28 | 45823 | 1999-03-29 |
| 45866 | 43137 | 1999-03-29 | 1999-05-16 | 45866 | 1999-03-29 |
| 46780 | 55729 | 1999-03-29 | 2000-03-28 | 46780 | 1999-03-29 |
| 68262 | 57090 | 1999-03-29 | 2000-03-28 | 68262 | 1999-03-29 |
| 69335 | 77710 | 1999-03-29 | 2000-03-28 | 69335 | 1999-03-29 |
| 69573 | 52088 | 1999-03-29 | 2000-03-28 | 69573 | 1999-03-29 |
| 67465 | 81145 | 1999-03-29 | 2000-03-28 | 67465 | 1999-03-29 |
| 67472 | 48556 | 1999-03-29 | 2000-03-28 | 67472 | 1999-03-29 |
| 67475 | 79215 | 1999-03-29 | 2000-03-28 | 67475 | 1999-03-29 |
| 71081 | 60817 | 1999-03-29 | 2000-03-28 | 71081 | 1999-03-29 |
+--------+--------+------------+------------+--------+------------+
20 rows in set (1.39 sec)
mysql> select * from salaries limit 2000000,20;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 410897 | 50288 | 1992-09-06 | 1993-09-06 |
| 410897 | 51656 | 1993-09-06 | 1994-09-06 |
| 410897 | 53408 | 1994-09-06 | 1995-09-06 |
| 410897 | 55183 | 1995-09-06 | 1996-09-05 |
| 410897 | 55166 | 1996-09-05 | 1997-09-05 |
| 410897 | 59264 | 1997-09-05 | 1998-09-05 |
| 410897 | 62534 | 1998-09-05 | 1999-09-05 |
| 410897 | 62825 | 1999-09-05 | 2000-09-04 |
| 410897 | 66931 | 2000-09-04 | 2001-09-04 |
| 410897 | 68054 | 2001-09-04 | 9999-01-01 |
| 410898 | 66156 | 1990-10-25 | 1991-10-25 |
| 410898 | 69219 | 1991-10-25 | 1992-10-24 |
| 410898 | 70371 | 1992-10-24 | 1993-10-24 |
| 410898 | 70666 | 1993-10-24 | 1994-10-24 |
| 410898 | 73620 | 1994-10-24 | 1995-10-24 |
| 410898 | 75197 | 1995-10-24 | 1996-10-23 |
| 410898 | 78083 | 1996-10-23 | 1997-10-23 |
| 410898 | 81043 | 1997-10-23 | 1998-10-23 |
| 410898 | 81128 | 1998-10-23 | 1999-10-23 |
| 410898 | 84051 | 1999-10-23 | 2000-10-22 |
+--------+--------+------------+------------+
20 rows in set (0.42 sec)
前一个有子查询、连接操作，还排序，当然比后一个慢了。有什么问题。
子查询 需要查几次表,而连接查询只需要查一次表,对于数据量大的尽量使用连接查询,对于数据量小的 使用子查询可以,容易控制
你那sql先把limit数据查出来 然后再连接查询，量大的时候性能会直接下降，大数据最好分开查询