SQL Server索引

索引這部份呢，在上課時老師也說沒有絕對的答案
是要依使用者的操作習慣來最佳化系統查找方式

例如：
一個班級，要找某座號的學生，用座號做索引是較佳的方式
那如果今天要找成績80分以上的學生
那前面那個「座號索引」一點用都沒有
要用成績做索引才是較好的方式。
那如果今天要找跳最高，或著跑最快的學生呢?
「成績索引」、「座號索引」都沒有用了


所以說索引沒有唯一解，只有盡可能的最佳解
這還挺有趣的，上課時我把筆記打在註解裡
就不特別整理了

use 練練
go
--建立索引，最好找使用者最常查找的條件為主
--索引不要建太多，會造成系統損耗，每次資料變動都會連動索引
---------------------------------------------------------------------
---------------------------------------------------------------------
SELECT * FROM 巨巨 --原始是Heap堆積

--叢集索引(Clustered Index)
--每張表只能有一個，可以沒有
--影響資料表的資料的排列
--在建立任何索引，該表會被鎖住，建完才釋放


CREATE CLUSTERED INDEX 叢集索引
ON 巨巨(價錢)
--ON 索引群(巨巨)
--可以的話，最好把索引建群並獨立在其他硬碟

---------------------------------------------------------------------
---------------------------------------------------------------------
--非叢集索引(NonClustered Index)
--每張表最多可以有249個
--不影響資料表的資料的排列(索引本身是排序的)
--非叢集索引是以 叢集索引/Heap堆積 為基底製作而成的
--一定比叢集索引還要來得大，另外可以設定DESC OR ASC
--若原叢集索引發生變更(例如索引的欄位變更)
--則跟隨在上的非叢集索引完全作廢，需重建立

CREATE NONCLUSTERED INDEX 非叢集索引 ON 巨巨(品名)


--建立非叢集索引時，在搜尋上找東西最好條件明確
--如果SQL Server用叢集索引就可以找得到答案，那就不會用非叢集索引
--像下面的*號是全部都找出來，但條件是價錢
--就會用上面建立的叢集索引，而下一句是要品名及價錢
--條件明確要'黃魚'，那就會用依品名建立的非叢集索引


SELECT * FROM 巨巨 WHERE 價錢 BETWEEN 10 AND 50
SELECT 品名,價錢 FROM 巨巨 WHERE 品名='黃魚'


--在SQL Server中，用索引搜尋是最好的查找方式
--索引搜尋 優於> 索引掃描 優於> 資料表掃描


---------------------------------------------------------------------
---------------------------------------------------------------------
--複合索引(多欄索引)
--最多能結合16個欄位
--不可在叢集索引上用複合索引(建議)
--拿來做索引的資料大小不得超過 900 Bytes
--先製作辨識率較高的欄位
CREATE NONCLUSTERED INDEX 姓名非叢集 ON 巨巨(名,姓)


---------------------------------------------------------------------
---------------------------------------------------------------------
USE 中文北風
GO

--INCLUDE(包含)
--非叢集索引(NonClustered Index) 的 Include 功能
--增加索引查詢的涵蓋率
--針對無法做索引的欄位可以一起併入查詢
SELECT * FROM 員工
CREATE NONCLUSTERED INDEX 員工索引
ON 員工(姓名) INCLUDE(薪資,稱呼,內部分機號碼)

--缺點
--導致索引的體積變大
--索引的查詢效率降低
--增加索引被維護的機會

--DROP INDEX 員工索引 ON 員工

SELECT 姓名,薪資,稱呼,內部分機號碼 FROM 員工 WHERE 姓名='黎國明'


---------------------------------------------------------------------
---------------------------------------------------------------------
--非叢集索引(NonClustered Index) 的 Filter 功能
--Filter索引體積較小
--Filter索引的查詢效率較高
--降低索引被維護的機會

CREATE NONCLUSTERED INDEX 電話非叢集索引
ON 員工(電話號碼) INCLUDE (姓名,職稱,薪資,內部分機號碼)
WHERE 電話號碼 IS NOT NULL

SELECT 姓名,電話號碼,職稱,薪資,內部分機號碼 FROM 員工 WHERE 電話號碼 LIKE '(02)%'

CREATE NONCLUSTERED INDEX 業務非叢集索引
ON 員工(職稱) INCLUDE (姓名,薪資,內部分機號碼)
WHERE 職稱 ='業務'

DROP INDEX 業務非叢集索引 ON 員工

SELECT 姓名,職稱,薪資,內部分機號碼 FROM 員工 WHERE 職稱 = '業務' 


---------------------------------------------------------------------
---------------------------------------------------------------------
--填滿因數
--索引會隨使用者刪、增、改等因素造成破碎
--刪會造成內部破碎，而增會造成外部破碎
--假設價錢5~10的產品索引，把$7的全刪光了，那索引會有一段空白
--而外部是整頁已填滿，又在其中插入一筆，這時索引不會自動往後退
--而是另外開一頁來放，這時讀取就很慘烈了，要跳過去跳過來
--填滿因數是每頁都預留一些空白，好讓未來插入資料時可以直接寫
--是利用內部破碎的空白來減緩外部破碎的可能



CREATE NONCLUSTERED INDEX 訂單編號非叢集
ON 訂貨明細(訂單號碼)
WITH(FILLFACTOR = 70,PAD_INDEX=ON,ONLINE=ON)
--每頁只用70%，所有二元樹都要用，ONLINE的意思是建索引時該表不鎖

--重組，當破碎程度不大時，可以用重組----------------------------------------
ALTER INDEX 訂單編號非叢集 ON 訂貨明細 REORGANIZE
 WITH(FILLFACTOR=70,PAD_INDEX=ON,ONLINE=ON) 

--當建，當破碎程度太兇狠，就打掉重建-----------------------------------------
ALTER INDEX 訂單編號非叢集 ON 訂貨明細 REBUILD WITH(ONLINE=ON) 
ALTER INDEX ALL ON 訂貨明細 REBUILD