[查詢優化]影響執行計畫的因素4-Selectivity(選擇性)

        選擇性是一種獨特性的衡量，大多用來描述述詞，如果要計算的話就是符合資料列/總資料列的比率

        假設有個員工資料表，總共100位員工，男女各半，資料表上有ID(身分證或護照)及性別欄位，如果述詞為" ID= '某個員工ID' "，可以預期只會回傳一個員工，選擇性為1/100=0.01，表示有較高的獨特性，所以ID欄位有高選擇性，如果述詞為" 性別 ＝ '男生' "，可以預期將會回傳50位員工，選擇性為50/100=0.5，有較低的獨特性，所以性別欄位的是低選擇性

        大資料裡找小資料，使用索引是很有效率的，所以通常述詞裡有高選擇性欄位，就很適合拿來做選索引欄位囉，像前述所提的ID欄位，很適合拿來做索引欄位，性別欄位就較不適合囉



        SQL Server查詢最佳化工具會評估述詞是屬於高選擇性還是低選擇性來決定索引的使用，也就是評估述詞以預期會有多少資料列回傳來決定，通常高選擇性就會使用索引搜尋，低選擇性就使用資料表掃描

        來看一下例子，先建立範例資料表，我是使用SQL 2012 (RC 0)作測試的

USE [tempdb]

GO

IF OBJECT_ID('testTable') IS NOT NULL

    BEGIN

   DROP TABLE testTable

   END

GO

CREATE TABLE [dbo].[testTable](

[Id] [BIGINT] NULL,

[Name] [VARCHAR](10)  NULL

)

GO

CREATE NONCLUSTERED INDEX [IX_TestIndex] ON [dbo].[testTable]

(

[Id] ASC

)

GO

INSERT INTO testTable

SELECT TOP (100000) rn = ROW_NUMBER() OVER

      (ORDER BY c1.[object_id]),c1.name

      FROM sys.columns AS c1

      CROSS JOIN sys.columns AS c2

      CROSS JOIN sys.columns AS c3

      ORDER BY c1.[object_id]

OPTION (MAXRECURSION 0)

GO

        testTable總共有100000筆資料，Id欄位有建立索引，Id欄位本身是屬於高選擇性欄位，先執行以下兩個查詢，你會發現，述詞" Id < 100 "是高選擇性的，查詢最佳化工具預期有較少的資料回傳，選擇使用索引搜尋；述詞" Id < 5000 "是低選擇性的，查詢最佳化工具預期有較多的資料回傳，選擇使用資料表掃描

--1

SELECT Name FROM testTable WHERE Id < 100

--2

SELECT Name FROM testTable WHERE Id < 5000

        在我查詢優化系列的第一章已提過，查詢最佳化工具會評估各種可能的執行計畫，找出一個最佳的，也就是成本最低的，但實際上不可能滿足各種情況的，快速選擇一個足夠好的執行計畫比較貼切。接下來我在不同的述詞（選擇性）下，比較使用索引跟不使用索引(資料表掃描)的成本差異，然後再看看實際上查詢最佳化工具自己的選擇

        如果述詞是用等於，有沒有使用索引成本差很多，如下：

--1

SELECT Name FROM testTable WHERE Id = 100

--2

SELECT Name FROM testTable WITH (INDEX(0)) WHERE Id = 100

        如果述詞是用小於，我測試的範圍從小於100到小於135，如下：

--1

SELECT Name FROM testTable WHERE Id < 100

--2

SELECT Name FROM testTable WITH (INDEX(0)) WHERE Id < 100

--1

SELECT Name FROM testTable WHERE Id < 120

--2

SELECT Name FROM testTable WITH (INDEX(0)) WHERE Id < 120

--1

SELECT Name FROM testTable WHERE Id < 125

--2

SELECT Name FROM testTable WITH (INDEX(0)) WHERE Id < 125

--1

SELECT Name FROM testTable WHERE Id < 130

--2

SELECT Name FROM testTable WITH (INDEX(0)) WHERE Id < 130

--1

SELECT Name FROM testTable WHERE Id < 135

--2

SELECT Name FROM testTable WITH (INDEX(0)) WHERE Id < 135

        我把以上幾種情況整理成折線圖，把原本的述詞條件轉換資料量來看，索引搜尋的成本會隨著資料量增加而增加，資料表掃描的情況剛好相反，兩者在資料量130的地方成本是一樣的，理論上，查詢最佳化工具會選擇成本較低的執行計畫，所以130以前的應該都是選擇索引搜尋，130之後的會選擇使用資料表掃描

       實際上我試了一下，述詞到150時，查詢最佳化工具都還是選用索引搜尋，到155時才選用資料表掃描呢，如下

--1

SELECT Name FROM testTable WHERE Id < 150

--2

SELECT Name FROM testTable WHERE Id < 155

       然後版本不同，狀況也不太一樣，後來我又在SQL 2008 R2上測試，是在145時才切換的喔！