RDS SQL Server - 最佳实践 - 高CPU使用率系列之非SARG查询

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: # 摘要 阿里云RDS SQL Server客户遇到最多的一个问题便是高CPU使用率导致导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死。本系列文章第四篇分析非SARG查询导致CPU的高利用率的解决之道。 # 问题引入 “鸟啊,你听说过RDBMS的非SARG查询语句吗?我还是今天第一次听说呢!”。老鸟有些不解的问菜鸟。 “哈哈,鸟哥,孤陋寡闻,土鳖了吧。它可是导致RDBMS

摘要

阿里云RDS SQL Server客户遇到最多的一个问题便是高CPU使用率导致导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死。本系列文章第四篇分析非SARG查询导致CPU的高利用率的解决之道。

问题引入

“鸟啊,你听说过RDBMS的非SARG查询语句吗?我还是今天第一次听说呢!”。老鸟有些不解的问菜鸟。
“哈哈,鸟哥,孤陋寡闻,土鳖了吧。它可是导致RDBMS数据库CPU高使用率的又一个重要的原因呢!今天就让我细细道来。”,菜鸟开始得意忘形起来。”。

场景分析

SARG是Search Argument英文的缩写形式,非SARG简单来说就是指查询谓词(特别是WHERE字句或者连接操作的ON字句)导致索引失效,查询优化器无法使用高效的Index Seek操作,退而求其次的使用效率相对较低的Index Scan操作,从而导致了CPU使用率的上升和查询性能降低。常见的非SARG操作包括在WHERE语句中使用到了标量函数Datediff,Dateadd,Year,Rtrim,Upper,Lower,LIKE完全模糊匹配(格式如LIKE ‘%XXX%’)或者是用户自定义函数等。

解决方法

解决非SARG查询的核心思想是通过查询逻辑的等价改写或者功能设计层面的优化来避免WHERE语句中使用标量函数,我们仅以以下几种常见的非SARG操作来举例说明,其他类推。

DATEDIFF

比如,我们需要查询出当天的订单信息记录,非SARG的写法如下(在WHERE语句中使用了Datediff函数):

USE TestDb
GO

CREATE INDEX IX_OrderDate
ON dbo.SalesOrder([OrderDate])
WITH (FILLFACTOR = 90);
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON

-- find out sales info of today.
SELECT 
    ItemID, OrderQty, Price,OrderDate
FROM dbo.SalesOrder
WHERE DATEDIFF(DAY, OrderDate, GETDATE()) = 0

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

从性能指标来看,I/O消耗为8406,CPU消耗为171毫秒,执行时间消耗为211毫秒,截图如下所示:
01.png

执行计划窗口,SQL Server优化器选择走Index Scan,截图如下:
02.png

其实,我们可以换个思维逻辑想想,当天的数据,其实是指时间大于等于当天凌晨零点零分,并且时间小于等于当前时刻的记录。那么等价逻辑的改写代码如下:

USE TestDb
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE
    @today DATETIME = CONVERT(CHAR(10), GETDATE(), 120)
;
SELECT 
    ItemID, OrderQty, Price,OrderDate
FROM dbo.SalesOrder
WHERE OrderDate >= CONVERT(CHAR(10), GETDATE(), 120)
    AND OrderDate <= GETDATE();
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

优化后性能指标,I/O消耗为3,CPU消耗为0毫秒,执行时间消耗为109毫秒。
03.png

查询优化器选择了更为高效的Index Seek的执行计划操作上来,截图如下:
04.png

从性能指标对比来看,I/O从8406降低到3,CPU消耗从171毫秒降低到0毫秒,执行时间从211降低到109毫秒,特别是I/O和CPU的提升非常明显。

UPPER/LOWER

由于SQL Server默认的排序规则是忽略大小写的,换句话说SQL Server认为UPPER和upper是相等的。所以在做字符串比较运算的时候,可以省略UPPER或者是LOWER函数的使用,以免造成非SARG查询,导致CPU使用率增加。
当然需要特别注意的是:在改写非SARG查询之前需要再次确认你的数据库是忽略大小写的,即含有case-insensitive关键字。检查方法如下:

