MySQL SQL优化案例：LIMIT M,N大偏移量分页

原查询语句：

SELECT
  loan_document_id,
  contract_id,
  applicant_contract_id,
  buyer_id,
  buyer_name,
  seller_id,
  seller_name,
  loan_document_no,
  loan_document_type,
  order_content,
  amount,
  buyer_cost,
  seller_cost,
  apply_date,
  apply_amount,
  can_apply_amount,
  start_time,
  end_time,
  loan_due_date,
  ar_due_date,
  buyback_due_date,
  lending_date,
  lending_amount,
  write_off_amount,
  write_off_date,
  submit_time,
  loan_document_state,
  state_change_time,
  attachment_count,
  created_by,
  create_time,
  update_by,
  update_time,
  delete_flag,
  digital_sign,
  pay_state,
  pay_state_change_time,
  pay_apply_time,
  loan_state,
  apply_date,
  applied_pay_amount,
  loan_state_change_time
FROM
  t_loan_document
WHERE
  (
    loan_document_state = 0
    OR loan_document_state = 5
    OR loan_document_state = 7
  )
AND ar_due_date < '2015-12-24 11:09:09'
AND delete_flag = 0
LIMIT 439000,
 100;

原语句执行计划：

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_loan_document | ALL  | NULL          | NULL | NULL    | NULL | 608512 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

意味着要全表扫描，预估的扫描行数为608512行。执行了一下结果为：100 rows in set (12.03 sec)，也即返回100条记录耗时12.03秒。

这个查询获取的字段数目非常多，且LIMIT偏移量非常大 LIMIT 439000,100意味着先要找到前439000条满足where条件的记录而后舍弃这些记录取之后满足where条件的100条，这样的代价注定会非常高。

看一下该语句执行过程中各个阶段耗费的资源情况：

set profiling=1;
select loan_document_id ...
show profiles;
show profile cpu,block io for query 1;
+----------------------+-----------+----------+------------+--------------+---------------+
|