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 表加个字段不会很慢的 表大了就不容易加字段了。

[abcfyk]

@sohoer 可能是思路问题。放mysql做也许不是个好办法。

[abcfyk]

@frankzeng 不是实时数据，所以目前做了一些小修改。

@npc0der 后来又做了一些小修改。

首先修改了取SKU的接口，只取SKUID，不取其他信息。能节省大量时间，再缓存到一些文件中。暂时没有放在redis，速度提升已经非常大了。只是还剩下一个内存占用的问题。

就是PHP 无法手动释放内存，你 unset 了某个变量并不会马上释放这个变量占用的内存空间，、还是要等 GC 来自动回收。默认的128M内存真的是捉襟见肘。。。

[jhdxr]

@Septembers 我现在有个单表2亿的，在合适的索引的配合下，目前我的查询均可以在0.3s内完成。然后上个月把数据同样地拷了一份到postgresql里去，配合同样的索引，效率远不如mysql。用explain看了下也已经走了索引了。不知道能否指教下有哪些需要注意的点？

[li24361]

@frankzeng 这样的话,数据实时更新了,统计表的信息就不准确了,必须每次存量跑

[li24361]

@yangqi 我觉得也是额