本系列博文還在更新中，收錄在專欄：#MS-SQL Server 專欄中。

本系列文章列表如下：

【SQL Server】 Linux 運(yùn)維下對 SQL Server 進(jìn)行安裝、升級、回滾、卸載操作
【SQL Server】數(shù)據(jù)庫開發(fā)指南（一）數(shù)據(jù)庫設(shè)計(jì)的核心概念和基本步驟
【SQL Server】數(shù)據(jù)庫開發(fā)指南（二）MSSQL數(shù)據(jù)庫開發(fā)對于庫、表、數(shù)據(jù)類型、約束等相關(guān)操作
【SQL Server】數(shù)據(jù)庫開發(fā)指南（三）面向數(shù)據(jù)分析的 T-SQL 編程技巧與實(shí)踐
[ 云原生 | Docker ] 構(gòu)建高可用性的 SQL Server：Docker 容器下的主從同步實(shí)現(xiàn)指南
【SQL Server】數(shù)據(jù)庫開發(fā)指南（五）T-SQL 高級查詢綜合應(yīng)用與實(shí)戰(zhàn)
【SQL Server】數(shù)據(jù)庫開發(fā)指南（六）索引和視圖的使用技巧、方法與綜合應(yīng)用
【SQL Server】數(shù)據(jù)庫開發(fā)指南（七）MS-SQL存儲過程全面解析：種類、優(yōu)點(diǎn)和創(chuàng)建方法詳解
【SQL Server】數(shù)據(jù)庫開發(fā)指南（八）高級數(shù)據(jù)處理技術(shù) MS-SQL 事務(wù)、異常和游標(biāo)的深入研究
【SQL Server】數(shù)據(jù)庫開發(fā)指南（九）詳細(xì)講解 MS-SQL 觸發(fā)器的基礎(chǔ)概念與應(yīng)用場景

一、索引

1.1 什么是索引

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

1.2 索引分類

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

1.2.1 聚集索引

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

1.2.2 非聚集索引

非聚集索引不改變表中數(shù)據(jù)列的物理存儲位置，數(shù)據(jù)與索引分開存儲，通過索引指向的地址與表中的數(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é)果集永久存儲在索引中，可以創(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_name
on 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ī)則與建議：

1. 命名應(yīng)該具有描述性：索引的名稱應(yīng)該能夠清晰地表達(dá)其作用和關(guān)聯(lián)的列或表。使用有意義的名稱可以使其他開發(fā)人員更容易理解索引的用途。

2. 包含表名和列名：在索引名稱中包含相關(guān)表名和列名（長表名可適當(dāng)縮寫，但要確?？梢远ㄎ坏奖恚?，可以使索引更具可讀性，并且可以避免在不同表之間使用相同名稱的索引時(shí)的沖突。

3. 使用統(tǒng)一的命名約定：為了提高一致性，可以定義一套命名約定，并在整個(gè)數(shù)據(jù)庫中使用。例如，可以使用特定的前綴或后綴來標(biāo)識索引的類型（如 idx_ 表示非聚集索引）。

4. 避免過長的名稱：索引名稱不應(yīng)該過長，以免在使用索引時(shí)引起不便。盡量使用簡潔但描述性的名稱。

5. 避免使用保留關(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_name
go
create index idx_stu_name
on
student(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_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 適合的創(chuàng)建索引的列

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

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

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

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

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

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

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

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

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

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

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

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

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

不常用的列：對于很少用于查詢的列，創(chuàng)建索引可能沒有太大意義。如果一個(gè)列很少用于查詢條件或連接操作，那么為其創(chuàng)建索引可能只會增加額外的開銷而不帶來實(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 為什么要使用視圖，而不是表（面試可能會被問到）

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

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

1. 簡化查詢，提高復(fù)用性

   想象一下，一個(gè)人員寬表，里面有幾百個(gè)字段，但是你每次只需要用到這個(gè)表中的姓名、性別、年齡這三個(gè)字段，那么你可以創(chuàng)建一個(gè)視圖來直接使用，或者你這個(gè)人員表經(jīng)常和另外一個(gè)履歷表 join 組合在一起，而只取了其中的部分字段，并且頻繁使用這幾個(gè)字段。那么無疑創(chuàng)建視圖是一個(gè)好做法。當(dāng)然這種情況也可以說明使用視圖能夠簡化查詢。

2. 提高安全性

   • 通過視圖，可以限制用戶對敏感數(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_student
go
create view v_student
as
select id, name, age, sex from student;

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

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

1. 視圖名稱必須遵循標(biāo)識符的規(guī)則，該名稱不得與該架構(gòu)的任何表的名稱相同。
2. 你可以對其他視圖創(chuàng)建視圖。允許嵌套視圖，但嵌套不得超過32層。視圖最多可以有1024個(gè)字段。
3. 不能將規(guī)則和 default 定義于視圖相關(guān)聯(lián)。
4. 視圖的查詢不能包含 compute 子句、compute by 子句或 into 關(guān)鍵字。
5. 定義視圖的查詢不能包含 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_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 加密視圖

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

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

-- 加密視圖
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';

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

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

1. 重新創(chuàng)建視圖（先刪除已加密的視圖，然后使用新的查詢邏輯重新創(chuàng)建視圖。）。
2. 創(chuàng)建新視圖（創(chuàng)建一個(gè)新的，視圖名稱不同，之后調(diào)用這個(gè)新的）。
3. 暴力解密之后修改（一般需要借助第三方工具或輔助，該方式個(gè)人不推薦）

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

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

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

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

視圖更新必須遵循以下規(guī)則：文章來源地址http://www.zghlxwxcb.cn/news/detail-461019.html

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

[ 本文作者 ]   bluetata
[ 原文鏈接 ]   https://blog.csdn.net/dietime1943/article/details/130899849
[ 最后更新 ]   05/27/2023 12:31
[ 版權(quán)聲明 ]   如果您在非 CSDN 網(wǎng)站內(nèi)看到這一行，
說明網(wǎng)絡(luò)爬蟲可能在本人還沒有完整發(fā)布的時(shí)候就抓走了我的文章，
可能導(dǎo)致內(nèi)容不完整，請去上述的原文鏈接查看原文。

到了這里，關(guān)于【SQL Server】數(shù)據(jù)庫開發(fā)指南（六）索引和視圖的使用技巧、方法與綜合應(yīng)用的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！