索引是什麼

圖書館（Database）內有許多書架（Table），書架的每一層有許多書本（Data），當我們需要找某本書時，如果我們想找的書剛好在書架第一層，那就幸運的可以很快就找到，但如果沒這麼幸運的話就必須一層一層書架、一本一本書慢慢抽出來找（Full Scan），曠日費時，這時候我們就需要索引來幫助我們縮短找尋資料的時間。

索引像是圖書館內的書籍分類區（e.g. 歷史、地理、人文）、收錄編號、出版社等，方便我們在查找書籍時能夠有一個方向去找，大幅降低搜尋時間。

索引的運作方式

假設有個"會員基本資料表"，Primary Key 設在身分證字號欄位，而基本上 DBMS 設定 Primary Key 時，也同時會建立一個索引，此時資料庫會建立另一張表（暫稱"會員基本資料表_ID_INDEX"），並存放已排序後的身分證字號（暫稱"ID_INDEX_COL"），且每個 ID_INDEX_COL 都會連結到原表的資料。

"會員基本資料表_ID_INDEX"會使用有效率的演算法作為儲存結構（e.g. Binary、B-Tree）

在搜尋身分證字號時，DBMS 會去搜尋"會員基本資料表_ID_INDEX" 的 "ID_INDEX_COL"，並且連結到原表，進而將完整資料撈出來。

Clustered Index 與 Non-Clustered Index

• Clustered Index 叢集索引

  • 將資料表的指定欄位做排序，對規律、有序的資料效用高
  • 實體資料表與索引表中的資料順序會是一樣的
  • 一個資料表只能有一個叢集索引
  • 通常用在唯一值欄位（Primary Key）

• Non-Clustered Index 非叢集索引

  • 實體資料表與索引表中的順序是不一樣的，非叢集索引不會去異動實體資料表，所以實體資料表保持原本的順序，而索引表則依非叢集索引欄位來排序
  • 承上，一個資料表中可以有多個非叢集索引
  • 可以由多個欄位組成，e.g. "姓氏欄位" + "名子欄位"
  • 時常用在會被 ORDER BY、WHERE、JOIN 以及 Foreign Key 的欄位

有建索引就好棒棒嗎？

• 以空間換取時間

  • 因為資料表在建立索引時，同時也會建立一張索引表，如此就必須花更多的磁碟空間來存放，硬體設備勢必得做好規劃

• 異動資料表時，同時也會異動索引表

  • 若資料表頻繁的被異動（新、刪、修），則會導致索引破碎，且因為實體資料表被異動時，索引表也要跟著異動，導致效能降低

• 需要例行性的重新建立索引，否則會越撈越慢

• 對識別性較低、無序的欄位建立索引，效用不高

  • e.g.GUID
  • https://www.dotblogs.com.tw/jamesfu/2016/01/20/guid_2
  • https://segmentfault.com/a/1190000040638366
    • 可看 del 欄位的 update 處理時間

索引破碎

當對實體資料表進行異動時，索引表也會異動，也因為如此索引表的排序會亂掉，而導致資料搜尋時間越來越長，此為索引破碎，所以才不建議對建立索引的欄位進行頻繁異動。

https://jerrywu-3165.medium.com/sql-server-index-fragmentation-%E7%94%A2%E7%94%9F%E5%8E%9F%E5%9B%A0-%E8%A9%B2%E5%A6%82%E4%BD%95%E5%81%9A%E5%B0%8D%E6%87%89%E7%9A%84%E8%AA%BF%E6%95%B4-f26e01e7c86c

協助建立索引工具

• Database Engine Tuning Advisor
  • [SQL Server]善用Database Engine Tuning Advisor改善SQL效率

為 Day3 活動資料 Table 建立索引

以活動資料主檔來說，這裡的 Id 被設定為 PK，也就是說它是適合被設定為叢集索引的；
CategoryId 活動類型 Id 則較常拿去做篩選（Where）或是排序（Order by），所以我會將它設定為非叢集索引。

本日結語

今天試著描述索引的原理以及使用方法，參考了許多文章，真的是很感謝這些樂於分享大佬們，希望自己也能一直走在作為分享者的路上。
描述上有任何錯誤或不清楚的話，再請各位指教了。

明天的內容會使用 Entity Framework Core 以 Code First 的方式來建立資料庫的 Table，相信一定有人等待實際開發的內容很久了吧XD，抱歉來晚了！

本日內容就到這了～我們明天再見！

Reference

鐵人賽： 30天與資料庫共舞
[Rails 效能優化] 資料庫索引 Database Indexing
Nic: 什麼是 B-tree (Balance tree)
Nic: 什麼是 B+ Tree
資料庫索引深入淺出(一)
GUID Primary Key資料庫避雷守則
黑暗執行緒：GUID Primary Key資料庫避雷守則
大型網站架構學習筆記