iT邦幫忙

2023 iThome 鐵人賽

DAY 27
1

過去幾年的 Data Engineer 職涯雖然主要在雲端服務上,但還是會碰到 reverseETL 議題以及操作 SQL, 這時會碰到效率問題,所以 index 是必需的知識,但是有幾種 index,與 pk, fk 的關係…還是一知半解,透過這篇整理索引相關知識

索引(index)

  • 目的: SQL索引是資料庫的關鍵元素之一,主要目的為加速查詢速度

  • 組成方式:索引通常包含一個或多個欄位,一張 table 有索引時,資料就會依照索引的鍵值排序及儲存,以便更迅速地檢索,索引通常使用 B-Tree 結構構建(B-Tree 介紹),若沒有索引的就是堆積(Heap)

  • 類型:有兩種主要類型:叢集索引(clustered-index)和非叢集索引(non-clustered-index)

    叢集索引通常關聯於主鍵,實現物理排序;非叢集索引則用於提高搜索和連接操作的效能。正確的索引設計對於資料庫性能至關重要,可以顯著提高查詢效率

叢集索引 非叢集索
定義 叢集索引將資料表或檢視中的資料列依其索引鍵值實體排序與儲存 非叢集索引不會排序資料,他會多一存放索引指標(pointer),指向包含索引指標的資料列
每張 table 上限數 1個 多個
多欄位索引 V V
查詢速度 較快 較慢
更新索引 只要更新資料,就需要依據索引重新排列資料 不影響資料
適合作為叢集索引的欄位的特性 - 常拿來被查詢
  • 唯一性高(e.g. ID)
  • 不常更新
  • 範圍查詢(>,<,BETWEEN)
  • GROUP BY
  • 常被排序(ORDER BY) | 1.非範圍查詢
    2.常常被更新
    3.常拿來被查詢 |
    | 應用 | 1.主鍵(PK)
    2.唯一性約束(unique constrain) | 用於其他需要查詢的欄位 |

Table 的條件約束與索引的關係(SQL Server)

  • PRIMARY KEY 條件約束

    若不指定,設定PRIMARY KEY(PK)時,資料庫引擎會自動建立叢集索引,且也建立唯一索引*,

    若已存在叢集索引 ,則 SQL Server 會使用非叢集索引強制執行主索引鍵

    *個人心得:有興趣再研究唯一索引,基本上只要設定 PK 或唯一條件約束,SQL Server 就會幫你建立相關索引

  • 唯一條件約束(unique conatrain)

    建立具有 UNIQUE 條件約束的資料表時,SQL Server 會自動建立非叢集索引

其他注意事項

  • 資料表中的索引數量過多會影響到 INSERTUPDATEDELETE 和 MERGE …等執行的效能,因為只要資料表中的資料一變更,所有的索引也都必須隨之適當調整
  • 資料行上以 ORDER BY 或 GROUP BY 子句指定的索引可讓 Database Engine 無須排序資料,因為資料列已有排序。 這種方式可以提高查詢的執行效能
  • 如果資料表有叢集索引,或索引位於索引檢視中,則非叢集索引的指標(pointer)為資料列的叢集索引鍵

如何建立叢集及非叢集索引

相關寫法網路都查得到就不多介紹囉

建立叢集索引

建立非叢集索引

參考資料:

SQL Server 及 Azure SQL 索引架構與設計指南

叢集和非叢集索引

[MS SQL 筆記] SQL Index

[MS SQL 筆記] Clustered 和 NonClustered Index 的差異

建立索引(1)-叢集與非叢集索引


上一篇
Fundamental data engineering 讀書心得 - 資料工程的未來
下一篇
SQL Server 增加或更新 PK 的語法整理
系列文
如何借助 dbt 優化當代資料倉儲及資料工程師的水肥之路分享30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言