詳解資料庫中的索引和視圖

摘要：索引就是資料表中資料和相應的存盤位置的串列，利用索引可以提高在表或視圖中的查找資料的速度，

本文分享自華為云社區《資料庫開發指南（六）索引和視圖的使用技巧、方法與綜合應用》，作者： bluetata ，

一、索引

1.1 什么是索引

索引就是資料表中資料和相應的存盤位置的串列，利用索引可以提高在表或視圖中的查找資料的速度，它類似于書籍的索引，可以幫助快速定位和檢索資料，在資料庫中，索引是對一個或多個列的值進行排序和存盤的結構，它們包含指向實際資料位置的指標，

1.2 索引分類

資料庫中索引主要分為兩類：聚集索引和非聚集索引，SQL Server 還提供了唯一索引、索引視圖、全文索引、XML 索引等等，聚集索引和非聚集索引是資料庫引擎中索引的基本型別，是理解其他型別索引的基礎，

1.2.1 聚集索引

聚集索引是值表中資料行的物理存盤順序和索引的存盤順序完全相同，聚集索引根據索引順序物理地重新排列了用戶插入到表中的資料，因此，每個表只能創建一個聚集索引，聚集索引經常創建在表中經常被搜索到的列或按順序訪問的列上，在默認情況下，主鍵約束自動創建聚集索引，

1.2.2 非聚集索引

非聚集索引不改變表中資料列的物理存盤位置，資料與索引分開存盤，通過索引指向的地址與表中的資料發生關系，

非聚集索引沒有改變表中物理行的位置，索引可以在以下情況下使用非聚集索引：

• 如果某個欄位的資料唯一性比較高
• 如果查詢所得到的資料量比較少

1.2.3 聚集索引和非聚集索引的區別

這里用一個表格簡單的總結一下聚集索引和非聚集索引的區別：

1.2.4 其他型別索引

除了以上索引，還有以下型別索引：

• 唯一索引：如果希望索引鍵都不同，可以創建唯一索引，聚集索引和非聚集索引都可以是唯一索引，
• 包含新列索引：索引列的最大數量是16個，索引列的位元組總數的最高值是900，如果當多個列的位元組總數大于900，切又想在這些劣種都包含索引是，可以使用包含新列索引
• 視圖索引：提供視圖查詢效率，可以視圖的索引物理化，也就是說將結果集永久存盤在索引中，可以創建視圖索引，
• XML索引：是與xml資料關聯的索引形式，是XML二進制blob的已拆分持久表示形式
• 全文索引：一種特殊型別的基于標記的功能性功能，用于幫助在字串中搜索賦值的詞

1.3 創建索引

1.3.1 語法

create [unique] [clustered | noclustered]
index index_name
on table_name (column_name ...)
[with fillfactor=x]

引數解釋

unique 唯一索引
clustered 聚集索引
noclustered 非聚集索引
fillfactor 填充因子大小，范圍在 0-100 直接，表示索引頁填滿的空間所占的百分比，

1.3.2 創建索引的命名規則最佳實踐

在 MSSQL 中，索引的命名規則的最佳實踐可以有一些常見的準則，以提高可讀性和維護性，這個潛在的要求不僅試用于 SQL Server 資料庫，同樣在其他資料庫例如 MySQL、Oracle 中都同樣值得注意，

下面是個人總結的一些命名規則與建議：

1. 命名應該具有描述性：索引的名稱應該能夠清晰地表達其作用和關聯的列或表，使用有意義的名稱可以使其他開發人員更容易理解索引的用途，
2. 包含表名和列名：在索引名稱中包含相關表名和列名（長表名可適當縮寫，但要確保可以定位到表），可以使索引更具可讀性，并且可以避免在不同表之間使用相同名稱的索引時的沖突，
3. 使用統一的命名約定：為了提高一致性，可以定義一套命名約定，并在整個資料庫中使用，例如，可以使用特定的前綴或后綴來標識索引的型別（如 idx_ 表示非聚集索引），
4. 避免過長的名稱：索引名稱不應該過長，以免在使用索引時引起不便，盡量使用簡潔但描述性的名稱，
5. 避免使用保留關鍵字和特殊字符：確保索引名稱不與 MSSQL 的保留關鍵字或特殊字符沖突，以避免語法錯誤，

1.3.3 創建索引示例

-- 普通索引
if (exists (select * from sys.indexes where name = 'idx_stu_name'))
 drop index student.idx_stu_name
go
create index idx_stu_name
on
student(name);
-- 聯合索引
if (exists (select * from sys.indexes where name = 'idx_uqe_clu_stu_name_age'))
 drop index student.idx_uqe_clu_stu_name_age
go
create unique clustered index idx_uqe_clu_stu_name_age
on student(name, age);
if (exists (select * from sys.indexes where name = 'idx_cid'))
 drop index student.idx_cid
go
if (exists (select * from sys.indexes where name = 'idx_cid'))
 drop index student.idx_cid
