多表联查 group by order by 优化问题请教

SELECT
count( 0 )
FROM
(
SELECT
LIST.PROJECT_CODE,
LIST.PROJECT_NAME,
LIST.TASK_ID,
LIST.TASK_CODE,
LIST.TASK_NAME,
TASK.TASK_MANAGER_NAME,
TASK.CITY,
LIST.DESIGN_NAME,
LIST.DESIGN_SPECIFICATIONS,
LIST.MEASUREMENT_UNIT,
IF
( LIST.DELETE_FLAG = 0, LIST.DESIGN_AMOUNT, 0 ) AS DESIGN_AMOUNT,
LIST.UNIT_PRICE,
LIST.REMARKS,
LIST.APPROVAL_COMPLETE_TIME,
TASK.REPLY_DATE,
GROUP_CONCAT(
DISTINCT
IF
( COOP.PROJECT_ROLE_CODE = 'ALL_SJY' OR COOP.PROJECT_ROLE_CODE = 'ALL_SJZFZR', COOP.COOPERATION_COMPANY_NAME, NULL )) AS COOPERATION_COMPANY_NAME,
DIS.DISPATCH_UNIT_NAME,
( CASE TASKSTATUS.TASK_COMPLETE_STATUS WHEN '0' THEN '未完工' WHEN '1' THEN '已完工' ELSE NULL END ) AS TASK_COMPLETE_STATUS
FROM
MMAT_MATERIAL_LIST LIST -- 项目物资表
LEFT JOIN MMAT_TASKS TASK ON LIST.TASK_ID = TASK.TASK_ID -- 任务表
LEFT JOIN MMAT_PROJECT_COOPERATNER COOP ON LIST.TASK_ID = COOP.TASK_ID -- 项目成员表
AND COOP.DELETE_FLAG = 0
LEFT JOIN MMAT_DISPATCH_MESSAGE DIS ON LIST.TASK_ID = DIS.TASK_ID -- 派工单
AND DIS.DELETE_FLAG = 0
LEFT JOIN MMAT_TASKS_STATUS TASKSTATUS ON LIST.TASK_ID = TASKSTATUS.TASK_ID -- 任务状态
AND TASK.DELETE_FLAG = 0
WHERE
1 = 1
AND LIST.MATERIAL_SOURCE = '甲供'
AND DIS.DELIVERY_TYPE = '02'
AND LIST.TASK_ID IN (

从其他查询拿到的结果，数据量在 5000 左右

)
GROUP BY
LIST.TASK_ID,
LIST.DESIGN_NAME,
LIST.DESIGN_SPECIFICATIONS,
LIST.MEASUREMENT_UNIT
ORDER BY
LIST.PROJECT_CODE,
LIST.TASK_CODE DESC
) tmp_count

有如上 sql ，查询出来数据大概需要 20 秒上下，非常慢

GROUP BY 是因为生产环境有部分重复数据且被其他业务引用，短时间无法清理，故此使用了 GROUP BY 进行了去重

COOP 表虽然本 sql 没用到，但是部分业务会在这个表加条件

实际前台是个分页查询，这个是分页对应的 count 总数量的。

分页查询也很慢

查询结果约 5 万数据

执行计划如下图
https://s1.ax1x.com/2022/05/18/Ooa9UA.png

请问各位大佬该如何优化

nothingistrue

2022-05-18 14:14:18 +08:00

TASK_ID IN (从其他查询拿到的结果，数据量在 5000 左右) ，我感觉时间都耗在这里，这你怎么优化都没用。

鉴于你这里要去重的都是 LIST 表，可以先用子查询去重（相关 WHERE 条件要跟进去，不然就成全表去重了）到 LIST2 ，再用 LIST2 当主表去做后面的连接。此项提升有限或者没提升，不过会让 SQL 更易读。

ORDER BY 只放到最外层，里面除非要 LIMIT 否则都是无用功。

说点题外话，GROUP BY 去重要慎用，因为 GROUP BY 本质上是只取第一条丢弃其它的，不是去重。比如：假设 SELECT A,B,C GROUP BY A,B ，且 C 不是 A,B 派生的，那么当出现 A1-B1-C1 ，A1-B1-C2 这样数据的时候，你就只能查询出 A1-B1-C1 或 A1-B1-C2 ，而丢失另外一条。看楼主这个 ORDER BY LIST.PROJECT_CODE,LIST.TASK_CODE DESC ，感觉上 PROJECT_CODE 并不是它 GROUP BY 那几个字段的派生字段，大概率要丢失数据。

wym7223645

2022-05-18 15:34:48 +08:00

@nothingistrue 去重是每个表都有重复的，所以单 LIST 去重没效果，目前这种重复数据类似之前没做控制用户做了多次点击生成了两个相同的数据，所以取哪个都无所谓。

尝试过把 left 的表做一个子查询，子查询以及外层度加了条件效果都不大，感觉时间消耗在了 in 和 group by 上面

zmal

2022-05-20 10:29:01 +08:00

问题太多了，填完这个坑还有下个坑。op 你说的重复无意义数据其实是接口没做幂等生成的重复数据，本就不应该存在。
单从解决这个 sql 的角度来说，查询慢主要是因为 in 查询触发了全表扫描。
下策：mysql 有个 in 查询是否走索引的阈值配置，酌情调整该参数。但这个方案不解决根本问题。
中策：越复杂的查询精准分页越困难且无意义，劝说业务方放弃精准分页想法，用“加载更多”的类游标方式,每页数据不定长。去掉无意义的计算函数，去掉 groupby orderby ，子查询拆出来，在内存中分页，每次 in 查询中只查询不多于 10 条 task_id ，结果在内存中去重。
上策：提桶跑路。