RDS SQL Server - 最佳实践 - 高CPU使用率系列之数据类型转换

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: # 摘要 前两篇文章讨论了导致CPU高使用率的两个重要原因是索引缺失和索引碎片,本系列文章之三讨论数据类型隐式转换话题。 # 场景分析 在SQL Server中,比较运算符(大于、小于、等于或者连接)两端的数据类型需要保持一直才能进行。否则,SQL Server会按照数据类型优先级由低到高进行隐式转化,然后再进行比较。这个行为可以通过执行计划中的CONVERT_IMPLICIT关键字看出来,

摘要

前两篇文章讨论了导致CPU高使用率的两个重要原因是索引缺失和索引碎片,本系列文章之三讨论数据类型隐式转换话题。

场景分析

在SQL Server中,比较运算符(大于、小于、等于或者连接)两端的数据类型需要保持一直才能进行。否则,SQL Server会按照数据类型优先级由低到高进行隐式转化,然后再进行比较。这个行为可以通过执行计划中的CONVERT_IMPLICIT关键字看出来,后面的测试例子中,我们可以清楚的看到这一点。如果很不幸,导致SQL Server正式表字段数据类型隐式转换会带来几个方面的问题:
 数据经过了转换,所以执行计划无法走更优的Index Seek,进而选择Index Scan
 由于Index Scan,所以I/O消耗很高
 数据类型转换计算和I/O飙高,导致CPU使用率很高
SQL Server数据类型转化参照表如下图(图片来自微软官网):
01.png

解决问题

在这里,我们将这个例子详细分解为五个小步骤:
 测试环境:搭建简单的测试环境。
 执行查询:SQL Server会隐式转换正式表字段中的数据为INT类型
 数据类型隐式转换:数据类型隐式转换的表现形式
 解决问题:修改查询语句比较运算符右边的数据类型为字符串格式
 效率对比:查询修改前后的性能对比

测试环境

我们在本系列文章之一RDS SQL Server - 最佳实践 - 高CPU使用率系列之索引缺失创建的测试表上稍作修改:删除索引 => 修改字段ItemID为VARCHAR数据类型 => 创建索引。代码如下:

USE TestDb
GO

--===Drop index included
DROP INDEX IX_UserID_OrderDate_@ItemID_@OrderQty_@Price
ON dbo.SalesOrder;
GO

--===Change Data type for Testing Data convertion
ALTER TABLE dbo.SalesOrder
ALTER COLUMN ItemID VARCHAR(8) NOT NULL
GO

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

EXEC sys.sp_help 'dbo.SalesOrder'
GO

从结果展示来看,ItemID已经变成了VARCAHR数据类型,展示如下:
02.png

执行查询

现在我们执行下面的查询语句,打开实际执行计划,注意WHERE ItemID = 250子句,等号右边的数据类型为INT,相比左边的数据类型VARCHAR优先级要高。所以,SQL Server必须先隐式转换正式表中该字段存储的所有数据为INT以后,再与250比较。这个转换操作会导致I/O飙高,无法使用索引和CPU使用率走高。

USE TestDb
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON

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

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

从性能指标页面来看,I/O为14950,CPU消耗327毫秒,时间消耗362毫秒,截图如下:
03.png

数据类型隐式转化

我们可以从执行计划中的CONVERT_IMPLICIT关键字看出,SQL Server做了数据类型的隐式转换,并且转换的是正式表中的数据。执行计划截图如下:
04.png

解决问题

问题分析清楚了,解决问题的方便变得非常简单。我们只需要保证比较运算符两端的数据类型一致,不让SQL Server隐式转换正式表字段中的数据,这样就可以大大减少I/O和CPU开销了。所以,我们把WHERE ItemID = 250子句修改为WHERE ItemID = '250'即可,修改后的查询如下:

USE TestDb
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT 
    ItemID, OrderQty, Price
FROM dbo.SalesOrder
WHERE ItemID = '250'        --correct here to ItemID = '250'
    AND OrderDate >= DATEADD(MONTH, -1, GETDATE())
    AND OrderDate <= GETDATE();

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

性能指标页面显示I/O消耗为61,CPU消耗为16毫秒,执行时间消耗为190毫秒,截图如下所示:
05.png

修改查询后的执行计划,走到了Index Seek,没有了CONVERT_IMPLICIT关键字的存在。执行计划截图如下:
06.png

性能对比

我们将修改查询语句前后的性能指标数据对比一下,执行时间提高了47.51%,CPU消耗提高了95.1%,I/O消耗提高了99.6%,平均性能提高了80.74%。对比数据做图如下:
07.png

总结

SQL Server做正式表字段数据类型隐式转换是导致高CPU使用率的另一重大原因,这篇文章详细分析了导致的原因、表象和解决问题的办法,指导我们平时在写查询语句的时候,需要时刻牢记比较运算符两端的数据类型保持一致。

引用文章

Data Type Conversion

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
25天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
44 0
|
25天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
36 0
|
25天前
|
SQL 人工智能 算法
【数据库SQL server】传统运算符与专门运算符
【数据库SQL server】传统运算符与专门运算符
17 0
|
25天前
|
SQL 数据库 数据安全/隐私保护
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
36 0
|
27天前
|
SQL 流计算
Flink CDC 1.12版本引入了对SQL Server的支持
【1月更文挑战第26天】【1月更文挑战第124篇】Flink CDC 1.12版本引入了对SQL Server的支持
22 1
|
2月前
|
SQL 关系型数据库 MySQL
使用Flink CDC从SQL Server同步数据到MySQL
使用Flink CDC从SQL Server同步数据到MySQL
104 1
|
3天前
|
SQL 存储 数据安全/隐私保护
|
5天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
24天前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
73 0
|
25天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
22 0