查询当按照日期范围查询时不使用日期字段索引

Question

查询当按照日期范围查询时不使用日期字段索引

jie 25

线上根据日期范围查询效率降低，执行计划从日期索引变为全主键扫描,io飙升，为了找到线上查询效率降低的原因和优化方案，我进行了以下验证：

将表按照三种方式创建：

a.主键（record_id),聚集索引,日期字段(fday)创建非聚集索引对应表名为 balance_resource_detail_xxx_clusteredpK

b.主键（record_id),非聚集唯一索引，日期字段(fday),创建聚集索引,对应表名为balance_resource_detail_xxx_nonclusteredpK

c.主键（record_id),聚集索引,日期字段(fday)创建其他所有字段的include索引,对应表名为

balance_resource_detail_xxx_clusteredpK_include

表结构为a时，当主键为聚集索引时候，索引字段如果不是include，查询内容包含其他字段时，优化器更倾向于通过pk扫描，性能变得极低，表结构为b时，基于日期查询效率很高，但基于主键(record_id）的范围查询效率极低，我很担心在集群环境中这样设计是否影响主节点数据同步到辅助节点，特别是大批量的更新和删除（因为表复制是通过主键),表结构为c，感觉最保险，但这种涉及索引占用空间巨大（数据量相同情况下a:9N,b:300M:c:18G），因此想确认采用b方案创建表是否会影响主节点到辅助节点数据同步,或存在什么风险（从测试结果来看如果对主键单个record_id值进行查询没问题，单如果对record_id进行范围查询100w范围，会走全主键扫描，IO飙升）

0 个注释

问题作者接受的答案

Lakshmi Narayana Garikapati 1,335 Microsoft 外部员工审查方

Hi jie,

Thanks for sharing the detailed verification results. Based on your test outcomes:

Replication/Data Sync: Changing the clustered index from record_id to fday (scheme b) does not affect primary-to-secondary synchronization. Replication relies on the transaction log, not on index definitions, so data consistency is preserved.

Performance Trade-offs: • Scheme (a) favors primary key lookups but struggles when queries need non-included fields. • Scheme (b) is excellent for date-based queries but range scans on record_id can cause full clustered scans and high I/O. • Scheme (c) balances both but at the cost of very large index space.

Risks with Scheme (b): The main risk is query performance degradation when workloads involve large PK range scans. Inserts/updates may also be slower if fday values are not sequential, since the clustered index enforces physical row order.

Recommendation: If your workload is heavily mixed (both date-based and PK range queries), scheme (c) or a hybrid approach is safer. For example, keep the clustered PK on record_id and add a selective non-clustered index on fday with INCLUDE columns for the most frequently queried fields. This avoids the 18G overhead while still covering common queries.

Closing line you could add: In short, scheme (b) won’t break replication, but it does introduce query performance risks for PK ranges. A hybrid indexing strategy is usually the most practical compromise between efficiency and storage.

Thanks,

Lakshmi.

0 个注释

1 个其他答案

你的答案

Answer 1

jie 25

验证通过自增id(record_id)与日期字段(fday) 创建联合主键，聚集索引的确在性能和索引空间上获得了很好的效果，谢谢回复。

0 个注释