V2EX = way to explore

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

现在注册

已注册用户请登录

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

Mysql 一张表有 1400W 条数据，加了索引，查询一下也需要 2-5S。除了分库分表有没有其他的好办法？

最重要的是我还需要 Group 里面的数据进行 Sum 操作，这时候就更慢了..

求大神赐教。

第 1 条附言 · 2018-12-24 09:37:30 +08:00

表结构

|列 | 描述 | | ----------- | ----------- | | id | 主键 | | device_code | 设备编号索引 | | rid | 设备参数ID，一个设备有N个参数索引 | | value | 参数对应的值 | | created_at | 添加时间索引 |

查询语句

查询指定时间内设备所有的每小时的总和（PS：数据每隔十分钟上传一次，每隔设备一次有12条，十二个参数值）

'select a.rid,DATE_FORMAT(a.created_at,"%m-%d") as date,sum(value) as value from meyer_report 
 ' where device_code="'
+ device_code + '" and  created_at>="'
+ start +'" and created_at<="' + end + '" GROUP BY a.rid,DATE_FORMAT(a.created_at,"%H")'

第 2 条附言 · 2018-12-24 09:44:34 +08:00

md 不支持table 贴一个图片

表结构

第 3 条附言 · 2018-12-24 09:45:29 +08:00

数据库用的是阿里云的 RDS

MySQL

赐教

分库

查询

54 条回复 • 2018-12-25 08:48:30 +08:00

lqw3030

2018-12-24 08:19:23 +08:00 via iPhone

分表

Ehco1996

2018-12-24 08:27:00 +08:00

加 cache/换 nosql

tt67wq

2018-12-24 08:32:15 +08:00

explain 看下咯如果索引都用上了还不行那就要分片了

Vegetable

2018-12-24 08:45:12 +08:00 via Android

索引合适的话不至于这么慢吧，我觉得还是先尝试优化索引

sagaxu

2018-12-24 08:50:21 +08:00 via Android

1400 万就要分库分表，怕是对分库分表有什么误会

SoulSleep

2018-12-24 08:53:14 +08:00

1400w 2-5s 如果还走索引了，优化下 mysql 配置吧...
先 explain 看走不走索引，再看看服务器性能问题。

jowan

2018-12-24 08:59:40 +08:00

1400W 还不至于分表分库
/t/472324
我之前的 650W 联合索引加 SUM 统计才 900ms
现在 3000W 数据了也是这个速度
建议检查下查询用到的索引和服务器性能

noahzh

2018-12-24 09:00:43 +08:00

走索引不代表就快,你 1400 万的数据,如果索引设计不好,索引出来数据有 1300 万,走不走索引都没有意义了.
还是那句话先把表结构贴出来再说.

showecho

2018-12-24 09:03:05 +08:00

如果是 = 查询，加了索引应该秒出结果吧；
如果是 like 就会比较慢了；
个人感觉优化索引+服务器性能，用了很多服务器了，2 核和 4 核差别还是挺明显的

turan12

2018-12-24 09:04:14 +08:00

楼主你把 sql 语句和表结构贴出来看看

Allianzcortex

2018-12-24 09:10:32 +08:00

@Ehco1996 要优化的不是 web 的响应时间是数据库的查询时间。换 nosql 意义也不大，用不到切换 RDBMS 的程度

xe2vxe2v

2018-12-24 09:19:49 +08:00

加索引，和用索引还是有区别的，建议用 explain 命令查看 SQL 执行过程的结果分析

weizhen199

2018-12-24 09:36:56 +08:00

能不能具体点，如果 1400w 差 1 条走索引还要 2s 那肯定有问题的

dielianxiang

2018-12-24 09:46:16 +08:00

@weizhen199 已经贴上去求指教

dielianxiang

2018-12-24 09:46:34 +08:00

@turan12 贴了

mooncakejs

2018-12-24 09:46:54 +08:00 via iPhone

group by sum 看起来优化不了了，再怎么样也不会秒出的。修改下结构吧，插入时计算触发器之类的，不然就定时汇总。

