问题描述

麻烦帮忙看下这个SQL查询该怎么改进

有三张表，分别是EquipmentMessage，Sensor，SensorInputData，Sensor以
EquipmentMessage的主键equipmentId做外键，SensorInputData则以Sensor的主键sensorId做外键。SensorInputData是大表，可能有千万条数据，另外两个都是小表，最多不会超过200条数据。我的这个查询第二句是一个“*”号，此时查询速度很快，大概170ms，如果我只想要其中个别字段，查询速度极慢，需要8s。现在我就想要其中个别字段，该怎么改进

 select
*   --这里用“*”查询很快，但是如果改成equipment.equipmentName就很慢了
from
(
    select
    sensorData.valueType+':'+(Convert(varchar(20),round(sensorData.value,2)))+sensor.sensorUnit as 数据,
    Convert(varchar(20),sensorData.date,120) as 日期,
    sensor.equipmentId as ref_equipmentId
    from Sensor as sensor
    join SensorInputData as sensorData on sensor.sensorId=sensorData.sensorId
    and not exists
    (
        select 1 from SensorInputData as t where t.sensorId=sensorData.sensorId and t.date>sensorData.date
    )
) sensorData join EquipmentMessage as equipment
on equipment.equipmentId=sensorData.ref_equipmentId

解决方案

http://blog.chinaunix.net/uid-20476365-id-3238091.html