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 · 203 天前 · 1373 次点击
    这是一个创建于 203 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    (如果我刚才那个思路不行,只是针对你放出来的这个 SQL 来说,from 后面那个子查询和 left 后面那个子查询你都没限制时间范围,那么理论上会全表查询的,合计扫描两遍。。。,全表查完之后,你对派生表限制了时间范围,还是用函数计算的 day 。。。,你可以用>=或者<=啊,这样也会快一些)
    512357301
        9
    512357301  
       189 天前 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  
       189 天前 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  
       189 天前 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
       187 天前
    @512357301 感谢提供的思路。
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   4170 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 05:52 · PVG 13:52 · LAX 21:52 · JFK 00:52
    ♥ Do have faith in what you're doing.