RDS SQL Server - 最佳实践 - 高CPU使用率系列之二索引碎片

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: # 摘要 上一篇文章分析了高CPU使用率的原因之一是索引缺失,接下来本系列文章之二的“索引碎片”是CPU高使用率的又一常见的原因。解决索引碎片问题是解决SQL Server服务响应缓慢,查询超时的又一利器。 # 问题引入 “鸟哥,我上一篇文章分享了因为索引缺失导致CPU高使用率的话题,反响不错。接下来,我打算分享索引碎片导致CPU高使用率的话题。”,菜鸟主动找到老鸟汇报工作。 上一篇文章详

摘要

上一篇文章分析了高CPU使用率的原因之一是索引缺失,接下来本系列文章之二的“索引碎片”是CPU高使用率的又一常见的原因。解决索引碎片问题是解决SQL Server服务响应缓慢,查询超时的又一利器。

问题引入

“鸟哥,我上一篇文章分享了因为索引缺失导致CPU高使用率的话题,反响不错。接下来,我打算分享索引碎片导致CPU高使用率的话题。”,菜鸟主动找到老鸟汇报工作。
上一篇文章详情参见链接:RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失

索引碎片是什么

索引碎片既指索引文件页中的空白空间;又指被Page Split的索引页;还指索引失序的数据页。前面两种我们称之为索引内部碎片,后面一种我们叫着索引外部碎片。
前面是干瘪瘪的理论描述,下面举一个关于电话簿实例就比较好理解了:假如电话簿按照城市名称、姓名和电话号码组合排序的方式来存储所有人员的联系电话。以下几种情况都需要更新电话簿:
 当一个人更换电话号码的时候或者改名字的时候(西方国家女子结婚后,会修改姓氏为老公的姓,比如:奥巴马的老婆叫米歇尔-奥巴马):需要更新操作
 当一个人从一个城市搬迁到另一个城市的时候:需要删除原来的记录,在相应的城市插入新记录
 当有新人办理了电话业务的时候:需要在相应城市插入记录
 当作废电话号码的时候:需要删除对应条目
这些操作带来的后果是:更新操作可能导致失序(out of order);删除操作导致空白条目(empty space);插入操作导致分页(page split)。结果就是最终形成电话簿(类似于索引)的碎片外部碎片和内部碎片。

索引碎片的危害

清楚了索引是什么的问题,我们来看看索引碎片的危害。
假如,一本完整存放(没有任何碎片)的电话簿刚好1000页,而由于前面讲的种种操作,导致了10%的碎片,那么最终我们需要1100页来存放。我们每一本书将浪费100页的纸张来印刷,也将浪费掉每个人10%的查询和阅读时间。放在SQL Server索引碎片的角度,原理是相通的:由于SQL Server读取数据的最小单位是数据页,而不是单条记录,所以,相同的查询语句需要SQL Server读取更多的磁盘宽度,加之索引碎片会浪费更多的内存资源来存放读取到的数据。因此,碎片化程度越高意味着更高的内存使用浪费和更低的查询性能。微软建议索引碎片率在5%到30%之间,做索引重组;碎片率超过30%,做索引重建工作。
详情参考链接:Reorganize and Rebuild Indexes

解决方法

我们从以下几个方面来描述解决方法:
 模拟产生索引碎片
 获取索引碎片信息
 重建索引
 前后对比

模拟产生索引碎片

我假设需要变更100万条数据记录,这些变更包括UPDATE、DELETE和INSERT操作。在前一篇文章创建的表dbo.SalesOrder基础上,我们使用如下方法变更数据:

USE TestDb
GO

SET NOCOUNT ON

DECLARE
    @do INT = 0
    , @loop INT = 1000000
    , @sql NVARCHAR(MAX) = N''
;

WHILE @do < @loop
BEGIN
        SET @sql = 
        CASE  
            WHEN @do % 3 = 0
                THEN N'DELETE TOP(1) FROM dbo.SalesOrder;'
            WHEN @do % 3 = 1
                THEN N'UPDATE TOP(1) A SET OrderQty = OrderQty + 1, Price = Price - 1, OrderDate = GETDATE() FROM dbo.SalesOrder AS A'
            WHEN @do % 3 = 2
                THEN N'INSERT INTO dbo.SalesOrder(OrderID, ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment)
                            SELECT TOP 1 NEWID(),ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment
                            FROM dbo.SalesOrder
                '
            ELSE N''
        END

        EXEC sys.sp_executesql @sql

        IF @do % 2000 = 0
        BEGIN
            RAISERROR('%d rows', 10, 1, @do) WITH NOWAIT
        END

        SET @do = @do + 1;
END
GO

获取索引碎片信息

我们可以使用系统函数sys.dm_db_index_physical_stats来获取索引碎片信息。查询索引碎片的方法如下:

USE TestDb
GO

SELECT 
        db_name(database_id) AS db_name
        ,object_name(ix.object_id) AS object_name
        ,ix.name
        ,avg_fragmentation_in_percent
        ,*
FROM sys.dm_db_index_physical_stats(db_id(),object_id('dbo.SalesOrder','U'),NULL,NULL,'LIMITED') AS fra
    CROSS APPLY sys.indexes AS ix WITH (NOLOCK) 
WHERE ix.object_id = fra.object_id
    and ix.index_id = fra.index_id

EXEC sys.sp_spaceused SalesOrder

查询结果展示如下图所示:
01.png

重建索引

