不懂就问： mysql 中大数据量日环比计算时间太久

[DavZhn]

大概 765W 的数据，单表查询，需要频繁的计算某字段的日环比，目前 SQL 计算时间超过 30s，请各位大佬指点迷津；

需求：在总表中查询某个月的日环比

目前方案：

• 1 、直接查，时间超过 30s，pass
• 2 、将计算结果再次落表，查计算结果表，由于筛选条件众多，且落表 SQL 分条件查询结果后落表时间久也影响使用，待定；

目前 SQL：

SELECT
	right(t.day,2) AS day,
	t.R11 as num,
	y.R11 ynum,
	CASE
	WHEN y.R11 IS NULL
	OR y.R11 = 0 THEN
	0.00 ELSE round((t.R11/y.R11)-1, 2 )
END cc

FROM
( SELECT day, CONVERT (R11 , DECIMAL) as R11 FROM 原始数据表 ) t

LEFT JOIN
( SELECT REPLACE(date_add( day, INTERVAL 1 DAY ),"-","") tomorrow, CONVERT (R11 , DECIMAL) as R11 FROM 原始数据表 ) y ON t.day = y.tomorrow

where left(t.day,6) = concat(#{year},#{month})

order by t.day

大佬轻喷，不胜感激。

[zoharSoul]

上数仓, 大力出奇迹

[7Qi7Qi]

不用子查询，用 with

[DavZhn]

@7Qi7Qi 5.7 貌似不支持 with ？ 如果这样的话 又牵扯到版本升级的问题了。emmm 还是感谢你的建议

[BiChengfei]

我觉得：
1. 可以做一个缓存视图(view)，用来保存统计结果，实现：写一个存储过程， 当有数据新增的时候执行统计 sql(你发出来的那个)， 然后代码直接从视图中查询结果 -- 这种就是缓存的思路，redis 缓存也可以
2. day 字段加索引(没有测试，我觉得这样会快一点)
```
SELECT
DATE_FORMAT(t.day, '%d'),
t.R11 as num,
y.R11 ynum,
CASE WHEN y.R11 IS NULL OR y.R11 = 0 THEN 0.00 ELSE round((t.R11/y.R11)-1, 2 ) END cc
FROM
(SELECT STR_TO_DATE(day,'%Y-%m-%d') as day, CONVERT(R11, DECIMAL) as R11 FROM 原始数据表 ) t
left JOIN ( SELECT date_add(STR_TO_DATE(day,'%Y-%m-%d'), INTERVAL 1 DAY) as tomorrow, CONVERT(R11, DECIMAL) as R11 FROM 原始数据表 ) y ON t.day = y.tomorrow
where t.day BETWEEN #{startTime} and #{endTime}
order by t.day
```

几百万条数据对 mysql 来说洒洒水啊，完全有优化空间

[DavZhn]

@BiChengfei Re:
感谢提供的思路；
1 、缓存的话 我们的查询条件要日期区间、大类（ 9 类）、区域（网格或汇总）这些条件筛选，且汇总数据一定大于网格汇总，有部分不属于任何一个网格，所以在做缓存的时候是不是需要把所有的匹配条件枚举出来刷一遍？
2 、我刚看了下 day 是有索引的，但是不会走，还是全表扫，是不是因为对 day 字段做了函数操作导致的。

[BiChengfei]

@DavZhn
昨天的思路不太好，缓存你可以考虑。
今天有另一个思路，不知道你表中的 day 的数据格式，不过可以加工下，然后加个 tomorrow 字段，再创建合适索引，查询语句把 order by 去掉（因为 explain 中有 Using filesort，排序可以前端或者后端做一下，不过影响好像不大）
如果原始表结构不能变动，那就新建一个专门来查询的表，以前我们大数据量就是构件冗余表，专门用来查询
本地测试 DDL:
-- day 、add_day 字段都是 yyyy-MM-dd 格式，本地有 6000 条数据，这样改造后，效率从 20 s 变成了 300 ms 内
CREATE TABLE `t_v2_data`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`day` varchar(200) DEFAULT NULL,
`R11` varchar(200) DEFAULT NULL,
`add_day` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_day_R11_add_day` (`day`, `R11`, `add_day`)
) ENGINE = InnoDB AUTO_INCREMENT = 6001 DEFAULT CHARSET = latin1;

查询语句：
SELECT
DATE_FORMAT(t.day, '%d'),
t.R11 as num,
y.R11 ynum,
CASE WHEN y.R11 IS NULL OR y.R11 = 0 THEN 0.00 ELSE round((t.R11/y.R11)-1, 2 ) END cc
FROM
(SELECT day, R11 FROM t_v2_data ) t
left JOIN ( SELECT add_day, R11 FROM t_v2_data ) y ON t.day = y.add_day
where t.day between '2020-5-01' and '2020-5-30'

[DavZhn]

@BiChengfei 感谢大佬，我尝试一下。

[512357301]

今天太晚了，我先说个思路，明天中午补 SQL，可以考虑一次性把 2 天的数据都取出来，然后在 select 的时候，用 sum(if(day=今天,1,0))的方式求和今天的数量，用 sum(if(day=昨天,1,0))的方式求和昨天的数量，然后第三个字段是环比
这样就不用子查询和 left join 了

(如果我刚才那个思路不行，只是针对你放出来的这个 SQL 来说，from 后面那个子查询和 left 后面那个子查询你都没限制时间范围，那么理论上会全表查询的，合计扫描两遍。。。，全表查完之后，你对派生表限制了时间范围，还是用函数计算的 day 。。。，你可以用＞＝或者＜＝啊，这样也会快一些)

[512357301]

这是我写的 SQL(不过一次只能查一天的)：

SELECT
right(t.day,2) AS day_of_month,
sum(if(day = 20210716,CONVERT(t.R11,DECIMAL),1,0)) as qiantian_num,
sum(if(day = 20210717,CONVERT(t.R11,DECIMAL),1,0)) as zuotian_num,
sum(if(day = 20210717,CONVERT(t.R11,DECIMAL),1,0)) / sum(if(day = 20210716,CONVERT(t.R11,DECIMAL),1,0)) -1 as huanbi
concat(round(sum(if(day = 20210717,CONVERT(t.R11,DECIMAL),1,0)) / sum(if(day = 20210716,CONVERT(t.R11,DECIMAL),1,0)) -1,4)*100,'%') as huanbi_baifenbi
FROM 原始数据表 t
where t.day between 20210716 and 20210715
group by right(t.day,2)


如果只是改你的原始 SQL 的话，我觉得应该这么改下，可能会快一些：
SELECT
right(t.day,2) AS day,
CONVERT (t.R11 , DECIMAL) as num,
y.R11 ynum,
CASE
WHEN y.R11 IS NULL OR y.R11 = 0 THEN 0.00 ELSE round((t.R11/y.R11)-1, 2 )
END cc
FROM 原始数据表 t
LEFT JOIN(
SELECT
REPLACE(date_add( day, INTERVAL 1 DAY ),"-","") tomorrow
,CONVERT (R11,DECIMAL) as R11
FROM 原始数据表 t2
//缩小数据的查询范围
where t2.day between REPLACE(DATE_SUB(CONCAT(#{year},'-',#{month},'-',01),"-",""), INTERVAL 1 DAY ) and concat(#{year},#{month},#{day})
) y ON t.day = y.tomorrow
where t.day between concat(#{year},#{month},01) and concat(#{year},#{month},#{day})
order by t.day

[512357301]

最后半段需要更正下：

//缩小数据的查询范围
where t2.day between REPLACE(DATE_SUB(CONCAT(#{year},'-',#{month},'-',01), INTERVAL 1 DAY ),"-","") and concat(#{year},#{month},#{day})
) y ON t.day = y.tomorrow

[512357301]

最后半段需要更正下：

//缩小数据的查询范围
where t2.day between REPLACE(DATE_SUB(CONCAT(#{year},'-',#{month},'-',01), INTERVAL 1 DAY ),"-","") and concat(#{year},#{month},#{day})
) y ON t.day = y.tomorrow
where t.day between concat(#{year},#{month},01) and concat(#{year},#{month},#{day})
order by t.day

[DavZhn]

@512357301 感谢提供的思路。