当前位置: 首页 > news >正文

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

引言

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

一、为什么需要索引优化?

  • 性能瓶颈:全表扫描(Table Scan)可能导致简单查询耗时数秒

  • 资源浪费:未使用索引的查询消耗额外CPU和I/O资源

  • 隐性成本:缺失索引可能使关键业务操作延迟数倍

据统计,合理添加索引可使查询性能提升10-100倍(来源:Microsoft SQL Server性能调优白皮书)

二、核心诊断查询

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

SELECT TOP 10ROUND(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_percentFROM sys.dm_db_missing_index_group_stats AS migsINNER JOIN sys.dm_db_missing_index_groups AS migON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS midON mig.index_handle = mid.index_handleWHERE mid.database_id = DB_ID()ORDER BY improvement_measure DESC;

结果解读:

  • improvement_measure:综合改进指标(值越大优先级越高)

  • improvement_percent:预估查询性能提升百分比

  • seek_operations:该索引可能被使用的次数

2. 高开销扫描查询定位

SELECT TOP 5qs.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_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS query_text,qp.query_planFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qp.query_plan.exist('//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') = 1ORDER BY avg_logical_reads DESC;

关键指标:

  • avg_logical_reads > 1000 表示严重I/O问题

  • 执行计划中出现 Index Scan 警告

3. 未索引的热点列检测

SELECT TOP 10t.name AS TableName,c.name AS ColumnName,SUM(us.user_scans) AS total_scansFROM sys.tables tJOIN sys.columns c ON t.object_id = c.object_idLEFT JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.column_id = c.column_idLEFT JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = ic.index_idLEFT JOIN sys.dm_db_index_usage_stats us ON us.object_id = t.object_id AND us.index_id = i.index_idWHERE i.index_id IS NULL  -- 无索引列AND us.user_scans > 0GROUP BY t.name, c.nameORDER BY total_scans DESC;

三、索引创建黄金法则

1. 索引设计原则

-- 标准结构CREATE INDEX IX_Table_KeyColumnsON dbo.Table (Column1 ASC, Column2 DESC)INCLUDE (Column3, Column4)WITH (FILLFACTOR = 90); -- 针对频繁更新表-- 筛选索引(针对热点数据)CREATE INDEX IX_Orders_ActiveON 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_updatesFROM sys.dm_db_index_usage_stats usJOIN sys.indexes ix ON us.object_id = ix.object_id AND us.index_id = ix.index_idWHERE 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+)

SELECTq.query_id,t.query_sql_text,rs.avg_duration,rs.avg_logical_io_reads,p.query_planFROM sys.query_store_query qJOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_idJOIN sys.query_store_plan p ON q.query_id = p.query_idJOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_idWHERE rs.last_execution_time > DATEADD(DAY, -7, GETDATE())ORDER BY rs.avg_logical_io_reads DESC;

五、避坑指南

  1. 索引覆盖陷阱:包含过多INCLUDED列会显著增大索引体积

  2. 参数嗅探问题:使用OPTION(RECOMPILE)解决参数敏感查询

  3. 锁升级风险:单索引超过8KB可能引发锁升级

  4. 统计信息滞后:开启AUTO_UPDATE_STATISTICS_ASYNC

结语

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

  • 改进潜力(improvement_measure) > 100,000 的索引

  • 逻辑读取(avg_logical_reads) > 5000 的查询

  • 扫描次数(total_scans) > 10,000 的热点列

文章转载自:LuoCore

原文链接:如何识别SQL Server中需要添加索引的查询 - LuoCore - 博客园

体验地址:JNPF快速开发平台

http://www.lryc.cn/news/583172.html

相关文章:

  • nl2sql的解药pipe syntax
  • Linux入门篇学习——Linux 编写第一个自己的命令
  • 一天一道Sql题(day04)
  • 详解Kafka重平衡机制详解
  • Vue+ElementUI聊天室开发指南
  • Vue3 Element plus table有fixed列时错行
  • 7.神经网络基础
  • 【深度学习】【入门】Sequential的使用和简单神经网络搭建
  • 【机器学习】BeamSearch算法
  • 华为OD机试_2025 B卷_观看文艺汇演问题(Python,100分)(附详细解题思路)
  • 七牛云C++开发面试题及参考答案
  • Vue 3 中父子组件双向绑定的 4 种方式
  • mysql互为主从失效,重新同步
  • qml加载html以及交互
  • HarmonyOS中各种动画的使用介绍
  • C语言extern的用法(非常详细,通俗易懂)
  • 〔从零搭建〕数据湖平台部署指南
  • 17.Spring Boot的Bean详解(新手版)
  • OpenCV颜色矩哈希算法------cv::img_hash::ColorMomentHash
  • STM32-待机唤醒实验
  • [Leetcode] 预处理 | 多叉树bfs | 格雷编码 | static_cast | 矩阵对角线
  • User手机上如何抓取界面的布局uiautomatorviewer
  • 【机器人】Aether 多任务世界模型 | 4D动态重建 | 视频预测 | 视觉规划
  • 速卖通跨境运营破局:亚矩阵云手机如何用“本地化黑科技”撬动俄罗斯市场25%客单价增长
  • React 编译器与性能优化:告别手动 Memoization
  • 开始读 PostgreSQL 16 Administration Cookbook
  • 苍穹外卖项目日记(day04)
  • 【Netty+WebSocket详解】WebSocket全双工通信与Netty的高效结合与实战
  • 冷冻电镜重构的GPU加速破局:从Relion到CryoSPARC的并行重构算法
  • 《重构项目》基于Apollo架构设计的项目重构方案(多种地图、多阶段、多任务、状态机管理)