# Time: 2021-05-08T15:10:37.000511+08:00
# User@Host: username[password] @ [ip] Id: 220505940
# Query_time: 66.684190 Lock_time: 0.000395 Rows_sent: 14 Rows_examined: 58193
SET timestamp=1620457837;
SELECT
fr.id, fr.stock_code, fr.stock_name, fr.app_id, fr.task_id, fr.user_id, fr.task_type, fr.data, fr.config, fr.result_msg, fr.error_stack, fr.video_info, fr.status, fr.ctime, fr.mtime, fr.deleted
FROM financial_report fr
LEFT JOIN app_template app
ON fr.app_id = app.id
WHERE fr.deleted = 0 AND app.status = 1 AND fr.task_type IN (2, 3) order by ctime desc LIMIT 19222, 14;
优化:
/*建索引*/
CREATE INDEX deleted_status_template_scope_user_id ON app_template (deleted, status, template_scope, user_id);
ALTER TABLE financial_report DROP INDEX idx_task_type;
CREATE INDEX task_type_deleted_app_id_ctime ON financial_report (task_type, deleted, app_id, ctime);
/*采用覆盖索引*/
SELECT
fr.id, fr.stock_code, fr.stock_name, fr.app_id, fr.task_id, fr.user_id, fr.task_type, fr.data, fr.config, fr.result_msg, fr.error_stack, fr.video_info, fr.status, fr.ctime, fr.mtime, fr.deleted
FROM financial_report fr
JOIN (SELECT id
FROM financial_report fr
WHERE fr.deleted = 0 AND fr.app_id IN (SELECT id from app_template where deleted=0 AND
`status`=1) AND fr.task_type IN (2, 3) order by ctime desc LIMIT 19222, 14) fr_b
ON fr.id = fr_b.id;
/*由于pagehelper插件的分页是直接加在最外层的sql语句上,所以上述sql语句需要分成两个*/
SELECT id
FROM financial_report fr_a
WHERE fr_a.deleted = 0 AND fr_a.app_id IN (SELECT id from app_template where deleted=0 AND `status`=1 and id = 20) AND fr_a.task_type IN (2, 3) order by ctime desc LIMIT 19222, 14;
SELECT
fr.id, fr.stock_code, fr.stock_name, fr.app_id, fr.task_id, fr.user_id, fr.task_type, fr.data, fr.config, fr.result_msg, fr.error_stack, fr.video_info, fr.status, fr.ctime, fr.mtime, fr.deleted
FROM financial_report fr
where fr.id in (2603, 12602, 2601, 2600, 2599, 2598, 2597, 2596, 2595, 2594, 2593, 2592, 2591, 2590);