统计三分钟,五分钟,十分钟,半小时 表数据记录数:
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的优化就好了!
本文章为原创,未经允许不得转载