mysql优化分页查询

一般在mysql的分页查询中，通过参加覆盖索引能够比较好的提交性能；
mysql中 使用limit 10000,10  返回的结果是10001到 10010行记录；
前面的10000行记录会被扫描，而不被使用，查询和排序的代价比较高；

思路：
在索引上完成排序分页，然后根据主键回表查询需要的行内容；

mysql> explain select  id from  material_data order by id desc limit 400000,10;
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | material_data | NULL       | index | NULL          | PRIMARY | 4       | NULL | 400010 |      100 | Using index |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+


mysql> explain select  t.*  from  material_data t
inner join  (
    select  id from  material_data order by id desc limit 400000,10
) b on b.id =t.id ;
+----+-------------+---------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2>    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 400010 |      100 | NULL        |
|  1 | PRIMARY     | t             | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |      1 |      100 | NULL        |
|  2 | DERIVED     | material_data | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 400010 |      100 | Using index |
+----+-------------+---------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set