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