sql 语句的查询优化,各位看看可以怎么优化,新人初学,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的查询结果
图片说明

2个回答

,不知楼主用的什么数据库

yiiy22
KUkaKaaa mysql
4 年多之前 回复

太多了看的头都大了啊

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问