摘要：索引就是數(shù)據(jù)表中數(shù)據(jù)和相應(yīng)的存儲(chǔ)位置的列表，利用索引可以提高在表或視圖中的查找數(shù)據(jù)的速度。

本文分享自華為云社區(qū)《數(shù)據(jù)庫開發(fā)指南（六）索引和視圖的使用技巧、方法與綜合應(yīng)用》，作者： bluetata 。

一、索引1.1 什么是索引

索引就是數(shù)據(jù)表中數(shù)據(jù)和相應(yīng)的存儲(chǔ)位置的列表，利用索引可以提高在表或視圖中的查找數(shù)據(jù)的速度。它類似于書籍的索引，可以幫助快速定位和檢索數(shù)據(jù)。在數(shù)據(jù)庫中，索引是對一個(gè)或多個(gè)列的值進(jìn)行排序和存儲(chǔ)的結(jié)構(gòu)，它們包含指向?qū)嶋H數(shù)據(jù)位置的指針。

1.2 索引分類

數(shù)據(jù)庫中索引主要分為兩類：聚集索引和非聚集索引。SQL Server 還提供了唯一索引、索引視圖、全文索引、XML 索引等等。聚集索引和非聚集索引是數(shù)據(jù)庫引擎中索引的基本類型，是理解其他類型索引的基礎(chǔ)。

(資料圖片)

1.2.1 聚集索引

聚集索引是值表中數(shù)據(jù)行的物理存儲(chǔ)順序和索引的存儲(chǔ)順序完全相同。聚集索引根據(jù)索引順序物理地重新排列了用戶插入到表中的數(shù)據(jù)，因此，每個(gè)表只能創(chuàng)建一個(gè)聚集索引。聚集索引經(jīng)常創(chuàng)建在表中經(jīng)常被搜索到的列或按順序訪問的列上。在默認(rèn)情況下，主鍵約束自動(dòng)創(chuàng)建聚集索引。

1.2.2 非聚集索引

非聚集索引不改變表中數(shù)據(jù)列的物理存儲(chǔ)位置，數(shù)據(jù)與索引分開存儲(chǔ)，通過索引指向的地址與表中的數(shù)據(jù)發(fā)生關(guān)系。

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

如果某個(gè)字段的數(shù)據(jù)唯一性比較高如果查詢所得到的數(shù)據(jù)量比較少1.2.3 聚集索引和非聚集索引的區(qū)別

這里用一個(gè)表格簡單的總結(jié)一下聚集索引和非聚集索引的區(qū)別：

1.2.4 其他類型索引

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

唯一索引：如果希望索引鍵都不同，可以創(chuàng)建唯一索引。聚集索引和非聚集索引都可以是唯一索引。包含新列索引：索引列的最大數(shù)量是16個(gè)，索引列的字節(jié)總數(shù)的最高值是900。如果當(dāng)多個(gè)列的字節(jié)總數(shù)大于900，切又想在這些劣種都包含索引是，可以使用包含新列索引視圖索引：提供視圖查詢效率，可以視圖的索引物理化，也就是說將結(jié)果集永久存儲(chǔ)在索引中，可以創(chuàng)建視圖索引。XML索引：是與xml數(shù)據(jù)關(guān)聯(lián)的索引形式，是XML二進(jìn)制blob的已拆分持久表示形式全文索引：一種特殊類型的基于標(biāo)記的功能性功能，用于幫助在字符串中搜索賦值的詞1.3 創(chuàng)建索引1.3.1 語法

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

參數(shù)解釋

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

1.3.2 創(chuàng)建索引的命名規(guī)則最佳實(shí)踐

在 MSSQL 中，索引的命名規(guī)則的最佳實(shí)踐可以有一些常見的準(zhǔn)則，以提高可讀性和維護(hù)性。這個(gè)潛在的要求不僅試用于 SQL Server 數(shù)據(jù)庫，同樣在其他數(shù)據(jù)庫例如 MySQL、Oracle 中都同樣值得注意。

下面是個(gè)人總結(jié)的一些命名規(guī)則與建議：

