关于 mysql 优化，请教大神们~

This topic created in 2232 days ago, the information mentioned may be changed or developed.

查询结果才一万多条就用了 60s，请问大神们这句 sql 还有什么可优化的空间？


SELECT
	count(tto.id)
FROM tb_order o
LEFT JOIN (SELECT
		id,
		trucker_order_id,
		parents_id,
		carrier_org_name,
		fence_status
	FROM tb_order
	WHERE fence_status = '1'
	AND IFNULL(trucker_order_id, 1) != 1
) so ON FIND_IN_SET(o.id, so.parents_id)
LEFT JOIN tb_transport tt ON tt.order_id = so.id
LEFT JOIN tb_trucker_order tto ON tto.id = so.trucker_order_id
WHERE o.org_id = '153645643245457'
AND IFNULL(so.id, 1) != 1;

16 replies • 2020-04-10 11:29:06 +08:00

shoaly

Apr 9, 2020

用代码去做逻辑, 不要用 mysql 去编程....
大部分情况 mysql 只需要查一个表, 极少数才会连 2 个表

AngryPanda

Apr 9, 2020

@shoaly 那是你没做 ERP 之类的内部系统

jjianwen68

Apr 9, 2020

先 explain 看看

Vegetable

Apr 9, 2020

问这种问题带个 DDL 朋友...
show create table `tablename`;

tomczhen

Apr 9, 2020 via Android

自己看 explain，大神都快饿死了。

ic2y

Apr 9, 2020

你表结构都没有，没有索引之类的信息，大家怎么帮你看呢？

chihiro2014

Apr 9, 2020

先 explain 分析下情况再看

gonethen

Apr 9, 2020

@jjianwen68 #3
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY o ref org_id org_id 195 const 20391 Using where; Using index
1 PRIMARY <derived2> ALL 40783 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY tt ref oder_id oder_id 195 so.id 1 Using index
1 PRIMARY tto eq_ref PRIMARY PRIMARY 194 so.trucker_order_id 1 Using index
2 DERIVED tb_order ALL 40783 Using where

dapang1221

Apr 9, 2020

WHERE fence_status = '1' AND IFNULL(trucker_order_id, 1) != 1
这啥玩意？直接 and trucker_order_id is not null 不行么……这玩意肯定全表扫了啊

ON FIND_IN_SET(o.id, so.parents_id)
还有这啥？ join xx on x.id = xx.pid 然后 where 里加一个 xx.pid is not null 就行啊

dilu

Apr 9, 2020

SET profiling = 1;
#执行语句
show profiles;
show profile block io,cpu for query 1;

第二行换成你的 sql 跑一下贴结果来看看

dapang1221

Apr 9, 2020

有一说一我真就头一回见到 IFNULL 和 FIND_IN_SET 这俩东西，也许是我才疏学浅了吧……

gonethen

Apr 10, 2020

@dilu #10
![]( https://ae01.alicdn.com/kf/H79b8ac55d94d47fba9f6e6bf9aa5f8fbX.png)
![]( https://ae01.alicdn.com/kf/He2e6f02774ab41968118c56701b8a635G.png)

gonethen

Apr 10, 2020

@dapang1221 #9 parents_id 是以逗号隔开的多级父级 id，数据表结构是之前设计的锅，我不背

dilu

Apr 10, 2020 via Android

就执行结果来看你这速度一点不慢啊这个慢是偶发的还是必现的？

gonethen

Apr 10, 2020

@dilu #14 必现的。10k 的数据需要 60s 肯定是不行的，所以我想要优化一下

dilu

Apr 10, 2020

@gonethen 微信沟通吧 emh3MTk5NDA0MTI=