使用計算欄位建立索引來加快特定查詢

現實的資料規劃中常會遇到有人將一些有意義的代號組成一字串來儲存,這樣的設計模式常常會碰到的大問題就是需要拆字串來查詢。而拆字串的方式查詢往往都無法使用索引,因此造成Table Scan進而影響效能。

日前看見一篇文章針對這樣的情境,有效的利用計算行欄位索引來解決,自己實作一下並記錄下來。

首先我先建立一DB叫School及一Table叫Avacou(算是課程資料表),該資料表中有一個欄位叫Avano(就是開課碼)開課碼會是3個有意義的代碼組成的字串

接下來我來塞入一些測試資料,注意下圖中7筆資料的Avano欄位第5碼略有不同

我總共塞了22萬筆的資料。

由於Avano的2-5碼是有意義的,所以前端程式會利用Substring取出2-5碼後放入Where條件式查詢(如下圖所示),但目前對於Avano欄位並沒有建立索引,所以我們可以看見執行計畫是採用叢集索引掃描來抓資料。

既然Avano沒索引,我就幫他建索引,如下圖所示,我替Avano建了一個索引。

同樣的Query在建完Avano的索引後我們再跑一次。如下圖所示,依然是採Scan模式找資料。但比較好的是這一次是利用 IX_Avacou_Avano 這一個非叢集索引來掃描。

那到底要如何讓這一種Query可以有效的利用索引搜尋呢?


這時 計算型欄位 就可以派上用場了,如下圖所示我利用 Substring([Avano],2,4) 來建立一欄位叫 [dptno]。

建立完該欄位後我就利用dptno來搜尋,看看執行計畫會怎麼跑。如下圖所示,SQL依然是用 IX_Avacou_Avano 這一個非叢集索引來掃描。

此時我們針對dptno計算欄位一樣建立出一個索引 IX_Avacou_dptno,如下圖所示。

建立完dptno的索引後我們再一次利用dptno來搜尋,這一次的執行計畫就顯示出SQL已經改用 IX_Avacou_dptno 這一個索引來做搜尋了

而一開始那個 Substring([Avano],2,4) 的條件式呢?他是不是也會吃 IX_Avacou_dptno 這一個索引呢?如下圖所示,該Query真的也利用 IX_Avacou_dptno 來做搜尋了歐。

下圖兩個利用Substring來做的Query,一個是有索引可以做搜尋,另一個則是用索引掃描。其效能差異顯而易見(11% : 89%)

建議在資料表設計階段就要避免掉這種組合式的資料,不然一定會遇見需要拆字串才能查詢的情形。

以下是Demo Code

Create Database [School]
GO

USE [School]
GO

CREATE TABLE [dbo].[Avacou](
[AvacouID] [int] identity NOT NULL,
[Avano] [varchar](11) NULL,
[Avacna] [nvarchar](100) NULL,
[ClassRoom] [nvarchar](50) NULL,
CONSTRAINT [PK_Avacou] PRIMARY KEY CLUSTERED
([AvacouID])
)
GO

INSERT INTO [Avacou] ([Avano],[Avacna], [ClassRoom])
Values
 ('D010112345','中文1','SF441')
,('D010212345','中文2','SF442')
,('D010312345','中文3','SF443')
,('D010412345','中文4','SF444')
,('D010512345','中文5','SF445')
,('D010612345','中文6','SF446')
,('D010712345','中文7','SF447')
GO

Insert Into [Avacou] Select [Avano],[Avacna], [ClassRoom] From [Avacou]
GO 15

Select Count(*) From [Avacou]
GO


SELECT [AvacouID], [Avacna]
FROM [Avacou]
WHERE Substring([Avano],2,4) = '0101'
GO

CREATE NONCLUSTERED INDEX IX_Avacou_Avano
ON dbo.[Avacou] ([Avano], [Avacna])
GO

SELECT [AvacouID], [Avacna]
FROM [Avacou]
WHERE Substring([Avano],2,4) = '0101'
GO

ALTER TABLE dbo.[Avacou] ADD [dptno] AS Substring([Avano],2,4)
GO

SELECT [AvacouID], [Avacna]
FROM [Avacou]
WHERE [dptno] = '0101'
GO

CREATE NONCLUSTERED INDEX IX_Avacou_dptno
ON dbo.[Avacou] ([dptno], [Avacna])
GO

SELECT [AvacouID], [Avacna]
FROM [Avacou]
WHERE [dptno] = '0101'
GO

SELECT [AvacouID], [Avacna]
FROM [Avacou]
WHERE Substring([Avano],2,4) = '0101'
GO

 

我是ROCK

rockchang@mails.fju.edu.tw