技术咨询： Mysql 查询优化

[dielianxiang]

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")'

md 不支持table 贴一个图片

数据库用的是 阿里云的 RDS

[lqw3030]

分表

[Ehco1996]

加 cache/换 nosql

[tt67wq]

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

[Vegetable]

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

[sagaxu]

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

[SoulSleep]

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

[jowan]

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

[noahzh]

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

[showecho]

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

[turan12]

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

[Allianzcortex]

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

[xe2vxe2v]

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

[weizhen199]

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

[dielianxiang]

@weizhen199 已经贴上去 求指教

[dielianxiang]

@turan12 贴了

[mooncakejs]

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

[mineqiqi]

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

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

[dielianxiang]

@showecho 用的是 RDS 一核 2G 的

[septet]

explain 分析下

[mooncakejs]

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

[dielianxiang]

@mineqiqi created_at 的索引已经加了

[septet]

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

[mineqiqi]

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

[helone]

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

[nananqujava]

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

[VoidChen]

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

[jason19659]

换 nosql 或者是扔 ES 里查

[eloah]

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

[Mmiracle110]

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

[jakson]

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

[weizhen199]

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

[iyaozhen]

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

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

[ZCapping]

23 楼正解。

[jakson]

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

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

[jakson]

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

[JQZhang]

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

[luoyou1014]

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

[CRVV]

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

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

[byteli]

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

[Raymon111111]

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

[realpg]

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

[zeraba]

force index(created_at) 试试

[Marstin]

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

[dielianxiang]

我先试一下 谢谢各位

[chenqh]

1400W 拼什么要分表。。

[SakuraKuma]

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

[dielianxiang]

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

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

[dielianxiang]

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

[leon0903]

mark 我也算是 mysql 入门菜鸡

[likuku]

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

[akira]

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

[palfortime]

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

[dielianxiang]

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

[dielianxiang]

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