维护索引(3)——通过重建索引提高性能

前言:

重建一个索引只是在内部删除并重建索引,使得碎片消失、统计信息更新、物理顺序重新排列组织。它会压缩数据页,按照填充因子填充适当的数据。如果有需要,也会添加新的数据页。这些操作有利于提高数据查找的速度,但是这个工作如果发生在大表上面,将是非常耗时耗资源的。

准备工作:

首先先要决定是否达到了重建索引的临界值。否则,重组索引会更好。当碎片超过30%,那么重建索引会比较好。

重建索引有两种方式,在重建之前应该考虑使用哪种会更好:

1、 脱机:脱机重建索引是默认选项。它会锁住整个表,知道重建结束,没有人可以访问这个表。如果表非常大,这将持续几个小时甚至更久。但是它运行得更快,并且占用Tempdb的资源更少。

2、 联机:如果环境不允许脱机,那么可以使用联机重建,但是会占用非常多的资源,如果表数据类型为varchar(max),nvarchar(max)和text类型,将不会在联机模式下工作。

注意:是否联机重建索引只有开发版和企业版可用。其他版本只有脱机重建。

步骤:

1、 创建测试表:

  1. USE [AdventureWorks]
  2. GO
  3. IF EXISTS( SELECT *
  4. FROM sys.objects
  5. WHERE object_id = OBJECT_ID(N'[dbo].[ordDemo]')
  6. AND type IN( N'U' ) )
  7. DROP TABLE [dbo].[ordDemo]
  8. GO
  9. CREATE TABLE [dbo].[ordDemo]
  10. (
  11. [OrderID] [int] IDENTITY(1, 1)
  12. NOT NULL ,
  13. [OrderDate] [datetime] NULL ,
  14. [Amount] [money] NULL ,
  15. [Refno] [int] NULL
  16. )
  17. ON [PRIMARY]
  18. GO

再创建一个聚集索引idx_refno:

  1. USE [AdventureWorks]
  2. GO
  3. IF EXISTS ( SELECT *
  4. FROM sys.indexes
  5. WHERE object_id = OBJECT_ID(N'[dbo].[ordDemo]')
  6. AND name = N'idx_refno' )
  7. DROP INDEX [idx_refno] ON [dbo].[ordDemo] WITH ( ONLINE = OFF )
  8. GO

现在使用下面的脚本重建索引:

  1. --使用联机方式重建索引idx_refno
  2. ALTER INDEX [idx_refno] ON [ordDemo] REBUILD WITH (FILLFACTOR=80,ONLINE =ON)
  3. GO
  4. --使用脱机方式重建索引idx_refno
  5. ALTER INDEX [idx_refno] ON [ordDemo] REBUILD WITH (FILLFACTOR=80,ONLINE =OFF)
  6. GO
  7. --使用脱机方式重建表上所有索引:
  8. ALTER INDEX ALL ON [ordDemo] REBUILD WITH (FILLFACTOR=80,ONLINE =OFF )
  9. GO
  10. --使用DROP_EXISTING 来重建索引:
  11. CREATE CLUSTERED INDEX [idx_refno] ON [ordDemo](Refno)
  12. WITH (DROP_EXISTING=ON ,FILLFACTOR=70,ONLINE=ON )
  13. GO
  14. --使用DBCC DBREINDEX重建标上所有索引:
  15. DBCC DBREINDEX('ordDemo')
  16. GO
  17. --重建带有填充因子的索引:
  18. DBCC DBREINDEX('ordDemo','idx_refno',90)
  19. GO

2、 注意,DBCC DBREINDEX命令将在未来版本删除,所以不建议使用,并尽快改成其他方式实现。

分析:

在重建索引时,会锁住资源,直到进程完毕为止。重建会移除空白或者未使用的页,创建新的页,如果达到填充因子的限定,会分页,并以B-TREE方式存放这些数据页。

扩充信息:

基于个人经验,建议重建大表的索引时,把恢复模式改成大容量日志或者简单模式,避免日志文件的过度增长。但是更改恢复模式应该慎重,并在更改后立即做日志备份。

因为重建大表索引会非常耗时,所以不要不耐烦并停止重建操作,这样会引起一些危险的后果,并可能使得数据库进入恢复模式。

重建索引需要有sysadmin、db_onwer或者db_ddladmin角色。

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏学习有记

深入非聚集索引:SQL Server索引进阶 Level 2

12730
来自专栏数据和云

12C 新特性 | 标量子查询自动转换

优化器是 Oracle 数据库最引人入胜的部件之一,因为它对每一个 SQL 语句的处理都必不可少。优化器为每个 SQL 语句确定最有效的执行计划,这是基于给定的...

37570
来自专栏沃趣科技

SQL优化案例 | 从Exadata迁移到国产沃趣一体机一般方法探究(四)

存储技术的发展,闪存的出现,大大提升了数据库一体机的性能,随着闪存成本的降低,我相信未来Exadata也不会再有flashcache、flashlog相关的特性...

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

关于收缩数据文件的尝试(r5笔记第34天)

在数据库中对于数据文件都是提前规划,不够就加的情况,很少会留意到其实有些数据文件那么大,其实条件允许也是可以收缩收缩的。 这种情况在本地测试环境中尤为突出,本来...

35860
来自专栏Hadoop数据仓库

HAWQ技术解析(八) —— 大表分区

一、HAWQ中的分区表         与大多数关系数据库一样,HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表,外部分区表在后面“外部数据”篇讨...

27270
来自专栏软件测试经验与教训

测试工程师SQL面试题

测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。

2K30
来自专栏Hadoop实操

如何在Impala中实现拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义的,即是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。传统数据仓库一般采用拉链的方式保留主数据(...

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

MySQL 5.7中锁的一个通用问题

前几天分析了一个死锁的问题,有一个网友看了以后,就发了邮件给我问一个问题。一般来说,能够发送邮件提出问题的同学,都是很认真的,因为他要准备好日志,准备好操作过...

38890
来自专栏数据之美

一例 Hive join 优化实战

由于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异,因此,传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive ...

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

MySQL反连接的优化总结(r10笔记第51天)

今天同事有一个环境发现一条语句执行时间很长,感到非常奇怪。刚好有些时间,就抽空琢磨了下这个问题。 总体来看这个环境还是相对比较繁忙的,线程大概是200多个。 #...

32570

扫码关注云+社区

领取腾讯云代金券