原查询语句:
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;
+----------------------+-----------+----------+------------+--------------+---------------+
|