命名應(yīng)該具有描述性：索引的名稱應(yīng)該能夠清晰地表達(dá)其作用和關(guān)聯(lián)的列或表。使用有意義的名稱可以使其他開發(fā)人員更容易理解索引的用途。包含表名和列名：在索引名稱中包含相關(guān)表名和列名（長表名可適當(dāng)縮寫，但要確?？梢远ㄎ坏奖恚?，可以使索引更具可讀性，并且可以避免在不同表之間使用相同名稱的索引時(shí)的沖突。使用統(tǒng)一的命名約定：為了提高一致性，可以定義一套命名約定，并在整個(gè)數(shù)據(jù)庫中使用。例如，可以使用特定的前綴或后綴來標(biāo)識(shí)索引的類型（如 idx_ 表示非聚集索引）。避免過長的名稱：索引名稱不應(yīng)該過長，以免在使用索引時(shí)引起不便。盡量使用簡潔但描述性的名稱。避免使用保留關(guān)鍵字和特殊字符：確保索引名稱不與 MSSQL 的保留關(guān)鍵字或特殊字符沖突，以避免語法錯(cuò)誤。1.3.3 創(chuàng)建索引示例

-- 普通索引if (exists (select * from sys.indexes where name = "idx_stu_name")) drop index student.idx_stu_namegocreate index idx_stu_nameonstudent(name);-- 聯(lián)合索引if (exists (select * from sys.indexes where name = "idx_uqe_clu_stu_name_age")) drop index student.idx_uqe_clu_stu_name_agegocreate unique clustered index idx_uqe_clu_stu_name_ageon student(name, age);if (exists (select * from sys.indexes where name = "idx_cid")) drop index student.idx_cidgoif (exists (select * from sys.indexes where name = "idx_cid")) drop index student.idx_cidgo-- 非聚集索引create nonclustered index idx_cidonstudent (cid)with fillFactor = 30; --填充因子-- 聚集索引if (exists (select * from sys.indexes where name = "idx_sex")) drop index student.idx_sexgocreate clustered index idx_sexonstudent(sex);-- 聚集索引if (exists (select * from sys.indexes where name = "idx_name")) drop index student.idx_namegocreate unique index idx_nameonstudent(name);

1.4 適合的創(chuàng)建索引的列

一般情況，可以選擇那些對查詢性能有積極影響的列進(jìn)行索引創(chuàng)建，下面進(jìn)行一定的總結(jié)：

列的選擇性：選擇性是指列中不同值的數(shù)量與總行數(shù)的比例。如果某列具有較高的選擇性，即不同的值較多，那么為該列創(chuàng)建索引可能會(huì)有更好的效果。例如，在表示性別的列上創(chuàng)建索引可能沒有太大的幫助，因?yàn)橹挥袃蓚€(gè)可能的值。

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

數(shù)據(jù)表的大小：對于大型表，創(chuàng)建索引的影響可能更加顯著。較小的表可能不需要太多的索引，因?yàn)槿頀呙璧拈_銷相對較小。

數(shù)據(jù)更新頻率：索引的創(chuàng)建和維護(hù)也會(huì)增加對數(shù)據(jù)的寫操作的開銷。如果某個(gè)列的數(shù)據(jù)經(jīng)常發(fā)生變化，那么創(chuàng)建索引可能會(huì)帶來一定的性能開銷。

查詢性能優(yōu)化需求：通過分析查詢執(zhí)行計(jì)劃，可以確定是否存在潛在的性能瓶頸，并考慮為相關(guān)的列創(chuàng)建索引以改善查詢性能。

請注意過多的索引也可能會(huì)帶來維護(hù)開銷和存儲(chǔ)成本，因此需要在權(quán)衡索引數(shù)量和性能提升之間找到平衡點(diǎn)。定期監(jiān)控和評估索引的使用情況也是重要的，以確保索引仍然對數(shù)據(jù)庫性能產(chǎn)生積極影響。

1.5 不適合創(chuàng)建索引的列

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

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

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

