RDSSQLServer-最佳實踐-高CPU使用率系列之非SARG查詢

風移發表於2017-03-20
SQLServer

摘要

阿里雲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查詢的改寫方法。


相關文章