技术咨询： Mysql 查询优化

[dielianxiang]

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

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

求大神赐教。

[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]

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