索引外部碎片和内部碎片

2024-06-05 13:32
文章标签 索引 内部 外部 碎片

本文主要是介绍索引外部碎片和内部碎片,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

毫无疑问,给表添加索引是有好处的,你要做的大部分工作就是维护索引,在数据更改期间索引可能产生碎片,所以一些维护是必要的。碎片可能是你查询产生性能问题的来源。 

那么到底什么是索引碎片呢?索引碎片实际上有2种形式:外部碎片内部碎片。不管哪种碎片基本上都会影响索引内页的使用。这也许是因为页的逻辑顺序错误(即外部碎片)或每页存储的数据量少于数据页的容量(内部错误)。无论索引产生了哪种类型的碎片,你都会因为它而面临查询的性能问题。 

外部碎片

当索引页不在逻辑顺序上时就会产生外部碎片。索引创建时,索引键按照逻辑顺序放在一组索引页上。当新数据插入索引时,新的键可能放在存在的键之间。为了让新的键按照正确的顺序插入,可能会创建新的索引页来存储需要移动的那些存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相邻。创建新页的过程会引起索引页偏离逻辑顺序。

例子:就好比我有2间居室,但不在一个屋子里。外部碎片多,则需要进行更多的跨区扫描,从而造成更多的IO操作

内部碎片

当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助,然而设置一个fill factor(填充因子)会在索引页上留下空间,服务器内部碎片会导致索引尺寸增加,从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能。

例子:就好比2居室就住了一个人,空余一间居室。行分布在更多的页中,内部碎片会造成数据行分布在更多的页中,从而加重了扫描的页树,也会降低查询性能.

 

碎片解决方法

一旦你确定表或索引有碎片问题,那么你有4个选择去解决那些问题: 

  • 删除并重建索引
  • 使用DROP_EXISTING子句重建索引
  • 执行DBCC DBREINDEX
  • 执行DBCC INDEXDEFRAG

尽管每一个技术都能达到你整理索引碎片的最终目的,但各有各的优缺点。 

删除并重建索引 

用DROP INDEX和CREATE INDEX或ALTER TABLE来删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时,对于查询它不在可用,查询性能也许会受到明显的影响,直到重建索引为止。另一个潜在的缺陷是当都请求索引的时候会引起阻塞,直到重建索引为止。通过其他的处理也能解决阻塞,就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆,聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。 

删除并重建索引的确有一个好处就是通过重新排序索引页,使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用,以使那些索引回到它们应该在的位置。

使用DROP_EXISTING子句重建索引 

为了避免在重建聚集索引时表上的非聚集索引重建两次,可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值,以避免非聚集索引重建两次。和删除并重建索引一样,该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。 

除了和上一个方法一样的好处之外,该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。 

执行DBCC DBREINDEX (推荐)

DBCC DBREINDEX类似于第二种方法,但它物理地重建索引,允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引,不象第二种方法。 

DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的,所以如果在完成之前中断了,那么你会丢失所有已经执行过的碎片。使用这个语句同样也是重建索引,但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的。 

执行DBCC INDEXDEFRAG 

DBCC INDEXDEFRAG(在SQLServer2000中可用)按照索引键的逻辑顺序,通过重新整理索引里存在的叶页来减少外部碎片,通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCC INDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理,不会引起其他方法的阻塞问题。 这种方式不会重建索引,也不会生成新的页,仅仅是整理,当遇到加锁的页时跳过,所以不会造成阻塞。但同时,整理效果会差于前三种.

 

填充因子

    用来设置页的使用情况,值:0-100 以避免页拆分。使用填充因子会减少更新或者插入时的分页次数,但由于需要更多的页,则会对应的损失查找性能 ,填充因子的概念(预留一定的空间存放插入和更新新增加的数据,以避免页拆分)重建索引固然可以解决碎片的问题.但是重建索引的代价不仅仅是麻烦,还会造成阻塞。影响使用.而对于数据比较少的情况下,重建索引代价并不大。而当索引本身超过百兆的时候。重建索引的时间将会很让人蛋疼.填充因子的作用正是如此。对于默认值来说,填充因子为0(0和100表示的是一个概念),则表示页面可以100%使用。所以会遇到前面update或insert时,空间不足导致分页.通过设置填充因子,可以设置页面的使用程度:使用填充因子会减少更新或者插入时的分页次数,但由于需要更多的页,则会对应的损失查找性能.

