mysql5.7之后使用order by 再使用group by 按照时间排序会失效,配合having使用即可,实例如下
原始sql:
select distinct a.ip from (select ip from statistics order by created_time desc)a where
a.type=“1” group by a.ip,a.port;
改进后:
select distinct a.ip from (select ip from statistics having 1=1 order by created_time desc)a where
a.type=1 group by a.ip,a.port;
我这边的数据量是500万条,但having会大大影响到查询性能,虽然需要查询的字段已经做了索引,但还是无法提高
因为是要查寻最新的一条记录,所以使用主键索引作为分组去查询
优化如下:select distinct a.ip from (select ip,port,type,max(id)id from statistics where
created_time 》date_sub (now(),interval 1 hour)and type=“redis” group by ip,port ,type)a;
优化前2秒优化后0.2毫秒