SQL Server通过整理索引碎片和重建索引提高速度

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 本文章转载:http://database.51cto.com/art/201108/282408.htm SQL Server数据库中,当索引碎片太多时,就会拖慢数据库查询的速度。这时我们可以通过整理索引碎片和重建索引来解决,本文我们主要就介绍了这部分内容,希望能够对您有所帮助。

本文章转载:http://database.51cto.com/art/201108/282408.htm

SQL Server数据库中,当索引碎片太多时,就会拖慢数据库查询的速度。这时我们可以通过整理索引碎片和重建索引来解决,本文我们主要就介绍了这部分内容,希望能够对您有所帮助。

 

SQL Server数据库操作中,当数据库中的记录比较多的时候,我们可以通过索引来实现查询。但是当索引碎片太多的时候,就会很严重地影响到查询的速度。这时候我们可以采取两种方法来解决:一种时整理索引碎片,另一种是重建索引

 

索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构,用来帮助查找和整理数据。索引的重要性体现在能够使数据库引擎快速返回查询
结果。当对索引所在的基础数据表进行修改时(包括插入、删除和更新等操作),会导致索引碎片的产生。当索引的逻辑排序和基础表或视图的物理排序不匹配时,
就会产生索引碎片。随着索引碎片的不断增多,查询响应时间就会变慢,查询性能也会下降。在SQL Server
2005中,要解决这个问题,要么重新组织索引要么重新生成索引。

 

索引碎片的产生:http://blog.sina.com.cn/s/blog_792e033201013fkj.html

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

 

 

怎样确定索引是否有碎片? http://blog.tianya.cn/blogger/post_read.asp?BlogID=2587659&PostID=24488142
  
  SQLServer提供了一个数据库命令――DBCC SHOWCONTIG――来确定一个指定的表或索引是否有碎片。
  DBCC SHOWCONTIG
  数据库平台命令,用来显示指定的表的数据和索引的碎片信息。
  
  DBCC SHOWCONTIG 权限默认授予 sysadmin固定服务器角色或 db_owner 和 db_ddladmin固定数据库角色的成员以及表的所有者且不可转让。
  语法(SQLServer2000)
  
  DBCC SHOWCONTIG
  [ ( { table_name | table_id| view_name | view_id }
  [ , index_name | index_id ]
  )
  ]
  [ WITH { ALL_INDEXES
  | FAST [ , ALL_INDEXES ]
  | TABLERESULTS [ , { ALL_INDEXES } ]
  [ , { FAST | ALL_LEVELS } ]
  }
  ]
  
  语法(SQLServer7.0)
  
  DBCC SHOWCONTIG
  [ ( table_id [,index_id ]
  )
  ]

 

那么SQL Server如何的定期清理索引碎片呢?可以做个Job作业计划,定期的执行。

--更新统计信息
EXEC sp_updatestats



---索引优化
DECLARE @tableName NVARCHAR(50) ,
    @indexName NVARCHAR(50) ,
    @fragmentPercent NVARCHAR(20) ,
    @sql NVARCHAR(200)= ''
DECLARE indexFragment_cursor CURSOR
FOR
    SELECT  o.name AS tableName ,
            ix.name AS indexName ,
            avg_fragmentation_in_percent AS fragmentPercent--,
    --dip.fragment_count,
    --dip.avg_fragment_size_in_pages
    FROM    sys.dm_db_index_physical_stats(DB_ID() ,NULL ,NULL ,NULL ,NULL) dip
            INNER JOIN sys.indexes ix ON ix.index_id = dip.index_id
                                         AND ix.object_id = dip.object_id
            INNER JOIN sys.objects o ON ix.object_id = o.object_id
    WHERE   dip.index_id > 0
            AND avg_fragmentation_in_percent > 5
    ORDER BY avg_fragmentation_in_percent DESC

--打开游标
OPEN indexFragment_cursor 
FETCH NEXT 
    FROM indexFragment_cursor 
    INTO @tableName ,@indexName ,@fragmentPercent
