数据库和SQL

检测数据库的索引健康程度

本文参考了微软的相关技术文档

本文介绍索引碎片整理的方法及其对查询性能的影响。 一旦确定索引的碎片数量,就可以在你选择的工具中运行 Transact-SQL 命令,或使用 SQL Server Management Studio,以便通过重新组织索引重新生成索引来对索引进行碎片整理。

索引不健康是导致查询慢的主要原因,索引不健康是我们的通俗说法,专业的说法是——索引碎片。

什么是索引碎片,为什么我应该关注它:

  • 当索引包含的页中,索引中的逻辑排序(基于索引中的键值)与索引页中的物理排序不匹配时,就存在碎片。
  • 无论何时对基础数据执行插入、更新或删除操作,数据库引擎 都会自动修改索引。 例如,在表中添加行可能会导致拆分行存储索引中的现有页,以腾出空间来插入新键值。 随着时间的推移,这些修改可能会导致索引中的信息分散在数据库中(含有碎片)。 当索引包含的页中的逻辑排序(基于键值)与数据文件中的物理排序不匹配时,就存在碎片。
  • 大量碎片式索引可能会降低查询性能,因为需要额外 I/O 来查找索引指向的数据。 较多的 I/O 会导致应用程序响应缓慢,特别是在涉及扫描操作时。

碎片产生的原因

(1)由于我们软件使用时间长了或者客户数据亮特别大,存在各种增加删除取消的业务,因此导致了越来越严重的索引碎片

(2)MSSQL不够智能化,不会在空闲的时候自己去做修复(听说阿里自己研发的数据库是这样聪明的,会自动维护保养)

查询索引健康度的命令

下面这段SQL命令可以查看某个表的索引健康情况

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N’某某数据库’);
SET @object_id = OBJECT_ID(N'[某某数据库].[dbo].[Pos_RetailBill]’);

IF @00188db_id IS NULL
BEGIN;
PRINT N’Invalid database’;
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N’Invalid object’;
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘LIMITED’);
END;
GO

你看到的这一列就是索引健康程度百分比,数字越低越好,数字超过了30对查询效率就有影响,如果数字超过50, 就可能跟没有索引差别不大了。

查询全部表的索引健康指标方法

DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL

DECLARE @Defrag NVARCHAR(MAX)

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N’#Frag’))
DROP TABLE #Frag

CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment DECIMAL)

EXEC sp_msforeachdb ‘INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
) SELECT ”?” AS DBName
,t.Name AS TableName
,sc.Name AS SchemaName
,i.name AS IndexName
,s.avg_fragmentation_in_percent
FROM ?.sys.dm_db_index_physical_stats(DB_ID(”?”), NULL, NULL,
NULL, ”Sampled”) AS s
JOIN ?.sys.indexes i
ON s.Object_Id = i.Object_id
AND s.Index_id = i.Index_id
JOIN ?.sys.tables t
ON i.Object_id = t.Object_Id
JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID
WHERE s.avg_fragmentation_in_percent > 20
AND t.TYPE = ”U”
AND s.page_count > 8
ORDER BY TableName,IndexName’

SELECT * FROM #Frag ORDER BY AvgFragment DESC

DROP TABLE #Frag

执行上面的SQL命令,得到的结果见上图,针对这个查询结果,我们就可以判断当前的数据库有没有必要去做索引维护

总结

如果数据库操作慢,建议检查这个指标,并尽快继续数据库索引维护。

发表评论

电子邮件地址不会被公开。 必填项已用*标注