mineqiqi

2018-12-24 09:47:20 +08:00

DATE_FORMAT(a.created_at,"%m-%d") as date 不要格式化数据库字段，放到程序中处理，

created_at>="'+ start +'" and created_at<="' + end start 和 end 格式化成数据库中 created_at 的存储格式多半是 datetime，给 created_at 加索引

dielianxiang

2018-12-24 09:47:49 +08:00

@showecho 用的是 RDS 一核 2G 的

septet

2018-12-24 09:47:50 +08:00 via iPhone

explain 分析下

mooncakejs

2018-12-24 09:48:36 +08:00 via iPhone

也可以试试把 group by 做成虚拟列

dielianxiang

2018-12-24 09:48:52 +08:00

@mineqiqi created_at 的索引已经加了

septet

2018-12-24 09:50:53 +08:00 via iPhone

别在语句里用函数，会导致索引用不上

mineqiqi

2018-12-24 09:52:58 +08:00

@dielianxiang 不要格式化数据库字段 created_at，格式话你的查询条件 start 跟 end 跟 created_at 存储类型一致，这样才不会破坏 created_at 索引，1400 万的数据 explain type 是 range 效率 1s 左右，你先看下 explain 有没有用到索引吧，不会很慢的

helone

2018-12-24 09:56:37 +08:00

表的设计如果正常的话，几千万记录不至于这样，索引用的不对 explain 看下吧

nananqujava

2018-12-24 09:57:56 +08:00 via Android

感觉楼主这个贴完全就是 MySQL 的典型入门优化了

VoidChen

2018-12-24 10:07:39 +08:00

看到这个表，顺便问一下，以前用 oracle 的时候，也有类似场景，但是数据库数据格式是日期类型的，索引貌似不生效，有没有大佬知道是为什么？

jason19659

2018-12-24 10:23:56 +08:00

换 nosql 或者是扔 ES 里查

eloah

2018-12-24 10:38:39 +08:00

MySQL 不至于这么差,做这个应该是没问题的
你给语句和表结构又不给全,让人很绝望啊
不过楼上说的,不要在 sql 里面做格式变换是对的,感觉你那个 Group 做的格式变换问题很大
当然你这个业务,感觉用时序性的数据库会更适合一些

Mmiracle110

2018-12-24 10:43:16 +08:00

还是如楼上说的，使用 explain 查看下索引的命中情况，看下查询的情况，根据实际情况进行优化

jakson

2018-12-24 10:49:31 +08:00

兄弟，你这个语句在 mysql 的 sql 层次优化不了，我估计这个查出来的数目比较多，即使用了索引，查询是很快，但是对查出的数据进行 sum 等聚合操作，就是慢，也没得办法。

weizhen199

2018-12-24 10:52:19 +08:00

首先 group by 都慢的。。
然后你这 group by 加函数索引吧

iyaozhen

2018-12-24 10:52:22 +08:00 via Android

简单看了下可能 DATE_FORMAT(a.created_at,"%H")这里有点问题，你是希望小时聚合是吧，建议搞成子查询。

先查出时间范围内的数据（也要看数据量了），然后 select 的时候转换成小时，再从 select 出的结果里面直接 group by。

ZCapping

2018-12-24 10:55:33 +08:00

23 楼正解。

jakson

2018-12-24 10:58:11 +08:00

个人觉得，分表分库可能没有用，首先，这个是做了索引的，查询是会比较快的，慢是因为对索引过滤的这一部分数据进行 group by 汇聚的时候慢，楼主可以看看慢的时候，不进行 group by，看看有多少条数据。
如果过滤出的数据比较大，2-5 秒，也是比较正常。

对于这种解决办法，一般都是想着硬件上的优化了，分库，采用分布式的可能有用，在多台服务器上，各自 group by 一部分，然后再对各个服务器上 group by 后的再进行汇聚。不过这个也不一定快，万一各自 group by 后，各自的电脑的数据量还是很大，这样会有大量的网络 IO。
具体的情况，还得根据具体的数据情况来看

