如何識別SQL Server中需要添加索引的查詢
當前位置:點晴教程→知識管理交流
→『 技術文檔交流 』
引言在數據庫性能優化中,索引是提升查詢速度最有效的手段之一。然而,不恰當的索引會降低寫操作性能并增加存儲開銷。作為DBA,我們經常面臨這樣的挑戰:如何精準定位哪些查詢真正需要添加索引? 本文將分享幾種實用的T-SQL查詢,幫助您科學識別缺失索引,并提供最佳實踐指南。 一、為什么需要索引優化?
二、核心診斷查詢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;
結果解讀:
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;
關鍵指標:
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;
決策依據:
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;
五、避坑指南
結語精準的索引優化需要持續監控和迭代調整。建議每周運行一次診斷查詢,重點關注:
附錄工具推薦:
通過科學診斷和謹慎實施,您可以將查詢性能提升300%以上! ?轉自https://www.cnblogs.com/LuoCore/p/18972388 該文章在 2025/7/8 17:38:35 編輯過 |
關鍵字查詢
相關文章
正在查詢... |