過于頻繁的查詢列：某些列可能經(jīng)常被查詢，但它們的選擇性較低，即不同的值較少。在這種情況下，盡管查詢頻率高，但為該列創(chuàng)建索引可能不會(huì)帶來明顯的性能提升，因?yàn)樗饕氖褂眯Ч邢蕖?/p>

大文本或大二進(jìn)制列：對于存儲(chǔ)大文本或大二進(jìn)制數(shù)據(jù)的列，如長文本字段或圖像字段，創(chuàng)建索引的效果通常較差。這是因?yàn)樗饕旧硇枰加妙~外的存儲(chǔ)空間，并且對于大型數(shù)據(jù)的索引操作可能變得非常耗時(shí)。

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

需要注意的是，以上列舉的情況只是一般性的指導(dǎo)原則，具體是否適合創(chuàng)建索引還取決于具體的數(shù)據(jù)庫結(jié)構(gòu)、查詢模式和性能需求。在設(shè)計(jì)和創(chuàng)建索引時(shí)，應(yīng)根據(jù)具體情況進(jìn)行評估，并進(jìn)行性能測試和優(yōu)化以確保索引的有效性。

二、視圖2.1 什么是視圖

視圖就是一個(gè)虛擬的數(shù)據(jù)表，該數(shù)據(jù)表中的數(shù)據(jù)記錄是由一條查詢語句的查詢結(jié)果得到的。

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

如果你在面試的時(shí)候被問到這個(gè)問題，建議從下面這個(gè)流程來回答一下面試官。

首先介紹一下表的作用（比如表是直接存儲(chǔ)結(jié)構(gòu)化數(shù)據(jù)，可以擴(kuò)展增刪改之類的），之后在介紹一下視圖是什么，之后從兩個(gè)切入點(diǎn)來講解視圖的好處以及必要性，這兩個(gè)切入點(diǎn)是：復(fù)用性和安全性，這里來簡單總結(jié)一下：

簡化查詢，提高復(fù)用性想象一下，一個(gè)人員寬表，里面有幾百個(gè)字段，但是你每次只需要用到這個(gè)表中的姓名、性別、年齡這三個(gè)字段，那么你可以創(chuàng)建一個(gè)視圖來直接使用，或者你這個(gè)人員表經(jīng)常和另外一個(gè)履歷表 join 組合在一起，而只取了其中的部分字段，并且頻繁使用這幾個(gè)字段。那么無疑創(chuàng)建視圖是一個(gè)好做法。當(dāng)然這種情況也可以說明使用視圖能夠簡化查詢。提高安全性通過視圖，可以限制用戶對敏感數(shù)據(jù)的直接訪問。視圖可以控制用戶可以看到和操作的數(shù)據(jù)的范圍，提供更好的安全性和隱私保護(hù)。這里還拿剛才我講的姓名、性別、年齡三個(gè)字段，假如年齡是一個(gè)比較敏感的字段，那么對某些數(shù)據(jù)庫用戶只能查詢姓名和性別的話，那么就可以設(shè)置一個(gè)視圖分配給這個(gè)用戶。另外就是如果你要更新視圖的時(shí)候，也只能更新視圖所見的字段，用戶對視圖不可以隨意的更改和刪除，可以一定程度的保證數(shù)據(jù)的安全性。

講解完上述的兩個(gè)大的關(guān)鍵點(diǎn)后，也可以適當(dāng)自行發(fā)揮，比如視圖你可以調(diào)整表字段的顯示順序，或者字段名字等等。這些也是優(yōu)點(diǎn)?？梢赃m當(dāng)進(jìn)行講解。

2.3 創(chuàng)建視圖

創(chuàng)建視圖的時(shí)候，對命名視圖大家一般也有默認(rèn)的規(guī)則，一般情況可以使用 v_ 或 view_ + 表名（表縮寫）的形式。

例如：v_student

--創(chuàng)建視圖if (exists (select * from sys.objects where name = "v_student")) drop view v_studentgocreate view v_studentasselect id, name, age, sex from student;

