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

2022-02-16 01:25:56

摘要

阿里云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毫秒，截图如下所示：

执行计划窗口，SQL Server优化器选择走Index Scan，截图如下：

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

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毫秒。

查询优化器选择了更为高效的Index Seek的执行计划操作上来，截图如下：

从性能指标对比来看，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

结果展示如下图所示：

RTRIM

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

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

结果展示如下图所示：

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查询的改写方法。

码农公寓

摘要