[SQL][Performance]資料類型優先順序 ( Data Type Precedence )

[SQL][問題處理]資料類型優先順序 ( Data Type Precedence )

前一陣子在社群內介紹了 SARG 之後,也陸陸續續的收到一些朋友的問題,剛好自己在工作上也遇到一些實際的案例,就先把這個部份給整理一下做成一個小的 Lab,希望對大家有幫助。

 

首先先做個簡單的資料庫,裡面放了一些測試資料,這個範例會產生 10,000,000 ( 一千萬筆 ) 紀錄,因此如果您的測試環境硬碟速度不夠快的話,那麼大約會需要將近 20~30 分鐘。

-- 刪除範例資料庫
IF DB_ID('SARGDEMO') IS NOT NULL DROP DATABASE [SARGDEMO];
GO 
 
-- 建立資料庫
CREATE DATABASE [SARGDEMO]
GO
 
-- 變更復原模式為簡單
ALTER DATABASE [SARGDEMO] SET RECOVERY SIMPLE;
GO
 
USE [SARGDEMO]
GO
 
-- 建立資料表
CREATE TABLE [BigTable]
(
    A1    INT,
    A2    NVARCHAR(10),
    A3    VARCHAR(10),
    A4    NCHAR(200),
)
GO
 
DECLARE @I INT;
DECLARE @J INT;
DECLARE @K INT;
 
 
SET @J = 0;
 
-- 產生一千萬筆的資料,為了避免交易記錄檔過大,每一萬筆資料放在一個 Transaction 內
SET NOCOUNT ON;
WHILE @J < 1000
BEGIN
    SET @I = 0;
    BEGIN TRAN
    WHILE @I < 10000 
    BEGIN
        SET @K = @J*10000+@I ;
        INSERT INTO [BigTable] ( A1,A2,A3,A4 ) VALUES ( @K, RIGHT('0000000000'+LTRIM(STR(@K)),10), RIGHT('0000000000'+LTRIM(STR(@K)),10), NEWID())
        SET @I += 1; 
    END    
    COMMIT
    SET @J += 1; 
END
GO

 

接著我分別針對 A1 , A2 , A3 這三個欄位,都各建立一個索引

USE [SARGDEMO]
GO
 
-- 針對欄位 A1 建立一組 INDEX
CREATE NONCLUSTERED INDEX IX_BigTable_A1 ON [BigTable](A1);
GO
 
-- 針對欄位 A2 建立一組 INDEX
CREATE NONCLUSTERED INDEX IX_BigTable_A2 ON [BigTable](A2);
GO
 
-- 針對欄位 A3 建立一組 INDEX
CREATE NONCLUSTERED INDEX IX_BigTable_A3 ON [BigTable](A3);
GO
 
-- 檢查索引的統計資訊
DBCC SHOW_STATISTICS(BigTable,IX_BigTable_A1) 
GO
DBCC SHOW_STATISTICS(BigTable,IX_BigTable_A2) 
GO
DBCC SHOW_STATISTICS(BigTable,IX_BigTable_A3) 
GO

 

建立完測試資料,可以開始我們的測試。正常來說,如果我們要找資料,如果要找的資料所在的欄位剛好有建立索引,那麼 SQL Server 就會使用索引去查資料了。但這裡有個很微妙的狀況,當你要找的欄位其資料型態和查詢值的資料型態是不一致的狀況下,則 SQL Server 會預先做型態的轉換,以便資料比對。這樣說可能有點模糊,下面我們用個實例來表示

 

假設我們的查詢條件如下,在這個例子中欄位 A1 的型態是 INT,比對的資料 1234321 也是 INT,因此這個時候 SQL Server 不需要做資料型態轉換,就可以直接來查詢了

SELECT * FROM BigTable WHERE A1 = 1234321

image

 

但假設我們沒有注意到,寫 SQL 的時候寫成如下的方式,那麼這樣也是可以正常執行的。

SELECT * FROM BigTable WHERE A1 = '1234321'

 

