维护索引(1)——索引碎片

前言:

DBA的日常任务并不仅仅是创建需要的索引在对应的列上,实际上,DBA还要保持索引创建的高标准。

周而复始,DBA必须盯着一些非常重要的信息:

1、 索引的碎片级别

2、 丢失索引

3、 无效索引

查找索引碎片:

如果索引没有正确维护,那么碎片往往会成为性能瓶颈。微软建议当碎片百分比在5~30之间的时候,使用重组索引来代替更加耗资源的重建索引。如果碎片超过30%,可以使用重建索引。但是这仅仅是建议而不是绝对的事情。而且从2000开始,这个建议就没有改变过,但是从2000到2012,索引已经改变了许多。

现在先来检查一下环境,以便评估百分比的级别是否达到重建的地步。因为在进行这步耗资源的操作前,会有很多因素需要考虑,其中主要有:

1、 备份策略

2、 服务器工作负载

3、 可用磁盘空间

4、 恢复模式

虽然碎片对查询性能有很大的影响,但是它依然是基于表的,并且基于你如何使用表。大部分情况下,如果你仅仅从一个表中通过查询聚集索引上的主键来返回一条数据,那么碎片将不在考虑范围。

准备工作:

了解碎片之后,接着就要知道如何确定索引的碎片?此时只需要使用sys.dm_db_index_physical_stats系统函数和系统目录sys.Indexes联合查询即可。

步骤:

收集你的索引的碎片是第一个重要任务,可以使用以下脚本实现:

USE AdventureWorks 
 
GO  
 
--收集特定表上所有索引、堆的信息  
 
SELECT  sysIn.name AS IndexName ,  
 
 sysIn.index_id ,  
 
 func.avg_fragmentation_in_percent ,  
 
 func.index_type_desc AS IndexType ,  
 
 func.page_count 
 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'ordDemo'), NULL,  
 
                                       NULL, NULL) AS func 
 
 INNER JOIN sys.indexes AS sysIn ON func.object_id = sysIn.object_id 
 
                                           AND func.index_id = sysIn.index_id 
 
--聚集索引的Index_id为,非聚集索引的Index_id总是大于,如果不想看堆表的信息,可以使用  
 
--where sysIn.index_id>0;         
 
 
 
--收集在数据库中所有可用的索引信息,下面查询可能会运行时间比较久:  
 
USE AdventureWorks 
 
GO  
 
--收集特定表上所有索引、堆的信息  
 
SELECT  sysIn.name AS IndexName ,  
 
 sysIn.index_id ,  
 
 func.avg_fragmentation_in_percent ,  
 
 func.index_type_desc AS IndexType ,  
 
 func.page_count 
 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS func 
 
 INNER JOIN sys.indexes AS sysIn ON func.object_id = sysIn.object_id 
 
                                           AND func.index_id = sysIn.index_id 
 
WHERE sysIn.index_id > 0 ;  

分析:

在函数sys.dm_db_index_physical_stats中传入DB_ID()可以限制只返回当前数据库的信息,而object_ID(N’ordDemo’)是返回这个数据库中这个表的索引信息。这个系统函数:sys.dm_db_index_physical_stats可以提供非常详细的信息,为了知道索引名称,需要关联sys.indexes系统目录来获得名称。

扩展信息:

当在索引叶子节点中的数据逻辑顺序和物理顺序不一致的时候,就会出现碎片。当你创建一个索引时,会对所有东西排序,但是当数据通过DML语句操作是,并不能保证新数据能适应数据页的顺序。当你删除数据时,不仅只从实际数据页上删除数据,也会释放数据也的空间来用于其他数据的使用,这就会产生碎片。

记住,当索引第一次创建是,只有少量甚至没有碎片,但是在增删改操作后,就开始出现碎片。

原文发布于微信公众号 - 我为Net狂(dotNetCrazy)

原文发表时间:2016-10-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于interval partitioning(r3笔记65天)

今天一个开发的同事问我一个问题,说在系统中目前需要一个临时的解决方案,定义了一张表,目前想设定为分区表,因为会和外围系统交互产生大量的数据,所以需要按照小时来做...

2918
来自专栏北京马哥教育

五分钟 SQL Server 学习入门——基本篇

? 作者:My_heart_ 来源:http://blog.csdn.net/my_heart_/article/details/62425140 首先相信...

3934
来自专栏智能合约

数据插入失败引发的主键auto_increment问题

3113
来自专栏JAVA烂猪皮

用 Node + MySQL 如何处理 100G 数据

通过这个 Node.js 和 MySQL 示例项目,我们将看看如何有效地处理 数十亿行 占用 数百GB 存储空间的数据。

2035
来自专栏码神联盟

碎片化 | 第四阶段-35-Struts2-Spring结合jdbc实现查询列表-视频

如清晰度低,可转PC网页观看高清版本: http://v.qq.com/x/page/q0566q6xkdu.html 结合jdbc实现查询功能 步骤: 1...

3609
来自专栏用户画像

sql server 实验5.2 触发器的建立与使用

一、实验目的                                                     

1842
来自专栏高性能服务器开发

数据库进阶3 Mysql 性能优化20个原则(1)

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

1041
来自专栏北京马哥教育

数据库基础知识:数据库中的约束和三大范式

? 一.数据库中的范式: 范式, 英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后...

3147
来自专栏码神联盟

mysql数据库常见锁机制

关于互联网常见层次架构,由于小编还没整理完毕(预计周四推送),先来一篇数据库的干货,来满足下大家的胃口,关于mysql的行级锁、表级锁、页级锁的分析,这个在行业...

4229
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

36414

扫码关注云+社区

领取腾讯云代金券