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

技术咨询: Mysql 查询优化

  •  
  •   dielianxiang · 355 天前 · 3941 次点击
    这是一个创建于 355 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

    求大神赐教。

    第 1 条附言  ·  355 天前

    表结构

    |列 | 描述 | | ----------- | ----------- | | 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 条附言  ·  355 天前

    md 不支持table 贴一个图片

    表结构

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

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

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

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

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

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