SQL Server 索引维护常用方法总结

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 索引维护是数据库日常维护中一项重要的任务,SQL Server的索引维护其实主要围绕下面三个问题进行展开。 索引过多 索引不足 索引碎片率 本文同样从这3个角度出发,介绍一些实用的日常维护方法和工具。
索引维护是数据库日常维护中一项重要的任务,SQL Server的索引维护其实主要围绕下面三个问题进行展开。
  1. 索引过多
  2. 索引不足
  3. 索引碎片率

本文同样从这3个角度出发,介绍一些实用的日常维护方法和工具。

索引过多

索引过多是指每个表上面的非聚集索引很多,并且有些非聚集索引很少用到。 过多的索引,会导致增删数据的效率降低,数据库体积变大,索引以及统计信息的维护成本增加等负面影响,建议定期检查类似的索引,每个表上面的索引最好不要超过10个。

通过下面两个DMV,定期检查索引使用率,通过使用率决定是否需要该索引。sys.dm_db_index_operational_stats这个函数可以给出某个索引上面的insert,update和delete的操作情况。sys.dm_db_index_usage_stats这个视图可以给出访问索引的所有方法的操作概览。
--sys.dm_db_index_operational_stats
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
1

--sys.dm_db_index_usage_stats
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

 2

上述结果中,可以看到,CountryRegionCurrency和AddressType表中,有两个索引,没有使用过。 如果多次检查,这两个索引都还是没有使用过的话,建议将其删除。

索引不足

索引不足是指,要么缺少索引,要么有索引,但是没有覆盖所需的列,查询效果不好。 后者其实也可以归纳到索引不合适中。那么我们来看下,如何才能找到缺失的索引。

SQL Server提供下面4个DMV以供查询missing index的情况。SQL Server重启后,系统视图中的内容就会更新,需要定期的将该信息保存下来。
  1. sys.dm_db_missing_index_details 返回缺失的索引的详细信息。
  2. sys.dm_db_missing_index_group_stats 返回缺失索引组的概要信息。 
  3. sys.dm_db_missing_index_groups 返回缺失索引组中有哪些缺失的索引。
  4. sys.dm_db_missing_index_columns 返回表中缺失索引的列。
如何通过检测出来的缺失索引去新建索引,方法参考 Using Missing Index Information to Write CREATE INDEX Statements

下面语句,在每个库上面执行下面的查询,查看推荐建立的索引,包括创建语句。不过在创建索引前,需要综合考量表中已有的索引,是否有可以合并的情况。
Use DB
SELECT 
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

 3

创建index时,推荐按照下述顺序进行。

  • 将相等数据行列在最前
  • 将不相等的数据行列在相等的数据行后
  • 将include数据行列在create index语句的include子句中
  • 若要决定相等数据行的顺序,依据选择性排列这些数据行,将选择性最高的数据行排在最前

索引碎片率

新增、删除和修改数据时,数据库会自动维护索引。但时间长了之后,这些操作会造成数据不连续。这会对查找性能产生影响。

首先,观察索引碎片的严重程度。

内部不连续(Internal Fragmentation):数据页中有很多空闲空间;

外部不连续(External Fragmentation):

  • 硬盘中摆放的分页或区不连续,也就是数据表或索引散落在多个范围中,以及存放数据表或者索引的页不是按照实例连续存放的。
  • 逻辑数据顺序和实例在硬盘中的顺序不同。
1. 用DBCC SHOWCONTIG观察数据不连续
create index idCreditCard on CreditCard(CreditCardID)  with drop_existing
DBCC showcontig(CreditCard,idCreditCard)
4

红框中的参数反应外部不连续状况。索引idCreditCard总共用了43页,6个区,光标扫描区时转换了5次,每个区平均7.2页,扫描密度100%,逻辑扫描片段为0,区扫描碎片率33.33% (=读取时跳过的区数/总共使用的区数)。

最后两个参数Avg. Bytes Free per Page和Avg. Page Density (full)则反应的是内部不连续的情况,平均每页空闲字节数越大,说明内部不连续越严重。

可以通过定义一个临时表来观察数据不连续情况。

--BDCC Showcontig to show the fragmentation of table or index
create table #fraglist
(
objectName char (255),
objectID int,
IndexName char(255),
IndexID int,
Lvl int,
countPages int,
countRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecSize int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal
)
insert #fraglist exec('DBCC showcontig(CreditCard,idCreditCard) with tableresults')
select * from #fraglist

2. 通过sys.dm_db_index_physical_stats 观察数据不连续情况 

查看Department表的索引不连续情况:

select a.index_id,name,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats
(DB_ID(),object_id(N'HumanResources.Department'),null,null,null)
as a join sys.indexes as b on a.object_id=b.object_id
and a.index_id=b.index_id;


查看数据库中所有索引的碎片情况

use DB;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 5
ORDER BY indexstats.avg_fragmentation_in_percent DESC

6

 

3. 根据数据片段状况来判断是否要重组或者重建索引。

当索引碎片大于5%,小于等于30%时,建议reorganize该索引;当索引碎片率大于30%时,建议rebuild该索引。Rebuild Index比较耗性能,建议在非工作时间进行,同时,建议使用online 的方式来rebuild index,以减少锁的申请量。
  • 重组索引:
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE ;
  • 重建索引:
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )));
  • 维护计划重建索引
同时,对于自建的SQL Server数据库,还可以通过创建维护计划( maintenance plan)来重建索引。
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
9天前
|
SQL 监控 安全
Flask 框架防止 SQL 注入攻击的方法
通过综合运用以上多种措施,Flask 框架可以有效地降低 SQL 注入攻击的风险,保障应用的安全稳定运行。同时,持续的安全评估和改进也是确保应用长期安全的重要环节。
42 14
|
1月前
|
SQL BI 数据库
SQL操作的一些基本方法
【10月更文挑战第27天】SQL操作的一些基本方法
35 3
|
1月前
|
SQL 监控 固态存储
SQL优化有哪些方法?
【10月更文挑战第27天】SQL优化有哪些方法?
31 3
|
2月前
|
SQL 存储 数据库
SQL部分字段编码设置技巧与方法
在SQL数据库管理中,设置字段的编码对于确保数据的正确存储和检索至关重要
|
2月前
|
SQL 数据库 索引
SQL语句实现投影连接:方法与技巧详解
在SQL数据库查询中,投影和连接是两个核心概念
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
2月前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
2月前
|
SQL 存储 Serverless
SQL语句拆分时间字段的技巧与方法
在数据库操作中,经常需要处理时间数据
|
2月前
|
SQL 监控 数据库
SQL语句是否都需要解析及其相关技巧和方法
在数据库管理中,SQL(结构化查询语言)语句的使用无处不在,它们负责数据的查询、插入、更新和删除等操作
|
2月前
|
SQL 安全 关系型数据库
SQL语句中表名通配符的使用技巧与方法
在SQL查询中,通配符通常用于匹配字符串数据,如列值中的部分字符