go
-- 非聚集索引
create nonclustered index idx_cid
on
student (cid)
with fillFactor = 30; --填充因子
-- 聚集索引
if (exists (select * from sys.indexes where name = 'idx_sex'))
 drop index student.idx_sex
go
create clustered index idx_sex
on
student(sex);
-- 聚集索引
if (exists (select * from sys.indexes where name = 'idx_name'))
 drop index student.idx_name
go
create unique index idx_name
on
student(name);

1.4 適合的創建索引的列

一般情況，可以選擇那些對查詢性能有積極影響的列進行索引創建，下面進行一定的總結：

列的選擇性：選擇性是指列中不同值的數量與總行數的比例，如果某列具有較高的選擇性，即不同的值較多，那么為該列創建索引可能會有更好的效果，例如，在表示性別的列上創建索引可能沒有太大的幫助，因為只有兩個可能的值，

查詢頻率：觀察經常用于查詢條件的列，如果某個列經常用于搜索、過濾或連接操作，那么為該列創建索引可以提高查詢性能，

資料表的大小：對于大型表，創建索引的影響可能更加顯著，較小的表可能不需要太多的索引，因為全表掃描的開銷相對較小，

資料更新頻率：索引的創建和維護也會增加對資料的寫操作的開銷，如果某個列的資料經常發生變化，那么創建索引可能會帶來一定的性能開銷，

查詢性能優化需求：通過分析查詢執行計劃，可以確定是否存在潛在的性能瓶頸，并考慮為相關的列創建索引以改善查詢性能，

請注意過多的索引也可能會帶來維護開銷和存盤成本，因此需要在權衡索引數量和性能提升之間找到平衡點，定期監控和評估索引的使用情況也是重要的，以確保索引仍然對資料庫性能產生積極影響，

1.5 不適合創建索引的列

雖然在某些情況下創建索引可以提高查詢性能，但并不是所有列都適合創建索引，以下是一些不適合創建索引的列的情況：

低選擇性列：如果某個列的選擇性很低，即該列的不同值較少，創建索引可能不會帶來明顯的性能提升，例如，對于性別這樣只有幾個可能值的列，創建索引可能不會有太大意義，

經常更新的列：如果某個列的值經常被修改，那么為該列創建索引可能會帶來額外的維護成本和性能開銷，每次更新操作都需要更新索引，這可能會影響寫入性能，在這種情況下，需要仔細評估是否真的需要為該列創建索引，

過于頻繁的查詢列：某些列可能經常被查詢，但它們的選擇性較低，即不同的值較少，在這種情況下，盡管查詢頻率高，但為該列創建索引可能不會帶來明顯的性能提升，因為索引的使用效果有限，

大文本或大二進制列：對于存盤大文本或大二進制資料的列，如長文本欄位或影像欄位，創建索引的效果通常較差，這是因為索引本身需要占用額外的存盤空間，并且對于大型資料的索引操作可能變得非常耗時，

不常用的列：對于很少用于查詢的列，創建索引可能沒有太大意義，如果一個列很少用于查詢條件或連接操作，那么為其創建索引可能只會增加額外的開銷而不帶來實際的性能提升，

需要注意的是，以上列舉的情況只是一般性的指導原則，具體是否適合創建索引還取決于具體的資料庫結構、查詢模式和性能需求，在設計和創建索引時，應根據具體情況進行評估，并進行性能測驗和優化以確保索引的有效性，

二、視圖

2.1 什么是視圖

視圖就是一個虛擬的資料表，該資料表中的資料記錄是由一條查詢陳述句的查詢結果得到的，

2.2 為什么要使用視圖，而不是表（面試可能會被問到）

如果你在面試的時候被問到這個問題，建議從下面這個流程來回答一下面試官，

首先介紹一下表的作用（比如表是直接存盤結構化資料，可以擴展增刪改之類的），之后在介紹一下視圖是什么，之后從兩個切入點來講解視圖的好處以及必要性，這兩個切入點是：復用性和安全性，這里來簡單總結一下：

1. 簡化查詢，提高復用性
   想象一下，一個人員寬表，里面有幾百個欄位，但是你每次只需要用到這個表中的姓名、性別、年齡這三個欄位，那么你可以創建一個視圖來直接使用，或者你這個人員表經常和另外一個履歷表 join 組合在一起，而只取了其中的部分欄位，并且頻繁使用這幾個欄位，那么無疑創建視圖是一個好做法，當然這種情況也可以說明使用視圖能夠簡化查詢，
2. 提高安全性

• 通過視圖，可以限制用戶對敏感資料的直接訪問，視圖可以控制用戶可以看到和操作的資料的范圍，提供更好的安全性和隱私保護，這里還拿剛才我講的姓名、性別、年齡三個欄位，假如年齡是一個比較敏感的欄位，那么對某些資料庫用戶只能查詢姓名和性別的話，那么就可以設定一個視圖分配給這個用戶，
• 另外就是如果你要更新視圖的時候，也只能更新視圖所見的欄位，用戶對視圖不可以隨意的更改和洗掉，可以一定程度的保證資料的安全性，

