教你如何识别SQL Server中需要添加索引的查询
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">引言</a></li><li><a href="#_label1">一、为什么需要索引优化？</a></li><li><a href="#_label2">二、核心诊断查询</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">1. 缺失索引自动生成脚本</a></li><li><a href="#_lab2_2_1">2. 高开销扫描查询定位</a></li><li><a href="#_lab2_2_2">3. 未索引的热点列检测</a></li></ul><li><a href="#_label3">三、索引创建黄金法则</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_3">1. 索引设计原则</a></li><li><a href="#_lab2_3_4">2. 四要四不要</a></li></ul><li><a href="#_label4">四、高级技巧</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_5">1. 索引使用监控</a></li><li><a href="#_lab2_4_6">2. 查询存储深度分析（SQL Server 2016+）</a></li></ul><li><a href="#_label5">五、避坑指南</a></li><ul class="second_class_ul"></ul><li><a href="#_label6">结语</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>引言</h2><p>在数据库性能优化中，索引是提升查询速度最有效的手段之一。然而，不恰当的索引会降低写操作性能并增加存储开销。作为DBA，我们经常面临这样的挑战：如何精准定位哪些查询真正需要添加索引？ 本文将分享几种实用的T-SQL查询，帮助您科学识别缺失索引，并提供最佳实践指南。</p>
<p class="maodian"><a name="_label1"></a></p><h2>一、为什么需要索引优化？</h2>
<ul><li><p>性能瓶颈：全表扫描（Table Scan）可能导致简单查询耗时数秒</p></li><li><p>资源浪费：未使用索引的查询消耗额外CPU和I/O资源</p></li><li><p>隐性成本：缺失索引可能使关键业务操作延迟数倍</p></li></ul>
<blockquote><p>据统计，合理添加索引可使查询性能提升10-100倍（来源：Microsoft SQL Server性能调优白皮书）</p></blockquote>
<p class="maodian"><a name="_label2"></a></p><h2>二、核心诊断查询</h2>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>1. 缺失索引自动生成脚本</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p></p>
<p>结果解读：</p>
<ul><li><p><code>improvement_measure</code>：综合改进指标（值越大优先级越高）</p></li><li><p><code>improvement_percent</code>：预估查询性能提升百分比</p></li><li><p><code>seek_operations</code>：该索引可能被使用的次数</p></li></ul>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>2. 高开销扫描查询定位</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p>关键指标：</p>
<ul><li><p><code>avg_logical_reads</code> > 1000 表示严重I/O问题</p></li><li><p>执行计划中出现 <code>Index Scan</code> 警告</p></li></ul>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>3. 未索引的热点列检测</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p></p>
<p class="maodian"><a name="_label3"></a></p><h2>三、索引创建黄金法则</h2>
<p class="maodian"><a name="_lab2_3_3"></a></p><h3>1. 索引设计原则</h3>
<div class="jb51code"><pre class="brush:sql;">-- 标准结构
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';</pre></div>
<p></p>
<p class="maodian"><a name="_lab2_3_4"></a></p><h3>2. 四要四不要</h3>
<p>| 该做的 | 避免的 |</p>
<p>|---------------------------|--------------------------|</p>
<p>| 优先选择高选择性列 | 在bit类型列建索引 |</p>
<p>| INCLUDED列放常用查询字段 | 创建重复功能索引 |</p>
<p>| 定期重建碎片率>30%的索引 | 盲目接受所有系统建议 |</p>
<p>| 测试环境验证性能提升 | 在生产环境直接创建索引 |</p>
<p class="maodian"><a name="_label4"></a></p><h2>四、高级技巧</h2>
<p class="maodian"><a name="_lab2_4_5"></a></p><h3>1. 索引使用监控</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p></p>
<p>决策依据：</p>
<ul><li><p><code>user_updates</code> > 10 * (<code>user_seeks</code> + <code>user_scans</code>) → 考虑删除索引</p></li><li><p><code>user_lookups</code> 过高 → 需要优化INCLUDED列</p></li></ul>
<p class="maodian"><a name="_lab2_4_6"></a></p><h3>2. 查询存储深度分析（SQL Server 2016+）</h3>
<div class="jb51code"><pre class="brush:sql;">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;</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>五、避坑指南</h2>
<ol><li><p>索引覆盖陷阱：包含过多INCLUDED列会显著增大索引体积</p></li><li><p>参数嗅探问题：使用<code>OPTION(RECOMPILE)</code>解决参数敏感查询</p></li><li><p>锁升级风险：单索引超过8KB可能引发锁升级</p></li><li><p>统计信息滞后：开启<code>AUTO_UPDATE_STATISTICS_ASYNC</code></p></li></ol>
<p class="maodian"><a name="_label6"></a></p><h2>结语</h2>
<p>精准的索引优化需要持续监控和迭代调整。建议每周运行一次诊断查询，重点关注：</p>
<ul><li><p>改进潜力(improvement_measure) > 100,000 的索引</p></li><li><p>逻辑读取(avg_logical_reads) > 5000 的查询</p></li><li><p>扫描次数(total_scans) > 10,000 的热点列</p></li></ul>
<p>附录工具推荐：</p>
<ol><li><p><a href="https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit" rel="external nofollow"rel="noopener nofollow" target="_blank">sp_BlitzIndex</a> - 索引分析神器</p></li><li><p>Database Engine Tuning Advisor - 微软官方调优工具</p></li><li><p>SolarWinds DPA - 商业级性能监控平台</p></li></ol>
<p>通过科学诊断和谨慎实施，您可以将查询性能提升300%以上！欢迎在评论区分享您的索引优化实战经验。</p>
頁:
[1]