首页   注册   登录
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
宝塔
V2EX  ›  MySQL

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

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

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

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

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

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