ASP .NET Core + EF Core + MySQL 这个统计查询在 3 亿条记录的表下每次查询都需要 2~4 分钟，优化的办法只有用触发器或是后台每小时定期统计吗？

[drymonfidelia]

虽然后台慢不是不能用，但是销售一直在反映操作很慢让优化。销售后台时间选择器粒度只精确到天，不知道这个地方是不是可以做什么效果比较好的优化。

正常情况下日订单在五百万左右。这个页面需要显示每个 SKU 在不同阶段（状态）的订单的数量。有考虑过用 Redis 但是我们的 Redis 是单机的只用来缓存，经常 flushall 。再单独加一台 Redis 觉得不划算

return Ok(dbContext.Orders.Where(x => x.CreatedAt >= DateTimeOffset.FromUnixTimeMilliseconds(queryForm.StartDate) &&
                                             x.CreatedAt <= DateTimeOffset.FromUnixTimeMilliseconds(queryForm.EndDate))
            .Include(x => x.Sku).Where(x => x.Sku != null)
            .GroupBy(o => o.SkuId)
            .Select(g => new
            {
                SkuId = g.Key,
                SkuName = g.Select(o => o.Sku.TitleEng).FirstOrDefault(),
                Delivering = g.Count(o => o.Status == (int)OrderStatusEnum.Delivering),
                Cancelled = g.Count(o => o.Status == (int)OrderStatusEnum.Cancelled),
                InProcess = g.Count(o => o.Status == (int)OrderStatusEnum.InProcess),
                InReview = g.Count(o => o.ReviewTasks.Any(t => t.Pending && t.Result == false)),
                Total = g.Count()
            })
            .ToList());

能想到的索引都已经加了

[Index(nameof(Status))]
[Index(nameof(Input))]
[Index(nameof(SkuId))]
[Index(nameof(UserId))]
[Index(nameof(CreatedAt))]
[Index(nameof(UpdatedAt))]
[Index(nameof(OrderTag))]
[Index(nameof(SendPending))]
[Index(nameof(OrderSource))]
[Index(nameof(UserId), nameof(SkuId), nameof(FromMobileApp))]
[Index(nameof(UserId), nameof(Status))]
[Index(nameof(SkuId), nameof(Status))]
[Index(nameof(Status), nameof(RiskyScore))]
[Index(nameof(UserId), nameof(Input))]
[Index(nameof(UserId), nameof(InputTailing))]
public class Order : BaseEntity
{ ... }

[hez2010]

1. 你在代码里加的索引有通过 migration 应用到数据库吗？没同步到数据库表里面是没用的。
2. 建议用异步方法 ToListAsync 。
3. 你可以看看具体生成了什么 SQL ，你这个需求完全没有必要在 SQL 做 GroupBy 和 Select ，你可以先 Select 出来然后 ToList 再在应用端进行 GroupBy ，比如像下面这样，毕竟 MySQL 的数据库引擎的索引做的本身就完全是依托答辩，最好只把 MySQL 当作一个大号 KV 来用。
```cs
return Ok((await dbContext.Orders.Where(x => x.CreatedAt >= DateTimeOffset.FromUnixTimeMilliseconds(queryForm.StartDate) &&
x.CreatedAt <= DateTimeOffset.FromUnixTimeMilliseconds(queryForm.EndDate))
.Include(x => x.Sku).Where(x => x.Sku != null)
.Select(o => new
{
SkuId = o.SkuId,
SkuName = o.Sku.TitleEng,
Status = o.Status,
ReviewTasks = o.ReviewTasks
})
.ToListAsync())
.GroupBy(o => o.SkuId)
.Select(g => new
{
SkuId = g.Key,
SkuName = g.Select(o => o.SkuName).FirstOrDefault(),
Delivering = g.Count(o => o.Status == (int)OrderStatusEnum.Delivering),
Cancelled = g.Count(o => o.Status == (int)OrderStatusEnum.Cancelled),
InProcess = g.Count(o => o.Status == (int)OrderStatusEnum.InProcess),
InReview = g.Count(o => o.ReviewTasks.Any(t => t.Pending && t.Result == false)),
Total = g.Count()
})
.ToList());
```

[drymonfidelia]

@hez2010
1. 有，这些索引是很早以前就加的，在 MySQL 里看了有应用
3. 感谢，我测试下这种实现的效果

[sagaxu]

日订单在五百万个还请不起 DBA 或者资深开发嘛，单表 15 个索引，且好几个重复无意义

[drymonfidelia]

@sagaxu 以前有，离职了没招到新的。而且订单单价低，利润率不高

[drymonfidelia]

@sagaxu 这些索引为什么是无意义的？都是根据代码里不同查询建的，MySQL 好像没有像 MongoDB 那样自动统计不同索引命中次数的功能，不懂怎么看哪些没用

[MoYi123]

问数据库优化的问题不贴 explain 就算了, 现在连 sql 都没有了.

[yinmin]

直接在 mysql 里用 sql 的 select ，看看需要多久时间。另外，每天 500 万条记录，通常是需要每天凌晨做昨天数据预汇总（数据清洗），例如按小时先预汇总一次，然后从汇总表里取数据。

[yinmin]

清洗掉客户 id ，按小时（或者 15 分钟）汇总订单数据到“订单汇总表 1”，然后再按天汇总到“订单汇总表 2”。根据查询颗粒度，从“订单汇总表 1”或“订单汇总表 2”取数应该能优化到几秒的级别吧

[lbp0200]

统计分析，不适合 MySQL ，建议用分析型数据库，比如 duckdb

[bsg1992]

每天 500 万 一年就得 18 亿的数据 你确定 mysql 能扛得住？

[zhangeric]

按时间分表呗,可以用 shardingcore 这个库

[zhuyw2006]

简单的用 EFCORE ，复杂一点的用 Dapper+原生 SQL 比较方便优化

[netnr]

@MoYi123 OP 给出的 LINQ 就是真实的业务场景，给 SQL 反而是包装问题再提问

如 #9 所说，引入 DuckDB 直接统计应该能秒出，按时同步数据即可；

另外，可以先尝试一下用 DuckDB 附加 MySQL 再直接执行 SQL ，可能有改进，
我们有一个场景，在 MySQL 查询需要 16s ，通过 DuckDB 来查询降低到 4s

[encro]

你这个查询主要是 sku_id 和 CreatedAt ，需要索引是亿 sku_id 分区，以 createdAt 排序。

另外，请开启慢查询日志。

如果缺少 dba 我可以远程兼任下，一个月收费 2000 ，负责帮助发现问题以及给出解决方案。

[encro]

为什么 order 表会有 skuId ，一个 sku 一个订单？

[niubiman]

这个查询用 linq 手动 join 效果会更好一写

[drymonfidelia]

@encro 是的，因为每个订单都要有独立的购买者信息，只能一个 sku 一个订单，导致订单量很大。
@bsg1992 超过 3 个月的订单会归档。
@lbp0200 DuckDB 有办法配合 MySQL 使用么？还是每条数据都需要写两次

[drymonfidelia]

@zhangeric 有考虑过分表，但是如果按日分表，用户看历史订单需要同时查好几个表不知道效率会不会更低？按月分表的话感觉意义不是很大，因为这个场景是查指定日期间的统计数据，按理说不应该扫全表吧？
@yinmin 预汇总有个问题就是订单的状态（处理中、运输中、已收货等）是会改变的，业务需要每个状态的订单数量。我觉得这样预汇总的数据就不太能用了，不知道有没有办法能解决

[MOONLIGHTT]

看下 druid 吧，很成熟了已经。