不懂就问: mysql 中大数据量日环比计算时间太久 - V2EX
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
DavZhn
V2EX    MySQL

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

  •  
  •   DavZhn 2021-07-05 09:35:09 +08:00 2866 次点击
    这是一个创建于 1566 天前的主题,其中的信息可能已经有所发展或是发生改变。

    大概 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 

    大佬轻喷,不胜感激。

    12 条回复    2021-07-21 08:59:30 +08:00
    zoharSoul
        1
    zoharSoul  
       2021-07-05 10:05:20 +08:00
    上数仓, 大力出奇迹
    7Qi7Qi
        2
    7Qi7Qi  
       2021-07-05 10:23:10 +08:00
    不用子查询,用 with
    DavZhn
        3
    DavZhn  
    OP
       2021-07-05 10:37:39 +08:00
    @7Qi7Qi 5.7 貌似不支持 with ? 如果这样的话 又牵扯到版本升级的问题了。emmm 还是感谢你的建议
    BiChengfei
        4
    BiChengfei  
       2021-07-05 11:36:02 +08:00
    我觉得:
    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
        5
    DavZhn  
    OP
       2021-07-05 14:50:10 +08:00
    @BiChengfei Re:
    感谢提供的思路;
    1 、缓存的话 我们的查询条件要日期区间、大类( 9 类)、区域(网格或汇总)这些条件筛选,且汇总数据一定大于网格汇总,有部分不属于任何一个网格,所以在做缓存的时候是不是需要把所有的匹配条件枚举出来刷一遍?
    2 、我刚看了下 day 是有索引的,但是不会走,还是全表扫,是不是因为对 day 字段做了函数操作导致的。
    BiChengfei
        6
    BiChengfei  
       2021-07-06 11:35:15 +08:00
    @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
        7
    DavZhn  
    OP
       2021-07-06 14:58:51 +08:00
    @BiChengfei 感谢大佬,我尝试一下。
    512357301
        8
    512357301  
       2021-07-18 00:31:28 +08:00 via Android
    今天太晚了,我先说个思路,明天中午补 SQL,可以考虑一次性把 2 天的数据都取出来,然后在 select 的时候,用 sum(if(day=今天,1,0))的方式求和今天的数量,用 sum(if(day=昨天,1,0))的方式求和昨天的数量,然后第三个字段是环比
    这样就不用子查询和 left join 了

    (如果我刚才那个思路不行,只是针对你放出来的这个 SQL 来说,from 后面那个子查询和 left 后面那个子查询你都没限制时间范围,那么理论上会全表查询的,合计扫描两遍。。。,全表查完之后,你对派生表限制了时间范围,还是用函数计算的 day 。。。,你可以用>=或者<=啊,这样也会快一些)
    512357301
        9
    512357301  
       2021-07-18 19:04:39 +08:00 via Android
    这是我写的 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
        10
    512357301  
       2021-07-18 19:07:23 +08:00 via Android
    最后半段需要更正下:

    //缩小数据的查询范围
    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
        11
    512357301  
       2021-07-18 23:23:39 +08:00 via Android
    最后半段需要更正下:

    //缩小数据的查询范围
    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
        12
    DavZhn  
    OP
       2021-07-21 08:59:30 +08:00
    @512357301 感谢提供的思路。
    关于     帮助文档     自助推广系统     博客     API     FAQ     Solana     2603 人在线   最高记录 6679       Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 27ms UTC 12:30 PVG 20:30 LAX 05:30 JFK 08:30
    Do have faith in what you're doing.
    ubao msn snddm index pchome yahoo rakuten mypaper meadowduck bidyahoo youbao zxmzxm asda bnvcg cvbfg dfscv mmhjk xxddc yybgb zznbn ccubao uaitu acv GXCV ET GDG YH FG BCVB FJFH CBRE CBC GDG ET54 WRWR RWER WREW WRWER RWER SDG EW SF DSFSF fbbs ubao fhd dfg ewr dg df ewwr ewwr et ruyut utut dfg fgd gdfgt etg dfgt dfgd ert4 gd fgg wr 235 wer3 we vsdf sdf gdf ert xcv sdf rwer hfd dfg cvb rwf afb dfh jgh bmn lgh rty gfds cxv xcv xcs vdas fdf fgd cv sdf tert sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf shasha9178 shasha9178 shasha9178 shasha9178 shasha9178 liflif2 liflif2 liflif2 liflif2 liflif2 liblib3 liblib3 liblib3 liblib3 liblib3 zhazha444 zhazha444 zhazha444 zhazha444 zhazha444 dende5 dende denden denden2 denden21 fenfen9 fenf619 fen619 fenfe9 fe619 sdf sdf sdf sdf sdf zhazh90 zhazh0 zhaa50 zha90 zh590 zho zhoz zhozh zhozho zhozho2 lislis lls95 lili95 lils5 liss9 sdf0ty987 sdft876 sdft9876 sdf09876 sd0t9876 sdf0ty98 sdf0976 sdf0ty986 sdf0ty96 sdf0t76 sdf0876 df0ty98 sf0t876 sd0ty76 sdy76 sdf76 sdf0t76 sdf0ty9 sdf0ty98 sdf0ty987 sdf0ty98 sdf6676 sdf876 sd876 sd876 sdf6 sdf6 sdf9876 sdf0t sdf06 sdf0ty9776 sdf0ty9776 sdf0ty76 sdf8876 sdf0t sd6 sdf06 s688876 sd688 sdf86