V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
dielianxiang
V2EX  ›  数据库

技术咨询: Mysql 查询优化

  •  
  •   dielianxiang · 2018-12-24 08:08:12 +08:00 · 4800 次点击
    这是一个创建于 2146 天前的主题,其中的信息可能已经有所发展或是发生改变。

    Mysql 一张表有 1400W 条数据,加了索引,查询一下也需要 2-5S。除了分库分表 有没有其他的好办法?

    最重要的是我还需要 Group 里面的数据进行 Sum 操作,这时候就更慢了..

    求大神赐教。

    第 1 条附言  ·  2018-12-24 09:37:30 +08:00

    表结构

    |列 | 描述 | | ----------- | ----------- | | id | 主键 | | device_code | 设备编号 索引 | | rid | 设备参数ID,一个设备有N个参数 索引 | | value | 参数对应的值 | | created_at | 添加时间 索引 |

    查询语句

    查询指定时间内 设备所有的每小时的总和(PS: 数据每隔十分钟上传一次,每隔设备一次有12条,十二个参数值)

    'select a.rid,DATE_FORMAT(a.created_at,"%m-%d") as date,sum(value) as value from meyer_report 
     ' where device_code="'
    + device_code + '" and  created_at>="'
    + start +'" and created_at<="' + end + '" GROUP BY a.rid,DATE_FORMAT(a.created_at,"%H")'
    
    第 2 条附言  ·  2018-12-24 09:44:34 +08:00

    md 不支持table 贴一个图片

    表结构

    第 3 条附言  ·  2018-12-24 09:45:29 +08:00
    数据库用的是 阿里云的 RDS
    54 条回复    2018-12-25 08:48:30 +08:00
    lqw3030
        1
    lqw3030  
       2018-12-24 08:19:23 +08:00 via iPhone
    分表
    Ehco1996
        2
    Ehco1996  
       2018-12-24 08:27:00 +08:00
    加 cache/换 nosql
    tt67wq
        3
    tt67wq  
       2018-12-24 08:32:15 +08:00
    explain 看下咯 如果索引都用上了还不行 那就要分片了
    Vegetable
        4
    Vegetable  
       2018-12-24 08:45:12 +08:00 via Android
    索引合适的话不至于这么慢吧,我觉得还是先尝试优化索引
    sagaxu
        5
    sagaxu  
       2018-12-24 08:50:21 +08:00 via Android
    1400 万就要分库分表,怕是对分库分表有什么误会
    SoulSleep
        6
    SoulSleep  
       2018-12-24 08:53:14 +08:00
    1400w 2-5s 如果还走索引了,优化下 mysql 配置吧...
    先 explain 看走不走索引,再看看服务器性能问题。
    jowan
        7
    jowan  
       2018-12-24 08:59:40 +08:00
    1400W 还不至于分表分库
    /t/472324
    我之前的 650W 联合索引加 SUM 统计才 900ms
    现在 3000W 数据了也是这个速度
    建议检查下查询用到的索引和服务器性能
    noahzh
        8
    noahzh  
       2018-12-24 09:00:43 +08:00
    走索引不代表就快,你 1400 万的数据,如果索引设计不好,索引出来数据有 1300 万,走不走索引都没有意义了.
    还是那句话先把表结构贴出来再说.
    showecho
        9
    showecho  
       2018-12-24 09:03:05 +08:00
    如果是 = 查询,加了索引应该秒出结果吧;
    如果是 like 就会比较慢了;
    个人感觉优化索引+服务器性能,用了很多服务器了,2 核和 4 核差别还是挺明显的
    turan12
        10
    turan12  
       2018-12-24 09:04:14 +08:00
    楼主你把 sql 语句和表结构贴出来看看
    Allianzcortex
        11
    Allianzcortex  
       2018-12-24 09:10:32 +08:00
    @Ehco1996 要优化的不是 web 的响应时间是数据库的查询时间。换 nosql 意义也不大,用不到切换 RDBMS 的程度
    xe2vxe2v
        12
    xe2vxe2v  
       2018-12-24 09:19:49 +08:00
    加索引,和用索引还是有区别的,建议用 explain 命令查看 SQL 执行过程的结果分析
    weizhen199
        13
    weizhen199  
       2018-12-24 09:36:56 +08:00
    能不能具体点,如果 1400w 差 1 条走索引还要 2s 那肯定有问题的
    dielianxiang
        14
    dielianxiang  
    OP
       2018-12-24 09:46:16 +08:00
    @weizhen199 已经贴上去 求指教
    dielianxiang
        15
    dielianxiang  
    OP
       2018-12-24 09:46:34 +08:00
    @turan12 贴了
    mooncakejs
        16
    mooncakejs  
       2018-12-24 09:46:54 +08:00 via iPhone
    group by sum 看起来 优化不了了,再怎么样也不会秒出的。 修改下结构吧,插入时计算触发器之类的,不然就定时汇总。
    mineqiqi
        17
    mineqiqi  
       2018-12-24 09:47:20 +08:00
    DATE_FORMAT(a.created_at,"%m-%d") as date 不要格式化数据库字段,放到程序中处理,

    created_at>="'+ start +'" and created_at<="' + end start 和 end 格式化成数据库中 created_at 的存储格式多半是 datetime,给 created_at 加索引
    dielianxiang
        18
    dielianxiang  
    OP
       2018-12-24 09:47:49 +08:00
    @showecho 用的是 RDS 一核 2G 的
    septet
        19
    septet  
       2018-12-24 09:47:50 +08:00 via iPhone
    explain 分析下
    mooncakejs
        20
    mooncakejs  
       2018-12-24 09:48:36 +08:00 via iPhone
    也可以试试把 group by 做成虚拟列
    dielianxiang
        21
    dielianxiang  
    OP
       2018-12-24 09:48:52 +08:00
    @mineqiqi created_at 的索引已经加了
    septet
        22
    septet  
       2018-12-24 09:50:53 +08:00 via iPhone
    别在语句里用函数,会导致索引用不上
    mineqiqi
        23
    mineqiqi  
       2018-12-24 09:52:58 +08:00
    @dielianxiang 不要格式化数据库字段 created_at,格式话你的查询条件 start 跟 end 跟 created_at 存储类型一致, 这样才不会破坏 created_at 索引,1400 万的数据 explain type 是 range 效率 1s 左右,你先看下 explain 有没有用到索引吧,不会很慢的
    helone
        24
    helone  
       2018-12-24 09:56:37 +08:00
    表的设计如果正常的话,几千万记录不至于这样,索引用的不对 explain 看下吧
    nananqujava
        25
    nananqujava  
       2018-12-24 09:57:56 +08:00 via Android
    感觉楼主这个贴完全就是 MySQL 的典型入门优化了
    VoidChen
        26
    VoidChen  
       2018-12-24 10:07:39 +08:00
    看到这个表,顺便问一下,以前用 oracle 的时候,也有类似场景,但是数据库数据格式是日期类型的,索引貌似不生效,有没有大佬知道是为什么?
    jason19659
        27
    jason19659  
       2018-12-24 10:23:56 +08:00
    换 nosql 或者是扔 ES 里查
    eloah
        28
    eloah  
       2018-12-24 10:38:39 +08:00
    MySQL 不至于这么差,做这个应该是没问题的
    你给语句和表结构又不给全,让人很绝望啊
    不过楼上说的,不要在 sql 里面做格式变换是对的,感觉你那个 Group 做的格式变换问题很大
    当然你这个业务,感觉用时序性的数据库会更适合一些
    Mmiracle110
        29
    Mmiracle110  
       2018-12-24 10:43:16 +08:00
    还是如楼上说的,使用 explain 查看下索引的命中情况,看下查询的情况,根据实际情况进行优化
    jakson
        30
    jakson  
       2018-12-24 10:49:31 +08:00
    兄弟,你这个语句在 mysql 的 sql 层次优化不了,我估计这个查出来的数目比较多,即使用了索引,查询是很快,但是对查出的数据进行 sum 等聚合操作,就是慢,也没得办法。
    weizhen199
        31
    weizhen199  
       2018-12-24 10:52:19 +08:00
    首先 group by 都慢的。。
    然后你这 group by 加函数索引吧
    iyaozhen
        32
    iyaozhen  
       2018-12-24 10:52:22 +08:00 via Android
    简单看了下可能 DATE_FORMAT(a.created_at,"%H")这里有点问题,你是希望小时聚合是吧,建议搞成子查询。

    先查出时间范围内的数据(也要看数据量了),然后 select 的时候转换成小时,再从 select 出的结果里面直接 group by。
    ZCapping
        33
    ZCapping  
       2018-12-24 10:55:33 +08:00
    23 楼正解。
    jakson
        34
    jakson  
       2018-12-24 10:58:11 +08:00
    个人觉得,分表分库可能没有用,首先,这个是做了索引的,查询是会比较快的,慢是因为对索引过滤的这一部分数据进行 group by 汇聚的时候慢,楼主可以看看慢的时候,不进行 group by,看看有多少条数据。
    如果过滤出的数据比较大,2-5 秒,也是比较正常。

    对于这种解决办法,一般都是想着硬件上的优化了,分库,采用分布式的可能有用,在多台服务器上,各自 group by 一部分,然后再对各个服务器上 group by 后的再进行汇聚。 不过这个也不一定快,万一各自 group by 后,各自的电脑的数据量还是很大,这样会有大量的网络 IO。
    具体的情况,还得根据具体的数据情况来看
    jakson
        35
    jakson  
       2018-12-24 11:00:36 +08:00
    或者,在业务上另外想一个办法,再加一张表,专门用来维护
    JQZhang
        36
    JQZhang  
       2018-12-24 11:15:51 +08:00
    时间格式化是不是有问题啊,select 里是%m-%d 而 group by 里是%H
    luoyou1014
        37
    luoyou1014  
       2018-12-24 11:21:25 +08:00
    再开一列,把 created_at 按小时格式化好,然后 group by rid, format_created_at 试下,新加的字段要并入之前的联合索引。
    CRVV
        38
    CRVV  
       2018-12-24 11:29:05 +08:00
    1. 先查一下符合这个条件的记录有多少,你这个查询的总开销就取决于这部分有多少条记录
    where device_code= device_code and created_at>= start and created_at <= end
    这个过滤条件全都可以用索引,应该可以很快

    2. GROUP BY DATE_FORMAT(a.created_at, "%H")
    这个写法显然比必需的开销大
    byteli
        39
    byteli  
       2018-12-24 11:40:15 +08:00
    explain 贴出来看下,先知道每一步多少数据量才好真正得出结论
    Raymon111111
        40
    Raymon111111  
       2018-12-24 11:43:53 +08:00
    尽可能的语句简单, 东西拿到程序里算
    realpg
        41
    realpg  
       2018-12-24 12:39:22 +08:00
    进数据库的从来都是简单查询 逻辑在库外实现
    一些统计类 适当使用触发器之类进行冗余计算
    zeraba
        42
    zeraba  
       2018-12-24 12:42:02 +08:00 via Android
    force index(created_at) 试试
    Marstin
        43
    Marstin  
       2018-12-24 13:44:14 +08:00
    sum(value)的值考虑缓存下来,走个定时任务去统计
    dielianxiang
        44
    dielianxiang  
    OP
       2018-12-24 16:04:20 +08:00
    我先试一下 谢谢各位
    chenqh
        45
    chenqh  
       2018-12-24 16:05:26 +08:00
    1400W 拼什么要分表。。
    SakuraKuma
        46
    SakuraKuma  
       2018-12-24 16:36:29 +08:00
    赞同#41,查出来在业务机上跑逻辑。
    group by 这种耗时操作还是少用好。
    dielianxiang
        47
    dielianxiang  
    OP
       2018-12-24 17:30:09 +08:00
    各位,我后来把阿里云的服务器的数据 down 到本地跑,同样的数据同样的 sql 语句,跑完只要 0.3s. 后来我就升级了一下数据库的配置( 1 核 2G 升级到 2 核 4G ),现在执行结果是 0.4S 。但是仍然比我本地慢。

    后面我将继续根据大神的指点,将 group 操作放到业务逻辑里面去计算。谢谢各位了。
    dielianxiang
        48
    dielianxiang  
    OP
       2018-12-24 17:33:26 +08:00
    前期进行 group by 是因为需要根据是时间 将各个 rid 的值进行累加。如果后面效率不行 我将考虑 group by 和 sum 操作由业务代码处理。但是数据量巨大,也许会有其他的问题。
    leon0903
        49
    leon0903  
       2018-12-24 17:39:07 +08:00
    mark 我也算是 mysql 入门菜鸡
    likuku
        50
    likuku  
       2018-12-24 20:52:22 +08:00
    2 核 4G ... 现在真都这么省的么?几年前买二手服务器托管是直接上 16 核 16G RAM SSDx4 RAID10
    akira
        51
    akira  
       2018-12-24 22:06:46 +08:00
    增加 2 个字段,内容分别是
    DATE_FORMAT(a.created_at,"%m-%d")
    DATE_FORMAT(a.created_at,"%H")
    然后,sql 里面直接获取这个字段,不要再去计算了,这样查询速度应该可以有很大的提升
    -------------
    这种汇总统计需求的 sql,其实几秒出结果已经可以了,因为都是会做缓存的。
    palfortime
        52
    palfortime  
       2018-12-24 22:47:54 +08:00 via Android
    可以把 start 和 end 之间的时间按小时分割,分成几个 sql 进行查询,这样子可以去掉按小时来 group_by,查出来的数据量也不会多太多,又可以做成并发。
    dielianxiang
        53
    dielianxiang  
    OP
       2018-12-25 08:46:42 +08:00
    @likuku 哈哈 2 核 4g 够用了,前几天我还是用一核 2g
    dielianxiang
        54
    dielianxiang  
    OP
       2018-12-25 08:48:30 +08:00
    @akira 你是说数据库冗余这两个列么? DATE_FORMAT(a.created_at,"%m-%d") 和 DATE_FORMAT(a.created_at,"%H")
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   989 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 22:11 · PVG 06:11 · LAX 14:11 · JFK 17:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.