Mysql 一张表有 1400W 条数据,加了索引,查询一下也需要 2-5S。除了分库分表 有没有其他的好办法?
最重要的是我还需要 Group 里面的数据进行 Sum 操作,这时候就更慢了..
求大神赐教。
|列 | 描述 | | ----------- | ----------- | | 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")'
1
lqw3030 2018-12-24 08:19:23 +08:00 via iPhone
分表
|
2
Ehco1996 2018-12-24 08:27:00 +08:00
加 cache/换 nosql
|
3
tt67wq 2018-12-24 08:32:15 +08:00
explain 看下咯 如果索引都用上了还不行 那就要分片了
|
4
Vegetable 2018-12-24 08:45:12 +08:00 via Android
索引合适的话不至于这么慢吧,我觉得还是先尝试优化索引
|
5
sagaxu 2018-12-24 08:50:21 +08:00 via Android
1400 万就要分库分表,怕是对分库分表有什么误会
|
6
SoulSleep 2018-12-24 08:53:14 +08:00
1400w 2-5s 如果还走索引了,优化下 mysql 配置吧...
先 explain 看走不走索引,再看看服务器性能问题。 |
7
jowan 2018-12-24 08:59:40 +08:00
|
8
noahzh 2018-12-24 09:00:43 +08:00
走索引不代表就快,你 1400 万的数据,如果索引设计不好,索引出来数据有 1300 万,走不走索引都没有意义了.
还是那句话先把表结构贴出来再说. |
9
showecho 2018-12-24 09:03:05 +08:00
如果是 = 查询,加了索引应该秒出结果吧;
如果是 like 就会比较慢了; 个人感觉优化索引+服务器性能,用了很多服务器了,2 核和 4 核差别还是挺明显的 |
10
turan12 2018-12-24 09:04:14 +08:00
楼主你把 sql 语句和表结构贴出来看看
|
11
Allianzcortex 2018-12-24 09:10:32 +08:00
@Ehco1996 要优化的不是 web 的响应时间是数据库的查询时间。换 nosql 意义也不大,用不到切换 RDBMS 的程度
|
12
xe2vxe2v 2018-12-24 09:19:49 +08:00
加索引,和用索引还是有区别的,建议用 explain 命令查看 SQL 执行过程的结果分析
|
13
weizhen199 2018-12-24 09:36:56 +08:00
能不能具体点,如果 1400w 差 1 条走索引还要 2s 那肯定有问题的
|
14
dielianxiang OP @weizhen199 已经贴上去 求指教
|
15
dielianxiang OP @turan12 贴了
|
16
mooncakejs 2018-12-24 09:46:54 +08:00 via iPhone
group by sum 看起来 优化不了了,再怎么样也不会秒出的。 修改下结构吧,插入时计算触发器之类的,不然就定时汇总。
|
17
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 加索引 |
18
dielianxiang OP @showecho 用的是 RDS 一核 2G 的
|
19
septet 2018-12-24 09:47:50 +08:00 via iPhone
explain 分析下
|
20
mooncakejs 2018-12-24 09:48:36 +08:00 via iPhone
也可以试试把 group by 做成虚拟列
|
21
dielianxiang OP @mineqiqi created_at 的索引已经加了
|
22
septet 2018-12-24 09:50:53 +08:00 via iPhone
别在语句里用函数,会导致索引用不上
|
23
mineqiqi 2018-12-24 09:52:58 +08:00
@dielianxiang 不要格式化数据库字段 created_at,格式话你的查询条件 start 跟 end 跟 created_at 存储类型一致, 这样才不会破坏 created_at 索引,1400 万的数据 explain type 是 range 效率 1s 左右,你先看下 explain 有没有用到索引吧,不会很慢的
|
24
helone 2018-12-24 09:56:37 +08:00
表的设计如果正常的话,几千万记录不至于这样,索引用的不对 explain 看下吧
|
25
nananqujava 2018-12-24 09:57:56 +08:00 via Android
感觉楼主这个贴完全就是 MySQL 的典型入门优化了
|
26
VoidChen 2018-12-24 10:07:39 +08:00
看到这个表,顺便问一下,以前用 oracle 的时候,也有类似场景,但是数据库数据格式是日期类型的,索引貌似不生效,有没有大佬知道是为什么?
|
27
jason19659 2018-12-24 10:23:56 +08:00
换 nosql 或者是扔 ES 里查
|
28
eloah 2018-12-24 10:38:39 +08:00
MySQL 不至于这么差,做这个应该是没问题的
你给语句和表结构又不给全,让人很绝望啊 不过楼上说的,不要在 sql 里面做格式变换是对的,感觉你那个 Group 做的格式变换问题很大 当然你这个业务,感觉用时序性的数据库会更适合一些 |
29
Mmiracle110 2018-12-24 10:43:16 +08:00
还是如楼上说的,使用 explain 查看下索引的命中情况,看下查询的情况,根据实际情况进行优化
|
30
jakson 2018-12-24 10:49:31 +08:00
兄弟,你这个语句在 mysql 的 sql 层次优化不了,我估计这个查出来的数目比较多,即使用了索引,查询是很快,但是对查出的数据进行 sum 等聚合操作,就是慢,也没得办法。
|
31
weizhen199 2018-12-24 10:52:19 +08:00
首先 group by 都慢的。。
然后你这 group by 加函数索引吧 |
32
iyaozhen 2018-12-24 10:52:22 +08:00 via Android
简单看了下可能 DATE_FORMAT(a.created_at,"%H")这里有点问题,你是希望小时聚合是吧,建议搞成子查询。
先查出时间范围内的数据(也要看数据量了),然后 select 的时候转换成小时,再从 select 出的结果里面直接 group by。 |
33
ZCapping 2018-12-24 10:55:33 +08:00
23 楼正解。
|
34
jakson 2018-12-24 10:58:11 +08:00
个人觉得,分表分库可能没有用,首先,这个是做了索引的,查询是会比较快的,慢是因为对索引过滤的这一部分数据进行 group by 汇聚的时候慢,楼主可以看看慢的时候,不进行 group by,看看有多少条数据。
如果过滤出的数据比较大,2-5 秒,也是比较正常。 对于这种解决办法,一般都是想着硬件上的优化了,分库,采用分布式的可能有用,在多台服务器上,各自 group by 一部分,然后再对各个服务器上 group by 后的再进行汇聚。 不过这个也不一定快,万一各自 group by 后,各自的电脑的数据量还是很大,这样会有大量的网络 IO。 具体的情况,还得根据具体的数据情况来看 |
35
jakson 2018-12-24 11:00:36 +08:00
或者,在业务上另外想一个办法,再加一张表,专门用来维护
|
36
JQZhang 2018-12-24 11:15:51 +08:00
时间格式化是不是有问题啊,select 里是%m-%d 而 group by 里是%H
|
37
luoyou1014 2018-12-24 11:21:25 +08:00
再开一列,把 created_at 按小时格式化好,然后 group by rid, format_created_at 试下,新加的字段要并入之前的联合索引。
|
38
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") 这个写法显然比必需的开销大 |
39
byteli 2018-12-24 11:40:15 +08:00
explain 贴出来看下,先知道每一步多少数据量才好真正得出结论
|
40
Raymon111111 2018-12-24 11:43:53 +08:00
尽可能的语句简单, 东西拿到程序里算
|
41
realpg 2018-12-24 12:39:22 +08:00
进数据库的从来都是简单查询 逻辑在库外实现
一些统计类 适当使用触发器之类进行冗余计算 |
42
zeraba 2018-12-24 12:42:02 +08:00 via Android
force index(created_at) 试试
|
43
Marstin 2018-12-24 13:44:14 +08:00
sum(value)的值考虑缓存下来,走个定时任务去统计
|
44
dielianxiang OP 我先试一下 谢谢各位
|
45
chenqh 2018-12-24 16:05:26 +08:00
1400W 拼什么要分表。。
|
46
SakuraKuma 2018-12-24 16:36:29 +08:00
赞同#41,查出来在业务机上跑逻辑。
group by 这种耗时操作还是少用好。 |
47
dielianxiang OP 各位,我后来把阿里云的服务器的数据 down 到本地跑,同样的数据同样的 sql 语句,跑完只要 0.3s. 后来我就升级了一下数据库的配置( 1 核 2G 升级到 2 核 4G ),现在执行结果是 0.4S 。但是仍然比我本地慢。
后面我将继续根据大神的指点,将 group 操作放到业务逻辑里面去计算。谢谢各位了。 |
48
dielianxiang OP 前期进行 group by 是因为需要根据是时间 将各个 rid 的值进行累加。如果后面效率不行 我将考虑 group by 和 sum 操作由业务代码处理。但是数据量巨大,也许会有其他的问题。
|
49
leon0903 2018-12-24 17:39:07 +08:00
mark 我也算是 mysql 入门菜鸡
|
50
likuku 2018-12-24 20:52:22 +08:00
2 核 4G ... 现在真都这么省的么?几年前买二手服务器托管是直接上 16 核 16G RAM SSDx4 RAID10
|
51
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,其实几秒出结果已经可以了,因为都是会做缓存的。 |
52
palfortime 2018-12-24 22:47:54 +08:00 via Android
可以把 start 和 end 之间的时间按小时分割,分成几个 sql 进行查询,这样子可以去掉按小时来 group_by,查出来的数据量也不会多太多,又可以做成并发。
|
53
dielianxiang OP @likuku 哈哈 2 核 4g 够用了,前几天我还是用一核 2g
|
54
dielianxiang OP @akira 你是说数据库冗余这两个列么? DATE_FORMAT(a.created_at,"%m-%d") 和 DATE_FORMAT(a.created_at,"%H")
|