这是一个创建于 949 天前的主题，其中的信息可能已经有所发展或是发生改变。
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
请问各位大佬 该如何优化
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
请问各位大佬 该如何优化
 |
1
wym7223645 OP 跪求指导
|
 |
2
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 那几个字段的派生字段，大概率要丢失数据。 |
|
3
wym7223645 OP @nothingistrue 5000 条那个，如果数据量少 的确很快
|
|
4
wym7223645 OP @nothingistrue 去重是每个表都有重复的，所以单 LIST 去重没效果，目前这种重复数据 类似之前没做控制 用户做了多次点击 生成了两个相同的数据，所以取哪个都无所谓。
尝试过把 left 的表做一个子查询，子查询以及外层度加了条件 效果都不大，感觉时间消耗在了 in 和 group by 上面 |
 |
5
zmal 2022-05-19 09:29:40 +08:00
不要面向 sql 编程。
|
|
6
wym7223645 OP @zmal 是否可以给个建议呢
|
|
7
zmal 2022-05-20 10:29:01 +08:00
问题太多了，填完这个坑还有下个坑。op 你说的重复无意义数据其实是接口没做幂等生成的重复数据，本就不应该存在。
单从解决这个 sql 的角度来说，查询慢主要是因为 in 查询触发了全表扫描。 下策：mysql 有个 in 查询是否走索引的阈值配置，酌情调整该参数。但这个方案不解决根本问题。 中策：越复杂的查询精准分页越困难且无意义，劝说业务方放弃精准分页想法，用“加载更多”的类游标方式,每页数据不定长。去掉无意义的计算函数，去掉 groupby orderby ，子查询拆出来，在内存中分页，每次 in 查询中只查询不多于 10 条 task_id ，结果在内存中去重。 上策：提桶跑路。 |
|
8
wym7223645 OP @zmal 下策，中策 都不行，业务不同意 想去掉排序都不行 MMP ，选择了 躺烂的策略
|
Select Language