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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

For Existing Member Sign In

› 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

This topic created in 2993 days ago, the information mentioned may be changed or developed.

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 replies • 2018-04-21 22:22:21 +08:00

sonyxperia

Feb 24, 2018

楼主的耐心可以的……

raccooncity

Feb 24, 2018 via iPhone

所以建索引了吗。。。

zhs227

Feb 24, 2018

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

BBCCBB

Feb 24, 2018

厉害厉害,可以可以,666

frozenway

Feb 24, 2018

@zhs227 explain 没提示要加索引

toxicant

Feb 24, 2018

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

LeeSeoung

Feb 24, 2018

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

owenliang

Feb 24, 2018

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

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

SourceMan

Feb 24, 2018

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

frozenway

Feb 24, 2018

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

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

murmur

Feb 24, 2018

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

murmur

Feb 24, 2018

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

Tink

PRO

Feb 24, 2018

索引

cisisustring

Feb 24, 2018

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

Feb 24, 2018 via Android

@frozenway 2000 万我也加下来了

HTNecro

Feb 24, 2018

没啥说的，索引

dobelee

Feb 24, 2018

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

gbin

Feb 24, 2018 via Android

佩服

abusizhishen

Feb 24, 2018 via Android

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

abusizhishen

Feb 24, 2018 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

Feb 24, 2018 via Android

事实上年的

abusizhishen

Feb 24, 2018 via Android

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

lygmqkl

Feb 24, 2018

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

liuxu

Feb 24, 2018

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

alcarl

Feb 24, 2018 via Android

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

alcarl

Feb 24, 2018 via Android

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

laqow

Feb 24, 2018 via Android

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

ty89

Feb 24, 2018

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

justfindu

Feb 24, 2018

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

picasso250

Feb 24, 2018

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

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

woscaizi

Feb 24, 2018 via iPhone

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

rrfeng

Feb 24, 2018

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

yaoweilei

Feb 24, 2018

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

chcx

Feb 24, 2018

佩服耐心。 index.

frozenway

Feb 24, 2018

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

hanqi7012

Feb 24, 2018 via Android

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

frozenway

Feb 24, 2018

加了索引，然而还是很慢

wooyu

Feb 24, 2018

@hanqi7012 我已经报警了

SoulSleep

Feb 24, 2018 via iPhone

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

shyy06

Feb 24, 2018

Mysql 从删库到跑路

GeekCat

Feb 24, 2018

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

abusizhishen

Feb 24, 2018 via Android

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

abusizhishen

Feb 24, 2018 via Android

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

fireapp

Feb 24, 2018 via Android

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

aias

Feb 24, 2018

好多懂 MySQL 的。。

wwww961h

Feb 24, 2018

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

cxh116

Feb 24, 2018 via Android

有主键吗？ count id，别 *

troywinter

Feb 24, 2018

加索引，还有 late row query

CodemonkeyM

Feb 24, 2018 via Android

不要*，加索引。

wayne1027

Feb 24, 2018

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

fatpa

Feb 24, 2018

加个索引不好么

CodemonkeyM

Feb 25, 2018 via Android

@wayne1027 unless you are using MyISAM....

usedname

Feb 25, 2018 via iPhone

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

snBDX1b0jJM4ogKd

Feb 25, 2018 via Android

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

dujiangbo

Apr 2, 2018 via Android

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

YMB

Apr 21, 2018

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