sql请教

ppgjx 发表于 2022-4-24 14:56

原sql

select dct.task_id , dct.device_id , dct.cmd , dct.create_time , dct.exec_time ,dct.result_time , dct.task_status ,dct.push_count
, dv.token , dv.unlock_token,dv.magic,dv.topic,dv.`status` as device_status,dv.cert_id,ci.p12_path,ci.p12_password,ci.cert_status from
device_command_task dct LEFT JOIN device_info dv on dct.device_id = dv.device_id LEFT JOIN cert_info ci on dv.cert_id = ci.cert_id where dv.device_id in(select device_id from device_info where status = "TokenUpdate")

用到了子查询我知道子查询非常影响性能有办法优化吗?

hrjmo 发表于 2022-4-24 15:04

如果数据量很大，on条件用到的字段可以加索引。

sjdzy 发表于 2022-4-24 15:04

可以尝试用left join替代in eg： select t1.c1 ,t1.c2 from t1 left join t2 on t1.id = t2.id wheret2.idis not null

邓振振 发表于 2022-4-24 15:05

select dct.task_id , dct.device_id , dct.cmd , dct.create_time , dct.exec_time ,dct.result_time , dct.task_status ,dct.push_count
, dv.token , dv.unlock_token,dv.magic,dv.topic,dv.`status` as device_status,dv.cert_id,ci.p12_path,ci.p12_password,ci.cert_status from
device_command_task dct LEFT JOIN device_info dv on dct.device_id = dv.device_id and dvstatus = "TokenUpdate" LEFT JOIN cert_info ci on dv.cert_id = ci.cert_id

尽量不用in，你left后加and就行了。在看下执行计划应该有优化的控件，无非就加合适的索引

a397555462 发表于 2022-4-24 15:05

第一个为啥不用 inner join ?

ppgjx 发表于 2022-4-24 15:11

邓振振发表于 2022-4-24 15:05
select dct.task_id , dct.device_id , dct.cmd , dct.create_time , dct.exec_time ,dct.result_time , dc ...

这个sql语句和我查的不一样呀

JasonJHu 发表于 2022-4-24 15:12

试试用exists来替代in，它只要找到一条满足的就会返回true

gahdxbg 发表于 2022-4-24 17:44

学习中11111111111111111111

sphinx_young 发表于 2022-5-6 11:23

select dct.task_id,
   dct.device_id,
   dct.cmd,
   dct.create_time,
   dct.exec_time,
   dct.result_time,
   dct.task_status,
   dct.push_count,
   dv.token,
   dv.unlock_token,
   dv.magic,
   dv.topic,
   dv. status as device_status,
   dv.cert_id,
   ci.p12_path,
   ci.p12_password,
   ci.cert_status
from device_command_task dct
inner JOIN device_info dv
on (dct.device_id = dv.device_id and dv.status = 'TokenUpdate')
LEFT JOIN cert_info ci
on dv.cert_id = ci.cert_id

dct.device_id、dv.device_id、dv.cert_id 、 ci.cert_id 增加索引。
如果依然效率不高且dv.status = 'TokenUpdate'数据条数占比不超过50%，建议将dv.status 增加索引。

页: [1]

吾爱破解 - 52pojie.cn's Archiver

sql请教