找准了解决问题的方向,处理起来就变得轻车熟路了,从查询结果我发现主键碎片率达到了92.2%,已经是非常之高了。重建索引的方法:

USE TestDb
GO

ALTER INDEX ALL
ON dbo.SalesOrder REBUILD
WITH (ONLINE = ON, FILLFACTOR = 90)
;

再次执行索引碎片查询,结果如下:
02.png

前后对比

重整索引碎片以后,主键碎片率从92.2%降低到0.1%;索引空间、数据空间和总空间大小分别减少了4.3%、52.8%和72.4%,平均空间减少达43.17%。
03.png

注意事项

在产品环境中重建索引需要十分小心,原因是:
 重建索引会消耗大量的系统I/O读写资源。
 重建索引会导致查询进程的死锁或者锁等待,尤其是非企业版SQL Server(企业版可以使用ONLINE选项来最大限度规避这个问题)。
 重建索引会导致数据库日志文件暴涨,而因此会给Database Mirroring、Log Shipping和Backup带来压力。
所以,请选择业务低谷期进行索引碎片重整的操作。

总结

这篇文章从索引碎片是什么,有哪些危害,如何解决碎片问题和需要注意的事项等方面,详细探讨了导致高CPU使用率的又一常见原因--索引碎片。

引用

Stop Worrying About SQL Server Fragmentation
Reorganize and Rebuild Indexes

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
157 3
|
4月前
|
SQL 存储 监控
SQL数据库安装指南:步骤详解与最佳实践
安装和配置SQL数据库可能是一个复杂的过程,但通过遵循本文提供的详细步骤和最佳实践,您可以确保数据库的成功安装和高效运行。无论您是初学者还是经验丰富的数据库管理员,掌握SQL数据库的安装和管理技能都是至关重要的。通过不断学习和实践,您将能够更好地利用SQL数据库来支持您的业务需求和数据分析工作。记住,定期维护和优化数据库是保证其长期性能和稳定性的关键。祝您在安装和配置SQL
|
1月前
|
SQL 存储 数据库
SQL语句给予用户权限:技巧、方法与最佳实践
在数据库管理中,为用户分配适当的权限是确保数据安全性和操作效率的关键步骤
|
1月前
|
关系型数据库 MySQL 数据库
MySQL数据库:基础概念、应用与最佳实践
一、引言随着互联网技术的快速发展,数据库管理系统在现代信息系统中扮演着核心角色。在众多数据库管理系统中,MySQL以其开源、稳定、可靠以及跨平台的特性受到了广泛的关注和应用。本文将详细介绍MySQL数据库的基本概念、特性、应用领域以及最佳实践,帮助读者更好地理解和应用MySQL数据库。二、MySQL
110 5
|
1月前
|
SQL 数据管理 数据库
文章初学者指南:SQL新建数据库详细步骤与最佳实践
引言:在当今数字化的世界,数据库管理已经成为信息技术领域中不可或缺的一部分。作为广泛使用的数据库管理系统,SQL已经成为数据管理和信息检索的标准语言。本文将详细介绍如何使用SQL新建数据库,包括准备工作、具体步骤和最佳实践,帮助初学者快速上手。一、准备工作在开始新建数据库之前,你需要做好以下准备工作
120 3
|
2月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
3月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`&lt;s:checkbox&gt;`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
89 0
|
3月前
|
前端开发 开发者
Vaadin Grid的秘密武器:打造超凡脱俗的数据展示体验!
【8月更文挑战第31天】赵萌是一位热爱UI设计的前端开发工程师。在公司内部项目中,她面临大量用户数据展示的挑战,并选择了功能强大的Vaadin Grid来解决。她在技术博客上分享了这一过程,介绍了Vaadin Grid的基本概念及其丰富的内置功能。通过自定义列和模板,赵萌展示了如何实现复杂的数据展示。
43 0
|
3月前
|
SQL 开发框架 .NET
深入解析Entity Framework Core中的自定义SQL查询与Raw SQL技巧:从基础到高级应用的全面指南,附带示例代码与最佳实践建议
【8月更文挑战第31天】本文详细介绍了如何在 Entity Framework Core (EF Core) 中使用自定义 SQL 查询与 Raw SQL。首先,通过创建基于 EF Core 的项目并配置数据库上下文,定义领域模型。然后,使用 `FromSqlRaw` 和 `FromSqlInterpolated` 方法执行自定义 SQL 查询。此外,还展示了如何使用 Raw SQL 进行数据更新和删除操作。最后,通过结合 LINQ 和 Raw SQL 构建动态 SQL 语句,处理复杂查询场景。本文提供了具体代码示例,帮助读者理解和应用这些技术,提升数据访问层的效率和灵活性。
198 0
|
3月前
|
SQL 数据库 索引
SQL 编程最佳实践简直太牛啦!带你编写高效又可维护的 SQL 代码,轻松应对数据库挑战!
【8月更文挑战第31天】在SQL编程中,高效与可维护的代码至关重要,不仅能提升数据库性能,还降低维护成本。本文通过案例分析探讨SQL最佳实践:避免全表扫描,利用索引加速查询;合理使用JOIN,避免性能问题;避免使用`SELECT *`,减少不必要的数据传输;使用`COMMIT`和`ROLLBACK`确保事务一致性;添加注释提高代码可读性。遵循这些实践,不仅提升性能,还便于后期维护和扩展。应根据具体情况选择合适方法并持续优化SQL代码。
53 0