MySQL --- SQL 优化

inner join 代替 where in

select * from variable_frequency_drive_collection
    where id in (
        select max(id) from variable_frequency_drive_collection
        where device_type="1" and device_id in {all_compressor_id}
        group by device_id
);

优化后

select cvf.* from collection_variable_frequency_drive_collection cvf
INNER JOIN (
	select max(id) id from collection_variable_frequency_drive_collection
    where device_type="1" and device_id in (7,8,9,10)
    group by device_id
) r ON cvf.id=r.id order by cvf.id