Rock的SQL筆記本

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

首先我先建立一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