优化mysql limit offset

      在mysql中我们通常都使用limit做分页，而且经常会跟order by 连用。在order by 上加索引有时候是很有帮助的，不然系统会做很多的filesort。
      我们经常碰到的一个问题是limit的offset太高，如：limit 100000,20，这样系统会查询100020条，然后把前面的100000条都扔掉，这是开销很大的操作，导致查询很慢。假设所有分页的页面访问频率一样，这样的查询平均扫描表的一半数据。优化的方法，要么限制访问后面的页数，要么提升高偏移的查询效率。
     一个简单的优化办法是使用覆盖查询（covering index）查询，然后再跟全行的做join操作。如：
    

SQL>select * from user_order_info limit 1000000,5;

这条语句就可以优化为：

select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);

SQL>explain select * from user_order_info limit 1000000,5;
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows     | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | user_order_info | ALL  | NULL          | NULL | NULL    | NULL | 23131886 |       |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)

SQL>explain extended select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref     | rows     | filtered | Extra       |
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
|  1 | PRIMARY     |       | ALL    | NULL          | NULL    | NULL    | NULL    |        5 |   100.00 |             |
|  1 | PRIMARY     | user_order_info | eq_ref | PRIMARY       | PRIMARY | 42      | lim.pin |        1 |   100.00 |             |
|  2 | DERIVED     | user_order_info | index  | NULL          | PRIMARY | 42      | NULL    | 23131886 |   100.00 | Using index |
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
3 rows in set, 1 warning (0.66 sec)

根据两个explain的对比，可以清晰发现，第一个未使用索引，扫描了23131886行，第二个也扫描了同样的行数，但是使用了索引，效率提高了。这样可以直接使用index得到数据，而不去查询表，当找到需要的数据之后，在与全表join，获得其他的列。

ps：我翻译的太水了。。。