mysql按工作日查询统计优化_mysql 统计时间范围记录优化

统计三分钟,五分钟,十分钟,半小时 表数据记录数:

SELECT

COUNT(*),

(SELECT COUNT(*) FROM incremental_statistics i WHERE

TIMESTAMPDIFF(MINUTE, DATE_FORMAT(i.CREATE_TIME,'%Y-%m-%d %T'), '2019-05-13 15:02:39') BETWEEN 0 AND 3) as threeMin,

(SELECT COUNT(*) FROM incremental_statistics i WHERE

TIMESTAMPDIFF(MINUTE, DATE_FORMAT(i.CREATE_TIME,'%Y-%m-%d %T'), '2019-05-13 15:02:39') BETWEEN 0 AND 5) as fiveMin,

(SELECT COUNT(*) FROM incremental_statistics i WHERE

TIMESTAMPDIFF(MINUTE, DATE_FORMAT(i.CREATE_TIME,'%Y-%m-%d %T'), '2019-05-13 15:02:39') BETWEEN 0 AND 10) as tenMin,

(SELECT COUNT(*) FROM incremental_statistics i WHERE

TIMESTAMPDIFF(MINUTE, DATE_FORMAT(i.CREATE_TIME,'%Y-%m-%d %T'), '2019-05-13 15:02:39') BETWEEN 0 AND 30) as thirtyMin,

(SELECT COUNT(*) FROM incremental_statistics i WHERE

TIMESTAMPDIFF(MINUTE, DATE_FORMAT(i.CREATE_TIME,'%Y-%m-%d %T'), '2019-05-13 15:02:39') BETWEEN 0 AND 60) as oneHour

FROM

incremental_statistics ilss

结果是:

我们来用 EXPLAIN 分析一下:

结果发现,用的都是子查询,大家都应该知道,随着数据量越来越大,子查询会大大降低查询速度,我们来优化一下:

SELECT

COUNT(*),

SUM(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(ilss.CREATE_TIME,'%Y-%m-%d %T'), DATE_FORMAT('2019-05-13 15:02:39','%Y-%m-%d %T'))BETWEEN 0 AND 3) as threeMin,

SUM(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(ilss.CREATE_TIME,'%Y-%m-%d %T'), DATE_FORMAT('2019-05-13 15:02:39','%Y-%m-%d %T'))BETWEEN 0 AND 5) as fiveMin,

SUM(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(ilss.CREATE_TIME,'%Y-%m-%d %T'), DATE_FORMAT('2019-05-13 15:02:39','%Y-%m-%d %T'))BETWEEN 0 AND 10) as tenMin,

SUM(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(ilss.CREATE_TIME,'%Y-%m-%d %T'), DATE_FORMAT('2019-05-13 15:02:39','%Y-%m-%d %T'))BETWEEN 0 AND 30) as thirtyMin,

SUM(TIMESTAMPDIFF(MINUTE, DATE_FORMAT(ilss.CREATE_TIME,'%Y-%m-%d %T'), DATE_FORMAT('2019-05-13 15:02:39','%Y-%m-%d %T'))BETWEEN 0 AND 60) as oneHour

FROM

incremental_statistics ilss

结果:

两者对比:

发现结果是一样的,老铁没毛病,我们再看看 EXPLAIN 分析如下:

发现,子查询没了,这样sql的优化就好了!

本文章为原创,未经允许不得转载