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

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

这是一个创建于 2450 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

2018-02-24 11:23:57 +08:00

楼主的耐心可以的……

raccooncity

2018-02-24 11:25:47 +08:00 via iPhone

所以建索引了吗。。。

zhs227

2018-02-24 11:30:44 +08:00

不是应该直接 Ctrl+C 了吗。耐心不错。explain 一下，看看是不是需要给 IP 字段加上索引。

BBCCBB

2018-02-24 11:39:10 +08:00

厉害厉害,可以可以,666

frozenway

2018-02-24 11:40:41 +08:00

@zhs227 explain 没提示要加索引

toxicant

2018-02-24 11:41:57 +08:00

index 是个好东西啊.........

LeeSeoung

2018-02-24 11:51:47 +08:00

- -你确定不检查下么，不应该这么慢的。。

owenliang

2018-02-24 11:54:38 +08:00

group by 本来就是全量计算，最后聚合后的桶数量决定了排序的耗时。

你机器估计很普通吧？ 30 万对于生产服务器做统计也是小 case。

SourceMan

2018-02-24 12:00:49 +08:00

没有什么是加一台机器不能解决的，如果有，加两台！

frozenway

2018-02-24 12:05:28 +08:00

@owenliang 我的是在本地电脑执行的，win7，i5，8G 内存，2014 年买的

@toxicant 给 30 万数据加 index，会卡死的

murmur

2018-02-24 12:07:01 +08:00

40w 数据这个速度怕是在软盘上跑出来的吧

murmur

2018-02-24 12:07:38 +08:00

@frozenway 30w 这个数据行扫都不会这么卡，百万级数据索引照加不误，以现在 mysql 的性能优化够单表抗 1000w 都可以吧

Tink

2018-02-24 12:08:10 +08:00

索引

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

2018-02-24 12:21:10 +08:00 via Android

@frozenway 2000 万我也加下来了

HTNecro

2018-02-24 12:23:15 +08:00

没啥说的，索引

dobelee

2018-02-24 12:25:09 +08:00

40min 还没断开，楼主耐力惊人，佩服。

gbin

2018-02-24 12:32:57 +08:00 via Android

佩服

abusizhishen

2018-02-24 12:42:48 +08:00 via Android

建议 IP 转成整型存储，并加索引，以降低索引长度提高索引效率

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

2018-02-24 13:00:21 +08:00 via Android

事实上年的

abusizhishen

2018-02-24 13:00:54 +08:00 via Android

😂😂😂打错了，试试上面的

lygmqkl

2018-02-24 13:07:09 +08:00

mysql 金句用小结果驱动大结果。当然索引之类的都要做到位，其实在业务逻辑层面上可以规避这些风险的。

liuxu

2018-02-24 13:07:11 +08:00

看上去直接原因还是临时磁盘表用的太多，io 跟不上，换 SSD 会好些。但正确的姿势还是应该加索引了。。

alcarl

2018-02-24 13:19:08 +08:00 via Android

@cisisustring 不会，mysql 5.7 以下默认没有开这个检查

alcarl

2018-02-24 13:22:32 +08:00 via Android

默认配置的话，可能是 sort 的 buff 有点小，innodb 的 buf 也有点小，都有影响，调大一点会好一些

laqow

2018-02-24 13:33:18 +08:00 via Android

tt 和 ip 的类型改一下，改成定长字符串或整型，或算一列 hash 再建索引，应该不会慢

ty89

2018-02-24 13:40:17 +08:00

建议楼主顺便把 create table sh_agent_log 的结果贴一下

justfindu

2018-02-24 13:43:38 +08:00

不加索引也不应该这么慢啊才 30W

picasso250

2018-02-24 13:54:03 +08:00

如果 sh_agent_log 这个的数据还会不断增长,那么建议你用 redis 存 ip 记录吧.
这个需要单独处理.

如果 sh_agent_log 不再增长,你就是单独拿出来分析一下. 换个 SSD 吧.

woscaizi

2018-02-24 14:15:47 +08:00 via iPhone

看看类似 select * from table limit 0,20 的时间如何，然后再慢慢加 group by，看看哪个条件会导致慢。
另外，磁盘 io 怎么样，是 ssd 还是 hdd ？

rrfeng

2018-02-24 14:22:30 +08:00

30w …… 给我个文本文件我用 awk 1s 内给你跑出来

yaoweilei

2018-02-24 14:35:53 +08:00

30 万条，应该 40ms 内查出来哈，40 分钟实在是敬佩楼主的耐心。

chcx

2018-02-24 14:39:16 +08:00

佩服耐心。 index.

frozenway

2018-02-24 15:05:00 +08:00

@abusizhishen 你这个语句执行报错了

hanqi7012

2018-02-24 15:12:35 +08:00 via Android

40 分钟三十万有点慢啊
那天下的库 4 亿也没这么慢啊

frozenway

2018-02-24 15:19:43 +08:00

加了索引，然而还是很慢

wooyu

2018-02-24 15:41:44 +08:00

@hanqi7012 我已经报警了

SoulSleep

2018-02-24 17:00:49 +08:00 via iPhone

贴 explain 你发 sql 我只能吐槽排序 group limit......能不慢吗…… 40min ？那就太多了

shyy06

2018-02-24 17:16:56 +08:00

Mysql 从删库到跑路

GeekCat

2018-02-24 17:21:46 +08:00

30 万数据 40min....敢问硬盘 rpm 几百？

abusizhishen

2018-02-24 17:23:10 +08:00 via Android

@frozenway 我手机上发的，错误贴出来

abusizhishen

2018-02-24 17:25:31 +08:00 via Android

@frozenway 第二个 select 里少了个 IP 字段，加上试试

fireapp

2018-02-24 19:57:29 +08:00 via Android

讲真，100w 条文本，awk 也就一分钟

aias

2018-02-24 20:30:48 +08:00

好多懂 MySQL 的。。

wwww961h

2018-02-24 20:39:51 +08:00

30W 数据 40 分钟，你这机器性能挺好

cxh116

2018-02-24 20:45:21 +08:00 via Android

有主键吗？ count id，别 *

troywinter

2018-02-24 21:54:13 +08:00

加索引，还有 late row query

CodemonkeyM

2018-02-24 22:49:43 +08:00 via Android

不要*，加索引。

wayne1027

2018-02-24 23:15:54 +08:00

@cxh116 @CodemonkeyM，count(*) 没毛病
https://stackoverflow.com/questions/5179969/what-is-better-in-mysql-count-or-count1

fatpa

2018-02-24 23:29:07 +08:00

加个索引不好么

CodemonkeyM

2018-02-25 00:05:53 +08:00 via Android

@wayne1027 unless you are using MyISAM....

usedname

2018-02-25 12:06:29 +08:00 via iPhone

30w 用得着加索引？明显是机器烂的不行

snBDX1b0jJM4ogKd

2018-02-25 21:37:08 +08:00 via Android

@CodemonkeyM @wayne1027 count(*)实际上是计数所有不全为 null 的列。当然会比 count(1)慢一些

dujiangbo

2018-04-02 17:57:13 +08:00 via Android

是不是存储引擎的原因？
前两天我的库 COUNT 一下都几十秒，而且运行越来越慢，一怒之下都改成了 myisam，速度立马飞起。
我的机器就是普通的 PC，数据几十万条吧。

YMB

2018-04-21 22:22:21 +08:00

数据库做查询的话，建议分表。
或者把数据同步到 es 去查