记录一次sql的变通

需求：查询当天最高烟雾的前三名

遇到的问题：

首先看sql和结果：

SELECT 
     a.sensor_number as  sensorNumber,
     a.s_port as sPort,
     a.sync_time as syncTime,
     b.address as address
   from interface_data_list a FORCE INDEX(index_sync_time)
    STRAIGHT_JOIN interface_list b ON a.sensor_number=b.sensor_number
    STRAIGHT_JOIN user_project c ON b.project_list_id=c.project_list_id
   WHERE  c.user_id=27
    AND a.sync_time > CURDATE()
     ORDER BY sPort DESC

1.首先我是通过mysql将当天的所有烟雾数据查出来，数据多。
2.然后在后台进行去重复，
3.再根据时间字段排序，
4.得到想要的数据，效率很低，
5.指定数据格式时，for循环遍历的次数也增加，速度慢。

通过优化如下：

首先我们从sql入手，只要sql一次性查出前三名，我们就只需取三条数据，并且在后台也不需要去重了，

通过优化sql 如下：

SELECT  h.sensorNumber AS sensorNumber,
				h.tPort AS tPort,
                h.syncTime AS syncTime,
			    h.address AS address
FROM(
SELECT 
     a.sensor_number as  sensorNumber,
     a.t_port as tPort,
     a.sync_time as syncTime,
     b.address as address
   from interface_data_list a FORCE INDEX(index_sync_time)
        STRAIGHT_JOIN interface_list b ON a.sensor_number=b.sensor_number
		STRAIGHT_JOIN district e ON b.d_id=e.d_id
        STRAIGHT_JOIN user_project c ON b.project_list_id=c.project_list_id
   WHERE  c.user_id=27
    AND a.sync_time > CURDATE()
     ORDER BY tPort DESC LIMIT 1000000
)h GROUP BY h.sensorNumber ORDER BY h.tPort DESC LIMIT 3;

优化后，后台直接拿到数据进行循环调整想要的数据格式，最多也只循环三次。