狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

LOGO OA教程 ERP教程 模切知識交流 PMS教程 CRM教程 開發文檔 其他文檔  
 
網站管理員

如何識別SQL Server中需要添加索引的查詢

freeflydom
2025年7月8日 17:35 本文熱度 281

引言

在數據庫性能優化中,索引是提升查詢速度最有效的手段之一。然而,不恰當的索引會降低寫操作性能并增加存儲開銷。作為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;

  

五、避坑指南

  1. 索引覆蓋陷阱:包含過多INCLUDED列會顯著增大索引體積

  2. 參數嗅探問題:使用OPTION(RECOMPILE)解決參數敏感查詢

  3. 鎖升級風險:單索引超過8KB可能引發鎖升級

  4. 統計信息滯后:開啟AUTO_UPDATE_STATISTICS_ASYNC

結語

精準的索引優化需要持續監控和迭代調整。建議每周運行一次診斷查詢,重點關注:

  • 改進潛力(improvement_measure) > 100,000 的索引

  • 邏輯讀取(avg_logical_reads) > 5000 的查詢

  • 掃描次數(total_scans) > 10,000 的熱點列


附錄工具推薦:

  1. sp_BlitzIndex - 索引分析神器

  2. Database Engine Tuning Advisor - 微軟官方調優工具

  3. SolarWinds DPA - 商業級性能監控平臺

通過科學診斷和謹慎實施,您可以將查詢性能提升300%以上!

?轉自https://www.cnblogs.com/LuoCore/p/18972388


該文章在 2025/7/8 17:38:35 編輯過
關鍵字查詢
相關文章
正在查詢...
點晴ERP是一款針對中小制造業的專業生產管理軟件系統,系統成熟度和易用性得到了國內大量中小企業的青睞。
點晴PMS碼頭管理系統主要針對港口碼頭集裝箱與散貨日常運作、調度、堆場、車隊、財務費用、相關報表等業務管理,結合碼頭的業務特點,圍繞調度、堆場作業而開發的。集技術的先進性、管理的有效性于一體,是物流碼頭及其他港口類企業的高效ERP管理信息系統。
點晴WMS倉儲管理系統提供了貨物產品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質期管理,貨位管理,庫位管理,生產管理,WMS管理系統,標簽打印,條形碼,二維碼管理,批號管理軟件。
點晴免費OA是一款軟件和通用服務都免費,不限功能、不限時間、不限用戶的免費OA協同辦公管理系統。
Copyright 2010-2025 ClickSun All Rights Reserved