USE TestDb
GO
EXEC sys.sp_helpsort

结果展示如下图所示:
05.png

RTRIM

有的人在写字符串比较的时候,喜欢使用RTRIM函数来取消字符串最右边的空格后再来比较,其实这种做法是画蛇添足,完全没有必要的。理由是SQL Server在做字符串比较的时候,自动会忽略最右边的空格。这样做反而会导致非SARG查询,CPU使用率增高。
我们可以写一个非常简单的例子来证明这一点,在IF语句中,等号左边的RTRIM后紧跟一个空格符,等号右边的字符串TRIM后不包括空格,但是IF语句的判断结果为真。代码如下所示:

IF 'TRIM ' = 'TRIM'
    PRINT 'equals'
ELSE
    PRINT 'not equal'

结果展示如下图所示:
06.png

LIKE完全模糊匹配

WHERE语句中的LIKE完全模糊匹配,同样会导致SQL Server索引失效,同样也是属于非SARG查询的一种。这种场景的优化包含两种方法:第一种方法是从设计层面来优化,比如:我们经常碰到的场景是通过电话号码来完全模糊查询,但是客户的查询语句往往传入的电话号码又都是一个完整的电话号码。所以这里,我们完全可以将设计修改为电话号码仅支持完整电话号码查询,即将LIKE完全模糊匹配的设计修改成了等于操作方式,避免了非SARG操作;第二种解决方法:如果设计层面无法避免要使用完全模糊匹配查询,可以选择使用SQL Server Fulltext技术来解决LIKE字句完全模糊查询,以此来避免非SARG操作,降低CPU使用率。详情,可以参见文章:SQL Server FullText解决Like字句性能问题

总结

本篇文章分析了非SARG查询语句导致RDS SQL Server CPU使用率增高的原因以及解决这类问题的核心思想是改写语义逻辑,避免非SARG查询导致CPU高使用率,并且举例说明了几个典型的非SARG查询的改写方法。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
24天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
39 0
|
26天前
|
存储 关系型数据库 MySQL
深入理解MySQL索引:从原理到最佳实践
深入理解MySQL索引:从原理到最佳实践
51 0
|
1月前
|
SQL 存储 数据库
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
|
2月前
|
SQL 安全 数据库
写一段防止sql注入的sql查询
【1月更文挑战第14天】写一段防止sql注入的sql查询
88 31
|
7天前
|
SQL 关系型数据库 MySQL
Flink SQL 问题之查询时报错如何解决
Flink SQL报错通常指在使用Apache Flink的SQL接口执行数据处理任务时遇到的问题;本合集将收集常见的Flink SQL报错情况及其解决方法,帮助用户迅速恢复数据处理流程。
32 4
|
7天前
|
SQL DataWorks 关系型数据库
dataworks数据集问题之sql查询报错如何解决
DataWorks数据集是指在阿里云DataWorks平台内创建、管理的数据集合;本合集将介绍DataWorks数据集的创建和使用方法,以及常见的配置问题和解决方法。
21 1
|
26天前
|
监控 关系型数据库 MySQL
MySQL Binlog实战:在生产环境中的应用与最佳实践【实战应用】
MySQL Binlog实战:在生产环境中的应用与最佳实践【实战应用】
15 0
|
27天前
|
SQL 关系型数据库 MySQL
mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
|
27天前
|
SQL BI
sql中从不同表查询并组合数据示例
sql中从不同表查询并组合数据示例
|
2月前
|
SQL 存储 缓存
如何通过优化SQL查询提升数据库性能
SQL查询是数据库的核心功能之一,对于大型数据量的应用程序来说,优化SQL查询可以显著提升数据库的性能。本文将介绍如何通过优化SQL查询语句来提升数据库的性能,包括索引优化、查询语句优化以及其他一些技巧。

热门文章

最新文章

推荐镜像

更多