多表查询多条件sql优化

mysql innoDB

SELECT
toj.id,
toj.errormsg,
tuser.`name` opname,
tcompany.shortName,
tr.orderNum japanNumber,
tr.sendvisanum,
tr.`status` orderstatus,
tr.comshortname,
tc.shortname customershotname,
toj.acceptDesign number,
DATE_FORMAT(tr.sendVisaDate, '%Y-%m-%d') sendingTime,
DATE_FORMAT(tr.outVisaDate, '%Y-%m-%d') signingTime,
tr.STATUS japanState,
(
SELECT
count(*)
FROM
t_applicant_order_jp
WHERE
orderId = toj.id
) peopleNumber,
tr.isDisabled,
tr.zhaobaocomplete,
tr.zhaobaoupdate,
toj.visastatus visastatus,
toj.visaType,
toj.acceptDesign,
tr.id orderid
FROM
t_order tr
INNER JOIN t_order_jp toj ON toj.orderId = tr.id
LEFT JOIN t_company tcom ON tr.comId = tcom.id
LEFT JOIN t_company tcompany ON toj.sendsignid = tcompany.id
LEFT JOIN t_user tuser ON tr.salesOpid = tuser.id
LEFT JOIN t_customer tc ON tr.customerId = tc.id
 WHERE 
(tr.orderNum LIKE '%贾%' 
OR 
tc.linkman LIKE '%贾%' 
OR 
tc.mobile LIKE '%贾%' 
OR 
tc.email LIKE '%贾%'
 OR 
(SELECT 
GROUP_CONCAT(CONCAT(ta.firstName,ta.lastName) SEPARATOR 'төл') applyname 
FROM 
t_applicant ta 
INNER JOIN t_applicant_order_jp taoj ON taoj.applicantId = ta.id 
LEFT JOIN t_order_jp toj ON taoj.orderId = toj.id 
LEFT JOIN t_order tor ON toj.orderId = tor.id 
WHERE 
tor.id = tr.id 
GROUP BY toj.orderId) LIKE '%贾%' 
OR 
toj.acceptDesign LIKE '%贾%' 
OR 
(SELECT tap.passport 
FROM 
t_applicant ta INNER JOIN t_applicant_order_jp taoj ON taoj.applicantId = ta.id 
LEFT JOIN t_applicant_passport tap ON tap.applicantId = ta.id 
LEFT JOIN t_order_jp toj ON taoj.orderId = toj.id 
LEFT JOIN t_order tor ON toj.orderId = tor.id 
WHERE 
tor.id = tr.id GROUP BY toj.orderId) LIKE '%贾%') 
AND 
(tr.createTime BETWEEN '2019-03-18 00:00:00' AND '2019-03-21 23:59:59') 
AND 
(tr.status=1 AND tr.isDisabled=0) 
AND 
(toj.visatype=1) 
AND tr.comId=67 
ORDER BY tr.isDisabled ASC, tr.updatetime DESC, tr.id DESC;

t_order数据量二十万左右的时候查询需要18S左右，请问有什么优化方向吗

yanteng0124

1楼•2103天前

1	PRIMARY	tr	ALL	PRIMARY				233609	Using where; Using filesort
1	PRIMARY	tcom	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	toj	ref	FK_Reference_42	FK_Reference_42	5	juyo-test.tr.id	1	Using where
1	PRIMARY	tcompany	eq_ref	PRIMARY	PRIMARY	4	juyo-test.toj.sendsignid	1	
1	PRIMARY	tuser	eq_ref	PRIMARY	PRIMARY	4	juyo-test.tr.salesOpid	1	
1	PRIMARY	tc	eq_ref	PRIMARY	PRIMARY	4	juyo-test.tr.customerId	1	Using where
4	DEPENDENT SUBQUERY	toj	ref	PRIMARY,FK_Reference_42	FK_Reference_42	5	juyo-test.tr.id	1	Using where; Using index
4	DEPENDENT SUBQUERY	taoj	ref	FK_Reference_17,FK_Reference_41	FK_Reference_17	5	juyo-test.toj.id	1	Using where
4	DEPENDENT SUBQUERY	ta	eq_ref	PRIMARY	PRIMARY	4	juyo-test.taoj.applicantId	1	Using index
4	DEPENDENT SUBQUERY	tap	ref	FK_Reference_33	FK_Reference_33	5	juyo-test.taoj.applicantId	1	
3	DEPENDENT SUBQUERY	toj	ref	PRIMARY,FK_Reference_42	FK_Reference_42	5	juyo-test.tr.id	1	Using where; Using index
3	DEPENDENT SUBQUERY	taoj	ref	FK_Reference_17,FK_Reference_41	FK_Reference_17	5	juyo-test.toj.id	1	Using where
3	DEPENDENT SUBQUERY	ta	eq_ref	PRIMARY	PRIMARY	4	juyo-test.taoj.applicantId	1	
2	DEPENDENT SUBQUERY	t_applicant_order_jp	ref	FK_Reference_17	FK_Reference_17	5	juyo-test.toj.id	1	Using index

执行计划中只有驱动表扫描了23W，剩下的都是1，按理说应该很快，但最后结果用了8S左右，去掉那几个LIKE之后是2S多，速度也不快

wendal

2楼•2103天前

驱动表扫23w这么多啊,直接就是ALL

yanteng0124

3楼•2103天前

但后边的都是1，就算扫23W次也用不了多少时间吧

wendal

4楼•2103天前

tr的comId visatype 字段都没有索引?

yanteng0124

5楼•2103天前

单个索引，和覆盖索引都加过，加上之后速度更慢了

wendal

6楼•2103天前

但为啥它要全扫描呢

yanteng0124

7楼•2103天前

1	PRIMARY	tr	ref	PRIMARY,comid_index	comid_index	5	const	116804	Using where; Using filesort
1	PRIMARY	tcom	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	toj	ref	FK_Reference_42	FK_Reference_42	5	juyo-test.tr.id	1	Using where
1	PRIMARY	tcompany	eq_ref	PRIMARY	PRIMARY	4	juyo-test.toj.sendsignid	1	
1	PRIMARY	tuser	eq_ref	PRIMARY	PRIMARY	4	juyo-test.tr.salesOpid	1	
1	PRIMARY	tc	eq_ref	PRIMARY	PRIMARY	4	juyo-test.tr.customerId	1	
2	DEPENDENT SUBQUERY	t_applicant_order_jp	ref	FK_Reference_17	FK_Reference_17	5	juyo-test.toj.id	1	Using index

随便加了一个comId的索引，扫描数据为11W，所用时间一样

wendal

8楼•2103天前

区分度那么低呀...

yanteng0124

9楼•2103天前

因为是测试数据，数据都一样，所以会比正常的慢，但总有一天会到这样的体量吧，就没法优化了？

请先登陆