如何识别SQL Server中需要添加索引的查询

引言

在数据库性能优化中，索引是提升查询速度最有效的手段之一。然而，不恰当的索引会降低写操作性能并增加存储开销。作为DBA，我们经常面临这样的挑战：如何精准定位哪些查询真正需要添加索引？ 本文将分享几种实用的T-SQL查询，帮助您科学识别缺失索引，并提供最佳实践指南。
一、为什么需要索引优化？

• 性能瓶颈：全表扫描（Table Scan）可能导致简单查询耗时数秒
  
• 资源浪费：未使用索引的查询消耗额外CPU和I/O资源
  
• 隐性成本：缺失索引可能使关键业务操作延迟数倍
  

据统计，合理添加索引可使查询性能提升10-100倍（来源：Microsoft SQL Server性能调优白皮书）

二、核心诊断查询

1. 缺失索引自动生成脚本

1. SELECT TOP 10
3.    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS improvement_measure,
5.    DB_NAME(mid.database_id) AS database_name,
7.    OBJECT_NAME(mid.object_id) AS table_name,
9.    'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_'
11.        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')
13.        + CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + REPLACE(REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', ''), ']', '') ELSE '' END
15.        + '] ON ' + mid.statement
17.        + ' (' + ISNULL(mid.equality_columns, '')
19.        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
21.        + ISNULL(mid.inequality_columns, '') + ')'
23.        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
25.    migs.user_seeks AS seek_operations,
27.    migs.avg_user_impact AS improvement_percent
29. FROM sys.dm_db_missing_index_group_stats AS migs
31. INNER JOIN sys.dm_db_missing_index_groups AS mig
33.    ON migs.group_handle = mig.index_group_handle
35. INNER JOIN sys.dm_db_missing_index_details AS mid
37.    ON mig.index_handle = mid.index_handle
39. WHERE mid.database_id = DB_ID()
41. ORDER BY improvement_measure DESC;

复制代码

 
结果解读：

• improvement_measure：综合改进指标（值越大优先级越高）
  
• improvement_percent：预估查询性能提升百分比
  
• seek_operations：该索引可能被使用的次数
  

2. 高开销扫描查询定位

1. SELECT TOP 5
3.    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
5.    qs.execution_count,
7.    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
9.        ((CASE qs.statement_end_offset
11.            WHEN -1 THEN DATALENGTH(st.text)
13.            ELSE qs.statement_end_offset
15.        END - qs.statement_start_offset)/2) + 1) AS query_text,
17.    qp.query_plan
19. FROM sys.dm_exec_query_stats AS qs
21. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
23. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
25. WHERE qp.query_plan.exist('//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') = 1
27. ORDER BY avg_logical_reads DESC;

复制代码

　　
关键指标：

• avg_logical_reads > 1000 表示严重I/O问题
  
• 执行计划中出现 Index Scan 警告
  

3. 未索引的热点列检测

1. SELECT TOP 10
3.    t.name AS TableName,
5.    c.name AS ColumnName,
7.    SUM(us.user_scans) AS total_scans
9. FROM sys.tables t
11. JOIN sys.columns c ON t.object_id = c.object_id
13. LEFT JOIN sys.index_columns ic
15.    ON ic.object_id = t.object_id AND ic.column_id = c.column_id
17. LEFT JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = ic.index_id
19. LEFT JOIN sys.dm_db_index_usage_stats us ON us.object_id = t.object_id AND us.index_id = i.index_id
21. WHERE i.index_id IS NULL  -- 无索引列
23.    AND us.user_scans > 0
25. GROUP BY t.name, c.name
27. ORDER BY total_scans DESC;

复制代码

 
三、索引创建黄金法则

1. 索引设计原则

1. -- 标准结构
3. CREATE INDEX IX_Table_KeyColumns
5. ON dbo.Table (Column1 ASC, Column2 DESC)
7. INCLUDE (Column3, Column4)
9. WITH (FILLFACTOR = 90); -- 针对频繁更新表
11. -- 筛选索引（针对热点数据）
13. CREATE INDEX IX_Orders_Active
15. ON dbo.Orders (OrderDate)
17. WHERE Status = 'Processing';

复制代码

 
2. 四要四不要

| 该做的 | 避免的 |
|---------------------------|--------------------------|
| 优先选择高选择性列 | 在bit类型列建索引 |
| INCLUDED列放常用查询字段 | 创建重复功能索引 |
| 定期重建碎片率>30%的索引 | 盲目接受所有系统建议 |
| 测试环境验证性能提升 | 在生产环境直接创建索引 |
四、高级技巧

1. 索引使用监控

1. SELECT
3.    OBJECT_NAME(ix.object_id) AS TableName,
5.    ix.name AS IndexName,
7.    ix.type_desc AS IndexType,
9.    us.user_seeks,
11.    us.user_scans,
13.    us.user_lookups,
15.    us.user_updates
17. FROM sys.dm_db_index_usage_stats us
19. JOIN sys.indexes ix ON us.object_id = ix.object_id AND us.index_id = ix.index_id
21. WHERE us.database_id = DB_ID()
23.    AND OBJECTPROPERTY(us.object_id, 'IsUserTable') = 1;

复制代码

 
决策依据：

• user_updates > 10 * (user_seeks + user_scans) → 考虑删除索引
  
• user_lookups 过高 → 需要优化INCLUDED列
  

2. 查询存储深度分析（SQL Server 2016+）

1. SELECT
3.    q.query_id,
5.    t.query_sql_text,
7.    rs.avg_duration,
9.    rs.avg_logical_io_reads,
11.    p.query_plan
13. FROM sys.query_store_query q
15. JOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_id
17. JOIN sys.query_store_plan p ON q.query_id = p.query_id
19. JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
21. WHERE rs.last_execution_time > DATEADD(DAY, -7, GETDATE())
23. ORDER BY rs.avg_logical_io_reads DESC;

复制代码

　　
五、避坑指南

• 索引覆盖陷阱：包含过多INCLUDED列会显著增大索引体积
  
• 参数嗅探问题：使用OPTION(RECOMPILE)解决参数敏感查询
  
• 锁升级风险：单索引超过8KB可能引发锁升级
  
• 统计信息滞后：开启AUTO_UPDATE_STATISTICS_ASYNC
  

结语

精准的索引优化需要持续监控和迭代调整。建议每周运行一次诊断查询，重点关注：

• 改进潜力(improvement_measure) > 100,000 的索引
  
• 逻辑读取(avg_logical_reads) > 5000 的查询
  
• 扫描次数(total_scans) > 10,000 的热点列
  

附录工具推荐：

• sp_BlitzIndex - 索引分析神器
  
• Database Engine Tuning Advisor - 微软官方调优工具
  
• SolarWinds DPA - 商业级性能监控平台
  

通过科学诊断和谨慎实施，您可以将查询性能提升300%以上！欢迎在评论区分享您的索引优化实战经验。

来源：豆瓜网用户自行投稿发布，如果侵权，请联系站长删除