请教 MySQL 查询统计性能问题

[jowan]

遇到一个问题，现业务中有一张表如下，一共有 650W 条数据， cid = 1 有 9.6W 条数据，cid = 4 有 354W 条数据， 本地查询 A、C、D 的速度还可以接受，大概在 2s 左右， 查询 B 比较慢，耗费了 30s，

本人数据库优化这方面不是太懂， 请问一下各位这是什么原因，如何优化呢？

表结构

CREATE TABLE `wei_money_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `cid` tinyint(2) NOT NULL,
  `mid` int(11) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `money` decimal(16,2) NOT NULL,
  `log` varchar(120) NOT NULL,
  `addtime` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mid` (`mid`),
  KEY `cid` (`cid`),
  KEY `type` (`type`),
  KEY `money` (`money`),
  KEY `addtime` (`addtime`),
  KEY `log` (`log`),
  CONSTRAINT `wei_money_log_ibfk_1` FOREIGN KEY (`mid`) REFERENCES `wei_member` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询 A：

SELECT SUM(`money`) AS `money`
FROM `wei_money_log` 

查询 B：

SELECT SUM(`money`) AS `money`
FROM `wei_money_log` WHERE cid = 4

查询 C：

SELECT SUM(`money`) AS `money`
FROM `wei_money_log` WHERE cid = 1

查询 D：

SELECT SUM(`money`) AS `money`
FROM `wei_money_log` WHERE id < 10000000

[chenset]

这表全是索引...

[chenset]

B 的问题主要是 cid = 4 有 354W 条数据 导致的吧.

[jowan]

@chenset 实际业务中每个字段有检索条件 就都建了索引
刚试了下 id not in(1,2,3)的速度 就正常了 但是 = 4 就慢

[Rekkles]

你这个条件太宽泛 而且索引设置的也有问题 建议高频次查询数据存 redis

[likuku]

D ... < 10000000 ... 这得扫非常多的记录了，看起来这需求上也没办法了。

即时性很高？若即时要求不高（只需要给出前一个工作日 /上一个月），那么可以另外弄张表，
专门存储类似 table_name, sum 这样的 sum 记录表，每天深夜定时统计更新一次。

即时性很高，那么弄个触发器 /wei_money_log 这种数据变更事务里多加一个同时去更新 sum 记录表对应记录。

[lookatme]

总共才 650w，cid=4 都有 300w，用索引反而没有全表遍历快

[linpf]

@jowan not in 不应该是效率非常差吗？ 为什么会比直接=4 要快？ 我的世界观崩了

[jowan]

@linpf 当前这个场景 要快 而且快 15 倍多 耗时 2.8s ，直接=4，33s

[jowan]

@likuku < 10000000 这里只做个测试对比的，耗时 2.8s ，跟 cid=4 比起来 还是快多了

[likuku]

@jowan 不好意思，看走眼了...原来最慢的是 B 而不是 D ...

[linpf]

@jowan 这…… not in 不是不能利用到索引吗？ =4 不是可以利用到索引吗

[f4nyc]

因为 innodb 是聚簇索引啊，cid 是二级索引，先在二级索引查找到对应主键的值，然后再查聚簇索引，300w 次 O(logn)，当然没有直接遍历 O(n)来的快，差不多就是这个时间比
怀疑你的索引是无脑加的，推荐高性能 mysql

[f4nyc]

@linpf 聚簇索引了解一下

[jowan]

@f4nyc 因为实际业务中用到哪些检索条件就加了哪些索引，是无脑加的 (逃

[singer]

你的 cid,type,money 加索引没有意义啊。
可以按年份或者月份分表。都 30s 了，分表平均一下速度。
还有就是日志建议设置热点数据，只能查 6 个月内的数据

[glacer]

A 查询走的全表却比 B 查询快，原因是因为 A 用到了覆盖索引（即索引是 money 而查询字段也是 money，那么数据就可以直接在索引上获得了。B 查询用的索引是 cid 而字段还是 money，在索引到行 id 后还需要根据 id 查找数据）。
这里的要优化 B 的话可以按照这个思路，建一个(cid, money)的索引，这样 B 也就能用到覆盖索引了。
当然你这个表全是索引了...索引不是这样建的，这样索引会比数据还大，要根据实际查询的情况而定。

[jowan]

@singer 我也想限制 业务要求 哎。。。
@glacer 这是个后台统计账单功能，后台每个条件都能搭配检索，所以都无脑建了索引，加个 cid-money 的覆盖索引试试