我要评论引言
在数据库性能优化中，索引是提升查询速度最有效的手段之一。然而，不恰当的索引会降低写操作性能并增加存储开销。作为dba，我们经常面临这样的挑战：如何精准定位哪些查询真正需要添加索引？ 本文将分享几种实用的t-sql查询，帮助您科学识别缺失索引，并提供最佳实践指南。
一、为什么需要索引优化？
性能瓶颈：全表扫描（table scan）可能导致简单查询耗时数秒
资源浪费：未使用索引的查询消耗额外cpu和i/o资源
隐性成本：缺失索引可能使关键业务操作延迟数倍
据统计，合理添加索引可使查询性能提升10-100倍（来源：microsoft sql server性能调优白皮书）
二、核心诊断查询
1. 缺失索引自动生成脚本
select top 10
round(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) as improvement_measure,
db_name(mid.database_id) as database_name,
object_name(mid.object_id) as table_name,
'create index [ix_' + object_name(mid.object_id) + '_'
+ replace(replace(replace(isnull(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')
+ case when mid.inequality_columns is not null then '_' + replace(replace(replace(mid.inequality_columns, ', ', '_'), '[', ''), ']', '') else '' end
+ '] on ' + mid.statement
+ ' (' + isnull(mid.equality_columns, '')
+ case when mid.equality_columns is not null and mid.inequality_columns is not null then ',' else '' end
+ isnull(mid.inequality_columns, '') + ')'
+ isnull(' include (' + mid.included_columns + ')', '') as create_index_statement,
migs.user_seeks as seek_operations,
migs.avg_user_impact as improvement_percent
from sys.dm_db_missing_index_group_stats as migs
inner join sys.dm_db_missing_index_groups as mig
on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid
on mig.index_handle = mid.index_handle
where mid.database_id = db_id()
order by improvement_measure desc;
结果解读：
improvement_measure：综合改进指标（值越大优先级越高）improvement_percent：预估查询性能提升百分比seek_operations：该索引可能被使用的次数
2. 高开销扫描查询定位
select top 5
qs.total_logical_reads / qs.execution_count as avg_logical_reads,
qs.execution_count,
substring(st.text, (qs.statement_start_offset/2) + 1,
((case qs.statement_end_offset
when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as query_text,
qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
where qp.query_plan.exist('//relop[@physicalop="index scan" or @physicalop="clustered index scan"]') = 1
order by avg_logical_reads desc;关键指标：
avg_logical_reads> 1000 表示严重i/o问题执行计划中出现
index scan警告
3. 未索引的热点列检测
select top 10 t.name as tablename, c.name as columnname, sum(us.user_scans) as total_scans from sys.tables t join sys.columns c on t.object_id = c.object_id left join sys.index_columns ic on ic.object_id = t.object_id and ic.column_id = c.column_id left join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id left join sys.dm_db_index_usage_stats us on us.object_id = t.object_id and us.index_id = i.index_id where i.index_id is null -- 无索引列 and us.user_scans > 0 group by t.name, c.name order by total_scans desc;
三、索引创建黄金法则
1. 索引设计原则
-- 标准结构 create index ix_table_keycolumns on dbo.table (column1 asc, column2 desc) include (column3, column4) with (fillfactor = 90); -- 针对频繁更新表 -- 筛选索引（针对热点数据） create index ix_orders_active on dbo.orders (orderdate) where status = 'processing';
2. 四要四不要
| 该做的 | 避免的 |
|---------------------------|--------------------------|
| 优先选择高选择性列 | 在bit类型列建索引 |
| included列放常用查询字段 | 创建重复功能索引 |
| 定期重建碎片率>30%的索引 | 盲目接受所有系统建议 |
| 测试环境验证性能提升 | 在生产环境直接创建索引 |
四、高级技巧
1. 索引使用监控
select object_name(ix.object_id) as tablename, ix.name as indexname, ix.type_desc as indextype, us.user_seeks, us.user_scans, us.user_lookups, us.user_updates from sys.dm_db_index_usage_stats us join sys.indexes ix on us.object_id = ix.object_id and us.index_id = ix.index_id where us.database_id = db_id() and objectproperty(us.object_id, 'isusertable') = 1;
决策依据：
user_updates> 10 * (user_seeks+user_scans) → 考虑删除索引user_lookups过高 → 需要优化included列
2. 查询存储深度分析（sql server 2016+）
select q.query_id, t.query_sql_text, rs.avg_duration, rs.avg_logical_io_reads, p.query_plan from sys.query_store_query q join sys.query_store_query_text t on q.query_text_id = t.query_text_id join sys.query_store_plan p on q.query_id = p.query_id join sys.query_store_runtime_stats rs on p.plan_id = rs.plan_id where rs.last_execution_time > dateadd(day, -7, getdate()) 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%以上！欢迎在评论区分享您的索引优化实战经验。
到此这篇关于如何识别sql server中需要添加索引的查询的文章就介绍到这了,更多相关sqlserver添加索引查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论