jakson

2018-12-24 11:00:36 +08:00

或者，在业务上另外想一个办法，再加一张表，专门用来维护

JQZhang

2018-12-24 11:15:51 +08:00

时间格式化是不是有问题啊，select 里是%m-%d 而 group by 里是%H

luoyou1014

2018-12-24 11:21:25 +08:00

再开一列，把 created_at 按小时格式化好，然后 group by rid, format_created_at 试下，新加的字段要并入之前的联合索引。

CRVV

2018-12-24 11:29:05 +08:00

1. 先查一下符合这个条件的记录有多少，你这个查询的总开销就取决于这部分有多少条记录
where device_code= device_code and created_at>= start and created_at <= end
这个过滤条件全都可以用索引，应该可以很快

2. GROUP BY DATE_FORMAT(a.created_at, "%H")
这个写法显然比必需的开销大

byteli

2018-12-24 11:40:15 +08:00

explain 贴出来看下，先知道每一步多少数据量才好真正得出结论

Raymon111111

2018-12-24 11:43:53 +08:00

尽可能的语句简单, 东西拿到程序里算

realpg

2018-12-24 12:39:22 +08:00

进数据库的从来都是简单查询逻辑在库外实现
一些统计类适当使用触发器之类进行冗余计算

zeraba

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

force index(created_at) 试试

Marstin

2018-12-24 13:44:14 +08:00

sum(value)的值考虑缓存下来，走个定时任务去统计

dielianxiang

2018-12-24 16:04:20 +08:00

我先试一下谢谢各位

chenqh

2018-12-24 16:05:26 +08:00

1400W 拼什么要分表。。

SakuraKuma

2018-12-24 16:36:29 +08:00

赞同#41，查出来在业务机上跑逻辑。
group by 这种耗时操作还是少用好。

dielianxiang

2018-12-24 17:30:09 +08:00

各位，我后来把阿里云的服务器的数据 down 到本地跑，同样的数据同样的 sql 语句，跑完只要 0.3s. 后来我就升级了一下数据库的配置（ 1 核 2G 升级到 2 核 4G ），现在执行结果是 0.4S 。但是仍然比我本地慢。

后面我将继续根据大神的指点，将 group 操作放到业务逻辑里面去计算。谢谢各位了。

dielianxiang

2018-12-24 17:33:26 +08:00

前期进行 group by 是因为需要根据是时间将各个 rid 的值进行累加。如果后面效率不行我将考虑 group by 和 sum 操作由业务代码处理。但是数据量巨大，也许会有其他的问题。

leon0903

2018-12-24 17:39:07 +08:00

mark 我也算是 mysql 入门菜鸡

likuku

2018-12-24 20:52:22 +08:00

2 核 4G ... 现在真都这么省的么？几年前买二手服务器托管是直接上 16 核 16G RAM SSDx4 RAID10

akira

2018-12-24 22:06:46 +08:00

增加 2 个字段，内容分别是
DATE_FORMAT(a.created_at,"%m-%d")
DATE_FORMAT(a.created_at,"%H")
然后，sql 里面直接获取这个字段，不要再去计算了，这样查询速度应该可以有很大的提升
-------------
这种汇总统计需求的 sql，其实几秒出结果已经可以了，因为都是会做缓存的。

palfortime

2018-12-24 22:47:54 +08:00 via Android

可以把 start 和 end 之间的时间按小时分割，分成几个 sql 进行查询，这样子可以去掉按小时来 group_by，查出来的数据量也不会多太多，又可以做成并发。

dielianxiang

2018-12-25 08:46:42 +08:00

@likuku 哈哈 2 核 4g 够用了，前几天我还是用一核 2g

dielianxiang

2018-12-25 08:48:30 +08:00

@akira 你是说数据库冗余这两个列么？ DATE_FORMAT(a.created_at,"%m-%d") 和 DATE_FORMAT(a.created_at,"%H")

技术咨询： Mysql 查询优化

表结构

查询语句

md 不支持table 贴一个图片