WHILE @@FETCH_STATUS = 0 
    BEGIN
    --print @tableName+'----'+@indexName++'----'+@fragmentPercent
        SET @sql = 'ALTER INDEX ' + QUOTENAME(@indexName) + ' on '
            + QUOTENAME(@tableName)
            + CASE WHEN @fragmentPercent <= '30' THEN ' REORGANIZE;'
                   WHEN @fragmentPercent > '30' THEN ' REBUILD;'
              END
    --print @sql
        EXEC(@sql)
    --移到下一行记录
        FETCH NEXT 
        FROM indexFragment_cursor 
        INTO @tableName ,@indexName ,@fragmentPercent
    END

--关闭,释放游标
CLOSE indexFragment_cursor
DEALLOCATE indexFragment_cursor



GO

  SQL 2008 R2索引的重建:http://www.2cto.com/database/201204/128616.html

项目升级数据库由SQL2000升级到2008R2,今天对数据库表进行碎片扫描,发现有些表碎片较大,于是决定重建索引,联机帮助是最好的老师,将相关脚本摘录备后查。

参考sys.dm_db_index_physical_stats

检查索引碎片情况

 

SELECT

    OBJECT_NAME(object_id) as objectname,

    object_id AS objectid,  www.2cto.com  

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS fra

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 (来源于联机帮助)

 

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130); 

DECLARE @objectname nvarchar(130); 

DECLARE @indexname nvarchar(130); 

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;  www.2cto.com  

DECLARE @command nvarchar(4000); 

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 

-- and convert object and index IDs to names.

SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

 

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

 

-- Open the cursor.

OPEN partitions;

 

-- Loop through the partitions.

WHILE (1=1)

    BEGIN;

        FETCH NEXT

           FROM partitions

           INTO @objectid, @indexid, @partitionnum, @frag;

        IF @@FETCH_STATUS < 0 BREAK;

        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

        FROM sys.objects AS o  

        JOIN sys.schemas as s ON s.schema_id = o.schema_id

        WHERE o.object_id = @objectid;

        SELECT @indexname = QUOTENAME(name)

        FROM sys.indexes

        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)

        FROM sys.partitions

        WHERE object_id = @objectid AND index_id = @indexid;

 

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

        IF @frag < 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

        IF @frag >= 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

        IF @partitioncount > 1

            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  www.2cto.com  

        EXEC (@command);

        PRINT N'Executed: ' + @command;

    END;

 

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

 

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

  

 UPDATE STATISTICS更新统计信息来提高查询效率. 

该命令在一张表或者索引了的视图上更新查询优化统计数字信息. 默认情况下, 查询优化器已经更新了必要的用来提高查询计划的统计信息; 在某些情况下, 你可以通过使用UPDATE STATISTICS 命令或者存储过程sp_updatestats 来比默认更频繁地更新统计信息来提高查询效率. 

 

更新统计信息能确保查询能以最新的统计信息来编译. 然而, 更新统计信息会引起查询的重新编译. 我们建议不要过于频繁地更新统计信息, 因为这里有一个在提高查询计划和用来重新编译查询的权衡. 具体的权衡要看你的应用程序而定.

目录
相关文章
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
6月前
|
SQL 存储 关系型数据库
SQL优化策略与实践:组合索引与最左前缀原则详解
本文介绍了SQL优化的多种方式,包括优化查询语句(避免使用SELECT *、减少数据处理量)、使用索引(创建合适索引类型)、查询缓存、优化表结构、使用存储过程和触发器、批量处理以及分析和监控数据库性能。同时,文章详细讲解了组合索引的概念及其最左前缀原则,即MySQL从索引的最左列开始匹配条件,若跳过最左列,则索引失效。通过示例代码,展示了如何在实际场景中应用这些优化策略,以提高数据库查询效率和系统响应速度。
186 10
|
7月前
|
SQL 索引
【YashanDB知识库】字段加上索引后,SQL查询不到结果
【YashanDB知识库】字段加上索引后,SQL查询不到结果
|
12月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
1009 2
|
8月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
187 2
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
1479 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
12月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
166 3
|
12月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
2015 5
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")