RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: CPU高使用率往往会导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死,可以说CPU高使用率是数据库这种后台进程服务的第一大杀手。本系列文章之一的“索引缺失”就是CPU高使用率的最常见的原因之一。

摘要

CPU高使用率往往会导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死,可以说CPU高使用率是数据库这种后台进程服务的第一大杀手。本系列文章之一的“索引缺失”就是CPU高使用率的最常见的原因之一。

问题引入

“鸟啊,我们平时在服务阿里云RDS SQL Server客户的过程中,遇到最多的一个问题就是,客户反馈RDS SQL Server数据库CPU使用率很高(有时超过90%,甚至到100%),导致查询缓慢甚至超时,这类问题要如何解决啊?”。老鸟已经被类似的问题烦透了。
“鸟哥,关于CPU高使用率高问题,原因各式各样,不是一两句话能够说得清楚的。”,菜鸟开始卖关子了:“那,要不这样吧,我写一个专题系列文章来分析各种场景,以解决RDS SQL Server CPU高使用率的问题吧。”。

场景分析

关系型数据库(RDBMS)系统中,索引缺失最为常见会导致I/O读取很高,进而导致CPU使用率很高。这是因为当查询优化器在执行计划评估过程中,发现没有合适的索引可以使用时,不得不选择走全表扫描(Table Scan)或者近似于全表扫描的操作(Clustered Index Scan)来获取所需要的数据。这种大面积的数据扫面会导致I/O子系统读取操作频繁,SQL Server需要读取大量的数据并加载到内存中,这些操作最后都会使得CPU使用率飙高。这种场景中,解决CPU高使用率的问题,其实就变成了解决索引缺失的问题。我们可以从下面的例子中来看看如何发现和解决索引缺失的问题。

解决方法

在这里,我们将这个例子详细分解为五个小步骤:
 测试环境:搭建简单的测试环境。
 执行查询:创建缺失索引前后用于做性能对比的查询语句
 缺失索引:查找缺失索引的方法
 解决问题:创建缺失的索引
 效率对比:创建缺失索引前后的性能对比

测试环境

创建测试环境包括:创建测试数据库、测试表对象和初始化200万条记录。

-- Create testing database
IF DB_ID('TestDb') IS NULL
    CREATE DATABASE TestDb;
GO

USE TestDb
GO

-- create demo table SalesOrder
IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SalesOrder
END
GO

CREATE TABLE dbo.SalesOrder
(
    RowID INT IDENTITY(1,1) NOT NULL
    , OrderID UNIQUEIDENTIFIER NOT NULL
    , ItemID INT NOT NULL
    , UserID INT NOT NULL
    , OrderQty INT NOT NULL
    , Price DECIMAL(8,2) NOT NULL
    , OrderDate DATETIME NOT NULL 
        CONSTRAINT DF_OrderDate DEFAULT(GETDATE())
    , LastUpdateTime DATETIME NULL
    , OrderComment NVARCHAR(100) NULL
    , CONSTRAINT PK_SalesOrder PRIMARY KEY(
        OrderID
    )
);

-- data init for 2 M records.
;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(2000000)
    OrderID = NEWID()
    ,ItemIDRound = abs(checksum(newid()))
    ,Price = a.a * b.a * 10
    ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
), DATA
AS(
SELECT 
    OrderID
    ,ItemID = cast(ROUND((1300 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 101), 0) as int)
    ,UserID = cast(ROUND((500 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 10000), 0) as int)
    ,OrderQty
    ,Price = cast(Price AS DECIMAL(8,2))
    ,OrderDate = dateadd(day, -cast(ROUND((50 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 1), 0) as int) ,GETDATE())
FROM RoundData
)
INSERT INTO dbo.SalesOrder(OrderID, ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment)
SELECT 
    OrderID
    , ItemID
    , UserID
    , OrderQty
    , Price
    , OrderDate
    , LastUpdateTime = OrderDate
    , OrderComment = N'User ' + CAST(UserID AS NVARCHAR(8)) + N' purchased item ' + CAST(ItemID AS NVARCHAR(8))
FROM DATA;
GO

EXEC sys.sp_spaceused 'dbo.SalesOrder'

初始化了200万条数据,如下:
01.png

执行查询

查询用户10057在近一个月内的商品购买情况(为了获取性能对比信息,我打开了Time和I/O统计),建议在执行语句之前打开实际执行计划获取选项。打开实际执行计划,方法是点击SSMS中的下图方框中图标,或者使用快捷键CTRL + M:
02.png

执行查询语句:

----=== get User 10057 purchased Items Info
USE [TestDb]
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT 
    ItemID, OrderQty, Price
