求取一个 sql 优化建议

[Dlin]

先祖留下的 sql 如下：

SELECT
	COUNT(*) 
FROM
	(
	SELECT
		t2.create_time createTime,
		t2.platform,
		t2.id taskId,
		t2.order_id orderId,
		t1.business_contract_no businessContractNo,
		t3.contract_no,
		t4.contract_name 
	FROM
		esign_task_item t1
		INNER JOIN esign_task t2 ON t1.task_id = t2.id
		LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
		LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no 
	WHERE
		t1.business_contract_no IS NOT NULL 
	GROUP BY
		t1.business_contract_no,
		t2.order_id,
		t2.id 
	ORDER BY
	t1.id DESC 
	) TOTAL

这段 sql 其实也是分页插件生成的，先不说 count 很慢的问题。 问题是里面 group by 非常慢，得查 20 秒左右 执行计划如下：

[lyusantu]

都要查 count 了，left join 和 order by 的意义是什么呢

[wangritian]

槽点太多，说你不懂吧，还知道执行计划，说懂吧，写成这样的 SQL 你居然在认真分析[捂脸]

[Corolin]

MyBatis 吧... 不太理解这个里面的 group by 的用意... 去重？

select count(0) from (
SELECT 1
FROM
esign_task t2
INNER JOIN esign_task_item t1 ON t1.task_id = t2.id
WHERE
t1.business_contract_no IS NOT NULL
GROUP BY
t1.business_contract_no,
t2.order_id,
t2.id
) a

[5aSx5Lia]

这里应该是主要分页查询 t1 ，t2 表的数据吧，t3 ，t4 只是查询信息，感觉可以拆分成两次查询，先分页查询出前 N 条数据，然后再去查补充数据， 或者不用分页插件去查 count

[play78]

不知道你具体是什么数据，但是看后面的 group ，至少可以把 where 和 group 放到 t1 那里，减少后面关联后，数据过大吧？ t2,t3, t4, 这些数据都可以先 group 后，再进行 left join 关联。
SELECT
...
FROM (
select ... from esign_task_item where business_contract_no IS NOT NULL group by task_id, business_contract_no) as t1
INNER JOIN esign_task t2 ON t1.task_id = t2.id
LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
GROUP BY
t2.order_id,
t2.id

[wengyanbin]

自己写 sql 去查询总数，明显 mybatis 生成的 sql 很慢。

[buffzty]

@lyusantu 基本就是懒 直接把查询语句加个 count

[xuanbg]

WHERE
t1.business_contract_no IS NOT NULL

这就约等于取全部数据了吧，怎么都不可能快的。

[yhtbiy]

优化建议：
1. 在子查询中，可以将不需要的字段去除，只保留需要进行 COUNT 操作的字段，以减少内存和 CPU 的消耗。
2. 可以考虑将子查询中的 LEFT JOIN 改为 INNER JOIN ，以减少查询的数据量。
3. 可以为子查询中的表添加索引，以提高查询性能。
4. 可以考虑将子查询中的 GROUP BY 和 ORDER BY 操作移至外层查询，以减少子查询的数据量。
5. 可以根据实际情况，调整查询语句的顺序，以提高查询性能。

[imokkkk]

优化建议如下：

1. 索引优化：为表 esign_task_item 的列 business_contract_no 创建索引，以加快 WHERE 子句中的条件判断。

2. 子查询优化：将子查询中的 GROUP BY 子句中的 t2.order_id 和 t2.id 移至外层查询中，以避免在子查询中进行分组操作。

3. 子查询优化：将子查询中的 ORDER BY 子句中的 t1.id 改为 t2.id ，以避免在子查询中进行排序操作。

优化后的 SQL 如下：

SELECT
COUNT(*)
FROM
(
SELECT
t2.create_time createTime,
t2.platform,
t2.id taskId,
t2.order_id orderId,
t1.business_contract_no businessContractNo,
t3.contract_no,
t4.contract_name
FROM
esign_task_item t1
INNER JOIN esign_task t2 ON t1.task_id = t2.id
LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
WHERE
t1.business_contract_no IS NOT NULL
GROUP BY
t1.business_contract_no,
t2.id,
t2.order_id
ORDER BY
t2.id DESC
) TOTAL

[sujin190]