講解完上述的兩個大的關鍵點后，也可以適當自行發揮，比如視圖你可以調整表欄位的顯示順序，或者欄位名字等等，這些也是優點，可以適當進行講解，

2.3 創建視圖

創建視圖的時候，對命名視圖大家一般也有默認的規則，一般情況可以使用 v_ 或 view_ + 表名（表縮寫）的形式，

例如：v_student

--創建視圖
if (exists (select * from sys.objects where name = 'v_student'))
 drop view v_student
go
create view v_student
as
select id, name, age, sex from student;

2.4 創建視圖準則

創建視圖需要考慮一下準則：

1. 視圖名稱必須遵循識別符號的規則，該名稱不得與該架構的任何表的名稱相同，
2. 你可以對其他視圖創建視圖，允許嵌套視圖，但嵌套不得超過32層，視圖最多可以有1024個欄位，
3. 不能將規則和 default 定義于視圖相關聯，
4. 視圖的查詢不能包含 compute 子句、compute by 子句或 into 關鍵字，
5. 定義視圖的查詢不能包含 order by 子句，除非在 select 陳述句的選擇串列中還有 top 子句，

下列情況必須指定視圖中每列的名稱：

• 有列順序需求（在某些情況下，您可能希望定義視圖的結果集中列的順序，并且這與基礎表中的順序不同，）
• 視圖中的任何列都是從算術運算式、內置函式或常量派生而來
• 視圖中有兩列或多列具有相同名稱（通常由于視圖定義包含聯接，因此來自兩個或多個不同的列具有相同的名稱）
• 有指定列別名的需求，注意無論是否重命名，視圖列都需繼承原列的資料型別

2.5 修改視圖

修改視圖和修改表有點類似，可以直接使用 alter 關鍵字進行修改，示例如下：

alter view v_student
as
select id, name, sex from student;
alter view v_student(編號, 名稱, 性別)
as
 select id, name, sex from student
go
select * from v_student;
select * from information_schema.views;

2.6 加密視圖

如果你對某一個視圖有保護查詢邏輯、防止修改或者查詢加密的需求的時候，可以使用加密視圖操作，

在 SQL Server 中 使用with encryption后，可以在創建視圖時對其定義的 SQL 查詢進行加密，也就是說 MSSQL 會對該視圖的定義中的查詢陳述句進行加密，這意味著其他人無法直接查看或分析該視圖的查詢邏輯，壓根就看不到這個視圖內部結構了，

-- 加密視圖
if (exists (select * from sys.objects where name = 'v_student_info'))
 drop view v_student_info
go
create view v_student_info
with encryption --加密
as
 select id, name, age from student
go
--view_definition is null
select * from information_schema.views 
where table_name like 'v_student';

如何解密被加密的視圖，或者修改已經被加密的視圖：

一般情況一個視圖被加密后，你需要修改它，那么大致有3個方法：

1. 重新創建視圖（先洗掉已加密的視圖，然后使用新的查詢邏輯重新創建視圖，），
2. 創建新視圖（創建一個新的，視圖名稱不同，之后呼叫這個新的），
3. 暴力解密之后修改（一般需要借助第三方工具或輔助，該方式個人不推薦）

2.7 視圖能否被更新 update （面試可能會被問到）

視圖可以被更新嗎？什么情況下可以被更新？ 

如果面試官問了這兩個問題，那么他還算友好的提醒了你，如果直接問了一句話“視圖可以被更新嗎？”，那么我感覺有被挖坑的嫌疑，

視圖可以被更新，但不是所有的情況都可以，

視圖更新必須遵循以下規則：

1. 當視圖的欄位是通過欄位運算式（Field Expression）或常數（Constant）計算得出的結果時，對該視圖執行 INSERT 和 UPDATE 操作是不允許的，但可以執行 DELETE 操作，
2. 若視圖的欄位是來自庫函式，則此視圖不允許更新；
3. 若視圖的定義中有 GROUP BY 子句或聚集函式時，則此視圖不允許更新；
4. 若視圖的定義中有 DISTINCT 任選項，則此視圖不允許更新；
5. 若視圖的定義中有嵌套查詢，并且嵌套查詢的 FROM 子句中涉及的表也是匯出該視圖的基表，則此視圖不允許更新；
6. 若視圖是由兩個以上的基表匯出的，此視圖不允許更新（源表單一才可以被更新）；
7. 一個不允許更新的視圖上定義的視圖也不允許更新；
8. 由一個基表定義的視圖，只含有基表的主鍵或候補鍵，并且視圖中沒有用運算式或函式定義的屬性，才允許更新，

點擊關注，第一時間了解華為云新鮮技術~

標籤：其他

上一篇：MongoDB（一）

下一篇：返回列表