mysql优化sql
如下 项目用的是 mysql 是项目的查询SQL语句， 查询结果为 88 的竟然要1分40秒， 结果为5的要 2秒，
SQL语句不是我写的，大家看看有什么地方可以优化的，想要优化查询速度的话，可以从哪方面入手修改。
SELECT COUNT(DISTINCT ttsp.id) AS postpone_count, ttp.task_document_id AS task_document_id, ttm.id AS attention_id, tt.company_id, tt.create_by, ts.name AS principal, ts.staff_id AS principal_id, tt.postpone_time, tt.subtask_bind_step_id, tt.current_task_step_id, tt.create_time, tts.task_step_id AS has_taskstep, tts2.current_task_step, tt.po_predict_work_load, tt.task_priority, tt.expect_end_time, tts2.executor, tt.start_time, tt.end_time, tt.type, tta.task_annex_id AS annex, tt.task_id AS id, tt.title AS NAME, CASE WHEN ( (SELECT COUNT(*) FROM t_task_step WHERE task_id = tt.task_id AND del_flag = 0) > 0 ) THEN 'closed' WHEN ( (SELECT COUNT(*) FROM t_task WHERE pid = tt.task_id AND del_flag = 0) > 0 ) THEN 'closed' ELSE 'open' END AS state, '3' AS style, tt.author AS task_author, tt.work_load AS task_work_load, tm.comment AS content_comment, tn.comment AS time_comment, tq.comment AS other_comment, tnn.comment AS inner_comment, CONCAT(tt.task_id, '_t') AS id_type, SUM(tw.workload) count_workload, tt.pid, CASE WHEN tt.task_priority = '1' THEN 'task-emergency' WHEN tt.task_priority = '2' THEN 'task-imprtant' WHEN tt.task_priority = '3' THEN 'task-general' ELSE 'task-normal' END AS iconCls FROM t_task tt LEFT JOIN t_task_step tts ON ( tt.task_id = tts.task_id AND tts.del_flag = '0' ) LEFT JOIN t_workload tw ON ( tts.task_step_id = tw.task_step_id AND tw.del_flag = '0' ) LEFT JOIN t_task_content ttc ON ( tt.task_id = ttc.task_id AND ttc.del_flag = '0' ) LEFT JOIN (SELECT * FROM t_task_annex ORDER BY task_annex_id DESC) tta ON ( tt.task_id = tta.task_id AND tta.del_flag = '0' AND tta.annex IS NOT NULL ) LEFT JOIN t_project tp ON tt.project_id = tp.project_id LEFT JOIN t_company tco ON tt.company_id = tco.company_id LEFT JOIN (SELECT foreign_id, COMMENT, create_time FROM t_comment WHERE del_flag = '0' AND TYPE = 2 AND comment_type = 0 ORDER BY create_time DESC LIMIT 0, 1) AS tm ON tt.task_id = tm.foreign_id LEFT JOIN (SELECT foreign_id, COMMENT, create_time FROM t_comment WHERE del_flag = '0' AND TYPE = 2 AND comment_type = 1 ORDER BY create_time DESC LIMIT 0, 1) AS tn ON tt.task_id = tn.foreign_id LEFT JOIN (SELECT foreign_id, COMMENT, create_time FROM t_comment WHERE del_flag = '0' AND TYPE = 2 AND comment_type = 2 ORDER BY create_time DESC LIMIT 0, 1) AS tq ON tt.task_id = tq.foreign_id LEFT JOIN (SELECT foreign_id, COMMENT, create_time FROM t_comment WHERE del_flag = '0' AND TYPE = 2 AND comment_type = 3 ORDER BY create_time DESC LIMIT 0, 1) AS tnn ON tt.task_id = tnn.foreign_id LEFT JOIN (SELECT task_step_id, TYPE AS current_task_step, to_staff AS executor, to_staff_email AS executor_email FROM t_task_step WHERE del_flag = '0') tts2 ON tts2.task_step_id = tt.current_task_step_id LEFT JOIN t_staff ts ON (tt.executor = ts.staff_id) LEFT JOIN (SELECT ttp.task_id, GROUP_CONCAT(producttype_id SEPARATOR ',') AS producttype_id, task_document_id FROM (SELECT ttp.producttype_id, ttp.task_id, ttdl.task_document_id FROM t_task_producttype_link ttp LEFT JOIN (SELECT producttype_id, GROUP_CONCAT(id SEPARATOR ',') AS task_document_id FROM t_task_document_type_link GROUP BY producttype_id) ttdl ON ttp.producttype_id = ttdl.producttype_id) ttp GROUP BY task_id) ttp ON ttp.task_id = tt.task_id LEFT JOIN t_task_step_postpone_history ttsp ON ttsp.foreign_id = tt.task_id AND ttsp.type = 0 AND ttsp.del_flag = 0 LEFT JOIN (SELECT * FROM t_task_myattention WHERE user_id = '202b293f-da58-4cd3-b12d-40e4f9ce0d2c') ttm ON ttm.task_id = tt.task_id WHERE tco.del_flag = '0' AND tt.del_flag = '0' AND tp.del_flag = '0' AND tt.project_id = 44 AND tt.pid IS NULL GROUP BY tt.task_id ORDER BY tt.task_id DESC
这是 explain的查询结果
