mysql执行查询性能低下

Question

我想优化下面的 SQL 查询，目标是在 3-4 秒内获得结果。

select(
    ((select data from t_sayac_degerler where id = (select max(id) from t_sayac_degerler where adres = '1.8.0'  and tarih between '2016.02.15 11:00' and '2016.02.16 17:13')) 
    - 
    (select data from t_sayac_degerler where id = (select min(id) from t_sayac_degerler where adres = '1.8.0' and tarih between '2016.02.15 11:00' and '2016.02.16 17:13')))
    *
    (select multiplier from t_sayac where s_id = ((select s_id from t_sayac_degerler where id = (select max(id) from t_sayac_degerler where adres = '1.8.0' and tarih between '2016.02.15 11:00' and '2016.02.16 17:13'))))
    ) 
    as value

我怎样才能实现这个目标？

Answer 1

我只是好奇这是否会提高您的性能，通过删除嵌套的 Select 语句，我将删除嵌套循环。我假设 id 值是 int:

DECLARE @MaxID int, @MinID int, @s_id int;


SELECT  @MaxID=max(id) , @MinID = min(id) 
FROM       t_sayac_degerler 
WHERE   adres = '1.8.0' and tarih between '2016.02.15 11:00' and '2016.02.16 17:13';

SELECT @s_id=s_id 
FROM   t_sayac_degerler 
WHERE  id = @MaxID


SELECT(
    (
      (SELECT data 
       FROM   t_sayac_degerler 
       WHERE  id = @MaxID) 
       - 
      (SELECT data 
       FROM   t_sayac_degerler 
       WHERE  id = @MinID)
    )
    *
    (SELECT multiplier 
    FROM   t_sayac 
    WHERE  s_id = @s_id
    )
) 
as value