FROM dbo.SalesOrder
WHERE UserID = 10057
    AND OrderDate >= DATEADD(MONTH, -1, GETDATE())
    AND OrderDate <= GETDATE();

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

执行查询语句的I/O,CPU和时间消耗,其中,逻辑I/O读取消耗32295,CPU消耗451 ms,执行时间消耗648 ms,如下图展示:
03.png

执行计划走Clustered Index Scan(性能消耗几乎于Table Scan相近),索引缺失警告信息,如下图绿色字体,右键点击,然后选择Missing Indexes Details...可以打开缺失索引的详细信息:
04.png

索引缺失

除了上面事例讲解的执行计划查看缺失索引的方法以外,我们还可以使用三个重要的系统动态视图来查看缺失索引(每个视图具体含义,请自行查看帮助文档):
 sys.dm_db_missing_index_group_stats
 sys.dm_db_missing_index_groups
 sys.dm_db_missing_index_details
利用三个系统动态视图来查找缺失的索引,方法如下:

USE TestDb
GO

SELECT TOP 100 
    c.equality_columns
    , c.inequality_columns
    , c.included_columns
    , create_Index = 'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(equality_columns, '[', ''), ']',''), ',', '')
        + '_' + REPLACE(REPLACE(REPLACE(inequality_columns, '[', ''), ']',''), ',', '') 
        + '_' +REPLACE(REPLACE(REPLACE(included_columns, '[', '@'), ']',''), ', ', '_')  + char(10)
        + 'ON ' + SCHEMA_NAME(tb.schema_id) + '.' + object_name(tb.object_id) 
        + '('
            + case 
                when c.equality_columns is not null and c.inequality_columns is not null 
                    then c.equality_columns + ',' + c.inequality_columns
                when c.equality_columns is not null and c.inequality_columns is null 
                    then c.equality_columns
                when c.inequality_columns is not null 
                    then c.inequality_columns
            end
        + ')' + char(10)
        + case 
            when c.included_columns is not null 
            then 'INCLUDE (' + c.included_columns + ')'
            else ''
        end + char(10)
        + N'WITH (FILLFACTOR = 85);'
FROM sys.dm_db_missing_index_group_stats a
    INNER JOIN sys.dm_db_missing_index_groups b
        ON a.group_handle = b.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details c
        ON c.index_handle = b.index_handle
    INNER JOIN sys.tables as tb
        ON c.object_id = tb.object_id
WHERE db_name(database_id) = db_name()
    AND equality_columns is not null
    AND tb.object_id = object_id('dbo.SalesOrder', 'U')
ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans) DESC

执行后的查询结果如下图所示:
05.png

解决问题

无论是通过执行计划查看索引缺失,还是通过三个动态视图获取缺失索引,最终的目的就是解决问题,让我们创建这个缺失的索引:

--=== Create Missing Indexes
USE [TestDb]
GO
CREATE INDEX IX_UserID_OrderDate_@ItemID_@OrderQty_@Price
ON dbo.SalesOrder([UserID],[OrderDate])
INCLUDE ([ItemID], [OrderQty], [Price])
WITH (FILLFACTOR = 85);
GO

效率对比

创建了这个缺失索引以后,再次上面执行上面“执行查询”中的查询语句,执行计划和性能消耗对比。
执行计划,已经走到了更加高效的Index Seek上来了,如下图所示:
06.png

I/O读逻辑取消耗为126、CPU消耗为16 ms和执行时间消耗为198 ms,截图如下:
07.png

创建索引后,执行时间消耗,CPU消耗,I/O读取消耗,分别提高了3.27倍,28.19倍和256.3倍,平均性能提高了95.92倍。对比情况做图如下:
08.png

总结

这篇文章从理论结合实际,介绍了CPU高使用率的解决方法系列文章之一,缺失索引。从最终的测试结果来看,创建索引后,对于特定查询性能在CPU使用率、时间消耗和I/O读取三个方面都有很大提升,尤其是I/O读取操作提高了256.3倍,平均的性能提升达到了95.92倍,效果十分明显。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
263 9
|
1天前
|
SQL 存储 关系型数据库
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括:MySQL驱动用于建立Java系统与数据库的网络连接;数据库连接池提高多线程并发访问效率;MySQL中的连接池维护多个数据库连接并进行权限验证;网络连接由线程处理,监听请求并读取数据;SQL接口负责执行SQL语句;查询解析器将SQL语句解析为可执行逻辑;查询优化器选择最优查询路径;存储引擎接口负责实际的数据操作;执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
101 75
|
1月前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
100 22
 MySQL秘籍之索引与查询优化实战指南
|
1天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
14天前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
|
17天前
|
存储 关系型数据库 MySQL
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
|
2月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
1月前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
109 10
|
2月前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
69 8
|
2月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
93 7

热门文章

最新文章