過去幾年的 Data Engineer 職涯雖然主要在雲端服務上,但還是會碰到 reverseETL 議題以及操作 SQL, 這時會碰到效率問題,所以 index 是必需的知識,但是有幾種 index,與 pk, fk 的關係…還是一知半解,透過這篇整理索引相關知識
目的: SQL索引是資料庫的關鍵元素之一,主要目的為加速查詢速度
組成方式:索引通常包含一個或多個欄位,一張 table 有索引時,資料就會依照索引的鍵值排序及儲存,以便更迅速地檢索,索引通常使用 B-Tree 結構構建(B-Tree 介紹),若沒有索引的就是堆積(Heap)
類型:有兩種主要類型:叢集索引(clustered-index)和非叢集索引(non-clustered-index)
叢集索引通常關聯於主鍵,實現物理排序;非叢集索引則用於提高搜索和連接操作的效能。正確的索引設計對於資料庫性能至關重要,可以顯著提高查詢效率
叢集索引 | 非叢集索 | |
---|---|---|
定義 | 叢集索引將資料表或檢視中的資料列依其索引鍵值實體排序與儲存 | 非叢集索引不會排序資料,他會多一存放索引指標(pointer),指向包含索引指標的資料列 |
每張 table 上限數 | 1個 | 多個 |
多欄位索引 | V | V |
查詢速度 | 較快 | 較慢 |
更新索引 | 只要更新資料,就需要依據索引重新排列資料 | 不影響資料 |
適合作為叢集索引的欄位的特性 | - 常拿來被查詢 |
PRIMARY KEY 條件約束
若不指定,設定PRIMARY KEY(PK)時,資料庫引擎會自動建立叢集索引,且也建立唯一索引*,
若已存在叢集索引 ,則 SQL Server 會使用非叢集索引強制執行主索引鍵
*個人心得:有興趣再研究唯一索引,基本上只要設定 PK 或唯一條件約束,SQL Server 就會幫你建立相關索引
唯一條件約束(unique conatrain)
建立具有 UNIQUE 條件約束的資料表時,SQL Server 會自動建立非叢集索引
INSERT
、UPDATE
、DELETE
和 MERGE
…等執行的效能,因為只要資料表中的資料一變更,所有的索引也都必須隨之適當調整相關寫法網路都查得到就不多介紹囉
• 建立叢集索引
• 建立非叢集索引
參考資料:
SQL Server 及 Azure SQL 索引架構與設計指南
[MS SQL 筆記] Clustered 和 NonClustered Index 的差異