mysql - 优化 MySQL 中的 where 子句查询

Question

Using alias of a query to join with another table MySQL

与我之前的问题类似，它不适用于从子查询的结果中给出 1 的值。所以我把它改成了

select *
from rents r
where
  r.kickscooter_id in
   (select k.id 
    from support_works sw
    join kickscooters k 
      on k.serial_number = sw.serial_number
    join kickscooter_control_units kcu 
      on  kcu.kickscooter_id =  k.id 
        and kcu.particle_product_id in (9358, 9383)   
    where
      sw.work_type = 'deploy' and
      (sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'));

但是现在因为它需要检查每个 r.kickscooter_id，它是否属于子查询的结果列表，所以花费的时间太长了。

我该如何优化它。

Answer 1

您可以尝试使用 Join over Sub-query -

select r.*
from rents r
join kickscooters k on k.id = r.kickscooter_id
join support_works sw on k.serial_number = sw.serial_number
join kickscooter_control_units kcu on  kcu.kickscooter_id =  k.id
where kcu.particle_product_id in (9358, 9383)
and sw.work_type = 'deploy'
and sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'

稍后您可以在以下字段上创建索引以查看差异。

rents(kickscooter_id)
kickscooters(serial_number)
support_works(serial_number, work_type,updated_at)
kickscooter_control_units(kickscooter_id, particle_product_id)