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
182247236
V2EX  ›  MySQL

MySQL 查询数据太慢了,该怎么优化?

  •  
  •   182247236 · 2021-12-27 12:02:10 +08:00 · 7834 次点击
    这是一个创建于 1041 天前的主题,其中的信息可能已经有所发展或是发生改变。

    sql 执行语句 SELECT timestrap, bps FROM cdn_bandwidth WHERE (company_id = 1 AND domain_id IN (242,292,194,264,217,195,203,200,198,221,227,335,167,243,261,218,196,176,174,162,161,160,170,173,325,169,324,171,236,241,220,256,276,186,263,254,286,287,285,288,283,278,291,215,334,260,321,316,318,319,323,322,163,159,213,207,238,274,164,333,280,249,247,246,252,273,255,181,180,248,226,178,179,293,265,301,237,175,240,262,166,305,326,304,165,177,259,225,183,214,193,197,206,290,257,258,219,189,172,209,267,210,271,272,229,302,300,303,275,320,239,284,205,208,182,191,190,277,250,298,295,297,269,216,187,232,230,231,251,185,294,244,245,281,168,268,188,184,223,202,222,192,224,332,282,199,270,266,289,296,234,253,201,233,235,279,211,315,228,204,212) AND time BETWEEN '2021-12-01 00:00:00' AND '2021-12-26 23:59:59')

    查询出 1038259 条数据,共花费时间 125 秒。太长时间了。有没有办法能优化下。

    第 1 条附言  ·  2021-12-27 12:52:44 +08:00

    第 2 条附言  ·  2021-12-27 12:53:02 +08:00
    数据库表主要是用于存放域名的带宽数据,每个域名每 5 分钟一条数据,域名比较多,最多查询一个月数据,所以数量上就会比较多。
    第 3 条附言  ·  2021-12-27 12:55:17 +08:00
    因为查询最后需要合并数据结果,就查询的所有域名的相同时间的数据相加,这部因为觉得查询就很慢了,所以返回结果用 python 计算了
    第 4 条附言  ·  2021-12-27 12:56:51 +08:00

    索引

    第 5 条附言  ·  2021-12-27 14:19:43 +08:00

    第 6 条附言  ·  2021-12-30 17:04:12 +08:00
    因为数据库是在不熟悉,公司只有一位稍微懂点的同事,给我的意见都是分表,因为 IN 的条件太多了,无论如何都快不了的,所以还是用了最笨的方法,按天查询,每次返回的结果在用 pandas 处理,一个月的数据其实就是请求数据库 30 次,速度已经从 120s 降到了 20s ,虽然还是觉得太慢了,接下来试试能不能从多线程的思路解决,提高请求 mysql 的效率看看能不能再优化下,谢谢各位大佬。
    84 条回复    2022-01-02 16:11:26 +08:00
    harde
        1
    harde  
       2021-12-27 12:17:49 +08:00
    没索引吧?
    gadfly3173
        2
    gadfly3173  
       2021-12-27 12:18:32 +08:00
    https://github.com/XiaoMi/soar 可以试试这个 不过看你的数据量也优化不了什么了。。。
    jenlors
        3
    jenlors  
       2021-12-27 12:18:44 +08:00
    执行计划贴一下
    harde
        4
    harde  
       2021-12-27 12:18:54 +08:00
    单表数据量多大? 提问题的同时请让大家尽可能多的了解你的情况
    cheng6563
        5
    cheng6563  
       2021-12-27 12:19:03 +08:00
    给 time 上索引
    icaca
        6
    icaca  
       2021-12-27 12:25:06 +08:00
    125s 肯定没走索引,表也不大
    把所有列都加上索引。然后尽量把 in 换成 join 。
    优化到 100 毫秒应该没问题的。
    des
        7
    des  
       2021-12-27 12:27:39 +08:00 via iPhone   ❤️ 1
    “ 查询出 1038259 条数据” 好好想想,你页面需要展示这么多数据?
    cloverzrg2
        8
    cloverzrg2  
       2021-12-27 12:33:52 +08:00
    数据返回时间多少秒?
    182247236
        9
    182247236  
    OP
       2021-12-27 12:35:32 +08:00
    @des 查询出来的数据还需要写个 sum ,查询就这么慢了我就用 python 处理了,所以实际到最后的数据没有这么多的,就是查询这部太久了
    182247236
        10
    182247236  
    OP
       2021-12-27 12:37:49 +08:00
    @cloverzrg2 这个在哪里看?我是看 navcat 右下角 elapsed time 的
    182247236
        11
    182247236  
    OP
       2021-12-27 12:40:26 +08:00
    @harde 都加了 NORMAL BTREE 索引的
    182247236
        12
    182247236  
    OP
       2021-12-27 12:42:11 +08:00
    @harde 目前表单数量 11126889
    vanton
        13
    vanton  
       2021-12-27 12:42:16 +08:00
    explain 贴一下吧,你这样啥都看不出
    cloverzrg2
        14
    cloverzrg2  
       2021-12-27 12:42:27 +08:00
    @182247236 #10 可能有个 transfer data 之类的时间,你可以简单把 sql 改成 select (*) from 执行看一下时间
    kujio
        15
    kujio  
       2021-12-27 12:52:46 +08:00
    1.将数据按时间拆分成多个表:减少查询范围,
    2.查询结果看看在业务上能不能拆分:减少查询结果,
    182247236
        16
    182247236  
    OP
       2021-12-27 12:53:33 +08:00
    @long2ice 我这边贴出来了
    182247236
        17
    182247236  
    OP
       2021-12-27 12:55:32 +08:00
    @vanton 收到
    gesse
        18
    gesse  
       2021-12-27 12:56:56 +08:00
    按照你的表查询的内容、数据量来说, 其实应该可以从几个方面来优化:
    1. 按照历史日期分表
    2. 按数据活跃度分表
    3. 历史不会修改数据放入只读数据库,加快数据检索速度 https://help.aliyun.com/document_detail/26136.html
    ……
    等等
    182247236
        19
    182247236  
    OP
       2021-12-27 12:57:35 +08:00
    @icaca append 了,麻烦大佬看下
    harde
        20
    harde  
       2021-12-27 12:58:20 +08:00
    @182247236 贴一下 Explain 的。
    harde
        21
    harde  
       2021-12-27 12:59:13 +08:00
    索引是什么样子的,顺便也贴一下。即使单表上千万条,你这个业务查询也不需要这么久
    icaca
        22
    icaca  
       2021-12-27 13:02:38 +08:00   ❤️ 1
    一千万查一百万数据出来,走不走索引,差别都不大的
    icaca
        23
    icaca  
       2021-12-27 13:08:53 +08:00
    建议你在业务层优化下,单独起个服务做统计吧。
    rsyjjsn
        24
    rsyjjsn  
       2021-12-27 13:37:25 +08:00
    前端门外汉,不过我司貌似每天都在扯千万级别的数据量,解决都是分表分库
    liyunyang
        25
    liyunyang  
       2021-12-27 13:44:11 +08:00
    期待一下后续
    wolfie
        26
    wolfie  
       2021-12-27 13:52:51 +08:00
    贴 explain 啊。
    force 联合索引看看。
    lolizeppelin
        27
    lolizeppelin  
       2021-12-27 13:53:05 +08:00
    换 pg 上时序 哈哈哈哈
    duhui
        28
    duhui  
       2021-12-27 13:54:01 +08:00
    >每个域名每 5 分钟一条数据

    能不能每天统计一次, 然后一个月的话就算 30 天
    justfindu
        29
    justfindu  
       2021-12-27 13:59:44 +08:00   ❤️ 1
    不如另外跑一个数据归档吧. 比如按天归档, 这样数据量就可以降低 288 倍. 你这个查出来的数据量很大, 有没有索引真的差别不大了.
    Soar360
        30
    Soar360  
       2021-12-27 14:00:31 +08:00
    查询出 1038259 条数据

    这么多数据,仅仅是查出来,就算走索引网络传输也要好久了吧。我觉得你需要中间表了。
    zibber
        31
    zibber  
       2021-12-27 14:01:57 +08:00
    in 查询条件多了会不走索引
    akira
        32
    akira  
       2021-12-27 14:08:50 +08:00
    另外建一个每日数据汇总表,每天统计一次。
    每月的这个汇总表就可以直接基于每日的汇总就好了
    justicelove
        33
    justicelove  
       2021-12-27 14:13:37 +08:00
    1. 表按时间分区
    2. 试试对 domain_id 创建一个直方图
    roiding
        34
    roiding  
       2021-12-27 14:18:41 +08:00
    in 这么多字段 你确定走了索引?
    182247236
        35
    182247236  
    OP
       2021-12-27 14:19:55 +08:00
    @harde 好滴!
    182247236
        36
    182247236  
    OP
       2021-12-27 14:21:04 +08:00
    @wolfie 刚贴了 explain ,force 不太懂是啥 lol
    28Sv0ngQfIE7Yloe
        37
    28Sv0ngQfIE7Yloe  
       2021-12-27 14:25:00 +08:00
    看起来你需要一个 OLAP 数据库,或者离线处理?
    182247236
        38
    182247236  
    OP
       2021-12-27 14:25:15 +08:00
    我用 zabbix 或者别的一些数据监控,他们的都是 1 分钟存一条数据,数据量比我的大多了,怎么别人的查询就这么快...
    wolfie
        39
    wolfie  
       2021-12-27 14:32:04 +08:00
    @182247236 #36
    这索引就过滤一个 company_id

    from table
    force index() -- 这里添加 force ,括号值写 unique 索引全名
    where ... ...
    jenlors
        40
    jenlors  
       2021-12-27 14:38:33 +08:00
    为什么要使用 unque 类型的索引,试试强制使用那个联合索引,按理说可以使用到这个索引
    chengyunbo
        41
    chengyunbo  
       2021-12-27 14:41:18 +08:00
    表结构贴一下呢,看这个 explain ,time 没走到索引,另外千万级别的数据量,是要考虑中间表了,或者 es 这种,单从时间上看是 time 字段没走索引导致耗时很高
    clf
        42
    clf  
       2021-12-27 14:46:52 +08:00
    如果只写不删不更新,只用于统计。我觉得你可以考虑一下 clickhouse 用于存储此类数据。

    另外,数据计算,python 不一定比数据库快……建议直接在数据库 SQL 里完成统计,因为数据库和 python 间还有数据传输开销。
    182247236
        43
    182247236  
    OP
       2021-12-27 14:49:52 +08:00
    @long2ice 我查下联合索引怎么用
    182247236
        44
    182247236  
    OP
       2021-12-27 14:53:06 +08:00
    实在是头大,数据库知识不太懂,也是边做边学。有懂的请指教下!
    zengguibo
        45
    zengguibo  
       2021-12-27 14:55:26 +08:00
    这种最好优化了,就是按时间分表,一个月一张表,里面再加索引,到时候删除也方便
    182247236
        46
    182247236  
    OP
       2021-12-27 15:00:19 +08:00
    另外如果我查询 1 天的数据是飞快的,我还以为 30 天的数据也就是递增查询就完了
    RangerWolf
        47
    RangerWolf  
       2021-12-27 15:17:37 +08:00
    查询 1M 条数据,感觉无论如何也快不到哪里~ 如果可以使用 Clickhouse 之类的计算,可以直接计算汇总数据。速度要快的多~
    MySQL 本身确实不是很适合做这个事情。

    不知道这个表还有没有其他的字段,实在不行可以尝试给第一个索引增加新的字段,把 timestrap 跟 bps 都加到联合索引之中,这样就不需要回表查询,理论上会快很多。
    lscho
        48
    lscho  
       2021-12-27 15:24:11 +08:00
    数据库在哪?你在哪查的?
    182247236
        49
    182247236  
    OP
       2021-12-27 15:31:48 +08:00
    @lscho 数据库和我的服务器都在机房,很近的
    xhcarlin
        50
    xhcarlin  
       2021-12-27 15:34:23 +08:00
    如果有办法强制指定用那个联合索引,应该会快一些才对
    xinJang
        51
    xinJang  
       2021-12-27 15:35:48 +08:00
    我第一反应是子查询,果然数据库不行,泪奔
    xhcarlin
        52
    xhcarlin  
       2021-12-27 15:37:18 +08:00
    我现在的业务也有很多这类日志,不过用的是 mongodb ,单表也有 1500w+了。一般我们是做一个每日的中间表,然后数量就一下子下来了,python 这边压力也小一点
    182247236
        53
    182247236  
    OP
       2021-12-27 15:48:38 +08:00
    我现在检查到第一个问题,首先我查一天的所有数据需要的时间是 1s 左右,但是没走索引,所以我的索引是不是做的有问题?
    jenlors
        54
    jenlors  
       2021-12-27 16:41:56 +08:00
    jenlors
        55
    jenlors  
       2021-12-27 16:43:48 +08:00
    如果可以加下 limit 之类的,返回数据太多了
    freelancher
        56
    freelancher  
       2021-12-27 16:47:04 +08:00
    请个 DBA 。例如我。收费帮优化。
    neptuno
        57
    neptuno  
       2021-12-27 17:19:11 +08:00
    每天、或者每半天统计一次。然后再去计算一个月的
    w0017
        58
    w0017  
       2021-12-27 17:19:26 +08:00
    时间字段加索引,把 BETWEEN AND 换成大于小于
    zhoudaiyu
        59
    zhoudaiyu  
       2021-12-27 17:25:38 +08:00
    开个 SSH ,让我上去看看🐶
    rrfeng
        60
    rrfeng  
       2021-12-27 17:29:49 +08:00
    根本解决方案:上时序数据库。
    SQL 优化:加索引,预聚合,联合索引。
    SmiteChow
        61
    SmiteChow  
       2021-12-27 17:54:05 +08:00
    explain
    PeterZeng
        62
    PeterZeng  
       2021-12-27 17:56:46 +08:00
    你查询结果字段在没在联合索引里面,不在还要回表查那就很慢了
    fiypig
        63
    fiypig  
       2021-12-27 18:01:33 +08:00 via iPhone
    定时器报表统计啊
    chuann
        64
    chuann  
       2021-12-27 18:07:40 +08:00
    SELECT [...] FROM cdn_bandwidth WHERE [...] force index [explain 结果中那个 possible_keys]
    icaca
        65
    icaca  
       2021-12-28 07:47:52 +08:00
    我之前看错了 以为表有 100w 数据
    实际情况是查询结果有 100w 那基本上快不起来的(查询和传输)另外这么多数据应用端也容易挂掉 最好还是优化业务 降低返回的结果集的数量
    建议是
    尽量在数据库完成统计(我看业务并不复杂,应该 group by + sum ,就能搞定了吧?)
    实在不行就做一个统计的服务,最终效果肯定好很多,但是维护起来会麻烦一些(主要是害怕统计业务挂掉)
    void1900
        66
    void1900  
       2021-12-28 09:21:00 +08:00
    每个域名单独查
    opengps
        67
    opengps  
       2021-12-28 09:37:09 +08:00
    粗略的办法:where 用到哪些列,就给那些列来个组合索引
    yogapants
        68
    yogapants  
       2021-12-28 09:37:41 +08:00
    估计是数据太大,服务器硬件怎么样,数据库服务器是否是专机专用的有跑其他服务么,explain 一下看看啥原因,查询字段加索引尽量走覆盖索引,如果真的是数据量太大不行就上 TIDB 试一下。实在不行专门起一个定时服务项目跑统计服务,放到 redis 里面。
    vone
        69
    vone  
       2021-12-28 10:14:16 +08:00
    > 因为查询最后需要合并数据结果,就查询的所有域名的相同时间的数据相加,这部因为觉得查询就很慢了,所以返回结果用 python 计算了

    这句话应该是错的,sum 并不耗时,把 100w 行数据取回本地才是非常耗时的操作。
    nekoneko
        70
    nekoneko  
       2021-12-28 10:54:04 +08:00
    1. 明显的没走索引
    2. 先 count(*) 或者 sum 看看去掉数据传输用时多久,应该也不会快
    3. 联合索引去掉 domain_id,先 time 后 company_id 试试,考虑到回表把 timestrap 和 bps 也加到索引里面
    tyrantZhao
        71
    tyrantZhao  
       2021-12-28 11:13:08 +08:00
    返回 100w 数据感觉也正常吧,数据量太大了
    vinceall
        72
    vinceall  
       2021-12-28 11:13:46 +08:00
    company_id domain_id time 加索引,explain 看看,会不会锁表
    vinceall
        73
    vinceall  
       2021-12-28 11:17:16 +08:00
    @182247236 就是强制索引
    nekoneko
        74
    nekoneko  
       2021-12-28 11:24:32 +08:00
    @nekoneko #70 domain_id 好像还不能从索引中去掉,考虑下调换顺序,并且用 btree 类型的索引
    Protocol
        75
    Protocol  
       2021-12-28 12:09:16 +08:00
    解决了吗,楼主更新下问题进度
    MartinWu
        76
    MartinWu  
       2021-12-28 14:42:33 +08:00
    建议直接上 clickhouse ,加个 mysql engine 表,然后创建一个物化视图。
    liuhouer
        77
    liuhouer  
       2021-12-28 14:54:39 +08:00
    clickhouse + cloudcanal ,表用 replacemergetree ,设置自动 optimize ,0.1 秒返回结果,压缩率是 mysql 的 7+倍
    changs1986
        78
    changs1986  
       2021-12-29 11:59:25 +08:00
    看 explain, 走的 domain_id 索引, domain_id 区分度可能不太好. 建议调整下 time 的索引, 调整成 index(time, domain_id, company_id), 然后 where 改成 where time BETWEEN '2021-12-01 00:00:00' AND '2021-12-26 23:59:59' and domain_id in (xx,xx) and company_id=1
    182247236
        79
    182247236  
    OP
       2021-12-30 16:36:25 +08:00
    因为数据库是在不熟悉,公司只有一位稍微懂点的同事,给我的意见都是分表,因为 IN 的条件太多了,无论如何都快不了的,所以还是用了最笨的方法,按天查询,每次返回的结果在用 pandas 处理,一个月的数据其实就是请求数据库 30 次,速度已经从 120s 降到了 20s ,虽然还是觉得太慢了,接下来试试能不能从多线程的思路解决,提高请求 mysql 的效率看看能不能再优化下,谢谢各位大佬。
    ouxch
        80
    ouxch  
       2021-12-31 13:27:36 +08:00
    初看这个问题,从已提供的信息来看,只修改 sql 应该就能得到很大的优化,对索引调整可进一步优化。
    如不介意提供下:表结构语句、行数、期望输出、数据库实例(所在机器)的 CPU 核心数和内存大小
    orzwalker111
        81
    orzwalker111  
       2021-12-31 14:56:34 +08:00
    1.show index from t; 看下每个索引对应的统计行数 Cardinality
    2.建立联合索引(index_company_id_time_domin_id),company 和 time 哪个放左边,取决于谁的 Cardinality 大(统计数不太准确,domin_id 如果只是 in 操作使用的话,也可以不用放到联合索引,因为 5.6 有个”索引下推“技术能解决筛选问题)
    3.select timestrap, bps 这两个字段都不存在索引,所以每行记录都会回表——建议直接 select id ,然后再通过 ids 去查 timestrap, bps ,减少回表次数
    ----一个月 100 万数据,总表应该很大 rows 了,重新建立索引是个比较麻烦的事
    ----能联合索引就联合索引,别搞这么多单字段普通索引——都是坑
    orzwalker111
        82
    orzwalker111  
       2021-12-31 15:03:10 +08:00
    其实还有个问题,一个月 100w 数据,单靠这种方式去统计,数据量大了一定会有问题。考虑下增量方式处理,先确定好最终需要的统计结果模型,然后建立以天(或其他粒度)维度的新表记录;新表订阅原表的 DML 操作(业务代码),异步更新新表,最后统计、展示时只需要查新表就 OK 了
    zzmark06
        83
    zzmark06  
       2022-01-02 16:09:43 +08:00 via Android
    统计,建议扔了 mysql ,你这场景不适合
    考虑 hdfs ,clickhouse ,再来 10 倍量也能秒查
    zzmark06
        84
    zzmark06  
       2022-01-02 16:11:26 +08:00 via Android
    bps ,timestamp ,时序性数据上时序专用库也可以,总之比这个要好,现在这用法再怎么优化也快不起来的
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2657 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 12:15 · PVG 20:15 · LAX 05:15 · JFK 08:15
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.