遇到一个问题,现业务中有一张表如下,一共有 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
1
chenset 2018-07-19 14:15:36 +08:00
这表全是索引...
|
2
chenset 2018-07-19 14:16:52 +08:00
B 的问题主要是 cid = 4 有 354W 条数据 导致的吧.
|
3
jowan OP @chenset 实际业务中每个字段有检索条件 就都建了索引
刚试了下 id not in(1,2,3)的速度 就正常了 但是 = 4 就慢 |
4
Rekkles 2018-07-19 14:19:50 +08:00
你这个条件太宽泛 而且索引设置的也有问题 建议高频次查询数据存 redis
|
5
likuku 2018-07-19 14:25:55 +08:00
D ... < 10000000 ... 这得扫非常多的记录了,看起来这需求上也没办法了。
即时性很高?若即时要求不高(只需要给出前一个工作日 /上一个月),那么可以另外弄张表, 专门存储类似 table_name, sum 这样的 sum 记录表,每天深夜定时统计更新一次。 即时性很高,那么弄个触发器 /wei_money_log 这种数据变更事务里多加一个同时去更新 sum 记录表对应记录。 |
6
lookatme 2018-07-19 14:27:33 +08:00
总共才 650w,cid=4 都有 300w,用索引反而没有全表遍历快
|
12
f4nyc 2018-07-19 14:38:11 +08:00 via iPhone
因为 innodb 是聚簇索引啊,cid 是二级索引,先在二级索引查找到对应主键的值,然后再查聚簇索引,300w 次 O(logn),当然没有直接遍历 O(n)来的快,差不多就是这个时间比
怀疑你的索引是无脑加的,推荐高性能 mysql |
15
singer 2018-07-19 14:41:22 +08:00 via iPhone
你的 cid,type,money 加索引没有意义啊。
可以按年份或者月份分表。都 30s 了,分表平均一下速度。 还有就是日志建议设置热点数据,只能查 6 个月内的数据 |
16
glacer 2018-07-19 14:42:17 +08:00 2
A 查询走的全表却比 B 查询快,原因是因为 A 用到了覆盖索引(即索引是 money 而查询字段也是 money,那么数据就可以直接在索引上获得了。B 查询用的索引是 cid 而字段还是 money,在索引到行 id 后还需要根据 id 查找数据)。
这里的要优化 B 的话可以按照这个思路,建一个(cid, money)的索引,这样 B 也就能用到覆盖索引了。 当然你这个表全是索引了...索引不是这样建的,这样索引会比数据还大,要根据实际查询的情况而定。 |