SQL Server 2022 OLTP工作负载：基于操作占比的索引设计决策

场景背景 #

正在分析SQL Server 2022 OLTP数据库，基于Query Store数据（聚合sys.query_store_query_text与sys.query_store_runtime_stats）优化索引策略，观测到操作占比：SELECT约53%、INSERT约20%、UPDATE约25%、DELETE约5%。
核心背景：

• 应用类型：事务型任务/项目管理系统
• 表规模：当前适中，预计将显著增长
• 工作负载：读写混合（约50/50）
• 核心诉求：平衡非聚集索引的读性能提升与写入维护开销

问题1：操作占比对索引激进程度的实用解读方式 #

• 聚焦写入细分项：25%的UPDATE是核心考量点——UPDATE若涉及索引键列，会同时修改聚集索引与所有关联非聚集索引，开销远高于INSERT/DELETE。53%的SELECT占比说明读需求明确，但50%的总写入占比意味着每新增一个非聚集索引，所有写入操作都要多维护一套索引结构。
• 按ROI判断激进程度：
  • 若SELECT集中在少数高频、高延迟查询，针对性建索引的收益远大于写入开销，可适当激进；
  • 若SELECT是大量低频查询，优先保留通用索引并控制数量，避免过度维护；
  • 若UPDATE频繁修改索引键列，任何新增索引都要极其谨慎，甚至放弃部分低收益的读优化。

问题2：读写密集型工作负载的常用启发式划分规则 #

行业内通用的经验划分：

• 读密集型：读取占比≥70%，写入（INSERT/UPDATE/DELETE）≤30%——可激进创建非聚集索引，优先保障读性能；
• 写密集型：写入占比≥60%，读取≤40%——仅保留聚集索引、唯一约束索引与外键关联索引，非聚集索引能省则省；
• 读写平衡：读写占比在40%-60%区间（即当前场景）——必须精细权衡，不能一刀切，需结合单事务内的读写比例调整策略（比如任务管理系统中“创建任务+查询列表”的单事务，需兼顾读写效率）。

问题3：读写平衡场景下的典型索引策略 #

实践中采用**「针对性索引+精简通用索引」**的组合，核心是抓重点、控成本：

1. 优先优化高频高开销SELECT：用Query Store定位Top 20%消耗CPU/IO的读查询，为其创建覆盖索引（Include必要非键列，避免Key Lookup）——这类索引的ROI最高，高频读的性能提升足以抵消写入维护开销；
2. 通用索引只保留刚需：仅保留外键约束索引（避免DELETE/UPDATE时的表扫描）、唯一约束索引，其他通用单列索引除非被多个高频查询共享，否则不创建；
3. 定期清理无效索引：用sys.dm_db_index_usage_stats检查索引利用率，若某非聚集索引的user_seeks/user_scans/user_lookups极低，但user_updates很高，直接删除；
4. 优化索引的写入友好性：尽量将非聚集索引的键列设为极少更新的字段（如任务ID、创建时间）；若UPDATE的列不在非聚集索引中，索引维护开销会大幅降低。

问题4：资深从业者对全局占比与查询级分析的依赖优先级 #

两者结合，但查询级分析是核心，全局占比是方向框架：

• 全局占比用来定大策略：明确不能像读密集型那样盲目建索引，也不能像写密集型那样极端精简，为优化划定边界；
• 查询级分析才是落地核心：通过Query Store抓取Top开销查询，定位哪些慢读需要索引优化，哪些写入因现有索引导致延迟过高；比如若某UPDATE因维护5个非聚集索引导致事务超时，哪怕SELECT占比高，也要删除1-2个利用率低的索引；
• 辅以索引使用统计：用sys.dm_db_index_usage_stats看单个索引的读写次数比，若写入维护次数是读取次数的10倍以上，无论全局占比如何，都要考虑删除或调整——维护成本远大于收益。

内容的提问来源于stack exchange，提问作者Mohamad TAGHLOBI