select FROM_UNIXTIME(copyright_apply_time/1000,\’%Y-%m-%d\’) point,count(1) numsfrom resource_info
where copyright_apply_time >= 1539336488355
and copyright_apply_time <= 1539941288355 group by point
查看耗时:
耗时为1123毫秒 查看执行计划:
explain select FROM_UNIXTIME(copyright_apply_time/1000,\’%Y-%m-%d\’) point,count(1) numsfrom resource_info
where copyright_apply_time >= 1539336488355
and copyright_apply_time <= 1539941288355 group by point
执行计划结果:
索引是命中了,但是extra字段中出现了Using temporary和Using filesort
优化慢sql一
group by实质是先排序后分组,也就是分组之前必排序。通过分组的时候禁止排序优化sql 执行sql:
select FROM_UNIXTIME(copyright_apply_time/1000,\’%Y-%m-%d\’) point,
count(1) numsfrom resource_info
where copyright_apply_time >= 1539336488355
and copyright_apply_time <= 1539941288355 group by point order by null
select FROM_UNIXTIME(copyright_apply_time/1000,\’%Y-%m-%d\’) point,
count(1) numsfrom resource_info
where copyright_apply_time >= 1539855067355
and copyright_apply_time <= 1539941467355