EF Core创建的IoT大数据表查询超时，如何配置合适索引？

你原有的索引(UnternehmenId, Datum, IoTDeviceId)确实完全匹配查询的WHERE筛选条件，逻辑上没有问题，但性能差的核心原因是该索引不是覆盖索引，需要大量回表查询：

• 你查询需要返回的Zeit、Input1、Input2、Timestamp字段不在这个非聚集索引的叶子节点中
• 数据库匹配到符合条件的索引条目后，需要逐行通过主键去聚集索引中查找额外的字段值（也就是执行计划中的Key Lookup操作）
• 单日单设备1440条数据，8个设备14天就是16万次回表操作，开销随查询范围线性增长，直接导致超时。

1. 改造为覆盖索引（优先级最高） #

在原有索引基础上新增INCLUDE字段，把查询需要返回的所有非索引键字段包含进去，完全避免回表操作。
EF Core配置代码如下：

builder.HasIndex(r => new { r.UnternehmenId, r.Datum, r.IoTDeviceId })
       // 包含查询需要返回的所有额外字段
       .IncludeProperties(r => new { r.Zeit, r.Input1, r.Input2, r.Timestamp });

改造后的索引可以直接满足本次查询的所有筛选、返回需求，无需任何回表操作，查询耗时会降低到百毫秒级别，14天范围查询也不会超过1秒。

2. 辅助优化措施 #

• 调整聚集索引结构：当前使用无序GUID作为聚集索引主键，写入时会频繁出现页分裂，同时增大回表开销。建议将聚集索引修改为时序类组合键(UnternehmenId, Datum, Zeit, IoTDeviceId)，主键ID改为非聚集唯一索引，写入和时序查询性能都会有明显提升。
• 定期更新统计信息：偶发超时通常和统计信息过期有关，过期的统计信息会导致查询优化器选错索引、错误估算行数，建议按周更新表统计信息。
• 大表分区：如果数据量超过千万行，建议按Datum字段做表分区，查询时只会扫描对应日期范围的分区，避免全表扫描开销。

内容的提问来源于stack exchange，提问作者David Stania