mysql查看索引碎片比率 sql server索引碎片

转载

mob6454cc777577 2023-11-22 13:28:18

文章标签 mysql查看索引碎片比率 SQL Server Database 文章分类 MySQL 数据库

索引碎片的产生：

索引能够加快对表的访问速度，然而任何事物都有两面性，索引在带给我们便利的同时也会占用额外的磁盘空间，并且我们在对表进行增删改的操作时也要消耗额外的时间来更新索引。而在我们对包含索引的表进行增删改时，也会造成索引碎片，久而久之，索引碎片程度越来越高，反而会降低我们对表的访问速度。因此作为数据库管理员，要定期维护索引，修复索引碎片。

查询索引碎片：

通过sys.dm_db_index_physical_stats这个存储过程我们可以对索引的碎片信息进行查询，此存储过程适用于SQL 2005/2008/2008R2/2012。

简单的模拟以下索引碎片产生的过程：

1）首先创建一个带索引的表，并且向表中插入一些记录：

--CREATE DATABSE 
create database testDB
GO

Use testDB
GO

--CREATE TABLE
CREATE TABLE testTB (C1 INT, C2 DATE, C3 VARCHAR(50))
GO

--CREATE INDEX
CREATE clustered INDEX index01 on testTB(C1)
GO

--INSERT 100 RECORDS INTO THE TABLE
DECLARE @i int
SET @i=1
WHILE @i<=100
BEGIN
INSERT INTO testTB VALUES(@i,'20121026','testdata')
set @i=@i+1
END

2）此时对sys.dm_db_index_physical_stats进行第一次查询，索引碎片百分比为0：

--QUERY INDEX FRAGMENTATION INFO
SELECT database_id,object_id,index_id,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED');

3）接下来我对testTB表依次进行了删除，更新和增加记录的操作：

--DELETE SOME RECORDS FROM TABLE
DECLARE @j int
set @j=10
while @j<=100
BEGIN
DELETE FROM testTB WHERE C1=@j
SET @j=@j+10
END

--UPDATE TABLE
UPDATE testTB SET C2='20121025' WHERE C2='20121026'


--GO ON INSERTING RECORDS TO TABLE
DECLARE @i int
SET @i=101
WHILE @i<=800
BEGIN
INSERT INTO testTB VALUES(@i,'20121026','testdata')
set @i=@i+1
END

4）结合索引信息表sys_indexs我们做一个更详细的查询，可以看到索引碎片已经产生：

--QUERY INDEX FRAGMENTATION INFO AGAIN
SELECT DB_NAME(V1.database_id) as 'Database Name',
OBJECT_NAME(V1.object_id) as 'Table Name',
v2.name as 'Index Name',V1.index_id as "Index ID",
V1.avg_fragmentation_in_percent as 'Average Percent Fragmentation'
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED') AS V1
JOIN sys.indexes AS V2 
ON V1.object_id=V2.object_id and V1.index_id=V2.index_id

mysql查看索引碎片比率 sql server索引碎片_Server_02