如何设置填充因子的值:

    如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页,但同时因为需要更多的页,所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.

    具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:

        1.当读写比例大于100:1时,不要设置填充因子,100%填充

        2.当写的次数大于读的次数时,设置50%-70%填充

        3.当读写比例位于两者之间时80%-90%填充

   上面的数据仅仅是我的看法,具体设置的数据还要根据具体情况进行测试才能找到最优.

这篇关于索引外部碎片和内部碎片的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1033190

相关文章

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

STM32内部闪存FLASH(内部ROM)、IAP

1 FLASH简介  1 利用程序存储器的剩余空间来保存掉电不丢失的用户数据 2 通过在程序中编程(IAP)实现程序的自我更新 (OTA) 3在线编程(ICP把整个程序都更新掉) 1 系统的Bootloader写死了,只能用串口下载到指定的位置,启动方式也不方便需要配置BOOT引脚触发启动  4 IAP(自己写的Bootloader,实现程序升级) 1 比如蓝牙转串口,

FreeRTOS内部机制学习03(事件组内部机制)

文章目录 事件组使用的场景事件组的核心以及Set事件API做的事情事件组的特殊之处事件组为什么不关闭中断xEventGroupSetBitsFromISR内部是怎么做的? 事件组使用的场景 学校组织秋游,组长在等待: 张三:我到了 李四:我到了 王五:我到了 组长说:好,大家都到齐了,出发! 秋游回来第二天就要提交一篇心得报告,组长在焦急等待:张三、李四、王五谁先写好就交谁的

java线程深度解析(一)——java new 接口?匿名内部类给你答案

http://blog.csdn.net/daybreak1209/article/details/51305477 一、内部类 1、内部类初识 一般,一个类里主要包含类的方法和属性,但在Java中还提出在类中继续定义类(内部类)的概念。 内部类的定义:类的内部定义类 先来看一个实例 [html]  view plain copy pu

贝壳面试:什么是回表?什么是索引下推?

尼恩说在前面 在40岁老架构师 尼恩的读者交流群(50+)中,最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团的面试资格,遇到很多很重要的面试题: 1.谈谈你对MySQL 索引下推 的认识? 2.在MySQL中,索引下推 是如何实现的?请简述其工作原理。 3、说说什么是 回表,什么是 索引下推 ? 最近有小伙伴在面试 贝壳、soul,又遇到了相关的

Mysql高级篇(中)——索引介绍

Mysql高级篇(中)——索引介绍 一、索引本质二、索引优缺点三、索引分类(1)按数据结构分类(2)按功能分类(3) 按存储引擎分类(4) 按存储方式分类(5) 按使用方式分类 四、 索引基本语法(1)创建索引(2)查看索引(3)删除索引(4)ALTER 关键字创建/删除索引 五、适合创建索引的情况思考题 六、不适合创建索引的情况 一、索引本质 索引本质 是 一种数据结构,它用

ElasticSearch 6.1.1 通过Head插件,新建索引,添加文档,及其查询数据

ElasticSearch 6.1.1 通过Head插件,新建索引,添加文档,及其查询; 一、首先启动相关服务: 二、新建一个film索引: 三、建立映射: 1、通过Head插件: POST http://192.168.1.111:9200/film/_mapping/dongzuo/ {"properties": {"title": {"type":

ElasticSearch 6.1.1运用代码添加索引及其添加,修改,删除文档

1、新建一个MAVEN项目:ElasticSearchTest 2、修改pom.xml文件内容: <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.or

postgres数据库中如何看查询是否走索引,以及在什么情况下走索引

在 PostgreSQL 中,可以通过 EXPLAIN 或 EXPLAIN ANALYZE 查看查询计划,以判断查询是否使用了索引。除此之外,了解索引的使用条件对于优化查询性能也很重要。 1. 如何查看查询是否使用索引 使用 EXPLAIN 查看查询计划 EXPLAIN 显示 PostgreSQL 如何执行查询,包括是否使用索引。 EXPLAIN SELECT * FROM users WH

数据库系统 第42节 数据库索引简介

数据库索引是数据库表中一个或多个列的数据结构,用于加快数据检索速度。除了基础的B-Tree索引,其他类型的索引针对特定的数据类型和查询模式提供了优化。以下是几种不同类型的索引及其使用场景的详细说明和示例代码。 1. 位图索引 (Bitmap Index) 位图索引适用于具有少量不同值的列(例如性别、国家代码等),它使用位图来表示数据,从而提高查询效率。 适用场景:当列中的值域较小,且数据分布