這個時候要比對的資料雖然是字串型態,此時 SQL Server 會做資料類型轉換,我們可以從查詢計畫中看到,SQL Server 會先把字串 '1234321' 轉換為整數1234321 ,接著再透過索引去查詢資料。因此在這樣的狀況下,要比對的資料類型雖然沒有匹配,但 SQL Server 會自動幫我們做資料類型轉換處理,似乎沒有甚麼問題。

image

 

但如果今天我們要查詢的欄位是 Nchar 型態的狀況下,那狀況會怎麼樣呢 ? 我們先用一個正常的方式

SELECT * FROM BigTable WHERE A2 = N'0001234321'

image

 

此時如果我們把上述的 SQL 指令給改一下,寫成如下的方式

SELECT * FROM BigTable WHERE A2 = 1234321

 

則總執行成本則由之前不到 0.01 提升到 43

image

 

之所以會有這麼大的變化,主要是執行計畫中由原本的「索引搜尋」變成了「索引掃描」。而會變成這樣的原因,我們可以從下圖中看出,因為當類型不匹配的時候,SQL Server 幫我們將欄位的值做一個隱性的轉換成為 INT 類型,轉換好之後才去跟我們的檢索條件去比對,而這樣的處理需要針對這個 A2 的欄位一筆資料一筆資料的處理,使得 SQL Server 就無法從直接透過索引檢索,變成去使用這個索引去做每一筆資料的轉換,因此相對地就花上較多的時間。

image


 

那為什麼 SQL Server 不是將我們檢索條件由數值先轉換成為字串呢 ? 這個主要是因為 SQL Server 的「資料類型優先順序」 ( Data Type Precedence ),在 SQL Server 內當不同型別在比對的時候,SQL Server 會根據這個優先順序,以順序高的為主,將順序低的型別轉換成為順序高的,再進行資料比對。因此我們上面的例子中,因為 INT 型態比字串類型的優先順序來的高,因此會先把字串轉換成為數值,再進行比較,才會發生第一個案例是沒有影響的,但第二個案例中效能卻有明顯的差異。

image

 

上述的案例是我們比較常見的也容易發現的問題,大家也都比較容易發現到資料類型轉換的問題,但最近我們還遇到另外一種是我們比較容易忽略的。在這個案例中,主要是因為資料表是舊系統所建立的,而有部分程式利用 .Net 來做開發,因此就產生了 char 與 nchar 之間隱性轉換所引發的問題。為了測試這個問題,以下我們將要針對 A3 這個欄位來做查詢。

-- A3 的欄位型態是 varchar
SELECT * FROM BigTable WHERE A3 = '0000013579'  
SELECT * FROM BigTable WHERE A3 = N'0000013579'

 

從執行計畫中看起來,雖然 nchar & nvarchar 型態的優先順序比 char & varchar 來的高,但似乎就算沒有注意沒有甚麼差別,SQL Server 都可以處理也沒有甚麼效能上的差異。

image

 

但實際上是我們被這樣的結果給矇蔽了,誤以為只要是字串型態就都沒有問題了。因此如果我們調整一下語法,來看看這樣的狀況是否會改變呢 ? 在下面的指令中,我們調整一下原本的寫法,將原本的 = 改成為 between 的運算

SELECT * FROM BigTable WHERE A3 Between  '0000013579' AND  '0000014680' 
SELECT * FROM BigTable WHERE A3 Between N'0000013579' AND N'0000014680' 

 

從下圖的執行計畫中就可以明顯看出,如果欄位是 char 或者是 varchar 的狀況下,當我們又畫蛇添足的去加上 N 把資料設定為 Unicode 的話,那麼對效能也是會有很大的差異。從這個結果中我們可以看到 SQL Server 不會去使用我們既有的索引,還要我們去多建立一個新的索引,且因為我們是使用 select *  的語法,因此建議的索引還要我們去 INCLUDE 其他幾個欄位。

image

 

而如果我們就算在這個時候真的根據 SQL Server 所建議的,去新增加這一組新的 INDEX,雖然看起來執行成本從 500 降到 40,也有使用索引搜尋。

image

 

但這樣的結果雖然看起來不錯,但還是遠不如直接用正確的型態,而且這樣還要多花上許多的空間和時間來維護和存放索引。因此,如果在下 SQL 指令前多注意一下欄位類型和資料類型,那麼會遠比建立多餘的索引來的有效多了。