[不懂就问] MySQL 中大量数据的分组查询问题

[wuxi889]

# 场景

有一个 orders 表，表中含有 order_id, status, financial_status,paid_at,customer_id 字段（还有一对的其他字段），表索引有所有字段各自的索引，还有组合索引 status, financial_status,paid_at （是有效订单的必然前置条件）。现在我的数据跨度有 3 年，数据行数有 400 万条。

# 目的

计算复购率。复购率逻辑：比如查询 2022.01.01-2023.03.01 范围时，在这个时间段内出现 customer_id 重复有 2 次及以上时算复购，复购率=复购用户数 /用户数唯一数。

# 问题

目前在现有的条件查询中，查询 1.5 年数据时计算结果长达 150s 。因为查询的时间范围太大了，导致了 MySQL 的选择器在处理时组合索引没有使用，变成了扫全表。

请问大佬们，有没有什么方法可以压缩这个请求结果的时长到 3s 以内的?

# 其他
无法使用缓存，如果使用缓存的话所需要缓存的数量为 C(N,2) = N*(N-1)/2)

算了，帖子结束吧。我自己再想想

[noparking188]

能信息给全点嘛，看了想骂人

[silypie]

能把 customer_id 作为主键吗

[silypie]

要不按月查询，这样就能用上索引，然后做一下汇总

[wuxi889]

@silypie 这种复购的计算逻辑就是要按规定的时间范围去重，按月去汇总是没有意义的（一个月内也许没有复购，但是第二个月复购了。在查询第一个月时不算复购，查询第一到第二个月时，就算复购了）。

[meta]

你这种情况，如果对数据的实时性要求不高，可以先在后台做好每个用户的摘要数据，不必要每次都去流水里面查询。如果要即时数据，也可以先把历史数据做好摘要，即时数据只在当月的流水里面查询就可以了。

[iluhcm]

这种涉及到汇总的需求，要看投入的资源怎么样，ROI 值不值得。是否有必要做实时？离线 t+1 或者 h+1 行不行？
一种可行解是新建一张汇总表去做存储，按周期更新，按照用户维度去汇总。

[iluhcm]

@iluhcm 对实时性有要求的，可以考虑其他 olap 引擎。

[qiayue]

拆分问题，第一步找出所有购买两次以上的用户数量，第二步找出所有购买用户数量，用两个 sql 分别查一次，之后用程序算复购比例，这会比只用一个 sql 查询更快。
第一个 sql 找出所有购买了 2 次以上的用户数量，这里假设 status = 1 表示订单已支付：
select count(t.customer_id) buy_2_times_customer_c from (select customer_id,count(distinct order_id) order_c from order where paid_at>='2022.01.01' and paid_at<='2023.03.01' and status=1 group by customer_id having order_c>=2) t

第二个 sql 找出所有购买用户数量：
select count(distinct customer_id) total_customer_c from order where paid_at>='2022.01.01' and paid_at<='2023.03.01' and status=1

最后用 buy_2_times_customer_c / total_customer_c 就是比例了。

[qiayue]

我还帮你问了 gpt4
https://gpt.best/NoMhNsx1