2.4 創(chuàng)建視圖準(zhǔn)則

創(chuàng)建視圖需要考慮一下準(zhǔn)則：

視圖名稱必須遵循標(biāo)識(shí)符的規(guī)則，該名稱不得與該架構(gòu)的任何表的名稱相同。你可以對其他視圖創(chuàng)建視圖。允許嵌套視圖，但嵌套不得超過32層。視圖最多可以有1024個(gè)字段。不能將規(guī)則和 default 定義于視圖相關(guān)聯(lián)。視圖的查詢不能包含 compute 子句、compute by 子句或 into 關(guān)鍵字。定義視圖的查詢不能包含 order by 子句，除非在 select 語句的選擇列表中還有 top 子句。

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

有列順序需求（在某些情況下，您可能希望定義視圖的結(jié)果集中列的順序，并且這與基礎(chǔ)表中的順序不同。）視圖中的任何列都是從算術(shù)表達(dá)式、內(nèi)置函數(shù)或常量派生而來視圖中有兩列或多列具有相同名稱（通常由于視圖定義包含聯(lián)接，因此來自兩個(gè)或多個(gè)不同的列具有相同的名稱）有指定列別名的需求。注意無論是否重命名，視圖列都需繼承原列的數(shù)據(jù)類型2.5 修改視圖

修改視圖和修改表有點(diǎn)類似，可以直接使用 alter 關(guān)鍵字進(jìn)行修改，示例如下：

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

2.6 加密視圖

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

在 SQL Server 中 使用with encryption后，可以在創(chuàng)建視圖時(shí)對其定義的 SQL 查詢進(jìn)行加密。也就是說 MSSQL 會(huì)對該視圖的定義中的查詢語句進(jìn)行加密。這意味著其他人無法直接查看或分析該視圖的查詢邏輯。壓根就看不到這個(gè)視圖內(nèi)部結(jié)構(gòu)了。

-- 加密視圖if (exists (select * from sys.objects where name = "v_student_info")) drop view v_student_infogocreate view v_student_infowith encryption --加密as select id, name, age from studentgo--view_definition is nullselect * from information_schema.views where table_name like "v_student";

如何解密被加密的視圖，或者修改已經(jīng)被加密的視圖：

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

重新創(chuàng)建視圖（先刪除已加密的視圖，然后使用新的查詢邏輯重新創(chuàng)建視圖。）。創(chuàng)建新視圖（創(chuàng)建一個(gè)新的，視圖名稱不同，之后調(diào)用這個(gè)新的）。暴力解密之后修改（一般需要借助第三方工具或輔助，該方式個(gè)人不推薦）2.7 視圖能否被更新 update （面試可能會(huì)被問到）

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

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

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

視圖更新必須遵循以下規(guī)則：

當(dāng)視圖的字段是通過字段表達(dá)式（Field Expression）或常數(shù)（Constant）計(jì)算得出的結(jié)果時(shí)，對該視圖執(zhí)行 INSERT 和 UPDATE 操作是不允許的，但可以執(zhí)行 DELETE 操作。若視圖的字段是來自庫函數(shù)，則此視圖不允許更新；若視圖的定義中有 GROUP BY 子句或聚集函數(shù)時(shí)，則此視圖不允許更新；若視圖的定義中有 DISTINCT 任選項(xiàng)，則此視圖不允許更新；若視圖的定義中有嵌套查詢，并且嵌套查詢的 FROM 子句中涉及的表也是導(dǎo)出該視圖的基表，則此視圖不允許更新；若視圖是由兩個(gè)以上的基表導(dǎo)出的，此視圖不允許更新（源表單一才可以被更新）；一個(gè)不允許更新的視圖上定義的視圖也不允許更新；由一個(gè)基表定義的視圖，只含有基表的主鍵或候補(bǔ)鍵，并且視圖中沒有用表達(dá)式或函數(shù)定義的屬性，才允許更新。

點(diǎn)擊關(guān)注，第一時(shí)間了解華為云新鮮技術(shù)~

關(guān)鍵詞：