Mysql 如何提升 Group by + Having 型子查询的查询速度（千万级别数据）

[abcfyk]

如题所示。
有一出货记录表 outDetail。表中大约3000W条数据。
字段如 outId, skuId, outDate, outQty等。

现想查出最近三个月没有出货的SKU号。有如下SQL：
select skuId from outDetail group by skuId having max(outDate) < date_sub(curdate(), interval 90 day);

查询速度很慢。请问如何优化？

[npc0der]

额 找出 最近90天出库的 那批 skuId

然后找出 整库的 skuId

差集。。。。skuId 有索引的吧 应该会很快。。。。

[abcfyk]

@npc0der 目前是这么做的。但是目前SKU总数100W+， 最近90天有出货的大约30W。用PHP来筛选。但是我想尽量能在mysql层面做就在mysql里面做算了。用php脚本跑的话效率太差了。

[takatost]

这么大级别的数据量为何不分表

[Septembers]

1. 建议做表分区
2. 给时间做索引

[Septembers]

1. 建议做表分区
2. 给时间(outDate)做索引

[Septembers]

@takatost 如果是PgSQL单表 15亿都压力不大

[jk2r]

你先贴出create的sql／explain的结果，大家才好帮你看

[Mac]

LZ先说说慢到什么程度？

[abcfyk]

@takatost 因为有很多统计数据的业务需求需要所有出货记录一起统计。类似这种需求。。所以没有统计。。

[abcfyk]

@Septembers 表分区。。暂时比较难，outDate 已加索引。。

[abcfyk]

@jk2r

类似这种：
CREATE TABLE `outDetail`{
`id` int(11) NOT NULL AUTO_INCREMENT,
`outId` int(11) NOT NULL,
`skuId` varchar(30) NOT NULL,
`outDate` datetime NOT NULL,
`outQty` int(11) NOT NULL
PRIMARY KEY (`id`),
KEY `indexOutId` (`outId`),
KEY `indexSkuId` (`skuId`),
KEY `indexOutDate` (`outDate`),
}ENGINE=InnoDB DEFAULT CHARSET=utf8


explain 结果大概如下
+----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+
| 1 | SIMPLE | outDetail | index | NULL | IndexSkuId | 92 | NULL | 23980757 | |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+----------+-------+

[abcfyk]

@Mac PHP把全部100W 的 SKU查出来存入变量，大概需要一小时。查找最近三个月出货的SKU存到变量又要一小时。光取数据还没求差集的时间就2小时了。

[O14]

不知我理解的对不？
SELECT DISTINCT skuId FROM outDetail WHERE outDate < date_sub(curdate(), interval 90 day) AND skuId NOT IN (SELECT DISTINCT skuId FROM outDetail WHERE outDate > date_sub(curdate(), interval 90 day));

[takatost]

@Septembers 可惜是mysql

[sohoer]

select skuId from (select skuId from outDetail where outDate > date_sub(curdate(), interval 90 day)) as t group by skuId;

[frankzeng]

如果不是严格实时要求，你可以写个脚本把跑出来的数据存到另一张表里。

[cevincheung]

只有我来说上检索引擎吗？

https://www.elastic.co

[yangqi]

难道不是简单的一句
SELECT DISTINCT skuId FROM outDetail WHERE outDate < date_sub(curdate(), interval 90 day);

？

[WispZhan]

Group by + Having 本身就没有效率可言吧……

[npc0der]

取出你说的 30w 的这些skuId 不会很慢吧 3000w 的表不算很大啊 我这张表 38,643,224 记录 取出skuId 你到临时表里面去

或者你这样 skuID 你 100w 的那张表 加一个字段 标示为 90天内出库了 更新下 这样 筛选出来也快 做分页查询 其他业务也方便。


100w 表加个字段不会很慢的 表大了就不容易加字段了。