看你这个似乎改成 t2 left join t1 就不用 group by 了吧，因为你 t2 其实 group by 了主键 id 了，t2 left join t1 的时候就算 t1 里有重复的也恰好会展开，所以结果和你的这个用 group by 是一样的

[hcbb]

1.你确定这条 sql 能跑?都 group 了还能取出 t2.create_time createTime,t2.platform,不聚合搞个鸡儿 group ,不如直接 distinct
2.business_contract_no,id 这些都该有索引
3.由于你最终目的是 count(*),所有很多表没必要 join 进去,(默认你 id 是 unique index),esign_task 没必要 join 进去,where 加
t1.task_id is not null 即可,t3,t4 也可以想一下是不是满足 1.n 情况,进行去除
4.别用分页插件了

[hhjswf]

@lyusantu 都说是分页插件了，一条查分页数据，一条查总页数，count 就是查总页数

[iosyyy]

在这个查询中，表 t1 很大，而表 t2 、t3 和 t4 相对较小。为了优化查询性能，可以考虑以下几点：

索引优化：确保每个表上的相关列具有适当的索引。在表 t1 上，确保 business_contract_no 列上有索引，这将加速条件 t1.business_contract_no IS NOT NULL 的筛选。在其他表上，根据连接条件和筛选条件创建索引，以提高连接和过滤操作的效率。

重新排列连接顺序：根据表的大小和连接条件选择合适的连接顺序。将较小的表放在前面，以减少连接操作的数据量。在这个查询中，将 t2 表放在前面，t1 表放在后面可能更合适。

子查询优化：将子查询的结果存储到一个临时表中，并在最外层的 SELECT 语句中使用该临时表。这样可以避免在子查询中重复执行相同的操作，提高查询效率。

下面是优化后的查询代码：

sql
Copy code
-- 创建临时表存储子查询结果
```sql
CREATE TEMPORARY TABLE temp_result
SELECT
t2.create_time createTime,
t2.platform,
t2.id taskId,
t2.order_id orderId,
t1.business_contract_no businessContractNo,
t3.contract_no,
t4.contract_name
FROM
esign_task_item t1
INNER JOIN esign_task t2 ON t1.task_id = t2.id
LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
WHERE
t1.business_contract_no IS NOT NULL
GROUP BY
t1.business_contract_no,
t2.order_id,
t2.id
ORDER BY
t1.id DESC;
```

-- 查询临时表中的行数
SELECT COUNT(*) FROM temp_result;
请注意，根据实际情况，你可能需要调整索引、连接顺序和其他查询优化技术来进一步提升性能。这些优化建议提供了一般性的指导，具体的优化策略应根据数据库引擎和数据量等因素进行调整。

chatgpt 生成的我输入的条件就是 t1 是大表其他都是小表相连 如果其他也都是大表我建议你更改数据库结构减少笛卡尔积
另外这里的
```sql
GROUP BY
t1.business_contract_no,
t2.order_id,
t2.id
```
应该被删除掉

[iosyyy]

@iosyyy 再补充一点 t2.create_time createTime,
t2.platform,
t2.id taskId,
t2.order_id orderId,
t1.business_contract_no businessContractNo,
t3.contract_no,
t4.contract_name
这些实际上都应该直接 select 1 这些数据我看你的逻辑应该是不需要查的

[iosyyy]

@iosyyy #15 这样可以减少数据库缓存.. 对数据库见解比较浅 而且你这个需要具体问题具体分析 你给出的东西并不够多 建议补充下再说

[jack4536251]

现在还有手写原生 sql 的？不都是用 orm 吗

[8355]

很明显 最大的性能问题在这里
WHERE
t1.business_contract_no IS NOT NULL
GROUP BY
t1.business_contract_no,

GROUP BY t2.order_id,t2.id 意义也没有吧。。。

之后 但是并没有查询相关字段链表干嘛呢。。 直接删不就得了
INNER JOIN esign_task t2 ON t1.task_id = t2.id 可以子查询 不用连表 如果数据量是 1 对 1 的话可以完全不用查。。

完全删掉 屁用没有
LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no

这就是查 list 懒得单独写 count 的结果吧。

[cnoder]

我想问下 op esign 是什么意思🙋

[huigeer]

看不到执行计划的图片，再上一遍看看？
另外 group by 查总数试试
SQL_CALC_FOUND_ROWS & FOUND_ROWS()