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

执行一条 Mysql 语句用了 40 分钟,好慢啊

  •  
  •   frozenway · 2018-02-24 11:22:57 +08:00 · 10746 次点击
    这是一个创建于 2467 天前的主题,其中的信息可能已经有所发展或是发生改变。

    mysql> SELECT count(*) AS tt,agent,ip,isp FROM sh_agent_log GROUP BY ip ORDER BY tt DESC LIMIT 20;

    ...

    20 rows in set (40 min 27.17 sec)

    想统计一下访问日记里面访问次数最多的 IP,没想到要查询这么久,一共有 30 万条数据而已,请问怎么优化查询

    56 条回复    2018-04-21 22:22:21 +08:00
    sonyxperia
        1
    sonyxperia  
       2018-02-24 11:23:57 +08:00   ❤️ 1
    楼主的耐心可以的……
    raccooncity
        2
    raccooncity  
       2018-02-24 11:25:47 +08:00 via iPhone
    所以建索引了吗。。。
    zhs227
        3
    zhs227  
       2018-02-24 11:30:44 +08:00
    不是应该直接 Ctrl+C 了吗。 耐心不错。explain 一下,看看是不是需要给 IP 字段加上索引。
    BBCCBB
        4
    BBCCBB  
       2018-02-24 11:39:10 +08:00
    厉害厉害,可以可以,666
    frozenway
        5
    frozenway  
    OP
       2018-02-24 11:40:41 +08:00
    @zhs227 explain 没提示要加索引
    toxicant
        6
    toxicant  
       2018-02-24 11:41:57 +08:00
    index 是个好东西啊.........
    LeeSeoung
        7
    LeeSeoung  
       2018-02-24 11:51:47 +08:00
    - -你确定不检查下么,不应该这么慢的。。
    owenliang
        8
    owenliang  
       2018-02-24 11:54:38 +08:00
    group by 本来就是全量计算,最后聚合后的桶数量决定了排序的耗时。

    你机器估计很普通吧? 30 万对于生产服务器做统计也是小 case。
    SourceMan
        9
    SourceMan  
       2018-02-24 12:00:49 +08:00
    没有什么是加一台机器不能解决的,如果有,加两台!
    frozenway
        10
    frozenway  
    OP
       2018-02-24 12:05:28 +08:00
    @owenliang 我的是在本地电脑执行的,win7,i5,8G 内存,2014 年买的

    @toxicant 给 30 万数据加 index,会卡死的
    murmur
        11
    murmur  
       2018-02-24 12:07:01 +08:00
    40w 数据这个速度怕是在软盘上跑出来的吧
    murmur
        12
    murmur  
       2018-02-24 12:07:38 +08:00
    @frozenway 30w 这个数据行扫都不会这么卡,百万级数据索引照加不误,以现在 mysql 的性能优化够单表抗 1000w 都可以吧
    Tink
        13
    Tink  
       2018-02-24 12:08:10 +08:00
    索引
    cisisustring
        14
    cisisustring  
       2018-02-24 12:17:21 +08:00
    mysql> SELECT count(*) AS tt,agent,ip,isp FROM sh_agent_log GROUP BY ip ORDER BY tt DESC LIMIT 20;
    我是分割线
    只 gropu by ip 不会报错吗?
    sagaxu
        15
    sagaxu  
       2018-02-24 12:21:10 +08:00 via Android
    @frozenway 2000 万我也加下来了
    HTNecro
        16
    HTNecro  
       2018-02-24 12:23:15 +08:00
    没啥说的,索引
    dobelee
        17
    dobelee  
       2018-02-24 12:25:09 +08:00
    40min 还没断开,楼主耐力惊人,佩服。
    gbin
        18
    gbin  
       2018-02-24 12:32:57 +08:00 via Android
    佩服
    abusizhishen
        19
    abusizhishen  
       2018-02-24 12:42:48 +08:00 via Android
    建议 IP 转成整型存储,并加索引,以降低索引长度提高索引效率
    abusizhishen
        20
    abusizhishen  
       2018-02-24 13:00:03 +08:00 via Android
    SELECT agent,ip,isp FROM sh_agent_log as a
    inner join
    (SELECT count(*) AS tt,id FROM sh_agent_log GROUP BY ip ORDER BY tt DESC LIMIT 20) as b
    on a.id = b.id;
    abusizhishen
        21
    abusizhishen  
       2018-02-24 13:00:21 +08:00 via Android
    事实上年的
    abusizhishen
        22
    abusizhishen  
       2018-02-24 13:00:54 +08:00 via Android
    😂😂😂打错了,试试上面的
    lygmqkl
        23
    lygmqkl  
       2018-02-24 13:07:09 +08:00
    mysql 金句 用小结果驱动大结果。 当然索引之类的 都要做到位,其实在业务逻辑层面上可以 规避这些风险的。
    liuxu
        24
    liuxu  
       2018-02-24 13:07:11 +08:00
    看上去直接原因还是临时磁盘表用的太多,io 跟不上,换 SSD 会好些。但正确的姿势还是应该加索引了。。
    alcarl
        25
    alcarl  
       2018-02-24 13:19:08 +08:00 via Android
    @cisisustring 不会,mysql 5.7 以下默认没有开这个检查
    alcarl
        26
    alcarl  
       2018-02-24 13:22:32 +08:00 via Android
    默认配置的话,可能是 sort 的 buff 有点小,innodb 的 buf 也有点小,都有影响,调大一点会好一些
    laqow
        27
    laqow  
       2018-02-24 13:33:18 +08:00 via Android
    tt 和 ip 的类型改一下,改成定长字符串或整型,或算一列 hash 再建索引,应该不会慢
    ty89
        28
    ty89  
       2018-02-24 13:40:17 +08:00
    建议楼主顺便把 create table sh_agent_log 的结果贴一下
    justfindu
        29
    justfindu  
       2018-02-24 13:43:38 +08:00
    不加索引也不应该这么慢啊 才 30W
    picasso250
        30
    picasso250  
       2018-02-24 13:54:03 +08:00
    如果 sh_agent_log 这个的数据还会不断增长,那么建议你用 redis 存 ip 记录吧.
    这个需要单独处理.

    如果 sh_agent_log 不再增长,你就是单独拿出来分析一下. 换个 SSD 吧.
    woscaizi
        31
    woscaizi  
       2018-02-24 14:15:47 +08:00 via iPhone
    看看类似 select * from table limit 0,20 的时间如何,然后再慢慢加 group by,看看哪个条件会导致慢。
    另外,磁盘 io 怎么样,是 ssd 还是 hdd ?
    rrfeng
        32
    rrfeng  
       2018-02-24 14:22:30 +08:00
    30w …… 给我个文本文件我用 awk 1s 内给你跑出来
    yaoweilei
        33
    yaoweilei  
       2018-02-24 14:35:53 +08:00
    30 万条,应该 40ms 内查出来哈,40 分钟实在是敬佩楼主的耐心。
    chcx
        34
    chcx  
       2018-02-24 14:39:16 +08:00
    佩服耐心。 index.
    frozenway
        35
    frozenway  
    OP
       2018-02-24 15:05:00 +08:00
    @abusizhishen 你这个语句执行报错了
    hanqi7012
        36
    hanqi7012  
       2018-02-24 15:12:35 +08:00 via Android
    40 分钟三十万有点慢啊
    那天下的库 4 亿也没这么慢啊
    frozenway
        37
    frozenway  
    OP
       2018-02-24 15:19:43 +08:00
    加了索引,然而还是很慢
    wooyu
        38
    wooyu  
       2018-02-24 15:41:44 +08:00   ❤️ 1
    @hanqi7012 我已经报警了
    SoulSleep
        39
    SoulSleep  
       2018-02-24 17:00:49 +08:00 via iPhone
    贴 explain 你发 sql 我只能吐槽 排序 group limit......能不慢吗…… 40min ?那就太多了
    shyy06
        40
    shyy06  
       2018-02-24 17:16:56 +08:00
    Mysql 从删库到跑路
    GeekCat
        41
    GeekCat  
       2018-02-24 17:21:46 +08:00
    30 万数据 40min....敢问硬盘 rpm 几百?
    abusizhishen
        42
    abusizhishen  
       2018-02-24 17:23:10 +08:00 via Android
    @frozenway 我手机上发的,错误贴出来
    abusizhishen
        43
    abusizhishen  
       2018-02-24 17:25:31 +08:00 via Android
    @frozenway 第二个 select 里少了个 IP 字段,加上试试
    fireapp
        44
    fireapp  
       2018-02-24 19:57:29 +08:00 via Android
    讲真,100w 条文本,awk 也就一分钟
    aias
        45
    aias  
       2018-02-24 20:30:48 +08:00
    好多懂 MySQL 的。。
    wwww961h
        46
    wwww961h  
       2018-02-24 20:39:51 +08:00
    30W 数据 40 分钟,你这机器性能挺好
    cxh116
        47
    cxh116  
       2018-02-24 20:45:21 +08:00 via Android
    有主键吗? count id,别 *
    troywinter
        48
    troywinter  
       2018-02-24 21:54:13 +08:00
    加索引,还有 late row query
    CodemonkeyM
        49
    CodemonkeyM  
       2018-02-24 22:49:43 +08:00 via Android
    不要*,加索引。
    wayne1027
        50
    wayne1027  
       2018-02-24 23:15:54 +08:00   ❤️ 1
    fatpa
        51
    fatpa  
       2018-02-24 23:29:07 +08:00
    加个索引不好么
    CodemonkeyM
        52
    CodemonkeyM  
       2018-02-25 00:05:53 +08:00 via Android
    @wayne1027 unless you are using MyISAM....
    usedname
        53
    usedname  
       2018-02-25 12:06:29 +08:00 via iPhone
    30w 用得着加索引?明显是机器烂的不行
    snBDX1b0jJM4ogKd
        54
    snBDX1b0jJM4ogKd  
       2018-02-25 21:37:08 +08:00 via Android
    @CodemonkeyM @wayne1027 count(*)实际上是计数所有不全为 null 的列。当然会比 count(1)慢一些
    dujiangbo
        55
    dujiangbo  
       2018-04-02 17:57:13 +08:00 via Android
    是不是存储引擎的原因?
    前两天我的库 COUNT 一下都几十秒,而且运行越来越慢,一怒之下都改成了 myisam,速度立马飞起。
    我的机器就是普通的 PC,数据几十万条吧。
    YMB
        56
    YMB  
       2018-04-21 22:22:21 +08:00
    数据库做查询的话,建议分表。
    或者把数据同步到 es 去查
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2379 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 00:02 · PVG 08:02 · LAX 16:02 · JFK 19:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.